I get an error since there is no table lastupdate.
Even if I could create it manually I would like to know-:
what is it for an item? I have not created an item with that name
why from all tables only this one is not created automaticall?
thanks and cheers
19:07:04.153 [ERROR] [org.knowm.yank.Yank ] - Error in SQL query!!!
java.sql.SQLException: Table 'openhab.lastupdate_0075' doesn't exist Query: INSERT INTO lastupdate_0075 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? Parameters: [2019-01-12 18:57:50.84, 2019-01-12 18:57:50.84]
at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392) ~[272:org.openhab.persistence.jdbc:1.14.0.201901110308]
at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491) ~[272:org.openhab.persistence.jdbc:1.14.0.201901110308]
at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457) ~[272:org.openhab.persistence.jdbc:1.14.0.201901110308]
at org.knowm.yank.Yank.execute(Yank.java:194) [272:org.openhab.persistence.jdbc:1.14.0.201901110308]
at org.knowm.yank.Yank.execute(Yank.java:177) [272:org.openhab.persistence.jdbc:1.14.0.201901110308]
at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doStoreItemValue(JdbcBaseDAO.java:332) [272:org.openhab.persistence.jdbc:1.14.0.201901110308]
at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:141) [272:org.openhab.persistence.jdbc:1.14.0.201901110308]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:133) [272:org.openhab.persistence.jdbc:1.14.0.201901110308]
at org.openhab.core.persistence.internal.PersistenceServiceDelegate.store(PersistenceServiceDelegate.java:55) [267:org.openhab.core.compat1x:2.5.0.201901111428]
at org.eclipse.smarthome.core.persistence.internal.PersistItemsJob.run(PersistItemsJob.java:57) [106:org.eclipse.smarthome.core.persistence:0.11.0.201901111358]
at org.eclipse.smarthome.core.scheduler.RunnableWrapper.run(RunnableWrapper.java:39) [102:org.eclipse.smarthome.core:0.11.0.201901111358]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:?]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:?]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) [?:?]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) [?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:?]
at java.lang.Thread.run(Thread.java:748) [?:?]
I have the same error. This is exist only for Text Elements, not for Switches and Numbers.
I tested it with nowPlaying item of Bose SoundTouch Binding and the LastError Item of the LogReader-Binding. Error Message is
2020-01-05 22:42:26.282 [ERROR] [org.knowm.yank.Yank ] - Error in SQL query!!!
java.sql.SQLException: Error preparing query: Table 'openhab_productive.item0005' doesn't exist Query: INSERT INTO item0005 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? Parameters: [Touch, Touch]
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.stateChanged(PersistenceManagerImpl.java:437) [bundleFile:?]
at org.eclipse.smarthome.core.items.GenericItem$1.run(GenericItem.java:261) [bundleFile:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_152]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_152]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_152]
I use openHAB 2.5.0 on a Raspberry PI with the latest MariaDB Database. openHAB created an entry in the items Table with item0005 as name
And the same error again, JDBC+mySQL, migrated setup from OH2 to OH3.
Any thoughts on how to address? Looks like tables are auto-created on failure for numbers and switches, but not for text elements…
Please help!
Yes, this indeed seem to be a case. Table names are assigned dynamically so in case of item rename openHAB does not request renaming of tables. This is very costly operation and many older DBMS (sybase!) consider it as non-transactional operation.
From what you observed JDBC persistence seem to have a gap which causes above. I would suggest reporting this as an issue in openhab addons repo: Issues · openhab/openhab-addons · GitHub.
Ok, I think I managed to solve it.
jdbc.conf was missing “tableNamePrefix=Item” entry.
I think there is something magical is “Item” value in the code (capitalization and all) that makes the table creation code work without errors. Note that in your log file upfront prefix was set to “openhab_productive.item”.
Added it, deleted all existing tables from the mysql database, cleaned cache, restart - all looks good now.
