JDBC Mysql persistence in OH3

You’re welcome. Please report back if it is working.

Yes, as before, the items I want are working when I want them. I am grateful to you.

Hello @nelson.aponte

I’m having the same issue as you: migrated from OH 2.5 to OH3.0 and using mariaDB. I get lots of:

Exception occurred while querying persistence service ‘jdbc’: class java.lang.Double cannot be cast to class java.lang.String (java.lang.Double and java.lang.String are in module java.base of loader ‘bootstrap’)
java.lang.ClassCastException: class java.lang.Double cannot be cast to class java.lang.String (java.lang.Double and java.lang.String are in module java.base of loader ‘bootstrap’)

for every item persisted in mariaDB. Where you able to solve your issue?
KR

Hi @frieso, let’s call it a workaround.
Thanks to @cweitkamp we found out that the JDBC add-on is creating a completely new table in the database called “items” (with lowercase i) for the mapping, instead of using the original “Items” (with uppercase I) table used by the MySQL add-on (from OH 2.5). Additionally, the new table has a completely different order than the original one (because of this we get the class exceptions), and to make it worse, it cannot be defined in the jdbc.cfg what table to use for the mapping as it’s hard-coded (temporarily, hopefully).
What you have to do is rename the new (wrong) “items” table to something else (just in case) and then rename the original “Items” table to “items” (again, beware the lowercase “i”).
Chances are, the JDBC add-on may have stored trash information in some of your tables, so it would be better to delete that trash before renaming the Items/items tables. I deleted, table by table, everything created after the timestamp when I migrated to OH 3.0.

So I followed the following steps:

  1. Set a wrong password in the jdbc.cfg to prevent the driver from connecting to the DB while I was doing the housekeeping.

  2. Deleted the trash records.

  3. Renamed the tables.

  4. Corrected the credentials in the jdbc.cfg

Then it worked like a charm.

@cweitkamp my apologies for I haven’t been able to check the forum lately, or anything related to OH for that matter, so I haven’t been able to work on your requests.
I found out that an issue was already raised on MySQL > JDBC migration - Items table is not used for index but creates a new table called items · Issue #9637 · openhab/openhab-addons · GitHub

About the short how-to, do you want me to include the workaround?

Never mind. It looks like there is already a small section to describe how to migrate - I rather would try to implement a way to make the table name configurable by users instead of adding the workaround to the docs. We will keep this thread until someone :wink: will find the time to fix it:

I have the same issue as what initially discussed in this thread. I have mariadb on a remote server with a complete empty newlycreated database but still no tables are created.

2021-01-14 21:09:46.474 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::activate: persistence service activated
2021-01-14 21:09:46.494 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::updateConfig
2021-01-14 21:09:46.506 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::JdbcConfiguration
2021-01-14 21:09:46.524 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: configuration size = 12
2021-01-14 21:09:46.551 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: user=openhab
2021-01-14 21:09:46.555 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: password exists? true
2021-01-14 21:09:46.558 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: url=jdbc:mysql://remotehost:3306/openhab3?serverTimezone=Europe/Berlin
2021-01-14 21:09:46.571 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: found serviceName = ‘mysql’
2021-01-14 21:09:46.575 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: Init Data Access Object Class: ‘org.openhab.persistence.jdbc.db.JdbcMysqlDAO’
2021-01-14 21:09:46.591 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::initSqlTypes: Initialize the type array
2021-01-14 21:09:46.595 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::initSqlQueries: ‘JdbcMysqlDAO’
2021-01-14 21:09:46.599 [DEBUG] [hab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initSqlTypes: Initialize the type array
2021-01-14 21:09:46.603 [DEBUG] [hab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initSqlQueries: ‘JdbcMysqlDAO’
2021-01-14 21:09:46.606 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: dBDAO ClassName=org.openhab.persistence.jdbc.db.JdbcMysqlDAO
2021-01-14 21:09:46.611 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: errReconnectThreshold=5
2021-01-14 21:09:46.614 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: tableNamePrefix=Item
2021-01-14 21:09:46.618 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: tableUseRealItemNames=true
2021-01-14 21:09:46.622 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: tableIdDigitCount=0
2021-01-14 21:09:46.625 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: enableLogTime false
2021-01-14 21:09:46.701 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: load JDBC-driverClass was successful: ‘com.mysql.jdbc.Driver’
2021-01-14 21:09:46.706 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: configuration complete. service=jdbc
2021-01-14 21:09:46.715 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB
2021-01-14 21:09:46.719 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection isDriverAvailable: true
2021-01-14 21:09:46.723 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
2021-01-14 21:09:46.811 [WARN ] [.zaxxer.hikari.util.DriverDataSource] - Registered driver with driverClassName=com.mysql.jdbc.Driver was not found, trying direct instantiation.
2021-01-14 21:09:48.571 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::setDbConnected true
2021-01-14 21:09:48.574 [DEBUG] [hab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initAfterFirstDbConnection: Initializing step, after db is connected.
2021-01-14 21:09:48.650 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbMajorVersion = ‘5’
2021-01-14 21:09:48.663 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbMinorVersion = ‘5’
2021-01-14 21:09:48.667 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - driverMajorVersion = ‘8’
2021-01-14 21:09:48.670 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - driverMinorVersion = ‘0’
2021-01-14 21:09:48.675 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbProductName = ‘MySQL’
2021-01-14 21:09:48.692 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbProductVersion = ‘5.5.5-10.3.27-MariaDB-0+deb10u1’
2021-01-14 21:09:48.696 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB asking db for name as absolutely first db action, after connection is established.
2021-01-14 21:09:49.090 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::checkDBAcessability, first try connection: true
2021-01-14 21:09:49.094 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name ‘jdbc’ for queryable persistence service.
2021-01-14 21:09:49.098 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::updateConfig: configuration complete for service=jdbc.
2021-01-14 21:09:49.103 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name ‘jdbc’ for queryable persistence service.
2021-01-14 21:09:49.112 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name ‘jdbc’ for queryable persistence service.
2021-01-14 21:09:49.116 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name ‘jdbc’ for queryable persistence service.
2021-01-14 21:09:49.119 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name ‘jdbc’ for queryable persistence service.
2021-01-14 21:09:49.122 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name ‘jdbc’ for queryable persistence service.
2021-01-14 21:09:49.148 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB
2021-01-14 21:09:49.150 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection isDriverAvailable: true
2021-01-14 21:09:49.158 [ERROR] [nal.common.AbstractInvocationHandler] - An error occurred while calling method ‘QueryablePersistenceService.query()’ on ‘org.openhab.persistence.jdbc.internal.JdbcPersistenceService@1fae11b’: class java.lang.Integer cannot be cast to class java.lang.Long (java.lang.Integer and java.lang.Long are in module java.base of loader ‘bootstrap’)
java.lang.ClassCastException: class java.lang.Integer cannot be cast to class java.lang.Long (java.lang.Integer and java.lang.Long are in module java.base of loader ‘bootstrap’)
at org.openhab.persistence.jdbc.db.JdbcMysqlDAO.doPingDB(JdbcMysqlDAO.java:93) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcMapper.pingDB(JdbcMapper.java:65) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcMapper.checkDBAccessability(JdbcMapper.java:203) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.query(JdbcPersistenceService.java:155) ~[?:?]
at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
at 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:152) [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) [?:?]

If you’re running mariadb, why are you telling openHAB that it’s mysql in the db URL?

Edit: I don’t know if this is the cause of the problem. It just didn’t look right when I read through the log. I see the MariaDB DAO makes the same call.

sorry I have been trying so much back and forth. I get the same error message with jdbc:mariadb.

jdbc-mariadb and jdbc-mysql is the only persistence addon I have installed.

There is a problem, when openhab restarts, Tablename Suffix ID Count becomes ‘0’. Normally ‘3’ how can we solve this?

Are you sure that is all?
I noticed that ‘time’ columns changed from type datetime to timestamp. Not sure if there are more changes. Could not find all documentation.
Because all these changes where not clear to me at all, my db has exploded and i want to go back to a OH2.5 database backup and have to manually upgrade that to OH3 schema.
Is any upgrade script available?

Dear all

I have some similar issues since upgrading to OH3 (used mysql service with OH2.5 but had to change to JDBC) having mariaDB running on synology server and openhab on raspberry. What I tried so far:

  1. jdbc:mysql url=jdbc:mysql://192.168.xxx.xx:3306/openhab?serverTimezone=Europe/Berlin: does not connect at all (error messages in log)
  2. jdbc:maridb url=jdbc:mariadb://192.168.xxx.xx:3307/openhab with MariaDB10: does not connect at all (error messages in log)
  3. jdbc:maridb url=jdbc:mariadb://192.168.xxx.xx:3306/openhab with MariaDB5 does connect but following warning messages (no connection to database):

09:47:00.143 [WARN ] [.jdbc.internal.JdbcPersistenceService] - JDBC::store: No connection to database. Cannot persist item ‘Luxk (Type=NumberItem, State=6.75840000, Label=null, Category=null)’! Will retry connecting to database when error count:0 equals errReconnectThreshold:1

Does anybody has an idea what is wrong here? btw jdbc configuration file looks like that:

url=jdbc:mariadb://192.168.178.10:3306/openhab
user=xxxx
password=xxxxxxxxxx

Hi Mark

I have exactly the same log entries as you, see my post at the end of the thread. I cannot find the folder userdata, as described by you… where is it?

Thanks you in advance

@mhilbush I guess I found the file in /var/lib/openhab/config/org/openhab and I also found the line

reconnectCnt=“1”

I deleted the file, however it was created again. And also the warning (i.e. no connection to database) does not disappear. Do you have a hint how I can resolve this?

Yes, it’s normal for the file to be recreated.

Are you sure the database URL is correct in your jdbc.cfg file?

Hi Mark

Thank you for the reply

I’m pretty sure since the log file gives me quite some positive connection messages:

22:21:12.127 [INFO ] [.persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
22:21:12.189 [DEBUG] [tence.jdbc.internal.JdbcConfiguration] - JDBC::setDbConnected true
22:21:12.201 [DEBUG] [ab.persistence.jdbc.db.JdbcMariadbDAO] - JDBC::initAfterFirstDbConnection: Initializing step, after db is connected.
22:21:12.221 [DEBUG] [hab.persistence.jdbc.utils.DbMetaData] - dbMajorVersion = ‘5’
22:21:12.232 [DEBUG] [hab.persistence.jdbc.utils.DbMetaData] - dbMinorVersion = ‘5’
22:21:12.242 [DEBUG] [hab.persistence.jdbc.utils.DbMetaData] - driverMajorVersion = ‘1’
22:21:12.253 [DEBUG] [hab.persistence.jdbc.utils.DbMetaData] - driverMinorVersion = ‘3’
22:21:12.265 [DEBUG] [hab.persistence.jdbc.utils.DbMetaData] - dbProductName = ‘MySQL’
22:21:12.275 [DEBUG] [hab.persistence.jdbc.utils.DbMetaData] - dbProductVersion = ‘5.5.62-MariaDB’
22:21:12.286 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB asking db for name as absolutely first db action, after connection is established.
22:21:12.308 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::checkDBAcessability, first try connection: true
22:21:12.318 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name ‘jdbc’ for queryable persistence service.
22:21:12.328 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::updateConfig: configuration complete for service=jdbc.
22:21:12.340 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name ‘jdbc’ for queryable persistence service.
22:21:12.351 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name ‘jdbc’ for queryable persistence service.
22:21:12.361 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name ‘jdbc’ for queryable persistence service.
22:21:12.372 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name ‘jdbc’ for queryable persistence service.
22:21:12.381 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name ‘jdbc’ for queryable persistence service.
22:21:12.396 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name ‘jdbc’ for queryable persistence service.
22:22:00.687 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB
22:22:00.699 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection isDriverAvailable: true
22:22:00.712 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::checkDBAcessability, first try connection: true

but then - as in your case - the warning comes that it cannot connect to the database

[WARN ] [.jdbc.internal.JdbcPersistenceService] - JDBC::store: No connection to database. Cannot persist item ‘Luxk (Type=NumberItem, State=6.75840000, Label=null, Category=null)’! Will retry connecting to database when error count:0 equals errReconnectThreshold:1

What could be wrong here? With the old mysql service everything worked just perfect, I have this issue only after migration to OH3, since I was forced to move to JDBC

@mhilbush. sorry forgot to point the reply to your answer to you directly

I’m not sure. Can you post the contents of your services/jdbc.cfg file (editing out any sensitive information, of course).

Also, are you running Mysql or Mariadb?

Hi Mark,

Thank you very much for your relpy. Of course:

url=jdbc:mariadb://192.168.xxx.xx:3306/openhab
user=xxxx
password=xxxx

I’m runnning MariaDB 5 on my synology server. With mariaDB 10, there was no connection possible at all (i.e. error messages).

I’m not really sure what’s wrong. Your log messages above are missing the lines containing JDBC::updateConfig:, so it’s hard to tell what configuration is being used.

I suppose you could try explicitly setting reconnectCnt=0 in your services/jdbc.cfg, but you should only do that if you see it being set to 1 in the DEBUG log. For example, are you seeing something like this in your log?

2021-02-01 08:23:02.118 [DEBUG] [penhab.persistence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: errReconnectThreshold=1