When using OpenHAB3 Persitence together with MariaDB I got the following log entries:
2021-07-11 18:07:04.286 [ERROR] [org.knowm.yank.Yank] - Error in SQL query!!! java.sql.SQLException: Invalid default value for 'value' Query is : CREATE TABLE IF NOT EXISTS item0140 (time TIMESTAMP(3) NOT NULL, value TIMESTAMP(3), PRIMARY KEY(time)) Query: CREATE TABLE IF NOT EXISTS item0140 (time TIMESTAMP(3) NOT NULL, value TIMESTAMP(3), PRIMARY KEY(time))
2021-07-11 18:07:04.337 [ERROR] [org.knowm.yank.Yank] - Error in SQL query!!! java.sql.SQLException: Error preparing query: Table 'openhab3.item0140' doesn't exist Query: INSERT INTO item0140 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? Parameters: [2021-07-11 18:07:04.0, 2021-07-11 18:07:04.0]
The second entry is of course a result of the first entry.
The reason why this is happening is that MariaDB has changed it’s default behavior for the default value of date and timestamp columns . From a certain version Number (I’m not sure it’s about 5.6 or 5.8 a zero-Date is no longer tolerated. Therefore You get this “no information” error message from the Database.
It took me several hours to figure out what and why it has happened. So I wanted to share this information with you. How to solve. My solution was to change the MariaDB configuration file. If anybody knows a better solution, please tell me/us.
In the configuration file (in my case, Ubuntu, it is stored in /etc/mysql/my.cnf) is a line:
sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL
This was the default in my configuration. When I was asking in the DB-Client for the value of this variable I got:
select @@SQL_MODE;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@SQL_MODE |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
So even when not set, you got the trouble makers NO_ZERO_IN_DATE,NO_ZERO_DATE in your configuration.
After testing a lot of settings I finally figured out the value that worked was:
sql_mode = ALLOW_INVALID_DATES
After restarting MariaDB and OpenHAB3 the persitence of OpenHAB3 is now able to create the requested tables.
Unfortunately all values containg a date failed to create their tables before and so at this stage you still got a lot of error messages in the OpenHAB log-file but only the second one. The reason is, that the persitence had created entries in the items Table but failed to create the related table in the DB. So in a last step you have to remove the entries in item table where the create statement of the related table has failed. If this is to much work for you, stop OpenHAB or at least it’s persitence service and drop all the tables in the DB or recreate the DB. Then restart OpenHAB or the persistence service and the tables and entries will be created again. This time without the error messages