I’m having a strange(?) problem with my “JDBC MySQL” persistence.
On some of the items, when they receive an update, and persistence want to log them, I’m seeing a message like the following in the log:
22:22:45.067 [ERROR] [org.knowm.yank.Yank ] - Error in SQL query!!!
java.sql.SQLException: Table 'OpenHAB.item0121' doesn't exist Query: INSERT INTO item0121 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? Parameters: [2017-03-17, 2017-03-17]
at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.knowm.yank.Yank.execute(Yank.java:194)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.knowm.yank.Yank.execute(Yank.java:177)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doStoreItemValue(JdbcBaseDAO.java:332)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:141)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:133)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.openhab.core.persistence.internal.PersistenceServiceDelegate.store(PersistenceServiceDelegate.java:55)[186:org.openhab.core.compat1x:2.0.0.RC1]
at org.eclipse.smarthome.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:119)[104:org.eclipse.smarthome.core.persistence:0.9.0.b3]
at org.eclipse.smarthome.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:420)[104:org.eclipse.smarthome.core.persistence:0.9.0.b3]
at org.eclipse.smarthome.core.items.GenericItem$1.run(GenericItem.java:248)[99:org.eclipse.smarthome.core:0.9.0.b3]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)[:1.8.0_121]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)[:1.8.0_121]
at java.lang.Thread.run(Thread.java:745)[:1.8.0_121]
Anybody has a clue why this is happening - shouldn’t tables be created automatically? I have a lot of tables with data in.
The Table name “item0121” seems strange. AFAIK the Table names get incremented by 1 without a leading zero. I would first check if such table indeed does not exist ( select * from Items where ItemName=‘item0121’ should return no hits).
Next i would try removing one of the trouble Items, reload the items file(if you use items files) and then readd it. This should force a creation of a new table when the item gets persisted next time.
Thanks for your suggestion / idea.
I tried to comment out those items giving problem - and that removed the error-messages.
But I noticed a similar problem, when I tried to add an new item:
16:24:02.974 [ERROR] [org.knowm.yank.Yank ] - Error in SQL query!!!
java.sql.SQLException: Invalid default value for 'value' Query: CREATE TABLE IF NOT EXISTS item0129 (time TIMESTAMP(3) NOT NULL, value TIMESTAMP(3), PRIMARY KEY(time)) Parameters: []
at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.knowm.yank.Yank.execute(Yank.java:194)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.knowm.yank.Yank.execute(Yank.java:177)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doCreateItemTable(JdbcBaseDAO.java:322)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.openhab.persistence.jdbc.internal.JdbcMapper.createItemTable(JdbcMapper.java:128)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.openhab.persistence.jdbc.internal.JdbcMapper.getTable(JdbcMapper.java:264)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.query(JdbcPersistenceService.java:192)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.openhab.core.persistence.internal.QueryablePersistenceServiceDelegate.query(QueryablePersistenceServiceDelegate.java:47)[186:org.openhab.core.compat1x:2.0.0.RC1]
at org.eclipse.smarthome.core.persistence.internal.PersistenceManagerImpl.initialize(PersistenceManagerImpl.java:250)[104:org.eclipse.smarthome.core.persistence:0.9.0.b3]
at org.eclipse.smarthome.core.persistence.internal.PersistenceManagerImpl.added(PersistenceManagerImpl.java:394)[104:org.eclipse.smarthome.core.persistence:0.9.0.b3]
at org.eclipse.smarthome.core.persistence.internal.PersistenceManagerImpl.added(PersistenceManagerImpl.java:1)[104:org.eclipse.smarthome.core.persistence:0.9.0.b3]
at org.eclipse.smarthome.core.internal.items.ItemRegistryImpl.allItemsChanged(ItemRegistryImpl.java:105)[99:org.eclipse.smarthome.core:0.9.0.b3]
at org.eclipse.smarthome.model.item.internal.GenericItemProvider.modelChanged(GenericItemProvider.java:350)[124:org.eclipse.smarthome.model.item:0.9.0.b3]
at org.eclipse.smarthome.model.core.internal.ModelRepositoryImpl.notifyListeners(ModelRepositoryImpl.java:207)[123:org.eclipse.smarthome.model.core:0.9.0.b3]
at org.eclipse.smarthome.model.core.internal.ModelRepositoryImpl.addOrRefreshModel(ModelRepositoryImpl.java:120)[123:org.eclipse.smarthome.model.core:0.9.0.b3]
at org.eclipse.smarthome.model.core.internal.folder.FolderObserver.checkFile(FolderObserver.java:270)[123:org.eclipse.smarthome.model.core:0.9.0.b3]
at org.eclipse.smarthome.model.core.internal.folder.FolderObserver.access$1(FolderObserver.java:264)[123:org.eclipse.smarthome.model.core:0.9.0.b3]
at org.eclipse.smarthome.model.core.internal.folder.FolderObserver$WatchQueueReader.processWatchEvent(FolderObserver.java:145)[123:org.eclipse.smarthome.model.core:0.9.0.b3]
at org.eclipse.smarthome.core.service.AbstractWatchQueueReader.run(AbstractWatchQueueReader.java:122)[99:org.eclipse.smarthome.core:0.9.0.b3]
at java.lang.Thread.run(Thread.java:745)[:1.8.0_121]
16:24:02.975 [ERROR] [org.knowm.yank.Yank ] - Error in SQL query!!!
java.sql.SQLException: Table 'OpenHAB.item0129' doesn't exist Query: SELECT time, value FROM item0129 ORDER BY time DESC LIMIT 0,1 Parameters: []
at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:351)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:289)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.knowm.yank.Yank.queryObjectArrays(Yank.java:578)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.knowm.yank.Yank.queryObjectArrays(Yank.java:560)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doGetHistItemFilterQuery(JdbcBaseDAO.java:339)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.openhab.persistence.jdbc.internal.JdbcMapper.getHistItemFilterQuery(JdbcMapper.java:154)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.query(JdbcPersistenceService.java:201)[213:org.openhab.persistence.jdbc:1.9.0.RC1]
at org.openhab.core.persistence.internal.QueryablePersistenceServiceDelegate.query(QueryablePersistenceServiceDelegate.java:47)[186:org.openhab.core.compat1x:2.0.0.RC1]
at org.eclipse.smarthome.core.persistence.internal.PersistenceManagerImpl.initialize(PersistenceManagerImpl.java:250)[104:org.eclipse.smarthome.core.persistence:0.9.0.b3]
at org.eclipse.smarthome.core.persistence.internal.PersistenceManagerImpl.added(PersistenceManagerImpl.java:394)[104:org.eclipse.smarthome.core.persistence:0.9.0.b3]
at org.eclipse.smarthome.core.persistence.internal.PersistenceManagerImpl.added(PersistenceManagerImpl.java:1)[104:org.eclipse.smarthome.core.persistence:0.9.0.b3]
at org.eclipse.smarthome.core.internal.items.ItemRegistryImpl.allItemsChanged(ItemRegistryImpl.java:105)[99:org.eclipse.smarthome.core:0.9.0.b3]
at org.eclipse.smarthome.model.item.internal.GenericItemProvider.modelChanged(GenericItemProvider.java:350)[124:org.eclipse.smarthome.model.item:0.9.0.b3]
at org.eclipse.smarthome.model.core.internal.ModelRepositoryImpl.notifyListeners(ModelRepositoryImpl.java:207)[123:org.eclipse.smarthome.model.core:0.9.0.b3]
at org.eclipse.smarthome.model.core.internal.ModelRepositoryImpl.addOrRefreshModel(ModelRepositoryImpl.java:120)[123:org.eclipse.smarthome.model.core:0.9.0.b3]
at org.eclipse.smarthome.model.core.internal.folder.FolderObserver.checkFile(FolderObserver.java:270)[123:org.eclipse.smarthome.model.core:0.9.0.b3]
at org.eclipse.smarthome.model.core.internal.folder.FolderObserver.access$1(FolderObserver.java:264)[123:org.eclipse.smarthome.model.core:0.9.0.b3]
at org.eclipse.smarthome.model.core.internal.folder.FolderObserver$WatchQueueReader.processWatchEvent(FolderObserver.java:145)[123:org.eclipse.smarthome.model.core:0.9.0.b3]
at org.eclipse.smarthome.core.service.AbstractWatchQueueReader.run(AbstractWatchQueueReader.java:122)[99:org.eclipse.smarthome.core:0.9.0.b3]
at java.lang.Thread.run(Thread.java:745)[:1.8.0_121]
The users I’m using to access MySQL has “All Privileges” on the database.
Why can’t it create new databases automatically?
//Kongsted
If you can rule out permission problems (try logging on to your OH DB with OH database user/pass and create a table) i would say something is seriously f$%&ed up there. The JDBC binding has so many settings who knows what might be wrong. What i would do (just a suggestion; it is rather brute force!):
create new DB for openhab and triple check user/pass/permissions
remove JDBC binding
install/configure mysql binding to use new DB
take deep breath, restart OH and check persistence.
If this doesn’t work out at all,you could still go back to what you had before.
I think I’m getting a little closer. It seems like it’s only persistence-tables, where the value has to be a date, there’s a problem. If I’m using the same command as mentioned in the error-message, phpMyAdmin also complaining:
Error
SQL query:
CREATE TABLE IF NOT EXISTS item0999 (time TIMESTAMP(3) NOT NULL, value TIMESTAMP(3), PRIMARY KEY(time))
MySQL said: Documentation
#1067 - Invalid default value for 'value'
Is there a place to change the template of new tables? I fixed the command, so I was able to create the tables in phpMyAdmin myself, so if I could make the template similar to the command I used, the problem might be solved.
Well you might be able to do so by messing with the binding itself(like, re-programm and compile), or maybe through the jdbc.cfg files settings, but quite frankly i think you’d waste loads of time with uncertain results(if any).
Make a clean cut, create a new DB, do a reinstall of your persistence service of choice (IMHO when using MySQL the mysql binding is your best bet) and things should fly again.
