Wondering if it would be possible to add support for writing values to a mysql/mariadb instance? I already have a mysql/mariadb server on an rpi on my network so wondering what would be possible without adding a separate server for influxdb since the other application using mysql does not support influxdb. Also wondering about how/if there would be a way for a non mcsmqtt device to write to said database through mscmqtt?
Announcement
Collapse
No announcement yet.
Write to database options
Collapse
X
-
I use mySQL and MS SQLServer for mcsSprinklers so that part is easy to add. My thinking is that existing UI for external database would remain intact and there would be a global setting for which external database would be used. Is this something like you were thinking?
Also wondering about how/if there would be a way for a non mcsmqtt device to write to said database through mscmqtt?
mcsMQTT also supports a scripting interface so that anything that is being done by mcsMQTT can also be done using scripting.
Can you clarify your request?
-
Originally posted by Michael McSharry View PostI use mySQL and MS SQLServer for mcsSprinklers so that part is easy to add. My thinking is that existing UI for external database would remain intact and there would be a global setting for which external database would be used. Is this something like you were thinking?
I do not understand what is being requested. Currently mcsMQTT will write changed DeviceValues to an internal (SQLite) and/or external (InfluxDB) database for any HS device that has been selected for routing to the database. There is no dependency on a device being created by mcsMQTT. There is also a history capability to store MQTT messages to internal database. Obviously MQTT messages need to be routed through mcsMQTT to accomplish this.
mcsMQTT also supports a scripting interface so that anything that is being done by mcsMQTT can also be done using scripting.
Can you clarify your request?
For your 2nd question I think your explanation answered my question. I was looking to be able to send, for example, energy data from a zwave outlet to this mysql database as well, which from your explanation sounds like it is possible. My goal here is to eventually get Granfana going, which can use mysql data sources, and again try to avoid having a separate database server for this since I have one that is pretty much snoozing all the time.
Comment
-
Dweber85rc - For energy data, you should consider using a time-series centric DB such as InfluxDB. MySQL is a relational database and while it can store TS data, the queries can be a bit more complex. I run MySQL, Django, Apache, InfluxDB, and Grafana all on the same Intel Atom box with no performance issues.
Just my 2 cents."if I have seen further [than others], it is by standing on the shoulders of giants." --Sir Isaac Newton (1675)
Comment
-
Originally posted by kenm View PostDweber85rc - For energy data, you should consider using a time-series centric DB such as InfluxDB. MySQL is a relational database and while it can store TS data, the queries can be a bit more complex. I run MySQL, Django, Apache, InfluxDB, and Grafana all on the same Intel Atom box with no performance issues.
Just my 2 cents.
Comment
-
I am a novice with InfluxDB, but I have not observed any difference in ability to query data from it vs. a traditional relational database as long as one of the fields in the database is a timestamp of when the record was inserted or updated. I know that InfluxDB 2.x and flux query language has additional capabilities beyond the InfluxDB 1. x and other databases SQL, but since I was never lacking in ability to do what I wanted with SQL, I have never tried to look at what else flux query can do.
Comment
-
Originally posted by Michael McSharry View PostI am a novice with InfluxDB, but I have not observed any difference in ability to query data from it vs. a traditional relational database as long as one of the fields in the database is a timestamp of when the record was inserted or updated. I know that InfluxDB 2.x and flux query language has additional capabilities beyond the InfluxDB 1. x and other databases SQL, but since I was never lacking in ability to do what I wanted with SQL, I have never tried to look at what else flux query can do."if I have seen further [than others], it is by standing on the shoulders of giants." --Sir Isaac Newton (1675)
Comment
-
I cleared my backlog and now will be able to start testing the mySQL implementation tomorrow. I did post the required dll that supports mySQL. When I do post the mcsMQTT dll you will also need to capture this support file. Updates are at https://forums.homeseer.com/forum/hs...ge-log-hs4-hs3
Comment
-
The HS4 version of the plugin that supports multiple external databases is attached for your evaluation. I have not tested SQL Server. I am now going to port the implementation to HS3 plugin.
A radio has been added to the top of the History page for long term database to select among the four available. I suggest completing the database parameters of Database name, Table name, Username and Password before selecting MySQL as the database so that when the database is created it will have all the info it needs. It should not need a plugin restart, but if you have issue then give that a try.
I have tested storage of DeviceValue to the database. Reading the table of values for a given date range. Charting for a Device for a date range.
When viewed from MySQL Workbench the following schema is what should be expected to be created by the plugin.
Comment
Comment