I’m using OH2 since about 4 years and so far I have always found solutions for problems that I occasionally run into searching through this side. Great work that had been done.
Now I tried to upgrade to OH3.3 mostly to capture up with actual development and the ability to engage things that are not in OH2. The overall migration went smoothly except the fact that my sqlite based persistence does not work.
My SBC is a odroidc2 with the actual armbian installed:
uname -a
Linux odr2c2 5.10.123-meson64 #22.05.3 SMP PREEMPT Wed Jun 22 07:23:04 UTC 2022 aarch64 GNU/Linux
On OH2 I have installed a sqlite-jdbc-3.23.1.jar in the addons folder but I could not remember why I did that, probably it was a work-around.
With the migration I left out this file and used the UI “Other Add-ons” to have OH install sqlite for me.
The DB file is on the filesystem and the config reads:
jdbc:sqlite:./Hist_Sqlite.db
On every persistence access the logfile repeats:
2022-09-19 16:18:43.563 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
2022-09-19 16:18:43.887 [WARN ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: failed to open connection: Failed to initialize pool: Error opening connection
openhab> list -l | grep jdbc
279 │ Active │ 80 │ 3.3.0 │ mvn:org.openhab.addons.bundles/org.openhab.persistence.jdbc/3.3.0
280 │ Active │ 80 │ 3.16.1 │ mvn:org.xerial/sqlite-jdbc/3.16.1
It looks like things are installed correctly, but my suspicion is the xerial/sqlite-jdbc/3.16.1.
Looking into the jar archive I see that there is no version for aarch64 provided. Probably that was the reason why there was sqlite-jdbc-3.23.1.jar in the past which has this variant included.
Now it would like to know how I could tell OH to install a newer version of sqlite-jdbc. Simply copying the file to the addon location produces a version error in OH
The newer version seem also to include aarch64 libsqlitejdbc.so files.
But I do not have an answer to the question if this library is compatible to OH3 reps. what is required to make it compatible.
On the github for the mvnrepository I found that arm64 starts with version 3.20.0.
I think it is compatible because sqlite-jdbc-3.23.1.jar worked on the same SBC with OH2 before I migrated to OH3.
So where can I twek OH to download such a version >= 3.20.0.
I have a clone of my OH2 partition so I can retry the changes as often as required, or until I get bored.
I made some progress and found out that in OH3 at least one of the drivers must be installed so that org.openhab.persistence.jdbc gets loaded. I first tried to select sqlite also from the UI and as well copied sqlite-jdbc-3.23.1.jar to the addons folder. This results in an error because OH detects two versions of the sqlite driver. A workaround is to select any other persistence driver i.e. mariadb etc. and keep the required sqlite driver in the addon folder. After this, my sqlite access started working and I was able to access all the history and statistic values.
A final solution should certainly change the main repository so that a newer version of the sqlite driver gets referenced in org.openhab.persistence.jdbc.
I have the file on git where to change the referenced version for OH3 but I have no idea how to decide on what newer version of the sqlite driver would be best. The maven repository is at version 3.39.x meanwhile.
After quite some month I found time and motivation to further dig into a solution.
Luckily I recognized that someone else has updated the code so that OH3.4 now installs sqlite version 3.40.0.0. So my problem seems to be solved.
