MySql > JDBC MySQL Persistance Migration

LifeOfPyro · December 29, 2020, 2:38pm

Hi all,

attempted an upgrade from OH 2.5>3 today, rolled back for now as the REST api changes break a whole buttload of my integrated controllers in the house… so I guess I’ll have to reprogram them or figure out a way to turn off API auth.

One problem I did encounter was persistance migration. I run a lot of rules for controling heating/lighting brightness and temps based on room states - so I need to restore these values from persistance or nothing works.

Previously they were stored in MapDB (logs tell me OH3 cant use the same DB as incompatible) and MySQL.

Following the new JDBC MySql binding and setting the relevant options (Item prefix=Item etc) yields a recall from the MySQL Db… but none of the items are mapped correctly.

Eg, AC_Mode state - Expected item value=2, recalled item value=1.427255182

Am I missing a trick here for migrating this? Really dont want to have to manually resync 1800odd item

cweitkamp · December 29, 2020, 9:00pm

Hi, see here - might be a similar problem:

LifeOfPyro · December 29, 2020, 9:22pm

Cheers - I’ll give that a go again tomorrow and see how I get on with it

Just on a side note - I assume by following the Item123 syntax without leading zeros (Item123 vs Item0123) per the old binding won’t present any issues? (Ie the Jdbc binding will just add more digits if it requires them for new items)

cweitkamp · December 29, 2020, 9:33pm

It will. You have to set the following configuration to 0 if you migrated recently.

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

foobar2605 · May 31, 2021, 4:48pm

Just found this thread here and I also had a very hard time to migrate the persistence from OH 2.5 to 3.0.1 (see Problem with persistence in OH3 - #22 by foobar2605).

There was an issue with the openjdk11 I had to fix (previously I had oracle java 8) and currently I get a strange NumberFormatException:

2021-05-30 22:37:21.408 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::activate: persistence service activated
2021-05-30 22:37:21.412 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::updateConfig
2021-05-30 22:37:21.418 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::JdbcConfiguration
2021-05-30 22:37:21.425 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: configuration size = 15
2021-05-30 22:37:21.437 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: user=openhab
2021-05-30 22:37:21.441 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: password exists? true
2021-05-30 22:37:21.443 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: url=jdbc:mysql://127.0.0.1:3306/OpenHAB?serverTimezone=CET
2021-05-30 22:37:21.444 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: found serviceName = 'mysql'
2021-05-30 22:37:21.445 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: Init Data Access Object Class: 'org.openhab.persistence.jdbc.db.JdbcMysqlDAO'
2021-05-30 22:37:21.450 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::initSqlTypes: Initialize the type array
2021-05-30 22:37:21.453 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::initSqlQueries: 'JdbcMysqlDAO'
2021-05-30 22:37:21.455 [DEBUG] [hab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initSqlTypes: Initialize the type array
2021-05-30 22:37:21.457 [DEBUG] [hab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initSqlQueries: 'JdbcMysqlDAO'
2021-05-30 22:37:21.458 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: dBDAO ClassName=org.openhab.persistence.jdbc.db.JdbcMysqlDAO
2021-05-30 22:37:21.460 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: tableNamePrefix=Item
2021-05-30 22:37:21.462 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: tableUseRealItemNames=false
2021-05-30 22:37:21.463 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: tableIdDigitCount=0
2021-05-30 22:37:21.464 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: rebuildTableNames=true
2021-05-30 22:37:21.466 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: enableLogTime false
2021-05-30 22:37:21.477 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: load JDBC-driverClass was successful: 'com.mysql.jdbc.Driver'
2021-05-30 22:37:21.481 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: configuration complete. service=jdbc
2021-05-30 22:37:21.482 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB
2021-05-30 22:37:21.483 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection isDriverAvailable: true
2021-05-30 22:37:21.484 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
2021-05-30 22:37:21.504 [WARN ] [.zaxxer.hikari.util.DriverDataSource] - Registered driver with driverClassName=com.mysql.jdbc.Driver was not found, trying direct instantiation.
2021-05-30 22:37:21.889 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::setDbConnected true
2021-05-30 22:37:21.891 [DEBUG] [hab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initAfterFirstDbConnection: Initializing step, after db is connected.
2021-05-30 22:37:21.963 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbMajorVersion = '5'
2021-05-30 22:37:21.964 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbMinorVersion = '7'
2021-05-30 22:37:21.966 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - driverMajorVersion = '8'
2021-05-30 22:37:21.967 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - driverMinorVersion = '0'
2021-05-30 22:37:21.968 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbProductName = 'MySQL'
2021-05-30 22:37:21.969 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbProductVersion = '5.7.33-0ubuntu0.18.04.1'
2021-05-30 22:37:21.973 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB asking db for name as absolutely first db action, after connection is established.
2021-05-30 22:37:22.169 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::checkDBAcessability, first try connection: true
2021-05-30 22:37:22.171 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createItemsTableIfNot
2021-05-30 22:37:22.174 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doCreateItemsTableIfNot sql=CREATE TABLE IF NOT EXISTS items (ItemId INT NOT NULL AUTO_INCREMENT,itemname VARCHAR(500) NOT NULL,PRIMARY KEY (ItemId))
2021-05-30 22:37:22.176 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getItemIDTableNames
2021-05-30 22:37:22.179 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doGetItemIDTableNames sql=SELECT itemid, itemname FROM items
2021-05-30 22:37:22.244 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getItemTables
2021-05-30 22:37:22.250 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doGetItemTables sql=SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='OpenHAB' AND NOT table_name='items'
2021-05-30 22:37:22.278 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::formatTableNames: found Table Name= HISTORIC_ITEM
2021-05-30 22:37:22.286 [ERROR] [org.openhab.persistence.jdbc        ] - bundle org.openhab.persistence.jdbc:3.0.2 (261)[org.openhab.persistence.jdbc.internal.JdbcPersistenceService(320)] : The activate method has thrown an exception
java.lang.NumberFormatException: For input string: "ITEM"
        at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) ~[?:?]
        at java.lang.Integer.parseInt(Integer.java:652) ~[?:?]
        at java.lang.Integer.parseInt(Integer.java:770) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.formatTableNames(JdbcMapper.java:333) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.checkDBSchema(JdbcMapper.java:229) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.updateConfig(JdbcPersistenceService.java:220) ~[?:?]
        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.internal.framework.EquinoxBundle.start(EquinoxBundle.java:383) [org.eclipse.osgi-3.12.100.jar:?]
        at org.eclipse.osgi.internal.framework.EquinoxBundle.start(EquinoxBundle.java:402) [org.eclipse.osgi-3.12.100.jar:?]
        at org.apache.karaf.features.internal.service.BundleInstallSupportImpl.startBundle(BundleInstallSupportImpl.java:165) [bundleFile:?]
        at org.apache.karaf.features.internal.service.FeaturesServiceImpl.startBundle(FeaturesServiceImpl.java:1153) [bundleFile:?]
        at org.apache.karaf.features.internal.service.Deployer.deploy(Deployer.java:1036) [bundleFile:?]
        at org.apache.karaf.features.internal.service.FeaturesServiceImpl.doProvision(FeaturesServiceImpl.java:1062) [bundleFile:?]
        at org.apache.karaf.features.internal.service.FeaturesServiceImpl.lambda$doProvisionInThread$13(FeaturesServiceImpl.java:998) [bundleFile:?]
        at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
        at java.lang.Thread.run(Thread.java:829) [?:?]

I also tried JPA persistence but this also doesn’t work out of the box. There the mysql driver cannot be found.

My settings for JDBC/Mysql are:

url=jdbc:mysql://127.0.0.1:3306/OpenHAB?serverTimezone=CET
user=openhab
password=xxxxxx
tableNamePrefix=Item
tableUseRealItemNames=false
tableIdDigitCount=0
rebuildTableNames=false

Any help appreciated!

foobar2605 · June 3, 2021, 10:14am

Found the solution! There were some old tables called HISTORIC_ITEM and OPENJPA_SEQUENCE_TABLE in the schema that led to exceptions reading all Item tables.
After deleting them, the initialization runs fine.