On a relativly fresh 3.1 installation I wanted to enable mysql persistency using my mysql server, which is not the openhab server. So I thought this would be straightforward and found myself after hours of trying to figure out, what is not working.
So what did I do
Added the add-opn “JDBC Persistence MySQL”
Applied some changes in /srv/openhab-conf/services/jdbc.cfg
In "System Service → Persistence I finally enabled JDBC and removed the standard rrd
Later on I set JDBC and mysql package log to DEBUG, so that’s where I get the message from.
After restarting openhab I get something like this in the log
2021-11-13 00:26:04.998 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB
2021-11-13 00:26:04.999 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection isDriverAvailable: true
2021-11-13 00:26:05.007 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::checkDBAcessability, first try connection: true
2021-11-13 00:26:05.008 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::store: No connection to database. Cannot persist item 'DimmerRecepcion1_Signalstarke (Type=NumberItem, State=4, Label=Signalstärke, Category=QualityOfService, Tags=[Point], Groups=[DimmerRecepcion1])'! Will retry connecting to database when error count:0 equals errReconnectThreshold:2
To check the setup I did the following:
Connect from openhab server to mysql server using ssh (port + ip check): OK
Connect from openhab server to mysql server using mysql client: OK
Checking the mysql server log shows that at least a connection is made:
…
2021-11-12T23:25:31.504147Z 9403 [Note] Aborted connection 9403 to db: ‘openhab’ user: ‘openhab’ host: ‘10.30.255.1’ (Got an error reading communication packets)
…
Actually no tables are created, nothing is persisted, and yes, I verified the user is able to perform e.g. table creation with the native client.
So right now I’m stuck. Neither do me the log messages give any hint nor was I able to spot something in the forum what the problem could be. Bu I suppose it’s rather basic.
I disabled the default rrd4 configs by deleting them and disabling the service in the OH3 gui.
Would it be possible to do the following:
On the raspberry Pi: Install a fresh SQL or Mariadb sever (I use even docker on PIs for that) and try it?
On my setup mariadb and oh3 both run in docker and on the same machine.
just tried what you requested. Installed - locally - MariaDB on the openhab raspberry and reconfigured jdbc.cfg accordingly. Actually I set it up identically (user,pwd,schema etc.) to my regular Mysql server so I just had to change the IP.
The result is…somehow “interesting”: Up to the first log message where some value should be persisted, it looks absolutly identical from openhab’s log. With the local MariaDB I now get the following message:
failed notifying listener 'org.openhab.core.persistence.internal.PersistenceManagerImpl@16ae3b6' about state update of item Dimmerchiminea_Leistung: 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:95) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcMapper.pingDB(JdbcMapper.java:72) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcMapper.checkDBAccessability(JdbcMapper.java:211) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:136) ~[?:?]
at org.openhab.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:152) ~[?:?]
at org.openhab.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:473) ~[?:?]
at org.openhab.core.items.GenericItem.lambda$1(GenericItem.java:259) ~[?:?]
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:829) [?:?]
And maybe JFI the versions I’m using:
Mysql: 5.7.36-0ubuntu0.18.04.1
Mariadb: 5.5.5-10.3.31-MariaDB-0+deb10u1
It does look to me like mysql/mariadb ist not the first choice for openhab ,-))
After going through some threads in the community, I finally resolved it by chance. The problem was the setting reconnectCnt=0 which for some reason causes mariadb and mysql [driver] not to work properly. Deactivating this setting resolved issues for both databases.
I think OH documentation could be improved to avoid things like that. Going to JDBC - Persistence Services | openHAB it is provided that one must change services/jdbc.cfg, but as a newbie I did not found the location immediately. So went to good-ol`google search and was pointed to some bogus community entry with the above setting which I just copied. This obviously was an error.
However, pointing out, that the JDBC settings can be set via the UI (“Settings → Other Services → JDBC Persistence Service”) and without having to edit a file on the commandline would have resolved that, because in the UI this setting simply is not setable - and one would be not tempted to google erroneous results ,-))
Sorry, not ok ,-)) After looking into openhab.log I noticed that the connection seemed to got evicted for timeout on the server side and has not been reconnected.
java.sql.SQLTransientConnectionException: yank-default - Connection is not available, request timed out after 30000ms.
at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:555) ~[?:?]
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:188) ~[?:?]
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:147) ~[?:?]
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:83) ~[?:?]
at org.apache.commons.dbutils.AbstractQueryRunner.prepareConnection(AbstractQueryRunner.java:204) ~[?:?]
at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:455) ~[?:?]
at org.knowm.yank.Yank.execute(Yank.java:194) ~[?:?]
at org.knowm.yank.Yank.execute(Yank.java:177) ~[?:?]
at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doStoreItemValue(JdbcBaseDAO.java:333) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:156) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:143) ~[?:?]
at org.openhab.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:152) ~[?:?]
at org.openhab.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:473) ~[?:?]
at org.openhab.core.items.GenericItem.lambda$1(GenericItem.java:259) ~[?:?]
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:829) [?:?]
Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
This started after some 6~7 hours of putting values into the database. Obviously the reconnection mechanism is not working.
Also I find it quite strange to read then after the stacktrace messages like
2021-11-14 08:25:23.658 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'DimmerSala1_Helligkeit' as '100' in SQL database at Sun Nov 14 08:25:23 CET 2021 in 30035 ms.
which obviously is wrong.
======== EDIT ========
I noticed that my mysql server is set to default for connection timeout which is 28800 seconds or 8 hours. However, by default a jdbc connection is set with tcpKeepAlive=true by default and also I’m wondering, that the used jdbc library in OH3 does not automatically handle this.
Again, this seems so basic, I can’t be the only one facing this problem ,-) Maybe some jdbc.cfg parameters need to be tweaked accordingly?
Hi Sören,
influxdb is more often used together with openhab. As I use mariadb in other applications I decided to use mariadb for OH as well.
I don’t see any problems so far with my setup.
OH3 and mariadb run both in a Docker container on the same machine.
I can share the docker files and runs parameters if it would be helpfull to you.
However, what you see looks like a bug to me as well.
BR
Marco
Just a final one, and thanks Marco for looking into it. I really don’t know what went wrong with MySql, I’m using the same DB/server instance for other applications as well but with OH it’s the first time I run in such problems.
But never mind: I switched to influxdb, which worked instantenously. Maybe this kind of DB is anyhow more appropriate to time series than forcing mysql to do this job. We’ll see, thanks for your help !
