mySQL persistence won't work

Setup, Configuration and Use Beginners
,
1

Hi,

i have configured mySQL and at some point it has created a table “items” with nothing inside. Now it just says:

2016-08-14 22:50:24.991 [INFO ] [g.apache.aries.spifly.dynamic.bundle] - Bundle Considered for SPI providers: org.openhab.persistence.mysql
2016-08-14 22:50:24.991 [INFO ] [g.apache.aries.spifly.dynamic.bundle] - No 'SPI-Provider' Manifest header. Skipping bundle: org.openhab.persistence.mysql

when i restart the service. Here my config:

############################ mySQL Persistence Service ##################################
mysql:url=jdbc:mysql://127.0.0.1:3306/openhab
mysql:user=root
mysql:password=blabla
mysql:reconnectCnt=10
mysql:waitTimeout=30

I am running 2.0.0-SNAPSHOT Build #445

Thanks, Jens

2

Hi Jens,

I am not sure if that helps, but how did you configure your mySQL Persistence ?
In your openHAB/conf/services folder, you should have a file called mysql.cfg which includes your quoted config.
If so, you need to remove the leading mysql: from each line, as this is not needed in individual config files.
Furthermore, did you configure your persistence strategy in openHAB/conf/persistence/mysql.persist ?

Best Regards
Hans-Jörg

3

Hi Hans-Jörg,

that was the content of the conf/services/mysql.cfg file and even without the leading “mysql:” there is no difference. My conf/persistence/mysql.persist looks like this:

Stategies {
    default = everyChange
}
Items {
    * : strategy = everyChange
}

myopenhab persistence work pretty well…

Best Regards, Jens :relaxed:

4

Hi Jens,

after removing the leading mysql: and a restart of openhab, is there any difference in the logs ?

Best
Hans-Jörg

5

unfortunately not… the file looks now like

############################ mySQL Persistence Service ##################################

# the database url like 'jdbc:mysql://<host>:<port>/<database>'
url=jdbc:mysql://127.0.0.1:3306/openhab

# the database user
user=root

# the database password
password=blabla

# the reconnection counter
reconnectCnt=10

# the connection timeout (in seconds)
waitTimeout=30

and the logs after restart are (with little bit more detail):

2016-08-15 13:41:10.867 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'mysql.persist'
2016-08-15 13:41:15.659 [DEBUG] [org.openhab.persistence.mysql       ] - BundleEvent STARTING - org.openhab.persistence.mysql
2016-08-15 13:41:15.660 [DEBUG] [nal.MysqlPersistenceServiceActivator] - mySQL persistence bundle has been started.
2016-08-15 13:41:15.687 [DEBUG] [sql.internal.MysqlPersistenceService] - mySQL: Attempting to connect to database jdbc:mysql://127.0.0.1:3306/openhab
2016-08-15 13:41:16.135 [DEBUG] [sql.internal.MysqlPersistenceService] - mySQL: Connected to database jdbc:mysql://127.0.0.1:3306/openhab
2016-08-15 13:41:16.139 [DEBUG] [sql.internal.MysqlPersistenceService] - mySQL: Setting wait_timeout to 30 seconds.
2016-08-15 13:41:16.140 [DEBUG] [sql.internal.MysqlPersistenceService] - mySQL configuration complete.
2016-08-15 13:41:16.142 [DEBUG] [org.openhab.persistence.mysql       ] - ServiceEvent REGISTERED - {org.openhab.core.persistence.PersistenceService}={service.pid=org.openhab.mysql, waitTimeout=30, user=root, url=jdbc:mysql://127.0.0.1:3306/openhab, password=xxxxxxxx, reconnectCnt=10, component.name=org.openhab.persistence.mysql, component.id=184, service.id=335, service.bundleid=197, service.scope=bundle} - org.openhab.persistence.mysql
2016-08-15 13:41:16.142 [DEBUG] [org.openhab.persistence.mysql       ] - BundleEvent STARTED - org.openhab.persistence.mysql
2016-08-15 13:41:16.142 [INFO ] [g.apache.aries.spifly.dynamic.bundle] - Bundle Considered for SPI providers: org.openhab.persistence.mysql
2016-08-15 13:41:16.142 [INFO ] [g.apache.aries.spifly.dynamic.bundle] - No 'SPI-Provider' Manifest header. Skipping bundle: org.openhab.persistence.mysql```

and still not a single entry in the table "items"

Jens
6

Did you copy the according driver into the addons folder as described here ?

Ah, just saw the connected message in the log, so I guess you did.
Now I am running out of clues.

This thread might help you more than I can.

7

good idea :grin:

the persistence configuration i posted above does seems to work with myopenhab but not with mysql. I changed to

Strategies {
everyMinute : "0 * * * * ?"
everyHour : "0 0 * * * ?"
everyDay  : "0 0 0 * * ?"

default = everyChange
}

 		Items {
gSQL* : strategy = everyChange, everyHour, everyDay, restoreOnStartup

}

And now it works like a charm

Thanks, Jens

8

Glad it works and I could be a bit of a help. :wink:

Best
Hans-Jörg