(mysql) restoreOnStartup?

Add-ons Persistence Services
1

I cant get this to work …
Nothing gets restored when I restart openhab…

My persistence file:

Strategies {
        // if no strategy is specified for an item entry below, the default list will be used
        everyMinute     : "0 * * * * ?"
        every15Minutes : "0 */15 * * * ?"
        everyHour   : "0 0 * * * ?"
        everyDay    : "0 0 0 * * ?"
        default = everyChange
}

/*
 * Each line in this section defines for which item(s) which strategy(ies) should be applied.
 * You can list single items, use "*" for all items or "groupitem*" for all members of a group
 * item (excl. the group item itself).
 */

Items {
    // persist all items once a day and on every change and restore them from the db at startup
    * : strategy = everyChange, everyDay, restoreOnStartup
}

If I look into the mysql-database I can see that after the restart, new “items” are created as if openhab cant see the old data and continue with it… (and therefore unable to restore it…?)

mysql> show tables;
+-------------------+
| Tables_in_OpenHAB |
+-------------------+
| Item1             |
| Item10            |
| Item11            |
| Item12            |
| Item13            |
| Item14            |
| Item15            |
| Item16            |
| Item17            |
| Item18            |
| Item19            |
| Item2             |
| Item20            |
| Item21            |
| Item22            |
| Item23            |
| Item24            |
| Item25            |
| Item26            |
| Item27            |
| Item28            |
| Item29            |
| Item3             |
| Item30            |
| Item31            |
| Item32            |
| Item33            |
| Item4             |
| Item5             |
| Item6             |
| Item7             |
| Item8             |
| Item9             |
| Items             |
| item0001          |
| item0002          |
| item0003          |
| item0004          |
| item0005          |
| item0006          |
| item0007          |
| items             |
+-------------------+
42 rows in set (0.00 sec)

Please advice!

2

Please provide more info:

  • OH1 or OH2?
  • your OS? OH and MySQL on same box?
  • persistence service used?
  • persistence cfg file (minus passwords, of course)
  • entries from openhab.log during startup (try to grep for mysql or MySQL)

there should definitely be only one “Items” table; that alone is strange. also the item000x tables seem dodgy.

3

I use Openhab2 (2.1.0-SNAPSHOT Build #789)
Raspbian (RPI2), mysql on the same box

JDBC Persistence MySQL persistence-jdbc-mysql - 1.10.0.SNAPSHOT
MySQL Persistence persistence-mysql - 1.10.0.SNAPSHOT

Why do I need both by the way ?

This is a perhaps interesting part of the startup in the log:

2017-03-20 22:05:23.151 [INFO ] [ternal.scheduler.WeatherJobScheduler] - Starting and scheduling weatherJob-home with interval of 30 minutes
2017-03-20 22:05:23.858 [ERROR] [org.openhab.persistence.mysql       ] - [org.openhab.persistence.mysql(179)] The activate method has thrown an exception
java.lang.IllegalAccessError: tried to access method com.mysql.jdbc.ConnectionImpl.getInstance(Ljava/lang/String;ILjava/util/Properties;Ljava/lang/String;Ljava/lang/String$
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)[:1.8.0_121]
        at java.sql.DriverManager.getConnection(DriverManager.java:247)[:1.8.0_121]
        at org.openhab.persistence.mysql.internal.MysqlPersistenceService.connectToDatabase(MysqlPersistenceService.java:521)
        at org.openhab.persistence.mysql.internal.MysqlPersistenceService.activate(MysqlPersistenceService.java:190)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)[:1.8.0_121] ........

Than a bit later in the log:

2017-03-20 22:05:24.145 [WARN ] [org.openhab.persistence.mysql       ] - FrameworkEvent WARNING - org.openhab.persistence.mysql
org.osgi.framework.ServiceException: org.apache.felix.scr.impl.manager.SingleComponentManager.getService() returned a null service object
        at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.factoryGetService(ServiceFactoryUse.java:232)[org.eclipse.osgi-3.10.101.v20150820-1432.jar:]
        at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.getService(ServiceFactoryUse.java:111)[org.eclipse.osgi-3.10.101.v20150820-1432.jar:]
        at org.eclipse.osgi.internal.serviceregistry.ServiceConsumer$2.getService(ServiceConsumer.java:45)[org.eclipse.osgi-3.10.101.v20150820-1432.jar:]
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.getService(ServiceRegistrationImpl.java:496)[org.eclipse.osgi-3.10.101.v20150820-1432.jar:]
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.getService(ServiceRegistry.java:461)[org.eclipse.osgi-3.10.101.v20150820-1432.jar:]
        at org.eclipse.osgi.internal.framework.BundleContextImpl.getService(BundleContextImpl.java:619)[org.eclipse.osgi-3.10.101.v20150820-1432.jar:] ........

Later on:

2017-03-20 22:05:25.085 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource

This is the digest of what I think would be of any importance…

Also now I see that the mysql-connection is broken after the restart … no new data is logged into the database

4

Ah! I think that is the culprit: you have two bindings assigned to the same DB and in addition there could be an error in the setup/config!
actually if you only use mysql, you only need the mysql persistence binding.
I personally use MySQL only with mysql binding and it works A-OK.
I would suggest you uninstall the JDBC binding and remove all associated files(jdbc.cfg and jdbc.persistence), configure the mysql binding, wipe the DB clean and restart OH.

5

hmm… I followed this guide: openHAB2 & MySQL persistence setup

It states that both bindings should be installed… ?

I will try to uninstall jdbc and see if that helps! thanks in advance

6

one step forward and one back here :slight_smile: Now the system actually restores the previous values at restart … great!

But now when I try to access a temperature-graph in my sitemap nothing shows up … and in the log:

2017-03-21 12:41:12.068 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::query: unable to find table for query, no data in database for item 'Temperature'. Current number of tables in the database: 3
2017-03-21 12:41:12.072 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::query: try to generate the table for item 'Temperature'

Is this related to the JDBC/mysql-binding that I removed?

7

Well 80% ain’t that bad :wink:
My guess would be: either the Item “Temperature” has just not been persisted again, or you have directly referenced the JDBC persistence in your chart definition.
Quote from the Docs:

Chart [item=] [icon=“”] [label=“”] [refresh=xxxx]
[period=xxxx] [service=“”] [begin=yyyyMMddHHmm] [end=yyyyMMddHHmm]
service sets the persistence service to use. If no service is set, openHAB will use the first queryable persistence service it finds. Therefore, for an installation with only a single persistence service, this is not required.

8

Problem solved!
I added service=“mysql” to my sitemap item … I read somewhere that you didnt need that if you only had one persistence service… but apparently that wasnt correct in my case…

9

100% Woohoo! Maybe you still have jdbc as standard persistence in paperUI,Configuration?