MySQL Support

[Silenus]

Hey all,

I'm not sure if this has been brought up before, searching for just MySQL gave me a lot of results so it's possible I totally missed it.
Is there ever been talked about adding MySQL support?

I'm running a 3-node MariaDB with Galera cluster at home for all my database related servers/vm's/etc, and to make Domoticz compatible with a MySQL backend would be really nice.

Let me know what you guys think.

[Egregius]

Depends on what you want to do. It's not that hard to push sensor data to mysql. I only do it for thermometers, the rest I don't care about.

[Silenus]

So, what I want to do is that everything that needs a database can use my redundant MySQL cluster.
When I ran domoticz on an OrangePi a few months ago, I had an sd card die and that caused all my data being gone (didn't have a backup at the time).

I built this MySQL cluster just so I always have a backup and I can reinstall any server/vm/orangepi without messing around with the data in a database which is stored locally on a host.

[mackowiakp]

Maybe try my solution. The "killesr" of SD card are two temporary Domoticz files. That is domoticz.db-shm and domoticz.db-wal. That files are created from beginning each Domoticz (SQLLite) start. So I created tmpfs and moved (linked) it to ramdisk. So most I/O disk operations is done in RAM. My RPi has 1 GB RAM and graphical subsystem is turn off. Even video chip is turn off. So I have a lot of free RAM. All other Domoticz files are placed on connected USB M4 type SSD disk. Next I moved all other system temporary files to ramdisk, like ntp drift file etc. Practically all system log files are stored in ramdisk (if not necessary because RPi has 48 h UPS) or to connected SSD if necessery after power failure. So SD card is practically read-only.
All Domoticz related files are on-line backup to QNAP NAS via NFS connected share from QNAP to RPi.

[karolp]

Depends on what you want to do. It's not that hard to push sensor data to mysql. I only do it for thermometers, the rest I don't care about.

Egregius - could you reveal your way to do that. I trying to do this but I'm newbie. I have domoticz and mysql server on the same raspberry pi. I'm trying to forward water counter values to mysql directly from domoticz, after they arrived. I thout to do this by python script. I know how to write a script which write to mysql databese, but how to get counter value?

There is a couple of threads on this subject but I can't find almost ready solution.

Maybe lua script, activated by event (counter update) would be easier, or more suitable, but I don't how.

[Egregius]

I do it with pass2php of course.
I call a php script from within a lua script. All the rest is handled in php.

[mackowiakp]

I think the most easy way to store data in database for log time and for historical analysis is directly use build-in in Domoticz, interface to InfluxDB. It is DB dedicated to collect measurement data. Adding Grafana it is possible to generate custom rapports as charts, create dashboards etc. It is SQL DB so it is very easy to integrate collected results with other applications. I use it and it works without problems. Implementation takes 1 hour.

[karolp]

Yes. I know and I have started InfluxDB and Grafana already and they work great. But when problems arrive and for example I get some spikes (bad measurements), idx of device will change, or other problem shows it will be easier for me to manage them in MySQL because it has phpMyAdmin which is a big advantage of MySQL (from my point of view).

Does InfluxDB have some GUI to manage it like phpMyAdmin for MySQL?

Domoticz, influxDB, grafana, php, lua, python, dzVents - everything is new for me and I do not get it yet, so such GUI like phpMyAdmin is very helpful for me.

I call a php script from within a lua script. All the rest is handled in php.

In this thread
https://www.domoticz.com/forum/viewtopic.php?t=16827
there is some example to move data (every 2 min) from domoticz to MySQL, but your solution to forward data, when they appear (by lua and php) seems to be more suitable.
Could you share your lua script and php, and how to implement this php?

[Egregius]

Search for pass2php on the forum or click the link in my signature.

[karolp]

Thanks a lot.
Pass2php is that I was looking for.

[mackowiakp]

There is GUI functionally similar tu phpMyAdmin dedicated to InfluxDB. Named Chronograf. I dont know what version of Linux You use, but I have installed "QNAP community repo" on my NAS so InfluxDB, Telegraf, Chronograf and Kapacitor are available directly from QNAP GUI admin console.
On Linux based PC, I simply added YUM repo /etc/yum.repos.d/influxdb.repo like this:

Code: Select all

[influxdb]
name = InfluxDB Repository - RHEL $releasever
baseurl = https://repos.influxdata.com/rhel/$releasever/$basearch/stable
enabled = 1
gpgcheck = 1
gpgkey = https://repos.influxdata.com/influxdb.key

So InfluxDB, Telegraf, Chronograf and Kapacitor are now available via YUM command. And potential updates too of course. There is similar DEB repo. But I use Mageia 6 (RHL fork).

Grafana is separate product but available via YUM or DEB repo too. In case of YUM just add repo /etc/yum.repos.d/grafana.repo

Code: Select all

[grafana]
name=grafana
baseurl=https://packages.grafana.com/oss/rpm
repo_gpgcheck=1
enabled=1
gpgcheck=1
gpgkey=https://packages.grafana.com/gpg.key
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt

You have to look at Telegraf, because this is interface for influx DB, collecting measurements coming to InfluxDB in different forms (simple SQL command, WEB API, etc, etc).

[Egregius]

Thanks a lot.
Pass2php is that I was looking for.

Just reply to https://www.domoticz.com/forum/viewtopi ... 64&t=12343 if you need any help.
Once you have the basics running the possibilities are endless.

[karolp]

For now the easiest way for me was forwarding data to mysql after they arrived to domoticz. I wrote lua script in domoticz. It is my first script and probably it could be beter, there is a lot of commented lines with print, which I used to debugging, but the most important thing is that is working. It forward data from sensor named "Woda licznik" (it is water counter) and temperature named "TempArdGtw" by running python script which save data in mysql database.

Lua code:

Code: Select all

commandArray = {}

year 	= tonumber(os.date("%Y"));
month 	= os.date("%m");
day 	= os.date("%d");
hour 	= os.date("%H");
min 	= os.date("%M");
sec     = os.date("%S");
weekday = tonumber(os.date("%w"));
time    = os.date("%X");
datetime = year.."-"..month.."-"..day.." "..time;
--print(datetime);

for deviceName,deviceValue in pairs(devicechanged) do
    if (deviceName=='Woda licznik') then
        --print ("deviceName: "..deviceName);
        --print ("deviceValue: "..deviceValue);
        --print ("deviceName: "..deviceName..", deviceValue: "..deviceValue);
        print (deviceName..": "..deviceValue);
        local command = "python3 /home/pi/domoticz-scripts/ToMySQL.py Counter ".."'"..deviceName.."'".." "..deviceValue.." ".."'"..datetime.."'";
        --print(command)
        os.execute(command);
    elseif (deviceName=='TempArdGtw') then
        --print(type(deviceValue));
        --print(type(uservariables["ArdGtwTemp"]));
        if ( tostring(uservariables["ArdGtwTemp"]) ~= tostring(deviceValue) ) then
            --print(uservariables["ArdGtwTemp"]);
            --print(deviceValue);
            commandArray['Variable:ArdGtwTemp'] = deviceValue;
            --print("Temperatura zmieniła się");
            --print ("deviceName: "..deviceName..", deviceValue: "..deviceValue);
            print (deviceName..": "..deviceValue);
            local command = "python3 /home/pi/domoticz-scripts/ToMySQL.py Temperatury ".."'"..deviceName.."'".." "..deviceValue.." ".."'"..datetime.."'";
            print(command)
            os.execute(command);
        else
            --print("Temperature the same");
        end    
    end
end

return commandArray

Python code:

Code: Select all

import pymysql.cursors
import sys
# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='somepassword',
                             db='mydb',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

print('Number of arguments:', len(sys.argv), 'arguments.')
print('Argument List:', str(sys.argv))

#Name = "WodaLicznik"
#value = 170008

try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `" + sys.argv[1] + "` (`Name`, `Value`, `Datetime`) VALUES (%s, %s, %s)"
        print(sql)
        #cursor.execute(sql, (Name, value))
        val = sys.argv[2] + str(sys.argv[3]) + str(sys.argv[4])
        cursor.execute(sql, (sys.argv[2], sys.argv[3], sys.argv[4]))

    connection.commit()

finally:
    connection.close()

New temperature measurement is forwarded to mysql only if it is different from previous measurement.

It requires to prepare previous database and tables in MySQL. Except columns `Name`, `Value`, `Datetime` there is also column timestamp whith default value current_timestamp.

It works for me.
Data are shown in grafana in the same way like from InfluxDB.

[erem]

i once wrote a c program to selectively copy data from the domoticz database to mySQL.
i attached the program, it may be of some use to others.

[mackowiakp]

I moved to InfluxDB. I think it is better solution because Domoticz is equipped in ready to use interface to InfluxDB and DB is dedicated to collect measurements. Additionally there is Grafana, that is user defined dashboard available via WEB. The only problem is absence of ready mobile client similar to Grafama.

[gizmocuz]

Yes, it's better to use one of the build in push methods.
If influxDB is not what you want (i think it's great!), you could use the HTTP Pusher,
write a small nodeJS application that receives it, and store it in any database you want

[ayasystems]

domoticz should have the option to use mariadb instead of sql lite

[kiddigital]

domoticz should have the option to use mariadb instead of sql lite

Why? What would be the added value of that? What thing can one do that can’t be done now?

[sjb66]

SQL Lite is good for normal Domoticz operation but the ability to "Push Data" to MYSQL would allow you have custom sample intervals and retention periods for specific inputs.

[waltervl]

SQL Lite is good for normal Domoticz operation but the ability to "Push Data" to MYSQL would allow you have custom sample intervals and retention periods for specific inputs.

For that you can use the Domoticz push to influxDB function https://www.domoticz.com/wiki/Influxdb