Hello,
I have set up persistence service with JDBC and MariaDB.
MariaDB server is running on a synology NAS. My OpenHab instance is 2.5 and running on a Raspberry 4.
Persistence of number items is working fine. Table are created in MariaDB database, data are stored and can be read with Grafana, and no there is no error on Frontail log viewer.
But persistence of string items is not working. Table are not created in MariaDB database. In frontail i have this error:
2020-01-04 22:15:50.530 [ERROR] [org.knowm.yank.Yank ] - Error in SQL query!!!
java.sql.SQLException: Error preparing query: Table 'openhab_db.item0021' doesn't exist Query: INSERT INTO item0021 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? Parameters: [OL CHRG, OL CHRG]
at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392) ~[commons-dbutils-1.6.jar:1.6]
at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491) ~[commons-dbutils-1.6.jar:1.6]
at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457) ~[commons-dbutils-1.6.jar:1.6]
at org.knowm.yank.Yank.execute(Yank.java:194) [yank-3.2.0.jar:?]
at org.knowm.yank.Yank.execute(Yank.java:177) [yank-3.2.0.jar:?]
at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doStoreItemValue(JdbcBaseDAO.java:337) [bundleFile:?]
at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:145) [bundleFile:?]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:137) [bundleFile:?]
at org.openhab.core.persistence.internal.PersistenceServiceDelegate.store(PersistenceServiceDelegate.java:59) [bundleFile:?]
at org.eclipse.smarthome.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:137) [bundleFile:?]
at org.eclipse.smarthome.core.persistence.internal.PersistenceManagerImpl.stateUpdated(PersistenceManagerImpl.java:442) [bundleFile:?]
at org.eclipse.smarthome.core.items.GenericItem$1.run(GenericItem.java:259) [bundleFile:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_222]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_222]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_222]
Item0021 shown in this log is status of an UPS (OL CHRG).
I know there is some issue with mariadb string item. VARCHAR size for sqltype.STRING must be less than 4096. I have already done this setting in my jdbc.cgf file, and checked this setting in OpenHab Karaf console and found sqltype.STRING = VARCHAR(4096) so all is ok.
To debbug, i have tried to manually create the table in mariaDB through phpmyadmin, with column type VARCHAR(4096) for value column. And miracle ! created table is correctly populated by openhab persistence with string item value.
Issue can come from synology mariaDB package. I know there is some minor differences with standard mariaDB package.
So temporary solution is to manually create string item table. But i will continue to investigate and try to find a definitive fix.
Someone have in idea how to fix it? May be in jdbc driver ?
Thanks