Is anyone successfully using jdbc-mysql persistence in OH3? I’m trying to configure OH3 to access a mysql server on a remote host, and I can’t get it to work. I keep getting this in my log. I’ve confirmed permissions are correct on the DB side, and that I can connect to the database remotely from the openHAB host using the command line.
2020-12-10 17:56:51.918 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::store: No connection to database. Cannot persist item 'XXXXXXX (Type=DateTimeItem, State=2020-12-10T17:56:52.000-0500, Label=null, Category=clock)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:1
I’ve installed the jdbc-mysql addon.
My jdbc.cfg looks like this.
# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
url=jdbc:mysql://remote.host.name:3306/openhab3?serverTimezone=America/New_York
# the database user
user=myUser
# the database password
password=myPassword
# the reconnection counter
#reconnectCnt=1
# the connection timeout (in seconds)
#waitTimeout=
tableUseRealItemNames=true
So I put the jdbc persistence bundle in debug mode. Now I’m even more confused. Everything appears to work all the way up to the last line where it fails to store the item.
The shadow config file userdata/config/org/openhab/jdbc.config contained this line. Burned again by shadow config files… LOL
reconnectCnt="1"
So I blew away the file, and it started working. Kind of.
Log file now getting spammed with this. I guess the bundle doesn’t like Image item types and isn’t afraid to let you know it!
2020-12-11 07:39:45.133 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_DrivewayImage
2020-12-11 07:39:45.322 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_FrontDoorImage
2020-12-11 07:39:50.106 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_LivingImage
2020-12-11 07:39:50.147 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_PatioImage
2020-12-11 07:39:50.193 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_BasementImage
2020-12-11 07:39:50.942 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_KitchenImage
2020-12-11 07:39:55.214 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_DrivewayImage
2020-12-11 07:39:55.410 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_FrontDoorImage
2020-12-11 07:40:00.183 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_LivingImage
2020-12-11 07:40:00.224 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_PatioImage
2020-12-11 07:40:00.270 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_BasementImage
2020-12-11 07:40:01.005 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_KitchenImage
2020-12-11 07:40:05.320 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_DrivewayImage
2020-12-11 07:40:05.503 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_FrontDoorImage
20
It also appears to have issues with timestamps.
2020-12-11 07:36:20.093 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: Table created for item 'E2_Forecast4_DateTime' with dataType TIMESTAMP(3) in SQL database.
2020-12-11 07:36:20.094 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=e2_forecast4_datetime_0009; newTableName=null;
2020-12-11 07:36:20.095 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType DATETIMEITEM for Item E2_Forecast4_DateTime
2020-12-11 07:36:20.097 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'E2_Forecast4_DateTime' as Type 'DATETIMEITEM' in 'e2_forecast4_datetime_0009' with state '2020-12-15T00:00:00.000-0500'
2020-12-11 07:36:20.098 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: '2020-12-15T00:00:00.000-0500'
2020-12-11 07:36:20.100 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO setValueTypes dbType=TIMESTAMP(3); javaType=class java.sql.Timestamp;
2020-12-11 07:36:20.101 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: DateTimeItem: '2020-12-15 00:00:00.0'
2020-12-11 07:36:20.102 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO e2_forecast4_datetime_0009 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='2020-12-15 00:00:00.0'
2020-12-11 07:36:20.109 [ERROR] [org.knowm.yank.Yank ] - Error in SQL query!!!
java.sql.SQLException: Table 'openhab3.e2_forecast4_datetime_0009' doesn't exist Query: INSERT INTO e2_forecast4_datetime_0009 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? Parameters: [2020-12-15 00:00:00.0, 2020-12-15 00:00:00.0]
at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392) ~[bundleFile:?]
at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491) ~[bundleFile:?]
at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457) ~[bundleFile:?]
at org.knowm.yank.Yank.execute(Yank.java:194) [bundleFile:?]
at org.knowm.yank.Yank.execute(Yank.java:177) [bundleFile:?]
at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doStoreItemValue(JdbcBaseDAO.java:327) [bundleFile:?]
at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:149) [bundleFile:?]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:140) [bundleFile:?]
at org.openhab.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:140) [bundleFile:?]
at org.openhab.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:461) [bundleFile:?]
at org.openhab.core.items.GenericItem.lambda$1(GenericItem.java:259) [bundleFile:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
at java.lang.Thread.run(Thread.java:834) [?:?]
I compared your jdbc.cfg to mine and its exactly identical (the only thing that differs is the host name and the time zone). I wonder though, since it seems to be a group item you’re persisting, do you get the same problem for all items, independent of type? The only problem I have is persistence of QuantityType items, (which I think is fixed in latest snapshot), all others work good.
I’ll look into the second error first. It says it created the table.
2020-12-11 07:36:20.093 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: Table created for item 'E2_Forecast4_DateTime' with dataType TIMESTAMP(3) in SQL database.
2020-12-11 07:36:19.956 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: no table found for item 'E1_Runtime_LastModified' in sqlTables
2020-12-11 07:36:19.957 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createNewEntryInItemsTable
2020-12-11 07:36:19.958 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doCreateNewEntryInItemsTable sql=INSERT INTO items (ItemName) VALUES ('E1_Runtime_LastModified')
2020-12-11 07:36:19.964 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: getTableName with rowId=7 itemName=E1_Runtime_LastModified
2020-12-11 07:36:19.965 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType DATETIMEITEM for Item E1_Runtime_LastModified
2020-12-11 07:36:19.966 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=e1_runtime_lastmodified_0007; newTableName=E1_Runtime_LastModified;
2020-12-11 07:36:19.967 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createItemTable
2020-12-11 07:36:19.969 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doCreateItemTable sql=CREATE TABLE IF NOT EXISTS e1_runtime_lastmodified_0007 (time TIMESTAMP(3) NOT NULL, value TIMESTAMP(3), PRIMARY KEY(time))
2020-12-11 07:36:19.995 [ERROR] [org.knowm.yank.Yank ] - Error in SQL query!!!
java.sql.SQLException: Invalid default value for 'value' Query: CREATE TABLE IF NOT EXISTS e1_runtime_lastmodified_0007 (time TIMESTAMP(3) NOT NULL, value TIMESTAMP(3), PRIMARY KEY(time)) Parameters: []
at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392) ~[bundleFile:?]
at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491) ~[bundleFile:?]
at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457) ~[bundleFile:?]
at org.knowm.yank.Yank.execute(Yank.java:194) [bundleFile:?]
at org.knowm.yank.Yank.execute(Yank.java:177) [bundleFile:?]
at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doCreateItemTable(JdbcBaseDAO.java:317) [bundleFile:?]
at org.openhab.persistence.jdbc.internal.JdbcMapper.createItemTable(JdbcMapper.java:136) [bundleFile:?]
at org.openhab.persistence.jdbc.internal.JdbcMapper.getTable(JdbcMapper.java:274) [bundleFile:?]
at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:143) [bundleFile:?]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:140) [bundleFile:?]
at org.openhab.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:140) [bundleFile:?]
at org.openhab.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:461) [bundleFile:?]
at org.openhab.core.items.GenericItem.lambda$1(GenericItem.java:259) [bundleFile:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
at java.lang.Thread.run(Thread.java:834) [?:?]
I do not know why, but your problem sounds somehow familiar to me. Just ftr I am using jdbc persistence on OH3 too. But MariaDB instead of MySQL. I double checked my own sql_mode variable and it does not contain the mentioned string.
Thanks. Yeah those zero date settings were added in 5.7, so they are relatively new. In any event removing those from my config resolved the issue with creating tables for DateTime items.
Now if I can figure out what to do about these messages spamming my log file continuously.
2020-12-11 11:33:12.592 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for Zm_FrontDoorImage
2020-12-11 11:33:13.607 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for Zm_KitchenImage
2020-12-11 11:33:13.648 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for Zm_LivingImage
2020-12-11 11:33:13.888 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for Zm_DrivewayImage
2020-12-11 11:33:14.701 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for Zm_PatioImage
2020-12-11 11:33:17.669 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for Zm_BasementImage
@cweitkamp Do you think IMAGEITEM should be added to the sqlTypes HashMap? Since it adds the item to the db as a VARCHAR anyway, it would seem to make sense to not complain about it. WDYT?
Yes, why not. Location and Player are missing too. They can be stored as VARCHAR as well. Implementation to support them will close a long pending feature request.
Sure. I found a few spots to change. If I add the change centrally in JdbcBaseDAO, I need to also change in some of the other DB-specific DAOs. Haven’t found anything else yet.
thanks for the informative and exhaustive post - I am trying to set up this persistence as well and I’ll be building on your experience.
BTW, did you observe this issue https://github.com/openhab/openhab-core/issues/1973 yourself after installing JDBC MySQL? EDIT: forget it, I have been told that after installing the persistence you have to go to settings and select a default persistence otherwise everything is disconnectd. Leaving the comment for anyone having the same issue in the future
EDIT 2: I have installed successfully and data are being pushed to mySQL. Issue is that it looks like data cannot be used any longer to populate charts (Anlyze or custom made). Do you experience the same issue or it’s just my setup?
Apprecite your patience
My error is not the same one reported above, but it’s also related to JDBC-MySQL/MariaDB in OH3.
After I updated my existing configuration from 2.5 to 3.0 a few days ago, and setup the persistence-jdbc-mariadb, since the mysql driver I was using on 2.5 stopped being supported, I started having this problem:
12/26/2020 22:51:26.204 [ERROR] [tence.internal.PersistenceManagerImpl] - Exception occurred while querying persistence service 'jdbc': class java.lang.String cannot be cast to class java.lang.Number (java.lang.String and java.lang.Number are in module java.base of loader 'bootstrap')
java.lang.ClassCastException: class java.lang.String cannot be cast to class java.lang.Number (java.lang.String and java.lang.Number are in module java.base of loader 'bootstrap')
at org.openhab.persistence.jdbc.db.JdbcBaseDAO.getState(JdbcBaseDAO.java:469) ~[?:?]
at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doGetHistItemFilterQuery(JdbcBaseDAO.java:340) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcMapper.getHistItemFilterQuery(JdbcMapper.java:162) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.query(JdbcPersistenceService.java:202) ~[?:?]
at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
at org.openhab.core.internal.common.AbstractInvocationHandler.invokeDirect(AbstractInvocationHandler.java:154) [bundleFile:?]
at org.openhab.core.internal.common.Invocation.call(Invocation.java:52) [bundleFile:?]
at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
at java.lang.Thread.run(Thread.java:834) [?:?]
It’s the same reported on this thread:
Surprisingly that thread was created back on March but there seems to be no solution.
I tried uninstalling persistence-jdbc-mariadb and installing persistence-jdbc-mysql but nothing works.