MySQL Support

Use this forum to discuss possible implementation of a new feature before opening a ticket.
A developer shall edit the topic title with "[xxx]" where xxx is the id of the accompanying tracker id.
Duplicate posts about the same id. +1 posts are not allowed.

Moderators: leecollings, remb0

Silenus
Posts: 7
Joined: Sunday 13 November 2016 13:41
Target OS: Linux
Domoticz version: 4.9701
Location: Netherlands
Contact:

MySQL Support

Post by 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.
User avatar
Egregius
Posts: 2582
Joined: Thursday 09 April 2015 12:19
Target OS: Linux
Domoticz version: v2024.7
Location: Beitem, BE
Contact:

Re: MySQL Support

Post by 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
Posts: 7
Joined: Sunday 13 November 2016 13:41
Target OS: Linux
Domoticz version: 4.9701
Location: Netherlands
Contact:

Re: MySQL Support

Post by 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
Posts: 18
Joined: Friday 21 July 2017 19:26
Target OS: Linux
Domoticz version:
Contact:

Re: MySQL Support

Post by 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
Posts: 4
Joined: Tuesday 29 January 2019 13:04
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

Re: MySQL Support

Post by karolp »

Egregius wrote: Thursday 05 July 2018 22:12 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.
User avatar
Egregius
Posts: 2582
Joined: Thursday 09 April 2015 12:19
Target OS: Linux
Domoticz version: v2024.7
Location: Beitem, BE
Contact:

Re: MySQL Support

Post by 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
Posts: 18
Joined: Friday 21 July 2017 19:26
Target OS: Linux
Domoticz version:
Contact:

Re: MySQL Support

Post by 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
Posts: 4
Joined: Tuesday 29 January 2019 13:04
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

Re: MySQL Support

Post by 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.
Egregius wrote: Tuesday 29 January 2019 13:44 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?
User avatar
Egregius
Posts: 2582
Joined: Thursday 09 April 2015 12:19
Target OS: Linux
Domoticz version: v2024.7
Location: Beitem, BE
Contact:

Re: MySQL Support

Post by Egregius »

Search for pass2php on the forum or click the link in my signature.
karolp
Posts: 4
Joined: Tuesday 29 January 2019 13:04
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

Re: MySQL Support

Post by karolp »

Thanks a lot.
Pass2php is that I was looking for.
mackowiakp
Posts: 18
Joined: Friday 21 July 2017 19:26
Target OS: Linux
Domoticz version:
Contact:

Re: MySQL Support

Post by 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).
User avatar
Egregius
Posts: 2582
Joined: Thursday 09 April 2015 12:19
Target OS: Linux
Domoticz version: v2024.7
Location: Beitem, BE
Contact:

Re: MySQL Support

Post by Egregius »

karolp wrote: Tuesday 29 January 2019 22:49 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
Posts: 4
Joined: Tuesday 29 January 2019 13:04
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

Re: MySQL Support

Post by 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.
User avatar
erem
Posts: 230
Joined: Tuesday 27 March 2018 12:11
Target OS: Raspberry Pi / ODroid
Domoticz version: 2021.1
Location: Amsterdam/netherlands
Contact:

Re: MySQL Support

Post by 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.
Attachments
movedata - Copy.c
(8.96 KiB) Downloaded 138 times
Regards,

Rob
mackowiakp
Posts: 18
Joined: Friday 21 July 2017 19:26
Target OS: Linux
Domoticz version:
Contact:

Re: MySQL Support

Post by 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.
User avatar
gizmocuz
Posts: 2350
Joined: Thursday 11 July 2013 18:59
Target OS: Raspberry Pi / ODroid
Domoticz version: beta
Location: Top of the world
Contact:

Re: MySQL Support

Post by 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
Quality outlives Quantity!
ayasystems
Posts: 66
Joined: Tuesday 19 April 2016 23:37
Target OS: Raspberry Pi / ODroid
Domoticz version:
Contact:

Re: MySQL Support

Post by ayasystems »

domoticz should have the option to use mariadb instead of sql lite
User avatar
kiddigital
Posts: 435
Joined: Thursday 10 August 2017 6:52
Target OS: Raspberry Pi / ODroid
Domoticz version: Beta
Location: Netherlands
Contact:

Re: MySQL Support

Post by kiddigital »

ayasystems wrote: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?
One RPi with Domoticz, RFX433e, aeon labs z-wave plus stick GEN5, ha-bridge 5.4.0 for Alexa, Philips Hue Bridge, Pimoroni Automation Hat
One RPi with Pi foundation standard touch screen to display Dashticz
sjb66
Posts: 5
Joined: Tuesday 28 February 2023 16:58
Target OS: Windows
Domoticz version:
Contact:

Re: MySQL Support

Post by 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.
User avatar
waltervl
Posts: 5148
Joined: Monday 28 January 2019 18:48
Target OS: Linux
Domoticz version: 2024.7
Location: NL
Contact:

Re: MySQL Support

Post by waltervl »

sjb66 wrote: Sunday 29 October 2023 20:13 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
Domoticz running on Udoo X86 (on Ubuntu)
Devices/plugins: ZigbeeforDomoticz (with Xiaomi, Ikea, Tuya devices), Nefit Easy, Midea Airco, Omnik Solar, Goodwe Solar
Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest