[Solved] My first encounter with OH 3.1.0-M1: Can't solve errors - I have to fall back to 2.5.12

ohb1138 · March 7, 2021, 2:27pm

Hi all,
my OpenHAB environment runs on a Synology NAS in a docker container and I am leveraging the MARIADB v10 running on my NAS. I am currently running OH v2.5.12 and tried today to move to OH 3.1.0-M1. I experienced two major issues that let me fall back on my working 2.5.12 environment because this runs without any probelms.
But maybe you can help me solve the two issues so that I can migrate

The first issue seems to have to do with the fact that I still use .rules files as I have so complex rules that I refrain from rewriting them in ECMA. I am no programmer and hence I have too much respect to migrate my rules … Maybe there is another way to get this issue solved:

14:57:04.234 [WARN ] [org.eclipse.jetty.server.HttpChannel ] - /rest/addons
java.lang.NullPointerException: null
	at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:267) ~[?:?]
	at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:234) ~[?:?]
	at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:208) ~[?:?]
	at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:160) ~[?:?]
	at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:216) ~[?:?]
	at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:301) ~[?:?]
	at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doGet(AbstractHTTPServlet.java:225) ~[?:?]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:687) ~[bundleFile:3.1.0]
	at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:276) ~[?:?]
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:852) ~[bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:544) ~[bundleFile:9.4.20.v20190813]
	at org.ops4j.pax.web.service.jetty.internal.HttpServiceServletHandler.doHandle(HttpServiceServletHandler.java:71) ~[bundleFile:?]
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143) ~[bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:536) ~[bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127) ~[bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235) ~[bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1581) ~[bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233) ~[bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1307) ~[bundleFile:9.4.20.v20190813]
	at org.ops4j.pax.web.service.jetty.internal.HttpServiceContext.doHandle(HttpServiceContext.java:293) ~[bundleFile:?]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188) ~[bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:482) ~[bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1549) ~[bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186) ~[bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1204) ~[bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) ~[bundleFile:9.4.20.v20190813]
	at org.ops4j.pax.web.service.jetty.internal.JettyServerHandlerCollection.handle(JettyServerHandlerCollection.java:80) ~[bundleFile:?]
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127) ~[bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.server.Server.handle(Server.java:494) ~[bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:374) [bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:268) [bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311) [bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103) [bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117) [bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:782) [bundleFile:9.4.20.v20190813]
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:918) [bundleFile:9.4.20.v20190813]
	at java.lang.Thread.run(Thread.java:834) [?:?]
14:57:04.393 [WARN ] [f.transport.servlet.ServletController] - Can't find the request for http://richcube:9080/rest/items's Observer

The second issue is really preventing me from migrating as I do not get my mysql persistence up and running. I tried some adjustments but nothing worked and after some hours I needed to stop trying and reactivate my “production” environment to have OpenHAB back up and running for my home automation Find below the dump information and my jdbc-mysql related config files:

14:57:46.394 [INFO ] [.persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
14:57:46.396 [WARN ] [m.zaxxer.hikari.util.DriverDataSource] - Registered driver with driverClassName=com.mysql.jdbc.Driver was not found, trying direct instantiation.
14:57:46.401 [ERROR] [org.openhab.persistence.jdbc         ] - bundle org.openhab.persistence.jdbc:3.1.0.M1 (249)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(305)] : The activate method has thrown an exception
com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Access denied for user ''@'172.17.0.4' (using password: NO)
	at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:516) ~[?:?]
	at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:118) ~[?:?]
	at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:71) ~[?:?]
	at org.knowm.yank.YankPoolManager.createPool(YankPoolManager.java:101) ~[?:?]
	at org.knowm.yank.YankPoolManager.addDefaultConnectionPool(YankPoolManager.java:69) ~[?:?]
	at org.knowm.yank.Yank.setupDefaultConnectionPool(Yank.java:705) ~[?:?]
	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 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.apache.felix.scr.impl.inject.methods.BaseMethod.invokeMethod(BaseMethod.java:228) ~[bundleFile:?]
	at org.apache.felix.scr.impl.inject.methods.BaseMethod.access$500(BaseMethod.java:41) ~[bundleFile:?]
	at org.apache.felix.scr.impl.inject.methods.BaseMethod$Resolved.invoke(BaseMethod.java:664) ~[bundleFile:?]
	at org.apache.felix.scr.impl.inject.methods.BaseMethod.invoke(BaseMethod.java:510) [bundleFile:?]
	at org.apache.felix.scr.impl.inject.methods.ActivateMethod.invoke(ActivateMethod.java:317) [bundleFile:?]
	at org.apache.felix.scr.impl.inject.methods.ActivateMethod.invoke(ActivateMethod.java:307) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.createImplementationObject(SingleComponentManager.java:340) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.createComponent(SingleComponentManager.java:114) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.getService(SingleComponentManager.java:982) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.getServiceInternal(SingleComponentManager.java:955) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.getService(SingleComponentManager.java:900) [bundleFile:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse$1.run(ServiceFactoryUse.java:212) [org.eclipse.osgi-3.12.100.jar:?]
	at java.security.AccessController.doPrivileged(Native Method) [?:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.factoryGetService(ServiceFactoryUse.java:210) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.getService(ServiceFactoryUse.java:111) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceConsumer$2.getService(ServiceConsumer.java:45) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.getService(ServiceRegistrationImpl.java:508) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.getService(ServiceRegistry.java:461) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.getService(BundleContextImpl.java:624) [org.eclipse.osgi-3.12.100.jar:?]
	at org.apache.felix.scr.impl.manager.SingleRefPair.getServiceObject(SingleRefPair.java:86) [bundleFile:?]
	at org.apache.felix.scr.impl.inject.BindParameters.getServiceObject(BindParameters.java:47) [bundleFile:?]
	at org.apache.felix.scr.impl.inject.methods.BindMethod.getServiceObject(BindMethod.java:664) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.DependencyManager.getServiceObject(DependencyManager.java:2308) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.DependencyManager.doInvokeBindMethod(DependencyManager.java:1805) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.DependencyManager.invokeBindMethod(DependencyManager.java:1788) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.invokeBindMethod(SingleComponentManager.java:435) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.DependencyManager$MultipleDynamicCustomizer.addedService(DependencyManager.java:325) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.DependencyManager$MultipleDynamicCustomizer.addedService(DependencyManager.java:294) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.ServiceTracker$Tracked.customizerAdded(ServiceTracker.java:1216) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.ServiceTracker$Tracked.customizerAdded(ServiceTracker.java:1137) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.ServiceTracker$AbstractTracked.trackAdding(ServiceTracker.java:944) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.ServiceTracker$AbstractTracked.track(ServiceTracker.java:880) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.ServiceTracker$Tracked.serviceChanged(ServiceTracker.java:1168) [bundleFile:?]
	at org.apache.felix.scr.impl.BundleComponentActivator$ListenerInfo.serviceChanged(BundleComponentActivator.java:125) [bundleFile:?]
	at org.eclipse.osgi.internal.serviceregistry.FilteredServiceListener.serviceChanged(FilteredServiceListener.java:109) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:920) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:148) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEventPrivileged(ServiceRegistry.java:862) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEvent(ServiceRegistry.java:801) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.register(ServiceRegistrationImpl.java:127) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.registerService(ServiceRegistry.java:225) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.registerService(BundleContextImpl.java:469) [org.eclipse.osgi-3.12.100.jar:?]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager$3.register(AbstractComponentManager.java:906) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager$3.register(AbstractComponentManager.java:892) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.RegistrationManager.changeRegistration(RegistrationManager.java:128) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.registerService(AbstractComponentManager.java:959) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.activateInternal(AbstractComponentManager.java:732) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.enableInternal(AbstractComponentManager.java:666) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.enable(AbstractComponentManager.java:432) [bundleFile:?]
	at org.apache.felix.scr.impl.manager.ConfigurableComponentHolder.enableComponents(ConfigurableComponentHolder.java:665) [bundleFile:?]
	at org.apache.felix.scr.impl.BundleComponentActivator.initialEnable(BundleComponentActivator.java:338) [bundleFile:?]
	at org.apache.felix.scr.impl.Activator.loadComponents(Activator.java:382) [bundleFile:?]
	at org.apache.felix.scr.impl.Activator.access$200(Activator.java:49) [bundleFile:?]
	at org.apache.felix.scr.impl.Activator$ScrExtension.start(Activator.java:264) [bundleFile:?]
	at org.apache.felix.scr.impl.AbstractExtender.createExtension(AbstractExtender.java:196) [bundleFile:?]
	at org.apache.felix.scr.impl.AbstractExtender.modifiedBundle(AbstractExtender.java:169) [bundleFile:?]
	at org.apache.felix.scr.impl.AbstractExtender.modifiedBundle(AbstractExtender.java:49) [bundleFile:?]
	at org.osgi.util.tracker.BundleTracker$Tracked.customizerModified(BundleTracker.java:482) [osgi.core-6.0.0.jar:?]
	at org.osgi.util.tracker.BundleTracker$Tracked.customizerModified(BundleTracker.java:415) [osgi.core-6.0.0.jar:?]
	at org.osgi.util.tracker.AbstractTracked.track(AbstractTracked.java:232) [osgi.core-6.0.0.jar:?]
	at org.osgi.util.tracker.BundleTracker$Tracked.bundleChanged(BundleTracker.java:444) [osgi.core-6.0.0.jar:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:908) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:148) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.internal.framework.EquinoxEventPublisher.publishBundleEventPrivileged(EquinoxEventPublisher.java:213) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.internal.framework.EquinoxEventPublisher.publishBundleEvent(EquinoxEventPublisher.java:120) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.internal.framework.EquinoxEventPublisher.publishBundleEvent(EquinoxEventPublisher.java:112) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.internal.framework.EquinoxContainerAdaptor.publishModuleEvent(EquinoxContainerAdaptor.java:168) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.container.Module.publishEvent(Module.java:476) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.container.Module.start(Module.java:467) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.incStartLevel(ModuleContainer.java:1634) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.incStartLevel(ModuleContainer.java:1614) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.doContainerStartLevel(ModuleContainer.java:1585) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.dispatchEvent(ModuleContainer.java:1528) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.dispatchEvent(ModuleContainer.java:1) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230) [org.eclipse.osgi-3.12.100.jar:?]
	at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:340) [org.eclipse.osgi-3.12.100.jar:?]
Caused by: java.sql.SQLException: Access denied for user ''@'172.17.0.4' (using password: NO)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[?:?]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[?:?]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[?:?]
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836) ~[?:?]
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456) ~[?:?]
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246) ~[?:?]
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198) ~[?:?]
	at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:95) ~[?:?]
	at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:316) ~[?:?]
	at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:506) ~[?:?]
	... 92 more

my addons.cfg:

#
# See http://docs.openhab.org/configuration/packages.html for a detailed explanation of these packages.
#
package = standard

# Access Remote Add-on Repository
# Defines whether the remote openHAB add-on repository should be used for browsing and installing add-ons.
# This not only makes latest snapshots of add-ons available, it is also required for the installation of
# any legacy 1.x add-on. (default is true)
#
remote = true

# Include legacy 1.x bindings. If set to true, it also allows the installation of 1.x bindings for which there is
# already a 2.x version available (requires remote repo access, see above). (default is false)
#
#legacy = true

# A comma-separated list of bindings to install (e.g. "sonos,knx,zwave")
binding = km200,astro,exec,mail,openweathermap,mqtt,upnpcontrol,hue,shelly,avmfritz

# A comma-separated list of UIs to install (e.g. "basic,paper")
ui = basic,habpanel

# A comma-separated list of persistence services to install (e.g. "rrd4j,jpa")
persistence = jdbc-mysql

# A comma-separated list of actions to install (e.g. "mail,pushover")
# action =

# A comma-separated list of transformation services to install (e.g. "map,jsonpath")
transformation = map,regex,jsonpath,javascript,exec,scale

# A comma-separated list of voice services to install (e.g. "marytts,freetts")
#voice =

# A comma-separated list of miscellaneous services to install (e.g. "myopenhab")
# misc =

I have created several files to have the right persistence file mysql.persist and jdbc.persist and jdbc-mysql.persist. They all look like:

Strategies {
    every5Minutes : "0 0/5 * * * ?"
    default = everyChange, restoreOnStartup
}

Items {
    * : strategy = everyChange, every5Minutes, restoreOnStartup
}

My jdbc.cfg

############################ JDBC Persistence Service ##################################
# I N S T A L L   J D B C   P E R S I S T E N C E   S E R V I C E
#
# https://github.com/openhab/openhab/wiki/JDBC-Persistence
#
# Tested databases/url-prefix: jdbc:derby, jdbc:h2, jdbc:hsqldb, jdbc:mariadb, jdbc:mysql, jdbc:postgresql, jdbc:sqlite
#
# derby, h2, hsqldb, sqlite can be embedded,
# If no database is available it will be created, for example the url 'jdbc:h2:./testH2' creates a new DB in OpenHab Folder.
#
# Create new database, for example on a MySQL-Server use:
# CREATE DATABASE 'yourDB' CHARACTER SET utf8 COLLATE utf8_general_ci;

# D A T A B A S E  C O N F I G
# Some URL-Examples, 'service' identifies and activates internally the correct jdbc driver.
# required database url like 'jdbc:<service>:<host>[:<port>;<attributes>]'
# url=jdbc:derby:./testDerby;create=true
# url=jdbc:h2:./testH2
# url=jdbc:hsqldb:./testHsqlDb
# url=jdbc:mariadb://192.168.0.1:3306/testMariadb
# url=jdbc:mysql://192.168.0.1:3306/testMysql
# url=jdbc:postgresql://192.168.0.1:5432/testPostgresql
# url=jdbc:sqlite:./testSqlite.db
# url=
url=jdbc:mysql://192.1.0.8:3307/openhab?useLegacyDatetimeCode=false&serverTimezone=Europe/Berlin
# required database user
mysql:user=XXX

# required database password
mysql:password=YYY

mysql:default_time_zone='Europe/Berlin'

# E R R O R   H A N D L I N G
# optional when Service is deactivated (optional, default: 0 -> ignore)
#errReconnectThreshold=0
mysql:reconnectCnt=3
mysql:waitTimeout=28800

# I T E M   O P E R A T I O N S
# optional tweaking SQL datatypes
# see: https://mybatis.github.io/mybatis-3/apidocs/reference/org/apache/ibatis/type/JdbcType.html
# see: http://www.h2database.com/html/datatypes.html
# see: http://www.postgresql.org/docs/9.3/static/datatype.html
# defaults:
#sqltype.CALL          =   VARCHAR(200)
#sqltype.COLOR         =   VARCHAR(70)
#sqltype.CONTACT       =   VARCHAR(6)
#sqltype.DATETIME      =   DATETIME
#sqltype.DIMMER        =   TINYINT
#sqltype.LOCATION      =   VARCHAR(30)
#sqltype.NUMBER        =   DOUBLE
#sqltype.ROLLERSHUTTER =   TINYINT
#sqltype.STRING        =   VARCHAR(1024)
#sqltype.SWITCH        =   VARCHAR(6)
mysql:sqltype.string=VARCHAR(1024)

# For Itemtype "Number" default decimal digit count (optional, default: 3)
#numberDecimalcount=

# T A B L E   O P E R A T I O N S
# Tablename Prefix String (optional, default: "item")
# for Migration from MYSQL-Bundle set to 'Item'.
#tableNamePrefix=Item

# Tablename Prefix generation, using Item real names or "item" (optional, default: false -> "item")
# If true, 'tableNamePrefix' is ignored.
#tableUseRealItemNames=

# Tablename Suffix length (optional, default: 4 -> 0001-9999)
# for Migration from MYSQL-Bundle set to 0.
#tableIdDigitCount=

# Rename existing Tables using tableUseRealItemNames and tableIdDigitCount (optional, default: false)
# USE WITH CARE! Deactivate after Renaming is done!
#rebuildTableNames=true

# D A T A B A S E  C O N N E C T I O N S
# Some embeded Databases can handle only one Connection (optional, default: configured per database in packet org.openhab.persistence.jdbc.db.* )
# see: https://github.com/brettwooldridge/HikariCP/issues/256
# jdbc.maximumPoolSize = 1
# jdbc.minimumIdle = 1

# T I M E K E E P I N G
# (optional, default: false)
#enableLogTime=true

and finally my mysql.cfg

# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
# url=jdbc:mysql://9987578f9eb9:3306/openhab
# url=jdbc:mysql://localhost:3306/openhab
url=jdbc:mysql://192.1.0.8:3307/openhab?useLegacyDatetimeCode=false&serverTimezone=Europe/Berlin
user=XXX
password=YYY
reconnectCnt=3

default_time_zone='Europe/Berlin'

# the connection timeout (in seconds)
waitTimeout=28800

# Use MySQL Server time to store item values (=false) or use openHAB Server time (=true).
# For new installations, its recommend to set "localtime=true".
# (optional, defaults to false)
#localtime=true

# Tweak String VAR size
# sqltype.string=VARCHAR(65500)
sqltype.string=VARCHAR(1024)

Thank you for any advice!

Cheers Justus

Bruce_Osborne · March 7, 2021, 3:41pm

Why did you skip the OH 3.0.1 stable version of code?
It is a newer core codebase than 2.5.12 and will prepare you for 3.1.0 whenever it is stably released.

hmerk · March 7, 2021, 4:02pm

What is your Java version ??

ohb1138 · March 7, 2021, 4:51pm

Ok, I thought using 3.1.0 - Milestone Buld would be quite stable

ohb1138 · March 7, 2021, 4:52pm

It is the JAVA version that is delivered with the container. When I remember right it is JAVA 11.

Bruce_Osborne · March 7, 2021, 6:03pm

There are 3 different release chains with different stability expectations

Snapshots - usually release nightly and can be very unstable
Testing - periodic Milestone builds to get further big testing & later, there are Release Candidate builds.
Stable - Releases expected to be stable for general production use.

Milestone builds are not expected to be stable for production use.

ohb1138 · March 7, 2021, 6:33pm

Thank you … seems that I have run too far I will give it a try tomorrow using the 3.0.1.
Maybe the JAVA version fixes my persistence problem as it is most likely a lower version that in the 3.1.0. … But I am not sure if my first mentioned problem will disappear …

I kindly appreciate your advice and will try leveling down first!

Bruce_Osborne · March 7, 2021, 6:35pm

openHAB 3.X requires Java 11 & 2.5 requires Java 8.

ohb1138 · March 7, 2021, 8:26pm

Mmmmmh, ok. Then OH 3 is definitely JAVA 11 … have to check if the jdbc driver will still have issues … will test during the week …

rlkoshak · March 8, 2021, 5:30pm

That error has nothing to do with your .rules files. It seems to have something to do with the REST API or the Jetty server. It’s not clear what could be causing it without a whole lot more information.
The error points at MySQL refusing the connection from openHAB. '@'172.17.0.4 certainly doesn’t look like a user to me so I’d first check the configs. I’d guess that openHAB is trying to connect to localhost without username and password.

I’ve not used JDBC but I think you can configure the user/password and URL stuff in MainUI. Given the error received, it does not appear to be using your jdbc.cfg file. Maybe it needs to be jdbc-mysql.cfg?

ohb1138 · March 8, 2021, 5:51pm

Hi @rlkoshak,
thank you for this great advice.

I actually do not understand how/if the REST-API is available in OH3.x. I found that “restdocs” binding entry in the addons.cfg file does not work anymore and hence I removed it. Nevertheless this error occurs … Can you tell me what data resp. information I need to collect to find the reason for this problem?
Sometimes it needs a second pair of eyes to see the problem … you are right that this can not work. There is unfortunately no documentation how the cfg files have to be named for the new persistence bindings … I fear that the most users are configuring through the UI and store it where ever OpenHAB puts it … But running OH in the docker container I feel more safe having the config files. I will try out the different name for the cfg file and maybe I find some hints in the forum!!!

I have to find a free slot during the week to switch to OH3 again for testing as I only have one environment … so it may take a while until I can give feedback …

rlkoshak · March 8, 2021, 5:59pm

As in OH 2, the REST API is how all the web based UIs interact with openHAB. The REST API is always there. In OH 2 you could optionally install the REST API Docs. But in OH 3 the docs are installed by default and you can find them under Developer Tools.

What are you doing at the time of the error? Which UIs are you using at the time of the error? How are you reaching OH over the network? Stuff like that.

I run OH in a docker container and do everything through the UIs now. Everything you do through the UIs gets saved to a file too. Those files are of a different format though and in a different location (i.e. userdata). The changes you make to the JDBC configs will generate a jdbc-mysql.config or jdbc.config (or something like that) in userdata/config/org/openhab or userdata/config/org/binding. Items and Things and such get saved to userdata/jsondb.

It’s all text files.

I even use git to configuration control my OH setup. All that’s really different is I need to include much of userdata (not cache or tmp) in addition to conf.

ohb1138 · March 8, 2021, 6:12pm

This appears right away at startup when the container is “booting up”. At that point I am not really connected at all … I am mainly using HABPanel and the Basic UI some times mainly through the OPenHAB app on Android. I only use the browser based UIs for debugging. Maybe I had a browser window open during boot but it should not be connected at container startup.

rlkoshak · March 8, 2021, 7:17pm

If you have a browser window open during boot it is going to keep trying to connect while OH is restarting.

ohb1138 · March 11, 2021, 2:55pm

Hi all,

Thank you for all your great advice!!!

The two problems are solved now but another one from the jdbc persistence driver arised. But I will address this in a new topic.
Here are the solutions for my two issues raised her:

14:57:04.234 [WARN ] [org.eclipse.jetty.server.HttpChannel ] - /rest/addons
java.lang.NullPointerException: null
...

The hint from @rlkoshak solved this one to keep browser connections to OpenHAB closed during start up:

14:57:46.394 [INFO ] [.persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
14:57:46.396 [WARN ] [m.zaxxer.hikari.util.DriverDataSource] - Registered driver with driverClassName=com.mysql.jdbc.Driver was not found, trying direct instantiation.
14:57:46.401 [ERROR] [org.openhab.persistence.jdbc         ] - bundle org.openhab.persistence.jdbc:3.1.0.M1 (249)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(305)] : The activate method has thrown an exception
com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Access denied for user ''@'172.17.0.4' (using password: NO)
...

JDBC Mysql persistence in OH3

Hi all,

I found the reasons for my jdbc login problems. The syntax in the jdbc.cfg file was wrong. It seems to work with OH2.5 but obviously caused problems with OH3:

Previous syntax:

...
# required database user
mysql:user=XXX

# required database password
mysql:password=YYY

mysql:default_time_zone='Europe/Berlin'

# E R R O R   H A N D L I N G
# optional when Service is deactivated (optional, default: 0 -> ignore)
#errReconnectThreshold=0
mysql:reconnectCnt=3
mysql:waitTimeout=28800
...

New syntax working:

...
# required database user
user=XXX

# required database password
password=YYY

mysql:default_time_zone='Europe/Berlin'

# E R R O R   H A N D L I N G
# optional when Service is deactivated (optional, default: 0 -> ignore)
#errReconnectThreshold=0
reconnectCnt=3
waitTimeout=28800
...

So all problems of this topic are solved with all your support.

Thank you!!!

Falk · March 22, 2021, 12:45pm

I would like to continue this thread for the problem shown in the log above and also in my logs:

2021-03-22 01:37:56.708 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
2021-03-22 01:37:56.733 [WARN ] [.zaxxer.hikari.util.DriverDataSource] - Registered driver with driverClassName=com.mysql.jdbc.Driver was not found, trying direct instantiation.

The problem I am facing is that database connection works when installed but disappears once system is restarted. Tables and entries are created during the session.
addons.cfg persistence entry is empty when system starts up. I need to re-install the jdbc-mysql driver every time i am starting the system.
System is OH 3.0.1 on docker, database is mysql 5.7.33 on docker. After hours of trying to upgrade from 2.5.12, I made a fresh install. Still the issue remains.
Is there a better solution with OH 3.1M2?

Bruce_Osborne · March 22, 2021, 1:08pm

This thread was for a different issue that was resolved. Please start a new thread for your issue. We will even let you create it at no additional charge.

Falk · March 22, 2021, 2:25pm

thanks a lot … here is the new thread:
JDBC-mysql needs to get installed every time after OH restart