MySQL (OH2) to JDBC-MySQL (OH3) Migration failures

tommycw10 · January 18, 2022, 12:56am

Recently moving to a fresh install of OH3 from an old install of OH2. I had mysql persistence setup and running without issue for several years connecting to another machine running mysql on my LAN.

I now have a working OH3 install on the same machine I had OH2 on but using a new disk (fresh install, not upgrade) but cannot get the new JDBC-mysql persistence working.

The new install is the same IP as my old install and I have confirmed that I can still connect to my machine running the mysql server on the command line and everything is still there:

openhabian@opi:/etc/openhab/persistence $ sudo -u openhab mysql -u openhab -p -h 192.168.1.100 -D OpenHAB
[sudo] password for openhabian: 
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 7213
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [OpenHAB]> USE OpenHAB
Database changed
MySQL [OpenHAB]> show tables;
+-------------------+
| Tables_in_OpenHAB |
+-------------------+
| Item1             |
| Item10            |
| Item100           |
| Item101           |
| Item102           |
| Item103           |
| Item104           |
| Item105           |
| Item106           |
| Item107           |
| Item108           |
| Item109           |
| Item11            |
| Item110           |
| Item111           |
| Item112           |
| Item113           |
| Item114           |
| Item115           |
| Item116           |
| Item117           |
| Item118           |
| Item119           |
| Item12            |
| Item120           |
| Item121           |
| Item122           |
| Item123           |
| Item124           |
| Item125           |
| Item126           |
| Item127           |
| Item128           |
| Item129           |
| Item13            |
| Item130           |
| Item131           |
| Item132           |
| Item133           |
| Item134           |
| Item135           |
| Item136           |
| Item137           |
| Item138           |
| Item139           |
| Item14            |
| Item140           |
| Item141           |
| Item142           |
| Item15            |
| Item16            |
| Item17            |
| Item18            |
| Item19            |
| Item2             |
| Item20            |
| Item21            |
| Item22            |
| Item23            |
| Item24            |
| Item25            |
| Item26            |
| Item27            |
| Item28            |
| Item29            |
| Item3             |
| Item30            |
| Item31            |
| Item32            |
| Item33            |
| Item34            |
| Item35            |
| Item36            |
| Item37            |
| Item38            |
| Item39            |
| Item4             |
| Item40            |
| Item41            |
| Item42            |
| Item43            |
| Item44            |
| Item45            |
| Item46            |
| Item47            |
| Item48            |
| Item49            |
| Item5             |
| Item50            |
| Item51            |
| Item52            |
| Item53            |
| Item54            |
| Item55            |
| Item56            |
| Item57            |
| Item58            |
| Item59            |
| Item6             |
| Item60            |
| Item61            |
| Item62            |
| Item63            |
| Item64            |
| Item65            |
| Item66            |
| Item67            |
| Item68            |
| Item69            |
| Item7             |
| Item70            |
| Item71            |
| Item72            |
| Item73            |
| Item74            |
| Item75            |
| Item76            |
| Item77            |
| Item78            |
| Item79            |
| Item8             |
| Item80            |
| Item81            |
| Item82            |
| Item83            |
| Item84            |
| Item85            |
| Item86            |
| Item87            |
| Item88            |
| Item89            |
| Item9             |
| Item90            |
| Item91            |
| Item92            |
| Item93            |
| Item94            |
| Item95            |
| Item96            |
| Item97            |
| Item98            |
| Item99            |
| Items             |
+-------------------+
143 rows in set (0.058 sec)

I configured the JDBC persistence as follows:

# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
url=jdbc:mysql://192.168.1.100:3306/OpenHAB?serverTimezone=America/New_York

# the database user
user=openhab

# the database password
password=redacted

# the reconnection counter
reconnectCnt=3

# 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

#mysql:sqltype.string=VARCHAR(1024)

# for Migration from MYSQL-Bundle set to 'Item'.
jdbc:tableNamePrefix=Item
#
# for Migration from MYSQL-Bundle do not use real names.
jdbc:tableUseRealItemNames=false
#
# for Migration from MYSQL-Bundle set to 0.
jdbc:tableIdDigitCount=0

I have my same .persistence file from my previous install and renamed it from mysql.persistence to jdbc.persistence, although I’m not sure its required.

I put the binding in debug logging mode and stopped it, then restarted it through the console. Here is an example of what I see:

2022-01-17 19:25:48.243 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService] : BundleComponentActivator : ComponentHolder created.
2022-01-17 19:25:48.252 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService] : ConfigurableComponentHolder configuration updated for pid org.openhab.jdbc with change count 5
2022-01-17 19:25:48.253 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService] : Dependency Manager created $000interface=org.openhab.core.items.ItemRegistry, filter=null, policy=static, cardinality=1..1, bind=null, unbind=null, updated=null, field=null, field-option=null, collection-type=null, parameter=0
2022-01-17 19:25:48.255 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService] : Dependency Manager created $001interface=org.openhab.core.i18n.TimeZoneProvider, filter=null, policy=static, cardinality=1..1, bind=null, unbind=null, updated=null, field=null, field-option=null, collection-type=null, parameter=1
2022-01-17 19:25:48.257 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService] : Component created: DS=DS14, implementation=org.openhab.persistence.jdbc.internal.JdbcPersistenceService, immediate=false, default-enabled=true, factory=null, configuration-policy=optional, activate=activate, deactivate=deactivate, modified=null configuration-pid=[org.openhab.jdbc]
2022-01-17 19:25:48.259 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService] : Component Services: scope=singleton, services=[org.openhab.core.persistence.PersistenceService, org.openhab.core.persistence.QueryablePersistenceService]
2022-01-17 19:25:48.260 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService] : Component Properties: {service.config.label=JDBC Persistence Service, service.config.category=persistence, service.config.description.uri=persistence:jdbc, service.config.factory=false, service.pid=org.openhab.jdbc}
2022-01-17 19:25:48.262 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService] : Querying state disabled
2022-01-17 19:25:48.263 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService] : Querying state disabled
2022-01-17 19:25:48.264 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService] : Component can not be activated since it is in state disabled
2022-01-17 19:25:48.265 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService] : Querying state disabled
2022-01-17 19:25:48.267 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Updating target filters
2022-01-17 19:25:48.268 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : No change in target property for dependency $000: currently registered: false
2022-01-17 19:25:48.269 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] :  No existing service listener to unregister for dependency $000
2022-01-17 19:25:48.271 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Setting target property for dependency $000 to null
2022-01-17 19:25:48.272 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : New service tracker for $000, initial active: false, previous references: {}, classFilter: (objectClass=org.openhab.core.items.ItemRegistry), initialReferenceFilter (objectClass=org.openhab.core.items.ItemRegistry)
2022-01-17 19:25:48.273 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : dm $000 tracker reset (closed)
2022-01-17 19:25:48.278 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : dm $000 tracking 1 SingleStatic added {org.openhab.core.items.ItemRegistry}={service.id=190, service.bundleid=151, service.scope=bundle, component.name=org.openhab.core.internal.items.ItemRegistryImpl, component.id=21} (enter)
2022-01-17 19:25:48.279 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : dm $000 tracking 1 SingleStatic active: false trackerOpened: false optional: false
2022-01-17 19:25:48.281 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : dm $000 tracking 1 SingleStatic added {org.openhab.core.items.ItemRegistry}={service.id=190, service.bundleid=151, service.scope=bundle, component.name=org.openhab.core.internal.items.ItemRegistryImpl, component.id=21} (exit)
2022-01-17 19:25:48.282 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : dm $000 tracker opened
2022-01-17 19:25:48.284 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : registering service listener for dependency $000
2022-01-17 19:25:48.285 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : No change in target property for dependency $001: currently registered: false
2022-01-17 19:25:48.286 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] :  No existing service listener to unregister for dependency $001
2022-01-17 19:25:48.287 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Setting target property for dependency $001 to null
2022-01-17 19:25:48.288 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : New service tracker for $001, initial active: false, previous references: {}, classFilter: (objectClass=org.openhab.core.i18n.TimeZoneProvider), initialReferenceFilter (objectClass=org.openhab.core.i18n.TimeZoneProvider)
2022-01-17 19:25:48.289 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : dm $001 tracker reset (closed)
2022-01-17 19:25:48.295 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : dm $001 tracking 2 SingleStatic added {org.openhab.core.i18n.TranslationProvider, org.openhab.core.i18n.LocaleProvider, org.openhab.core.i18n.LocationProvider, org.openhab.core.i18n.TimeZoneProvider, org.openhab.core.i18n.UnitProvider}={location=42.88602714832883,-78.87814521789552, service.id=177, service.bundleid=151, service.scope=bundle, component.name=org.openhab.core.internal.i18n.I18nProviderImpl, language=en, service.config.label=Regional Settings, measurementSystem=US, component.id=18, timezone=America/New_York, service.config.category=system, region=US, service.config.description.uri=system:i18n, service.pid=[org.openhab.i18n, org.openhab.i18n]} (enter)
2022-01-17 19:25:48.296 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : dm $001 tracking 2 SingleStatic active: false trackerOpened: false optional: false
2022-01-17 19:25:48.298 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : dm $001 tracking 2 SingleStatic added {org.openhab.core.i18n.TranslationProvider, org.openhab.core.i18n.LocaleProvider, org.openhab.core.i18n.LocationProvider, org.openhab.core.i18n.TimeZoneProvider, org.openhab.core.i18n.UnitProvider}={location=42.88602714832883,-78.87814521789552, service.id=177, service.bundleid=151, service.scope=bundle, component.name=org.openhab.core.internal.i18n.I18nProviderImpl, language=en, service.config.label=Regional Settings, measurementSystem=US, component.id=18, timezone=America/New_York, service.config.category=system, region=US, service.config.description.uri=system:i18n, service.pid=[org.openhab.i18n, org.openhab.i18n]} (exit)
2022-01-17 19:25:48.299 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : dm $001 tracker opened
2022-01-17 19:25:48.300 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : registering service listener for dependency $001
2022-01-17 19:25:48.301 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Changed state from disabled to unsatisfiedReference
2022-01-17 19:25:48.303 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Component enabled
2022-01-17 19:25:48.303 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : ActivateInternal
2022-01-17 19:25:48.305 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Querying state unsatisfiedReference
2022-01-17 19:25:48.306 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Querying state unsatisfiedReference
2022-01-17 19:25:48.307 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Activating component from state unsatisfiedReference
2022-01-17 19:25:48.308 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Querying state unsatisfiedReference
2022-01-17 19:25:48.310 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Querying state unsatisfiedReference
2022-01-17 19:25:48.311 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Changed state from unsatisfiedReference to satisfied
2022-01-17 19:25:48.313 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : registration change queue [registered]
2022-01-17 19:25:48.317 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Checking constructor public org.openhab.persistence.jdbc.internal.JdbcPersistenceService(org.openhab.core.items.ItemRegistry,org.openhab.core.i18n.TimeZoneProvider)
2022-01-17 19:25:48.318 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : getReferenceClass: Looking for interface class org.openhab.core.items.ItemRegistry through loader of org.openhab.persistence.jdbc.internal.JdbcPersistenceService
2022-01-17 19:25:48.319 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : getParameterClass: Found class org.openhab.core.items.ItemRegistry
2022-01-17 19:25:48.320 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : getReferenceClass: Looking for interface class org.openhab.core.i18n.TimeZoneProvider through loader of org.openhab.persistence.jdbc.internal.JdbcPersistenceService
2022-01-17 19:25:48.322 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : getParameterClass: Found class org.openhab.core.i18n.TimeZoneProvider
2022-01-17 19:25:48.323 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Found constructor with 2 arguments : public org.openhab.persistence.jdbc.internal.JdbcPersistenceService(org.openhab.core.items.ItemRegistry,org.openhab.core.i18n.TimeZoneProvider)
2022-01-17 19:25:48.325 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : This thread collected dependencies
2022-01-17 19:25:48.325 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : getService (single component manager) dependencies collected.
2022-01-17 19:25:48.327 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Querying state satisfied
2022-01-17 19:25:48.327 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Querying state satisfied
2022-01-17 19:25:48.329 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : For dependency $000, optional: false; to bind: [[RefPair: ref: [{org.openhab.core.items.ItemRegistry}={service.id=190, service.bundleid=151, service.scope=bundle, component.name=org.openhab.core.internal.items.ItemRegistryImpl, component.id=21}] service: [null]]]
2022-01-17 19:25:48.330 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : For dependency $001, optional: false; to bind: [[RefPair: ref: [{org.openhab.core.i18n.TranslationProvider, org.openhab.core.i18n.LocaleProvider, org.openhab.core.i18n.LocationProvider, org.openhab.core.i18n.TimeZoneProvider, org.openhab.core.i18n.UnitProvider}={location=42.88602714832883,-78.87814521789552, service.id=177, service.bundleid=151, service.scope=bundle, component.name=org.openhab.core.internal.i18n.I18nProviderImpl, language=en, service.config.label=Regional Settings, measurementSystem=US, component.id=18, timezone=America/New_York, service.config.category=system, region=US, service.config.description.uri=system:i18n, service.pid=[org.openhab.i18n, org.openhab.i18n]}] service: [null]]]
2022-01-17 19:25:48.333 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : getting activate: activate
2022-01-17 19:25:48.334 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Locating method activate in class org.openhab.persistence.jdbc.internal.JdbcPersistenceService
2022-01-17 19:25:48.335 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Declared Method org.openhab.persistence.jdbc.internal.JdbcPersistenceService.activate([interface org.osgi.service.component.ComponentContext]) not found
2022-01-17 19:25:48.336 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Found activate method: public void org.openhab.persistence.jdbc.internal.JdbcPersistenceService.activate(org.osgi.framework.BundleContext,java.util.Map)
2022-01-17 19:25:48.338 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : invoking activate: activate: parameters [org.eclipse.osgi.internal.framework.BundleContextImpl, org.apache.felix.scr.impl.helper.ReadOnlyDictionary]
2022-01-17 19:25:48.339 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::activate: persistence service activated
2022-01-17 19:25:48.340 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::updateConfig
2022-01-17 19:25:48.341 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::JdbcConfiguration
2022-01-17 19:25:48.342 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: configuration size = 15
2022-01-17 19:25:48.343 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: user=openhab
2022-01-17 19:25:48.344 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: password exists? true
2022-01-17 19:25:48.344 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: url=jdbc:mysql://192.168.1.100:3306/OpenHAB?serverTimezone=America/New_York
2022-01-17 19:25:48.345 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: found serviceName = 'mysql'
2022-01-17 19:25:48.346 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: Init Data Access Object Class: 'org.openhab.persistence.jdbc.db.JdbcMysqlDAO'
2022-01-17 19:25:48.347 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::initSqlTypes: Initialize the type array
2022-01-17 19:25:48.348 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::initSqlQueries: 'JdbcMysqlDAO'
2022-01-17 19:25:48.349 [DEBUG] [hab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initSqlTypes: Initialize the type array
2022-01-17 19:25:48.350 [DEBUG] [hab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initSqlQueries: 'JdbcMysqlDAO'
2022-01-17 19:25:48.351 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: dBDAO ClassName=org.openhab.persistence.jdbc.db.JdbcMysqlDAO
2022-01-17 19:25:48.353 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: errReconnectThreshold=3
2022-01-17 19:25:48.354 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: tableNamePrefix=Item
2022-01-17 19:25:48.355 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: tableUseRealItemNames=false
2022-01-17 19:25:48.355 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: tableIdDigitCount=0
2022-01-17 19:25:48.356 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: enableLogTime false
2022-01-17 19:25:48.357 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: load JDBC-driverClass was successful: 'com.mysql.jdbc.Driver'
2022-01-17 19:25:48.358 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: configuration complete. service=jdbc
2022-01-17 19:25:48.359 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB
2022-01-17 19:25:48.360 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection isDriverAvailable: true
2022-01-17 19:25:48.360 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
2022-01-17 19:25:48.401 [WARN ] [.zaxxer.hikari.util.DriverDataSource] - Registered driver with driverClassName=com.mysql.jdbc.Driver was not found, trying direct instantiation.
2022-01-17 19:25:48.522 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::setDbConnected true
2022-01-17 19:25:48.523 [DEBUG] [hab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initAfterFirstDbConnection: Initializing step, after db is connected.
2022-01-17 19:25:48.677 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbMajorVersion = '8'
2022-01-17 19:25:48.679 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbMinorVersion = '0'
2022-01-17 19:25:48.680 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - driverMajorVersion = '8'
2022-01-17 19:25:48.681 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - driverMinorVersion = '0'
2022-01-17 19:25:48.682 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbProductName = 'MySQL'
2022-01-17 19:25:48.683 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbProductVersion = '8.0.27-0ubuntu0.20.04.1'
2022-01-17 19:25:48.684 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB asking db for name as absolutely first db action, after connection is established.
2022-01-17 19:25:48.793 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::checkDBAcessability, first try connection: true
2022-01-17 19:25:48.795 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service.
2022-01-17 19:25:48.796 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::updateConfig: configuration complete for service=jdbc.
2022-01-17 19:25:48.797 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : invoked activate: activate
2022-01-17 19:25:48.798 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Set implementation object for component
2022-01-17 19:25:48.799 [DEBUG] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (361)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(384)] : Changed state from satisfied to active
2022-01-17 19:25:48.800 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service.
2022-01-17 19:25:48.802 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service.
2022-01-17 19:25:48.803 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service.
2022-01-17 19:25:48.804 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service.
2022-01-17 19:25:48.805 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service.
2022-01-17 19:25:48.808 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service.
2022-01-17 19:25:53.426 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB
2022-01-17 19:25:53.428 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection isDriverAvailable: true
2022-01-17 19:25:53.487 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::checkDBAcessability, first try connection: true
2022-01-17 19:25:53.488 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::store:  No connection to database. Cannot persist item 'dryerVolts (Type=NumberItem, State=118.29, Label=Dryer Voltage, Category=energy)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:3
==> /var/log/openhab/events.log <==
2022-01-17 19:25:53.423 [INFO ] [openhab.event.ItemStateChangedEvent ] - Item 'dryerVolts' changed from 118.25 to 118.29
==> /var/log/openhab/openhab.log <==
2022-01-17 19:26:04.675 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB
2022-01-17 19:26:04.677 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection isDriverAvailable: true
2022-01-17 19:26:04.688 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::checkDBAcessability, first try connection: true
2022-01-17 19:26:04.689 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::store:  No connection to database. Cannot persist item 'poeWatts (Type=NumberItem, State=24.17, Label=PoE Power, Category=energy)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:3
2022-01-17 19:26:04.719 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB
2022-01-17 19:26:04.720 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection isDriverAvailable: true
2022-01-17 19:26:04.725 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::checkDBAcessability, first try connection: true
2022-01-17 19:26:04.726 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::store:  No connection to database. Cannot persist item 'poeVolts (Type=NumberItem, State=114.15, Label=PoE Voltage, Category=energy)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:3
==> /var/log/openhab/events.log <==
2022-01-17 19:26:04.677 [INFO ] [openhab.event.ItemStateChangedEvent ] - Item 'poeWatts' changed from 24.15 to 24.17
2022-01-17 19:26:04.720 [INFO ] [openhab.event.ItemStateChangedEvent ] - Item 'poeVolts' changed from 113.22 to 114.15

I don’t entirely know what I’m looking at here but I only see one line that kind of sticks out:

2022-01-17 19:25:48.401 [WARN ] [.zaxxer.hikari.util.DriverDataSource] - Registered driver with driverClassName=com.mysql.jdbc.Driver was not found, trying direct instantiation.

However directly after that the database seems to connect as it correctly pulls info on the version etc from my mysql server:

2022-01-17 19:25:48.677 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbMajorVersion = '8'
2022-01-17 19:25:48.679 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbMinorVersion = '0'
2022-01-17 19:25:48.680 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - driverMajorVersion = '8'
2022-01-17 19:25:48.681 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - driverMinorVersion = '0'
2022-01-17 19:25:48.682 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbProductName = 'MySQL'
2022-01-17 19:25:48.683 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbProductVersion = '8.0.27-0ubuntu0.20.04.1'

Its not until it actually tried to persist an item that it says there is no connection to the database:

JDBC::store:  No connection to database. Cannot persist item 'dryerVolts (Type=NumberItem, State=118.29, Label=Dryer Voltage, Category=energy)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:3

Googling around shows several other people having similar issues but none seem to be migrating with an existing mysql db and just get pointed to the set up instructions for a new DB. Anyone have any idea what is happening here?

Charley · January 19, 2022, 10:50am

In OH3 you should make a few changes.

The Items are now all in lowercase

And if you are using MariaDB

url=jdbc:mysql://192.168.1.100:3306/OpenHAB?serverTimezone=America/New_York

change to

url=jdbc:mariadb://192.168.1.100:3306/OpenHAB?serverTimezone=America/New_York

tommycw10 · January 19, 2022, 3:15pm

Hi @Charley Thanks for your reply

Yeah, I’ve tried both connecting to my original DB and used

tableNamePrefix=Item

To - in theory - get around that issue and continue using my historical data

Charley:

And if you are using MariaDB

url=jdbc:mysql://192.168.1.100:3306/OpenHAB?serverTimezone=America/New_York

change to

url=jdbc:mariadb://192.168.1.100:3306/OpenHAB?serverTimezone=America/New_York

I know mariadb is a community fork of mysql, but it spears I am running mysql not maria db on Ubuntu 20.04:

$ mysql --version
mysql  Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

ps aux | grep mysql
mysql       1145  0.5  4.3 3914076 356160 ?      Ssl  Jan18   6:21 /usr/sbin/mysqld
tom        12488  0.0  0.0   9040   736 pts/0    S+   09:42   0:00 grep --color=auto mysql
tom@mythfe:~$ ps aux | grep maria
tom        12490  0.0  0.0   9040   736 pts/0    S+   09:42   0:00 grep --color=auto maria

Is there a reason you suspect I need to use the mariadb connector rather than mysql?

I tried changing to the url you suggested and that complained that I had no driver, so I uninstalled the mysql binding and installed the mariadb binding and now I get a huge exemption thrown in the log.

Charley · January 19, 2022, 3:57pm

@tommycw10,

When I changed from OH2.x to OH 3.x I was not able to connect to through jdbc:mysql. After changing to jdbc:mariadb and also checking the privileges I was able to connect.

I have restored all my old data and it works perfect.

tommycw10 · January 19, 2022, 5:44pm

Thanks @Charley

Are you actually running a mysql server or mariadb server?

Interesting note you had here about privileges:

I have my original DB that I used in OH2 called “OpenHAB” and when that failed to work correctly, I created a new database “openhabdb2” and granted my existing user all permissions on that database.

Currently looking at my mysql server I have the jdbc:mysql plugin installed and I am trying to connect to my server. Looking at the server, it shows openhab logged in and using that new “openhabdb2” database:

mysql> show processlist;
+-----+-----------------+---------------------+-------------+---------+-------+------------------------+------------------+
| Id  | User            | Host                | db          | Command | Time  | State                  | Info             |
+-----+-----------------+---------------------+-------------+---------+-------+------------------------+------------------+
|   5 | event_scheduler | localhost           | NULL        | Daemon  | 85011 | Waiting on empty queue | NULL             |
|  11 | mythtv          | localhost           | mythconverg | Sleep   |    18 |                        | NULL             |
|  18 | mythtv          | localhost           | mythconverg | Sleep   | 15404 |                        | NULL             |
|  19 | mythtv          | localhost           | mythconverg | Sleep   |    53 |                        | NULL             |
|  23 | mythtv          | localhost           | mythconverg | Sleep   |    58 |                        | NULL             |
| 426 | mythtv          | localhost           | mythconverg | Sleep   | 15441 |                        | NULL             |
| 427 | mythtv          | localhost           | mythconverg | Sleep   | 15407 |                        | NULL             |
| 545 | root            | localhost           | NULL        | Query   |     0 | init                   | show processlist |
| 559 | openhab         | 192.168.1.200:45726 | openhabdb2  | Sleep   |  7021 |                        | NULL             |
| 564 | mythtv          | localhost           | mythconverg | Sleep   |  5178 |                        | NULL             |
| 567 | mythtv          | localhost           | mythconverg | Sleep   |  4671 |                        | NULL             |
| 570 | openhab         | 192.168.1.200:51018 | openhabdb2  | Sleep   |     8 |                        | NULL             |
| 571 | openhab         | 192.168.1.200:51020 | openhabdb2  | Sleep   |  1621 |                        | NULL             |
+-----+-----------------+---------------------+-------------+---------+-------+------------------------+------------------+

So I know that openhab IS successfully logging into the server. (also shown in my original posting where I see openhab pulls the server version info so I thought it was logged in then too)

However when I change an item that I have set up to be saved into this db, I get an error in the OH log:

2022-01-19 12:27:24.379 [INFO ] [ore.model.script.computerroom Motion] - !!!!!!!!!!!!!!!!!!!!!! Computer Room Motion Triggered:OPEN Daylight State: NULL Computerroom State: 100 Computerroom Threshold: OFF

2022-01-19 12:27:24.385 [INFO ] [model.script.computerroomMotiontimer] - !!!!!!!!!!!!!!!!!!! Comp Room Motion - Re-scheduled computerroomMotion timeer for 1 min

2022-01-19 12:27:24.389 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::store:  No connection to database. Cannot persist item 'computerroomMotionLHF (Type=DateTimeItem, State=2022-01-19T12:27:24.380722-0500, Label=Comuter Room, Category=calendar)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:3

2022-01-19 12:27:24.393 [INFO ] [penhab.core.model.script.Stairstimer] - !!!!!!!!!!!!!!!!!!! Comp Room motion but timer is null

==> /var/log/openhab/events.log <==

2022-01-19 12:27:24.372 [INFO ] [openhab.event.ItemStateChangedEvent ] - Item 'computerroomMotion' changed from CLOSED to OPEN

2022-01-19 12:27:24.389 [INFO ] [openhab.event.ItemStateChangedEvent ] - Item 'computerroomMotionLHF' changed from 2022-01-19T12:25:40.047626-0500 to 2022-01-19T12:27:24.380722-0500

I get the EXACT same error if I switch back to my original db “OpenHAB”

I can see I’m logged into the server and on the proper DB:

mysql> show processlist;
+-----+-----------------+---------------------+-------------+---------+-------+------------------------+------------------+
| Id  | User            | Host                | db          | Command | Time  | State                  | Info             |
+-----+-----------------+---------------------+-------------+---------+-------+------------------------+------------------+
|   5 | event_scheduler | localhost           | NULL        | Daemon  | 85745 | Waiting on empty queue | NULL             |
|  11 | mythtv          | localhost           | mythconverg | Sleep   |    32 |                        | NULL             |
|  18 | mythtv          | localhost           | mythconverg | Sleep   | 16138 |                        | NULL             |
|  19 | mythtv          | localhost           | mythconverg | Sleep   |     7 |                        | NULL             |
|  23 | mythtv          | localhost           | mythconverg | Sleep   |    12 |                        | NULL             |
| 426 | mythtv          | localhost           | mythconverg | Sleep   | 16175 |                        | NULL             |
| 427 | mythtv          | localhost           | mythconverg | Sleep   | 16141 |                        | NULL             |
| 545 | root            | localhost           | NULL        | Query   |     0 | init                   | show processlist |
| 564 | mythtv          | localhost           | mythconverg | Sleep   |  5912 |                        | NULL             |
| 567 | mythtv          | localhost           | mythconverg | Sleep   |  5405 |                        | NULL             |
| 575 | openhab         | 192.168.1.200:53304 | OpenHAB     | Sleep   |    10 |                        | NULL             |
| 576 | openhab         | 192.168.1.200:53306 | OpenHAB     | Sleep   |     9 |                        | NULL             |
| 577 | openhab         | 192.168.1.200:53308 | OpenHAB     | Sleep   |     9 |                        | NULL             |
+-----+-----------------+---------------------+-------------+---------+-------+------------------------+------------------+

However I get an error when trying to persist and item in the db:

022-01-19 12:30:54.998 [INFO ] [ore.model.script.computerroom Motion] - !!!!!!!!!!!!!!!!!!!!!! Computer Room Motion Triggered:OPEN Daylight State: NULL Computerroom State: 100 Computerroom Threshold: OFF

2022-01-19 12:30:55.014 [INFO ] [model.script.computerroomMotiontimer] - !!!!!!!!!!!!!!!!!!! Comp Room Motion - Re-scheduled computerroomMotion timeer for 1 min

2022-01-19 12:30:55.017 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::store:  No connection to database. Cannot persist item 'computerroomMotionLHF (Type=DateTimeItem, State=2022-01-19T12:30:55.000463-0500, Label=Comuter Room, Category=calendar)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:3

2022-01-19 12:30:55.021 [INFO ] [penhab.core.model.script.Stairstimer] - !!!!!!!!!!!!!!!!!!! Comp Room motion but timer is null

==> /var/log/openhab/events.log <==

2022-01-19 12:30:55.014 [INFO ] [openhab.event.ItemStateChangedEvent ] - Item 'computerroomMotionLHF' changed from 2022-01-19T12:29:56.150954-0500 to 2022-01-19T12:30:55.000463-0500

So then if I remove the jdbc:mysql binding and install the jdbc:mariadb binding, and I change my url to

url=jdbc:mariadb://192.168.1.100:3306/OpenHAB

I get a permission error:

022-01-19 12:35:21.049 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource

2022-01-19 12:35:21.069 [ERROR] [jdbc.internal.JdbcPersistenceService] - bundle org.openhab.persistence.jdbc:3.2.0 (377)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(381)] : The activate method has thrown an exception

com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Could not connect: Access denied for user 'openhab'@'192.168.1.200' (using password: YES)

It gives a very long dump and ends in "

2022-01-19 12:39:58.494 [WARN ] [ernal.PersistenceServiceRegistryImpl] - bundle org.openhab.core.persistence:3.2.0 (209)[org.openhab.core.persistence.internal.PersistenceServiceRegistryImpl(223)] : Could not get service from ref {org.openhab.core.persistence.PersistenceService, org.openhab.core.persistence.QueryablePersistenceService}={service.id=904, service.bundleid=377, service.scope=bundle, user=redacted, tableNamePrefix=Item, url=jdbc:mariadb://192.168.1.100:3306/openhabdb2, tableIdDigitCount=0, component.name=org.openhab.persistence.jdbc.internal.JdbcPersistenceService, reconnectCnt=3, service.config.label=JDBC Persistence Service, component.id=381, service.config.factory=false, password=redacted, localtime=true, waitTimeout=28800, tableUseRealItemNames=false, rebuildTableNames=false, service.config.category=persistence, service.config.description.uri=persistence:jdbc, service.pid=[org.openhab.jdbc, org.openhab.jdbc]}

2022-01-19 12:39:58.495 [WARN ] [ernal.PersistenceServiceRegistryImpl] - bundle org.openhab.core.persistence:3.2.0 (209)[org.openhab.core.persistence.internal.PersistenceServiceRegistryImpl(223)] : DependencyManager : invokeBindMethod : Service not available from service registry for ServiceReference {org.openhab.core.persistence.PersistenceService, org.openhab.core.persistence.QueryablePersistenceService}={service.id=904, service.bundleid=377, service.scope=bundle, user=redacted, tableNamePrefix=Item, url=jdbc:mariadb://192.168.1.100:3306/openhabdb2, tableIdDigitCount=0, component.name=org.openhab.persistence.jdbc.internal.JdbcPersistenceService, reconnectCnt=3, service.config.label=JDBC Persistence Service, component.id=381, service.config.factory=false, password=redacted, localtime=true, waitTimeout=28800, tableUseRealItemNames=false, rebuildTableNames=false, service.config.category=persistence, service.config.description.uri=persistence:jdbc, service.pid=[org.openhab.jdbc, org.openhab.jdbc]} for reference PersistenceService

Did you have to change any permissions on your server to change from connecting via mysql to mariadb?

Charley · January 20, 2022, 10:19am

@tommycw10

I have made a manual a long time ago

CREATE USER 'openhab'@'localhost' IDENTIFIED BY 'openhabian';

because you are using Ubuntu you have to check the Identified name for your distro

GRANT ALL PRIVILEGES ON OpenHAB.* TO 'openhab'@'localhost';
FLUSH PRIVILEGES;

OpenHAB.* is the name of the database and the tables are selected with the *

Watch-out the database name is casesensitive.

These privileges are not stored in your database but somewhere else

if you go to your terminal and open access mysql

sudo mysql
MariaDB [(none)]> show databases;
   
+--------------------+
| Database           |
+--------------------+
| OpenHAB            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.003 sec)

You see there is a database called mysql

MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| global_priv               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+
31 rows in set (0.001 sec)

Then you see the table user.

select * from user where User='openhab';
// all lot of data that end with
1 rows in set (0.003 sec)

This means you have a user if it says Empty set (0.003sec ) you don’t have the permissions to enter the database.

After restoring the database, I had to add the user and the privileges. See my instructions.
There is no difference in setting up between MariaDB and MySQL. How ever I prefer and use MariaDB.

From your errors

 JDBC::store:  No connection to database.

If JDBC can’t connect to the server, your error is something like: server not properly configured.

If you have no connection to the database (that is in the MySQL server) could mean privilege or missing user or missing database. (Your DB is there with the proper name, so check the other two possibilities as described before.)

Good luck

tommycw10 · January 20, 2022, 2:59pm

Hi @Charley - thanks for the continued help here - much appreciated.

Note: My mysql server and my openhab install are not on the same machine. My mysql server is running Ubuntu on a regular PC and my openhab install is running openhabian on an RPI.

So currently - from my RPI, I can log into the mysql machine’s db “OpenHAB” from the command line:

openhabian@opi:~ $ sudo -u openhab mysql -u openhab -p -h 192.168.1.100 -D OpenHAB
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 559
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [OpenHAB]>

I can create a table, add data, view that data and drop that table, all as user openhab from my RPI .

MySQL [OpenHAB]> CREATE TABLE fyi_links (id INTEGER PRIMARY KEY, url VARCHAR(80) NOT NULL, notes VARCHAR(1024), counts INTEGER, created TIMESTAMP DEFAULT CURRENT_TIMESTAMP());
Query OK, 0 rows affected (1.326 sec)

MySQL [OpenHAB]> INSERT INTO fyi_links VALUES (101, 'dev.fyicenter.com', NULL, 0, '2006-04-30');
Query OK, 1 row affected (0.092 sec)

MySQL [OpenHAB]> select * from fyi_links;
+-----+-------------------+-------+--------+---------------------+
| id  | url               | notes | counts | created             |
+-----+-------------------+-------+--------+---------------------+
| 101 | dev.fyicenter.com | NULL  |      0 | 2006-04-30 00:00:00 |
+-----+-------------------+-------+--------+---------------------+
1 row in set (0.001 sec)

MySQL [OpenHAB]> drop table fyi_links;
Query OK, 0 rows affected (0.512 sec)

I then tested if I could add data to an existing table:

MySQL [OpenHAB]> INSERT INTO Item2 VALUES ('2022-01-20 01:00:00','2022-01-20 01:00:00');
Query OK, 1 row affected (0.200 sec)

Seems OK (below truncated as too much data to post) (Also note the data from 7-JAN was the last data entered from my OH2 system)

MySQL [OpenHAB]> select * from Item2;
| 2022-01-07 09:25:27 | 2022-01-07 09:25:27 |
| 2022-01-07 09:26:49 | 2022-01-07 09:26:49 |
| 2022-01-07 09:27:08 | 2022-01-07 09:26:49 |
| 2022-01-20 01:00:00 | 2022-01-20 01:00:00 |
+---------------------+---------------------+
277156 rows in set (1.044 sec)

So it seems that I have the ability from the command line client on my RPI to connect to the OpenHAB DB on my mysql server, create new tables, drop tables, add data to new and existing tables. I’m not sure what other permissions my ‘openhab’ user might need or what else to test.

So, next I SSHed into my MYSQL machine and logged in as room in the command line client and went through the same stuff you showed:

+--------------------+
| Database           |
+--------------------+
| OpenHAB            |
| information_schema |
| mysql              |
| mythconverg        |
| openhabdb2         |
| performance_schema |
| phpmyadmin         |
| sys                |
+--------------------+
8 rows in set (0.03 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+
37 rows in set (0.01 sec)

mysql> select * from user where User='openhab';
+---------------+---------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host          | User    | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher             | x509_issuer              | x509_subject               | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+---------------+---------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| 192.168.1.200 | openhab | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          | 0x                     | 0x                       | 0x                         |             0 |           0 |               0 |                    0 | mysql_native_password | *redacted                                 | N                | 2020-11-30 18:11:23   |              NULL | N              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
+---------------+---------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
1 row in set (0.00 sec)

I then checked the user permissions

mysql> SHOW GRANTS FOR openhab@192.168.1.200;
+---------------------------------------------------------------------+
| Grants for openhab@192.168.1.200                                    |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `openhab`@`192.168.1.200`                     |
| GRANT ALL PRIVILEGES ON `OpenHAB`.* TO `openhab`@`192.168.1.200`    |
| GRANT ALL PRIVILEGES ON `openhabdb2`.* TO `openhab`@`192.168.1.200` |
+---------------------------------------------------------------------+

So, it seems that I have my user openhab@192.168.1.200 (the user from my RPI with ALL privileges on both the old DB from my OH2 install “OpenHAB” as well as my new DB I tried to create when connecting ot the old failed “openhabdb2”

Charley · January 20, 2022, 3:12pm

Why don’t you use it easier in your local network.

GRANT ALL PRIVILEGES ON `OpenHAB`.* TO `openhab`@`192.168.1.*`

did you add this line

FLUSH PRIVILEGES;

Maybe a silly question, is it working now?

I had an issue with the JDBC as well, did a remove and a new install.
Also moved from jdbc.cfg file, to settings → Other Services → JDBC Persistence Service (the jdbc.cfg file i have deleted)

tommycw10 · January 20, 2022, 6:45pm

Tries that and it gives me an error:

mysql> GRANT ALL PRIVILEGES ON `OpenHAB`.* TO `openhab`@`192.168.1.*`;
ERROR 1410 (42000): You are not allowed to create a user with GRANT

Yes, I did

Yes, I also did the same. I removed my jdbc.cfg file and put in in with the GUI as you suggested.

Charley · January 21, 2022, 8:42am

There was an error in the command the asterisks * should be percent % this way you can access from every device in your local network.

GRANT ALL PRIVILEGES ON `OpenHAB`.* TO `openhab`@`192.168.1.%`
FLUSH PRIVILEGES;

Have you also tried to restart openhab?

sudo systemctl restart openhab

Is it working now?

tommycw10 · January 21, 2022, 3:05pm

Charley:

There was an error in the command the asterisks * should be percent % this way you can access from every device in your local network.
GRANT ALL PRIVILEGES ON `OpenHAB`.* TO `openhab`@`192.168.1.%`
FLUSH PRIVILEGES;

Same error:

mysql> GRANT ALL PRIVILEGES ON `OpenHAB`.* TO `openhab`@`192.168.1.%`;
ERROR 1410 (42000): You are not allowed to create a user with GRANT

I don’t think the error is syntaxtual in the * vs % wildcards, its that the users in mysql are defined not just by uname, but uanme@host. So openhab@192.168.1.200 is a different user than openhab@192.168.1.%.

So I created this user with the wildcard name, grated it permissions and flushed:

mysql> CREATE USER `openhab`@`192.168.1.%`IDENTIFIED BY 'redacted';
Query OK, 0 rows affected (0.69 sec)

mysql> GRANT ALL PRIVILEGES ON `OpenHAB`.* TO `openhab`@`192.168.1.%`;
Query OK, 0 rows affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.06 sec)

So now this command to grant this user worked.

I have restarted OH and even cleared the cache

sudo systemctl stop openhab
sudo openhab-cli clean-cache
sudo systemctl start openhab

It still doesn’t want to authenticate:

com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Could not connect: Access denied for user 'openhab'@'192.168.1.200' (using password: YES)

tommycw10 · January 21, 2022, 3:26pm

Interesting stuff in my mysql error/quary log:

==> error.log <==
2022-01-21T15:23:47.417508Z 8 [Warning] [MY-010055] [Server] IP address '192.168.1.200' could not be resolved: Name or service not known

==> query.log <==
2022-01-21T15:23:47.420852Z	    8 Connect	openhab@192.168.1.200 on OpenHAB using TCP/IP
2022-01-21T15:23:47.420961Z	    8 Connect	Access denied for user 'openhab'@'192.168.1.200' (using password: YES)

Charley · January 21, 2022, 3:45pm

I am currently working on a NodeJS/Nginx server there I had an issue with MySQL that if the user was not able to login the users password is retracted.

Normally if you check with select * user you see the hash of the password starting with *
after 10 failed logins my password was gone.

What you could try

DROP USER 'openhab'@'localhost'

Then add the user again with the privileges.

If that doesn’t work I must find something else

tommycw10 · January 21, 2022, 4:27pm

@Charley

I dropped my openhab@192.168.1.200 and my openhab@192.168.1.% user (note: I never had an openhost@localhost user). I tried creating then an openhab@% user granted / flushed permissions. Same login error. I then dropped that user and created a new openhab@192.168.1.200 user, granted/flushed permissions I still get a login error but now it says 'using password: no"

2022-01-21T16:19:18.563670Z	    8 Connect	openhab@192.168.1.200 on OpenHAB using TCP/IP
2022-01-21T16:19:18.563836Z	    8 Connect	Access denied for user 'openhab'@'192.168.1.200' (using password: NO)

Is there a difference between a user with a mysql_native_password vs caching_sha2_password?
I noticed my original openhab@192.168.1.200 user had a password type of mysql_native_password but my newly created users all had caching_sha2_password.

Charley · January 21, 2022, 5:39pm

Is your password still in the configuration
Because the shows that no password was passed

Charley · January 21, 2022, 5:41pm

I do not know. I just know how to connect to the MySQL/MariaDB databases and how to work with it. Mostly from NodeJS

The JBDC layer is a bit of a mystic to me.

tommycw10 · January 21, 2022, 6:07pm

Yes, it is, seems strange to me too…

tommycw10 · January 21, 2022, 11:26pm

So, I’m not sure what is going on here but I cannot get this to authenticate using the Mariadb plugin to connect to my mysql db as suggested. So, I removed the jdbc:mariadb plugin and installed the jdbc:mysql plugin and it seems to authenticate, I get no error when first starting:

2022-01-21T23:11:03.618239Z	   29 Query	/* mysql-connector-java-8.0.22 (Revision: d64b664fa93e81296a377de031b8123a67e6def2) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2022-01-21T23:11:03.680101Z	   29 Query	SET NAMES utf8mb4
2022-01-21T23:11:03.681608Z	   29 Query	SET character_set_results = NULL
2022-01-21T23:11:03.684534Z	   29 Query	SET autocommit=1
2022-01-21T23:11:03.713458Z	   29 Query	set session transaction read write
2022-01-21T23:11:03.715366Z	   29 Query	SET autocommit=1
2022-01-21T23:11:03.718035Z	   29 Query	SELECT @@session.transaction_isolation
2022-01-21T23:11:03.720023Z	   29 Quit	
2022-01-21T23:11:03.821312Z	   30 Connect	openhab@192.168.1.200 on OpenHAB using SSL/TLS
2022-01-21T23:11:03.826871Z	   30 Query	/* mysql-connector-java-8.0.22 (Revision: d64b664fa93e81296a377de031b8123a67e6def2) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2022-01-21T23:11:03.836243Z	   30 Query	SET NAMES utf8mb4
2022-01-21T23:11:03.840213Z	   30 Query	SET character_set_results = NULL
2022-01-21T23:11:03.841815Z	   30 Query	SET autocommit=1
2022-01-21T23:11:03.843700Z	   30 Query	set session transaction read write
2022-01-21T23:11:03.845351Z	   30 Query	SET autocommit=1
2022-01-21T23:11:03.944594Z	   31 Connect	openhab@192.168.1.200 on OpenHAB using SSL/TLS
2022-01-21T23:11:03.948604Z	   31 Query	/* mysql-connector-java-8.0.22 (Revision: d64b664fa93e81296a377de031b8123a67e6def2) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2022-01-21T23:11:03.952746Z	   31 Query	SET NAMES utf8mb4
2022-01-21T23:11:03.953852Z	   31 Query	SET character_set_results = NULL
2022-01-21T23:11:03.955033Z	   31 Query	SET autocommit=1
2022-01-21T23:11:03.956541Z	   31 Query	set session transaction read write
2022-01-21T23:11:03.957797Z	   31 Query	SET autocommit=1
2022-01-21T23:11:04.050318Z	   32 Connect	openhab@192.168.1.200 on OpenHAB using SSL/TLS
2022-01-21T23:11:04.054130Z	   32 Query	/* mysql-connector-java-8.0.22 (Revision: d64b664fa93e81296a377de031b8123a67e6def2) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2022-01-21T23:11:04.058764Z	   32 Query	SET NAMES utf8mb4
2022-01-21T23:11:04.060359Z	   32 Query	SET character_set_results = NULL
2022-01-21T23:11:04.061872Z	   32 Query	SET autocommit=1
2022-01-21T23:11:04.063917Z	   32 Query	set session transaction read write
2022-01-21T23:11:04.065650Z	   32 Query	SET autocommit=1
2022-01-21T23:11:04.087894Z	   31 Query	SELECT DATABASE()

However, I set 1 item to be persisted by this so I can trigger it easily. It is a motion sensor in my computer room, the last heard from date gets set with each trip under the item name: computerroomMotionLHF

When I trigger it, I get the following in my openhab log:

2022-01-21 18:19:04.128 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::store:  No connection to database. Cannot persist item 'computerroomMotionLHF (Type=DateTimeItem, State=2022-01-21T18:19:04.120031-0500, Label=Comuter Room, Category=calendar)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:3

==> /var/log/openhab/events.log <==

2022-01-21 18:19:04.125 [INFO ] [openhab.event.ItemStateChangedEvent ] - Item 'computerroomMotionLHF' changed from 2022-01-21T18:16:20.369044-0500 to 2022-01-21T18:19:04.120031-0500

The below is what shows in the mysql query.log:

2022-01-21T23:19:04.128905Z	   31 Query	SELECT 1

I noticed that the time stands are 5 hours off and I am UTC-5. So it looks like my mysql server is reporting zulu time 23:19:04.128905Z and openHAB is local time. Not sure if that would be causing an issue?

Charley · January 28, 2022, 9:56am

Did you place your location at the end of the url.

url=jdbc:mysql://127.0.0.1:3306/OpenHAB?serverTimezone=Europe/Berlin

tommycw10 · January 30, 2022, 6:06pm

@Charley - I did. Thanks for all of your help. I really appreciate it. TBH, I’ve given up on this. I installed influxdb and moved on. I wish I had my historical data, but its not the end of the world…