Hi
I noticed some DB connection issues after I rebuilt a new Raspi4 with OH3.3. After checking DB configuration and permission, I searched for other hints, but I only found some older posts which are file based configurations, while my OH is configured over the GUI. Anyway, most of them are only about configuration, which I assume is not the issue here.
I use a Maria-DB which is running on my QNAP-NAS in the same subnet like my Raspi4.
After configure the persistence service I can see the follow output:
2022-10-03 18:52:43.239 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
2022-10-03 18:52:43.246 [WARN ] [.zaxxer.hikari.util.DriverDataSource] - Registered driver with driverClassName=com.mysql.jdbc.Driver was not found, trying direct instantiation.
==> /var/log/openhab/events.log <==
2022-10-03 18:52:55.537 [INFO ] [openhab.event.ItemStateChangedEvent ] - Item 'MS02TemperatureHumiditySensor_Temperature' changed from 22.93255131964809 °C to 22.854349951124142 °C
==> /var/log/openhab/openhab.log <==
2022-10-03 18:53:13.289 [WARN ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: failed to open connection: Failed to initialize pool: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
2022-10-03 18:53:13.292 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::logTime: 'pingDB':
afterAccess = 30053 ms
timeAverage50 = 30053 ms
timeAverage100 = 30053 ms
timeAverage200 = 30053 ms
afterAccessMin = 10000 ms
afterAccessMax = 30053 ms
1000Statements = 0 sec
statementCount = 1
2022-10-03 18:53:13.295 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
2022-10-03 18:53:13.302 [WARN ] [.zaxxer.hikari.util.DriverDataSource] - Registered driver with driverClassName=com.mysql.jdbc.Driver was not found, trying direct instantiation.
2022-10-03 19:31:02.135 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::store: No connection to database. Cannot persist state '86' for item 'MS02TemperatureHumiditySensor_Batterieladung (Type=NumberItem, State=86, Label=Batterieladung, Category=Battery, Tags=[Measurement, Energy])'! Will retry connecting to database when error count:0 equals errReconnectThreshold:0
^C
The logfile says that the DB is reachable, but not connectable. What I am confused about is the log about the driver which is not found, is it important or not?.
Does anybody have some hint how to go further and what to focus on?
By the way, I have another Raspi running which uses an identical DB on my NAS.
It’s very likely the problem. The driver is the part that actually knows how to speak to the database.
Beyond that I can’t be of much help. You might try uninstalling the add-on and reinstalling it to see if something went funky with the installation. Definitely review the docs for the add-on to make sure you don’t need to do anything external to make it work, such as download and install the driver jar file yourself or the like.
I tried to reinstall, reboot and so on different times, but without any conclusion. By the way, the link on the Addon for the documantionen is unfortunately a dead link, but let me see if I find something in the documentation.
That’s a problem unto itself. The JDBC docs are here. It lists a tested driver but doesn’t say whether you need to do anything to download or use that driver.
I’ve never used JDBC with openHAB so I know no more than what’s in the docs.
Since the last post I did a lot of things trying to find out where to search for possible cause of this issue.
I used a new db-server to check if the issue could have his cause on the db-server. I played a little bit with the configuration. I played a little bit around and I think I can say that the reason for my issue must have be on the raspberry pi (OH).
Why this conclusion:
When I change the persistence configuration to
jdbc:mariadb://server:port…
instead of
jdbc:mysql://server:port…
OH finds a driver, like you see in the follwing log.
2022-10-07 19:17:59.465 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
2022-10-07 19:20:10.527 [WARN ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: failed to open connection: Failed to initialize pool: Could not connect to address=(host=192.168.7.112)(port=3307)(type=master) : Connection timed out (Connection timed out)
But the DB is still not reachable. What I was wondering also is, that if I change the destination IP in the persistence configuration to a unreachable address, the log looks still the same. So I think OH is not sending any paket to the DB.
I found also this post which for me looks like maybe the same issue I have. But I don’t know the difference between JPA persistance and my configuration. I assume that the protocol below is the same…:
I appreciate every hint or idea to enclose my issue.
I found a workaround for my issue. On the raspberry I switched off the wifi and plugged in the LAN. As soon as a had connectivity the DB connection cames up.
2022-10-07 20:05:07.082 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
2022-10-07 20:05:07.120 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::logTime: 'pingDB':
afterAccess = 38 ms
timeAverage50 = 38 ms
timeAverage100 = 38 ms
timeAverage200 = 38 ms
afterAccessMin = 38 ms
afterAccessMax = 38 ms
1000Statements = 0 sec
statementCount = 1
2022-10-07 20:05:07.125 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::logTime: 'doCreateItemsTableIfNot':
afterAccess = 2 ms
timeAverage50 = 20 ms
timeAverage100 = 20 ms
timeAverage200 = 20 ms
afterAccessMin = 2 ms
afterAccessMax = 38 ms
1000Statements = 0 sec
statementCount = 2
2022-10-07 20:05:07.129 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::logTime: 'getItemIDTableNames':
afterAccess = 1 ms
timeAverage50 = 13 ms
timeAverage100 = 13 ms
timeAverage200 = 13 ms
afterAccessMin = 1 ms
afterAccessMax = 38 ms
1000Statements = 0 sec
statementCount = 3
The persistence config is:
jdbc:mariadb://192.168.7.112:3306
Looks like there is an issue with using the wifi when connecting to a db.
Hi Volks,
The issue is back again and it is also not running with LAN. It looks like the standard driver class can’t be used somehow.
2022-11-23 19:11:50.553 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
2022-11-23 19:11:50.702 [WARN ] [.zaxxer.hikari.util.DriverDataSource] - Registered driver with driverClassName=com.mysql.jdbc.Driver was not found, trying direct instantiation.
2022-11-23 19:12:21.469 [WARN ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: failed to open connection: Failed to initialize pool: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
Just yersterday I tried to switch the persistence on my openhabian installation (3.3) from RRD4j to a MySQL-Server, running on my Qnap NAS. After such errors and reading though a lot of postings here that looked like: “No real answer” or leading to an approach in which the MySQL JDBC-Driver was replaced by the MariaDB-Driver (although using a MySQL-Server) I gave this a try, too. What more to say: This worked the very moment I installed it, and set my connection sting to jdbc:mariadb://192.168.0.200:3306/openhab (were openhab is the database I set up in advance on the server using phpMyAdmin) in the GUI.
From all the reported problems there should be a discussion to delete the MySQL-driver from the official addons directory and declare the MariaDB as the standard to use MySQL oder MariaDB Servers for persistence.
There is not much to “build up new”. Just go to “other Add-ons”, install the JDBC Persistence MariaDB and remove the JDBC Persistence MySQL. The configuration of the JDBC Persistence will survive this procedure. If your connection string does not already looks like jdbc:mariadb://<server ip>:<server port>/<database> this is the only thing to be altered. The whole procedure should not take more than five minutes.
