Write to database options

[Michael McSharry]

I creates them. You just need to have mySQL server available.

[Dweber85rc]

Ok, cool. I'll be happy to do some testing as soon as you have a version ready

[Michael McSharry]

What I posted in this thread is for your testing with HS4. There is also the myself dll that you will likely need that was also mentioned in this thread.

[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

[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.

[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.

[Dweber85rc]

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

[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]

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.

[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

[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.

[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.

[Dweber85rc]

Thanks, it is populating the database again now

[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.

[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.