OH3 Persistence: RPi, MariaDB and Synology Using SSL: unable to recreate OH2.5 setup

Hi:
Using RPi i previously had OH2.5 successfully connected to a synology using SSL.

On my new OH3 installation using openhabian, I am using a jdbc.cfg file to provide the same configuration data as follows (which doesn’t work):

url=jdbc:mariadb://localIPaddress:3306/openhab1?useSSL=true&serverSslCert=/openHAB-userdata/etc/ssl/certs/ca.pem

When I use OH3 without the certificate, it works fine.

url=jdbc:mariadb://localIPaddress:3306/openhab1

Is there something wrong with the certificate location?
I believe I have correctly set user access (for both openhabian and for the router) on the Synology mariadb. It is the same certificate that I used previously on the OH2.5 installation.

To further clarify, between each failed attempt to login, I am setting mySQL admin command on the Synology

flush HOSTS

This appears to be important… I am also navigating to MainUI: Administration:Settings:Add-Ons:Persistence to confirm that “JDBC Persistence MariaDB” is indeed set up as a persistence service. I am then navigating to MainUI: Administration:Settings: Other Services:JDBC Persistence Service to check that the DSL file is correctly reflected (and clicking SAVE!), before then navigating to MainUI: Administration:Settings: SystemServices:Persistence to ensure that the radio button for “JDBC” is indeed available and ‘Checked’.

Log errors include the following:

Could not get service from ref {org.openhab.core.persistence.PersistenceService, org.openhab.core.persistence.QueryablePersistenceService}={service.id=541, service.bundleid=250, service.scope=bundle,…

I am convinced this is a cert file location issue. For example the need to include “static” for image files (e.g. https://community.openhab.org/t/url-to-serve-file-from-html-directory/81383) was news to me - is something similar happening here?)

Any suggestions appreciated !

  • is there any log entry in the SQL server’s log file ?
  • is there any log entry in openhab’s log file ( more than shown in your post ) ?
  • is that a self signed certificate ? Then as far as I understand it need to be trusted
  • even in case the file location of your cert would be wrong the thread you are refer to is about a complete different topic technically not related
  • see also: Using TLS/SSL with MariaDB Connector/J - MariaDB Knowledge Base

thanks for replying.
Yes self-signed certificate - which was used successfully previously.

Regarding location of the file - this is the place I found the answer - couldn’t find any reference to “static” in the Openhab Documentation… but apparently this is a thing…

Yes I have already trawled the TLS / SSL link you shared. I don’t think this is a database-side issue, but a problem of how the Openhabian system locates a cert file… I wonder is there a permissions issue related to where I have saved the certificate? I wouldn’t know how to solve that…

Openhab Log file detail below.

2022-04-18 22:33:46.554 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
2022-04-18 22:33:46.562 [ERROR] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.1.0 (250)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(270)] : The activate method has thrown an exception
at org.openhab.persistence.jdbc.internal.JdbcMapper.openConnection(JdbcMapper.java:187) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcMapper.pingDB(JdbcMapper.java:64) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcMapper.checkDBAccessability(JdbcMapper.java:211) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.updateConfig(JdbcPersistenceService.java:219) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.activate(JdbcPersistenceService.java:83) ~[?:?]
at org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:136) ~[?:?]
at org.mariadb.jdbc.internal.util.ExceptionMapper.throwException(ExceptionMapper.java:69) ~[?:?]
at org.mariadb.jdbc.Driver.connect(Driver.java:110) ~[?:?]
Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: /192.168.0.115:8080/openHAB-userdata/etc/ssl/certs/ca.pem (No such file or directory)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.getSslSocketFactory(AbstractConnectProtocol.java:258) ~[?:?]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.handleConnectionPhases(AbstractConnectProtocol.java:430) ~[?:?]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connect(AbstractConnectProtocol.java:358) ~[?:?]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:713) ~[?:?]
at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:471) ~[?:?]
at org.mariadb.jdbc.Driver.connect(Driver.java:105) ~[?:?]
at org.mariadb.jdbc.internal.MyX509TrustManager.(MyX509TrustManager.java:114) ~[?:?]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.getSslSocketFactory(AbstractConnectProtocol.java:246) ~[?:?]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.handleConnectionPhases(AbstractConnectProtocol.java:430) ~[?:?]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connect(AbstractConnectProtocol.java:358) ~[?:?]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:713) ~[?:?]
at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:471) ~[?:?]
at org.mariadb.jdbc.Driver.connect(Driver.java:105) ~[?:?]
2022-04-18 22:33:46.602 [WARN ] [ernal.PersistenceServiceRegistryImpl] - bundle org.openhab.core.persistence:3.1.0 (210)[org.openhab.core.persistence.internal.PersistenceServiceRegistryImpl(50)] : Could not get service from ref {org.openhab.core.persistence.PersistenceService, org.openhab.core.persistence.QueryablePersistenceService}={service.id=545, service.bundleid=250, service.scope=bundle, user=openhab1, url=jdbc:mariadb://DBIPAddress:3306/openhab1?useSSL=true&serverSslCert=//RPiIPAddress:8080/openHAB-userdata/etc/ssl/certs/ca.pem, component.name=org.openhab.persistence.jdbc.internal.JdbcPersistenceService, service.config.label=JDBC Persistence Service, component.id=270, service.config.factory=false, password=xxxxxx, tableUseRealItemNames=true, useSSL=true, rebuildTableNames=false, ssl_ca=/etc/ssl/certs/ca.pem, service.config.category=persistence, service.config.description.uri=persistence:jdbc, service.pid=[org.openhab.jdbc, org.openhab.jdbc]}

Don’t think I have logs enabled on the SQL server - will try to change that.

Most grateful for any further input !

This

from the log looks different ( check serverSslCert= ) to what your first post shows:

I think it needs to be the path that is used inside of Synology - that is not //RPiIPAddress:8080.

Is DBIPAdress the real entry or did you change it’s IP address to this string in your post ?

I had changed it at some point from Pi IP addresss to openhabian- have tried many variations to try to get it to work - sorry for introducing another variable; pls just assume these are the same location on the Pi, written using the same address.

I am actually using an IP address (192.168…). This kind of error I am able to avoid… ; )

Though as I understand it the location of the cert does indeed need to be on the RPi - I believe this is “one way client side TLS verification”… Apologies for the quotes this is all new to me…