Difficulties with MySQL Persistence

Dear Ladys or Gentleman,

i’ve got big difficulties with MySQL Persistence. I’ve used Paper UI to install the add-on MySQL Persistence. I have add a database to my MySQL Server and create mysql.persist and mysql.cfg files. It doesn’t work. Nothing happens! I tried “localhost”, “127.0.0.1”, “192.168.178.31” as address but nothing works. There where no tables in my database. I have no more idea how to solve it.

Could anyone help me?

Best Regards
Chris

You could look in openhab.log and see what messages the add-on produces.

1 Like

I’ve done it but there is no message wich response to that add-on.

Not even Loading model 'mysql.persist’ ? Your addon installation did not complete.

1 Like

Dear Rossko,

now i got it and than that Error:

2020-05-03 17:06:54.111 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://127.0.0.1/openHAB, user=root

java.sql.SQLException: The server time zone value ‘CEST’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.13.jar:8.0.13]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.13.jar:8.0.13]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[mysql-connector-java-8.0.13.jar:8.0.13]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[mysql-connector-java-8.0.13.jar:8.0.13]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73) ~[mysql-connector-java-8.0.13.jar:8.0.13]

I don’t understand. I tried to change the “localtime=false” in mysql.cfg but that doesn’t change the errors. Time zone set to ‘Europe/Berlin’.

Thank you for your reply.

Best regards
Chris

Ok, i searched the web and found this:

?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

Now this Errors are shown:

2020-05-03 17:24:23.142 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘mihome_sensor_ht_158d00042600a5_temperature’.
2020-05-03 17:24:23.148 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘miio_generic_04AB3CC8_actions_commands’.
2020-05-03 17:24:23.161 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘mqtt_topic_af89e966_PWR_Wohnzimmer_Bibliothek’.
2020-05-03 17:24:23.164 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘mihome_sensor_ht_158d00042600a5_batteryLevel’.
2020-05-03 17:24:23.168 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘mihome_sensor_ht_158d00042600a5_humidity’.
2020-05-03 17:24:23.176 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘mihome_sensor_ht_158d00042600a5_lowBattery’.
2020-05-03 17:24:23.180 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘123’.
2020-05-03 17:24:23.184 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘mihome_sensor_ht_b1295862_temperature’.
2020-05-03 17:24:23.188 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘mihome_sensor_ht_b1295862_humidity’.
2020-05-03 17:24:23.200 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘mihome_sensor_ht_b1295862_lowBattery’.
2020-05-03 17:24:23.203 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘mihome_sensor_ht_b1295862_batteryLevel’.
2020-05-03 17:24:23.207 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘mqtt_topic_af89e966_PWR_Wohnzimmer_Aquarium’.
2020-05-03 17:24:23.350 [ERROR] [ng.xml.internal.ThingTypeXmlProvider] - Could not register ThingType: zwave:aeotec_zw141_03_000

Best regards
Chris

This post looks helpful

1 Like

Some alternative advice

Have you got an Item named 123?

Names must not begin with numbers.

The item “123” is mysterious. I couldn’t find it anywhere. Now there are tables in our Database but they named not correct. They named:

Item1
Item2
Items

Item1 is temperature
Item2 is humidity

Thank you for your replys. They help me a lot by solving. I will write a summary at the end.

Best regards
Chris

Hi @rossko57,
I have set the server timezone in my mysql.cfg:

# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
# url=jdbc:mysql://9987578f9eb9:3306/openhab
# url=jdbc:mysql://localhost:3306/openhab
url=jdbc:mysql://MY.MARIADB.IP.ADDRESS:3306/openhab?serverTimezone=Europe/Berlin

But I experienced the problem anyway but it is not reproducable. When my OpenHAB environment in the docker container starts everthing works fine … some times the error appears during the day and some times not :thinking:

When i began to work with OpenHAB i’ve installed it on ubuntu via the software AND (i don’t know why) via commandline. I think this was the beginning of a long and very bad experience. I’m now sure the item123 was created by the second installation of OpenHAB and was not completely deleted. There where a lot of problems in fact of that bad installation! I couldn’t trace back all my actions for this so i’ve deleted absolutely all and started completely new. A new installation of ubuntu and a proper installation of OpenHAB an all the bindings. Now everything works fine.