openHAB2 & MySQL persistence setup

Thanks a lot, Christian
I guess the two similar named are confusing. So it’s like:

mySQL persistence with a stand alone / separate mySQL server
vs
mySQL.JCDB persistence with a built in JCDB server (usiing mySQL datastructure)!?
Is this correctly summarized?

No, not really. You need a mysql server (either on another machine or on the machine running openHAB) and then you need either mysql or jdbc persistance to talk to the mysql server. In my experience jdbc persistance is the preferrable one, it has more configuration options. Mysql persistance is dedicated for only writing to mysql databases while jdbc persistance can write to various different databases, that’s the big difference.

alright - thanks for the clarification.
I will give it a try soon :slight_smile:

one more :wink:
in your latest post you prefer the jcdb.mySQL, but in your initial post, you are talking about the “regular” mySQL.
So you found out later, that jcdb.mySQL works better?
Sorry for this rookie question.
I just wanna make sure before I mess up my current setup :wink:

I’m not really sure which initial post you are referring to, I’ve only posted once in this thread :slight_smile:

But I guess there must be advantages with the mysql persistence over the jdbc one or else it wouldn’t have existed. Just I haven’t found them.

/Daniel

Damn it - you are right.
I just checked the response from Christian and I did not recognize, that you jumped in.
Sorry for that :wink:
Thanks for your help

Thanks Got 1/2 working

2017-06-23 17:38:51.171 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'mySQL.persist'

But down the Log more I see

2017-06-23 17:38:56.708 [WARN ] [sql.internal.MysqlPersistenceService] - The SQL database URL is missing - please configure the sql:url parameter in openhab.cfg

so where is the openhab.cfg hidding

I Using ubuntu

did a reboot

and found this in the log

2017-06-23 14:33:38.385 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://192.168.1.24:3306/openHAB, user=openhab, password=Blabla
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'openhab'@'%' to database 'openHAB'

NOW im loss

did you do the following to allow the user openhab to log into the DB?
Login to your DB:
mysql -u root -p

and then:

CREATE USER ‘openhab’@‘localhost’ IDENTIFIED BY ‘password’;

GRANT ALL PRIVILEGES ON . TO ‘openhab’@‘localhost’ WITH GRANT OPTION;

Yes I did that

Hmmm, is your mySQL DB on the same computer than openhab?

Yes it is

Please try:
url=jdbc:mysql://127.0.0.1:3306/openHAB

Maybe the SQL assumes that you are coming from another computer if you don’t access the localhost IP adress?

By the way

Are you running OH2?

In this case the log entry seems to be old, because it should be “mysql.cfg” in case you use the mySQL persistence binding or “jdbc.cfg” if you use jdbc-mySQL binding.

Hi!

I do have some problems that cause me a lot of failure entries in my log. Since i installed mysql my system is not running smooth anymore. To solve this i would like to back up only the values i need (login data from spotify). can you give me an example how to back up a single item?

In addition to that i would like to clear my DB (get rid of the old values, previously backuped).

Thank you for your help!

You can define your item in the items section (* is for all items…)
Just have a look at what Christian_V wrote:

Mine is called “mysql.persist” and it looks something like this:

// persistence strategies have a name and a definition and are referred to in the "Items" section
Strategies {
// if no strategy is specified for an item entry below, the default list will be used
everyMinute	: "0 * * * * ?"
every5Minutes : "0 */5 * * * ?"
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

// additionally, persist all temperature and weather values every hour
   gTemperatur* : strategy = every5Minutes, restoreOnStartup

For example:

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

Thanks for the guide
I had everything in the directory
And I got this error

2017-11-08 21:19:39.100 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query ‘Temperature’.

Should I open a table for each variable separately for the first time?
Or should the software open on its own for the first time?

Thank you

@Christian_V Thank you very much for this tutorial.

Do you know by chance how to import data from an existing openhab-mysql-DB on a windows machine where OH1.8.3 is running to a RPI3 where openHabian with OH2.1 is running?

Cheers
John

Did you get a result? I got the same error :frowning:

2017-11-24 15:48:14.105 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'mysql.persist'
2017-11-24 15:48:14.568 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gStatusNetwork'.
2017-11-24 15:48:14.584 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gSchlaf'.
2017-11-24 15:48:14.590 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gOffice'.
2017-11-24 15:48:14.599 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gWohn'.
2017-11-24 15:48:14.606 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gDiele'.
2017-11-24 15:48:14.612 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gFunksteckdose'.
2017-11-24 15:48:14.618 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gDevices'.
2017-11-24 15:48:14.630 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gStatus'.
2017-11-24 15:48:14.637 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gAnwesenheit'.
2017-11-24 15:48:14.644 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gAlexa'.
2017-11-24 15:48:14.652 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Unable to find table for query 'gLicht'.

Anyways…can some explain me the definition?

Items {

// persist all items once a day and on every change and restore them from the db at startup

* : strategy = everyChange, everyDay, restoreOnStartup

// additionally, persist all temperature and weather values every hour

   gTemperatur* : strategy = every5Minutes, restoreOnStartup

Does this means that he is persist all items by everychange just once a day at time “X”? or is he persist every item on every change every time?

gTemperatur is just like a filter or how do i under stand the relation between the default and additional items/groups?

Regarding the persistence definition - it’s a little mess. I’m logging everything “*” on every change and in addition I’ve defined a group “gTemperatur” which I persisted every 5 min to get nice charts. I’ve got everything in the DB to be able to restore on startup (state persistence). Since I didn’t want to have multiple persistence services (mapdb could be used to store states from switches etc.) I’ve stored everything in MySQL.

You can define strategies like you want and schedule them on groups like mit “gTemerperatur*”.

To get rid of these errors you can try to restart your MySQL service. I’ve had problems when I started my OH service or the MySQL service and OH wasn’t able to re-establish the connection.

Hope that helps.
Christian

The errors in your log appear when there are no data available, means nothing has been persisted so far: [solved] mySQL: Unable to find table for query

I recommend that you change your persistence strategy either group-wise or for single items. Otherwise your DB will be spammed with a lot of information that you will never use

Which means I should delete the “default” entry and just add these items or groups which i only want to persist? right?