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.
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?
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:
jdbc:mysql url=jdbc:mysql://192.168.xxx.xx:3306/openhab?serverTimezone=Europe/Berlin: does not connect at all (error messages in log)
jdbc:maridb url=jdbc:mariadb://192.168.xxx.xx:3307/openhab with MariaDB10: does not connect at all (error messages in log)
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:
@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?
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
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?
A miracle has happened, JDBC is writing to the database you made my day!!! I tried hours and days without any clue, thank you so much!!!
But please can you explain me this behavior? The only thing, which I see now, is that it will probably create new items for my exiting ones, because of the Item vs item issue and the 00 in front of the numbers of the items, where there are none with the old mysql service
Even though you deleted the jdbc.config file in the userdata directory, I suspect it was still recreated with the same reconnectCnt=1 value. Since that parameter wasn’t specified in your jdbc.cfg file, it continued to use the value in the shadow config file until you overrode it by setting it explicitly.
