Write to database options

[Michael McSharry]

Any time any HS4 Feature / HS3 Device is updated its DeviceValue will be stored in the selected database (InfluxDB, mySQL, MS SQL Server, or SQLite) if the Feature/Device is one of the ones identified to be stored. A single setting also enables the storage of all HS Feature/Device updates in lieu of specific Feature/Device selection.

If you want other information beyond DeviceValue stored at the same time, then the fields to be included are specified in the setup

[Dweber85rc]

I am a bit confused about this release so a question about this version release.
Do i understand this correctly but will this release make it possible to use mcsMQTT to also store values from regular HS devices (not controled by this plugin) in the mcsMQTT DB?

This release was for the ability to store hs device values into an external MYSQL database, and it works great!

[AshaiRey]

I am a bit confused about this release so a question about this version release.
Do i understand this correctly but will this release make it possible to use mcsMQTT to also store values from regular HS devices (not controled by this plugin) in the mcsMQTT DB?

[Michael McSharry]

InfluxDB uses UTC time and the plugin converts between local and UTC in each direction. For the other databases I am storing local time and no time zone conversions performed. Daylight savings time will create some ambiguity in the data when not using UTC.

[Dweber85rc]

The goal with being able to write this to mysql was graphing in Grafana. Once the data was being sent there, it took a bit of learning to get grafana going, mostly it was the fact that mysql and grafana handle timestamps differently-something to do with Unix time vs UTC. But once I had that figured out was able to adjust the mysql statements and start getting some data plotted out.

[Dweber85rc]

Thanks, it is populating the database again now

[Michael McSharry]

I changed it in 5.23.21.3 to use TEXT rather than VARCHAR. I was able to create a new table without errors.

[Michael McSharry]

Originally I had limited the character field to 255 bytes and after reviewing the mySQL reference it seems the size was optional. I will look at this again.

[Dweber85rc]

I installed the update tonight after having some time and dropped the existing database to wipe it out and start fresh. Now I get this when I start mcsmqtt

ExecuteCommandMySQL CREATE TABLE mcsmqtt ( lastdate DATETIME NULL, device VARCHAR NULL , value FLOAT NULL ) at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBeh avior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at MCSMQTTHS4_2020.Database.ExecuteCommandMySQL(String sCommand) in C:\Users\Public\Documents\LaptopVM\MCSMQTT2020HS4\Database.v b:line 986 Line 0 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL , value FLOAT NULL )' at line 1

[Dweber85rc]

I discovered a mistake when doing the MS SQL Server testing in creation of the LastDate fieled in mySQL where it was created as a TEXT rather than a DATETIME. Best if you removed your table and recreated it with 5.23.20.1.

Ah, I did not notice that in the database and was running into some issues getting that field mapped correctly in Grafana. That might explain why.

[Michael McSharry]

I discovered a mistake when doing the MS SQL Server testing in creation of the LastDate fieled in mySQL where it was created as a TEXT rather than a DATETIME. Best if you removed your table and recreated it with 5.23.20.1.

[Dweber85rc]

That did it, the data is populating in the database now!!!

[Michael McSharry]

Don't know what happened as I was testing with this build, but it is fixed with 5.23.19.1 at https://forums.homeseer.com/forum/hs...ge-log-hs4-hs3

I also completed the port and testing for HS3 plugin at the same location.

[Michael McSharry]

The table name should be forced in the plugin to be lower case for myself. I need to look to see how it had upper case.

[Dweber85rc]

Oh, thanks I missed that before. I have the update installed and configured. However I am not getting any data written to the database. I am using an external mysql server. It did create the database and the tables correctly, so that should mean that the credentials should be correct. This error is in the HS log.

MySQLExecuteCommand INSERT INTO mcsMQTT (lastdate,device,value) Values ('2022-04-10 12:58:40','Main_Office_rssi:rssi',-73) at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBeh avior behavior) at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at MCSMQTTHS4_2020.Database.MySQLExecuteCommand(String sCommand) in C:\Users\Public\Documents\LaptopVM\MCSMQTT2020HS4\Database.v b:line 1042 Line 0 Table 'MQTT.mcsMQTT' doesn't exist