[SOLVED] [HELP] MySQL persistence not populating my database

I have started work on migrating my OH configuration on a new instance, going from 2.2.0 to 2.3.0. However I have found that persistence isn’t working for MySQL, I get the below error from my 2.3.0 instance. I’ve also found that it’s not working in my 2.2.0 instance either, both are connecting to same database server. I’m not sure when it stopped working for my 2.2.0 instance as I don’t restart openHAB or reboot the server.

I hope I’ve provided enough information below for someone to offer advice. I’ve search the forum and although there a number of similar error’s I’ve not found the exact same error. Although that could be down to my search skills.

VM running Centos 7.
MySQL Version: 5.6.42 - MySQL Community Server (GPL)

jdbc.cfg

url=jdbc:mysql://db01.local.mycyberspace.co.uk:3306/openHAB230
user=openhab
password=openhab
tableNamePrefix=Item
tableUseRealItemNames=false
tableIdDigitCount=0

mysql.persist

Strategies {
        default = everyUpdate
}
Items {
        * : strategy = everyChange, restoreOnStartup
}
2018-11-30 11:06:33.640 [ERROR] [org.knowm.yank.Yank                 ] - Error in SQL query!!!
java.sql.SQLException: Query was empty Query:  Parameters: []
        at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392) [258:org.openhab.persistence.jdbc:1.12.0]
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491) [258:org.openhab.persistence.jdbc:1.12.0]
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457) [258:org.openhab.persistence.jdbc:1.12.0]
        at org.knowm.yank.Yank.execute(Yank.java:194) [258:org.openhab.persistence.jdbc:1.12.0]
        at org.knowm.yank.Yank.execute(Yank.java:177) [258:org.openhab.persistence.jdbc:1.12.0]
        at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doUpdateItemTableNames(JdbcBaseDAO.java:315) [258:org.openhab.persistence.jdbc:1.12.0]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.updateItemTableNames(JdbcMapper.java:121) [258:org.openhab.persistence.jdbc:1.12.0]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.formatTableNames(JdbcMapper.java:341) [258:org.openhab.persistence.jdbc:1.12.0]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.checkDBSchema(JdbcMapper.java:214) [258:org.openhab.persistence.jdbc:1.12.0]
        at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.updateConfig(JdbcPersistenceService.java:219) [258:org.openhab.persistence.jdbc:1.12.0]
        at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.activate(JdbcPersistenceService.java:61) [258:org.openhab.persistence.jdbc:1.12.0]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:?]
        at org.apache.felix.scr.impl.inject.BaseMethod.invokeMethod(BaseMethod.java:229) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.inject.BaseMethod.access$500(BaseMethod.java:39) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.inject.BaseMethod$Resolved.invoke(BaseMethod.java:650) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.inject.BaseMethod.invoke(BaseMethod.java:506) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.inject.ActivateMethod.invoke(ActivateMethod.java:307) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.inject.ActivateMethod.invoke(ActivateMethod.java:299) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.manager.SingleComponentManager.createImplementationObject(SingleComponentManager.java:298) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.manager.SingleComponentManager.createComponent(SingleComponentManager.java:109) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.manager.SingleComponentManager.getService(SingleComponentManager.java:906) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.manager.SingleComponentManager.getServiceInternal(SingleComponentManager.java:879) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.manager.SingleComponentManager.getService(SingleComponentManager.java:823) [39:org.apache.felix.scr:2.0.12]
        at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse$1.run(ServiceFactoryUse.java:212) [?:?]
        at java.security.AccessController.doPrivileged(Native Method) ~[?:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.factoryGetService(ServiceFactoryUse.java:210) [?:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.getService(ServiceFactoryUse.java:111) [?:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceConsumer$2.getService(ServiceConsumer.java:45) [?:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.getService(ServiceRegistrationImpl.java:508) [?:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.getService(ServiceRegistry.java:461) [?:?]
        at org.eclipse.osgi.internal.framework.BundleContextImpl.getService(BundleContextImpl.java:624) [?:?]
        at com.eclipsesource.jaxrs.publisher.internal.ResourceTracker.addingService(ResourceTracker.java:39) [15:com.eclipsesource.jaxrs.publisher:5.3.1.201602281253]
        at org.osgi.util.tracker.ServiceTracker$Tracked.customizerAdding(ServiceTracker.java:941) [?:?]
        at org.osgi.util.tracker.ServiceTracker$Tracked.customizerAdding(ServiceTracker.java:870) [?:?]
        at org.osgi.util.tracker.AbstractTracked.trackAdding(AbstractTracked.java:256) [?:?]
        at org.osgi.util.tracker.AbstractTracked.track(AbstractTracked.java:229) [?:?]
        at org.osgi.util.tracker.ServiceTracker$Tracked.serviceChanged(ServiceTracker.java:901) [?:?]
        at org.eclipse.osgi.internal.serviceregistry.FilteredServiceListener.serviceChanged(FilteredServiceListener.java:109) [?:?]
        at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:920) [?:?]
        at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230) [?:?]
        at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:148) [?:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEventPrivileged(ServiceRegistry.java:862) [?:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEvent(ServiceRegistry.java:801) [?:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.register(ServiceRegistrationImpl.java:127) [?:?]
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.registerService(ServiceRegistry.java:225) [?:?]
        at org.eclipse.osgi.internal.framework.BundleContextImpl.registerService(BundleContextImpl.java:469) [?:?]
        at org.apache.felix.scr.impl.manager.AbstractComponentManager$3.register(AbstractComponentManager.java:887) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.manager.AbstractComponentManager$3.register(AbstractComponentManager.java:874) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.manager.RegistrationManager.changeRegistration(RegistrationManager.java:132) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.manager.AbstractComponentManager.registerService(AbstractComponentManager.java:941) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.manager.AbstractComponentManager.activateInternal(AbstractComponentManager.java:741) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.manager.AbstractComponentManager.enableInternal(AbstractComponentManager.java:675) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.manager.AbstractComponentManager.enable(AbstractComponentManager.java:430) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.manager.ConfigurableComponentHolder.enableComponents(ConfigurableComponentHolder.java:657) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.BundleComponentActivator.initialEnable(BundleComponentActivator.java:341) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.Activator.loadComponents(Activator.java:390) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.Activator.access$200(Activator.java:54) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.scr.impl.Activator$ScrExtension.start(Activator.java:265) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.utils.extender.AbstractExtender.createExtension(AbstractExtender.java:254) [39:org.apache.felix.scr:2.0.12]
        at org.apache.felix.utils.extender.AbstractExtender.modifiedBundle(AbstractExtender.java:227) [39:org.apache.felix.scr:2.0.12]
        at org.osgi.util.tracker.BundleTracker$Tracked.customizerModified(BundleTracker.java:482) [?:?]
        at org.osgi.util.tracker.BundleTracker$Tracked.customizerModified(BundleTracker.java:415) [?:?]
        at org.osgi.util.tracker.AbstractTracked.track(AbstractTracked.java:232) [?:?]
        at org.osgi.util.tracker.BundleTracker$Tracked.bundleChanged(BundleTracker.java:444) [?:?]
        at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:908) [?:?]
        at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230) [?:?]
        at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:148) [?:?]
        at org.eclipse.osgi.internal.framework.EquinoxEventPublisher.publishBundleEventPrivileged(EquinoxEventPublisher.java:213) [?:?]
        at org.eclipse.osgi.internal.framework.EquinoxEventPublisher.publishBundleEvent(EquinoxEventPublisher.java:120) [?:?]
        at org.eclipse.osgi.internal.framework.EquinoxEventPublisher.publishBundleEvent(EquinoxEventPublisher.java:112) [?:?]
        at org.eclipse.osgi.internal.framework.EquinoxContainerAdaptor.publishModuleEvent(EquinoxContainerAdaptor.java:168) [?:?]
        at org.eclipse.osgi.container.Module.publishEvent(Module.java:476) [?:?]
        at org.eclipse.osgi.container.Module.start(Module.java:467) [?:?]
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.incStartLevel(ModuleContainer.java:1634) [?:?]
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.incStartLevel(ModuleContainer.java:1614) [?:?]
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.doContainerStartLevel(ModuleContainer.java:1585) [?:?]
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.dispatchEvent(ModuleContainer.java:1528) [?:?]
        at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.dispatchEvent(ModuleContainer.java:1) [?:?]
        at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230) [?:?]
        at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:340) [?:?]

I would use mapdb for restoreOnStartUp. Far for efficient. SQL is very slow for that purpose.

Have you tried clearing the cache?

Thanks I’ll take a look. As it’s a fresh installation of openHAB I would hope there is no cache to clear.

Is there a way of viewing the contents of the mapdb? One of the reasons for using MySQL was so that I could use the data elsewhere.

You can use both
I use 3 persistence services
influxdb for graphing
mapdb to restore on startup
rrd4j for stats and persistence functions in rules

I’ve also got influxdb for graphing and that works just fine. Care to share more details on how you use rrd4j? I’m always interested in seeing what can be done with openHAB, sadly I have loads of ideas but not always able to put them into practise due to a total lack of knowledge.

Thanks

Garry

by the way: You may? have found a bug in the JDBC persistence addon when using MySQL as the DB backend
According to my understanding, org.knowm.yank.Yank is the JDBC Persistance Layer used by this OH2 addon.

You can post more info to see if we can see if we can reproduce this?
info like: Item configs, TRACE logs from the bundle, sample data for Item states being persisted, sample data from the DB (or a complete DB export, etc).

If you don’t want to go into deep debugging: forget it :slight_smile:

That wouldn’t help now would it. I’ll upload further details shortly.

1 Like

You can use this service to keep the value of an item and use this value in a rule. For example, if you have a item that reads power consumption and wanted to fire a rule based on the average power.

Example:

if(Power_Item.averageSince(now.minusMinutes(3)) > 1){
    Do something 
} 

rrd4j example

Strategies {
	// for rrd chart cron strategy every minute
	everyMinute : "0 * * * * ?"
	// get data reduced for older values to keep database small
	everyHour : "0 0 * * * ?"
	everyDay : "0 0 0 * * ?"

	default = everyChange
}

Items {
	Power_Item : strategy = everyUpdate, everyMinute, restoreOnStartup

Hope this helps.

3 Likes

I turned trace logging on and didn’t find anything beyond my original error. Which confused me no end. So decided to clear my 2.3.0 installation and start with the basics. Setup persistence and added a small number of things and items. Low and behold I don’t have any errors at all and the MySQL database is being populated.

So I’m not sure it’s a bug that we need to identify, but as I now build up my 2.3.0 installation I’ll keep track of things in case it happens again.

Cheers

Garry

1 Like