openHAB2 & MySQL persistence setup

siod · September 28, 2017, 9:11am

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

new11120 · November 9, 2017, 5:44pm

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

JohnnyX · November 11, 2017, 4:56pm

@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

Multisaft7 · November 24, 2017, 3:01pm

Did you get a result? I got the same error

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?

Christian_V · November 24, 2017, 4:49pm

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

JohnnyX · November 25, 2017, 3:03pm

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

Multisaft7 · November 25, 2017, 5:14pm

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

JohnnyX · November 25, 2017, 6:46pm

Right

Multisaft7 · November 26, 2017, 4:08pm

How am I able to see the yet stored data in my mysql persistence?!

JohnnyX · November 26, 2017, 5:30pm

e.g. with MySQL Workbench but basically this will work with any kind of SQL viewer/editor.

Multisaft7 · November 26, 2017, 10:34pm

but what is then my hostname to connect to my pi from windows with MYSQL Workbench,Sorry but I am a little bit confussed at the moment

Edit:

I have no clue why I did not get any connection…I did step by step your tutorial…
System: Openhabianpi with latest 2.2 Snapshot

2017-11-27 00:34:12.839 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'Date' in database with statement 'INSERT INTO Item19 (TIME, VALUE) VALUES(?,?) ON DUPLICATE KEY UPDATE VALUE=?;': No operations allowed after connection closed.

2017-11-27 00:34:12.846 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!

2017-11-27 00:34:12.850 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!

2017-11-27 00:34:12.857 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'Current_DateTime' in database with statement 'INSERT INTO Item18 (TIME, VALUE) VALUES(?,?) ON DUPLICATE KEY UPDATE VALUE=?;': No operations allowed after connection closed.

2017-11-27 00:54:53.346 [DEBUG] [hread.strategy.ExecuteProduceConsume] - EPC Prod/org.eclipse.jetty.io.ManagedSelector$SelectorProducer@1853c9e ran SelectChannelEndPoint@338e9e{/192.177.77.1:80<->51938,Open,in,out,FI,-,25/0,HttpConnectionOverHTTP@150cbd0}{io=1/1,kio=1,kro=1}:runFillable

2017-11-27 00:54:53.339 [DEBUG] [org.eclipse.jetty.server.HttpChannel] - sendResponse info=null content=DirectByteBuffer@1e8de52[p=0,l=165,c=32768,r=165]={<<<event: message\nda...mStateEvent"}\n\n>>>ONE\\"}","type":"T...\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00} complete=false committing=false callback=Blocker@8fca22{null}

2017-11-27 00:54:53.348 [DEBUG] [hread.strategy.ExecuteProduceConsume] - EPC Prod/org.eclipse.jetty.io.ManagedSelector$SelectorProducer@1853c9e produce exit

2017-11-27 00:54:53.349 [DEBUG] [e.jetty.util.thread.QueuedThreadPool] - ran EPC Prod/org.eclipse.jetty.io.ManagedSelector$SelectorProducer@1853c9e

2017-11-27 00:54:53.350 [DEBUG] [.eclipse.jetty.server.HttpConnection] - org.eclipse.jetty.server.HttpConnection$SendCallback@12a22f9[PROCESSING][i=null,cb=Blocker@8fca22{null}] generate: FLUSH (null,[p=0,l=165,c=32768,r=165],false)@COMMITTED

2017-11-27 00:54:53.359 [DEBUG] [org.eclipse.jetty.io.ChannelEndPoint] - flushed 171 SelectChannelEndPoint@1bed1a0{/2003:de:bbfd:b600:cc70:2df8:44c9:2fab:49484<->8080,Open,in,out,-,W,27/30000,HttpConnection@164ef4d}{io=0/0,kio=0,kro=1}

==> /var/log/openhab2/events.log <==

2017-11-27 00:54:53.361 [vent.ItemStateChangedEvent] - fritzdect200_office_power changed from 166.940 to 167.940

==> /var/log/openhab2/openhab.log <==

2017-11-27 00:54:53.365 [DEBUG] [org.eclipse.jetty.io.WriteFlusher   ] - Flushed=true 6/6+1 WriteFlusher@3f42f0{WRITING}

2017-11-27 00:54:53.370 [DEBUG] [.eclipse.jetty.server.HttpConnection] - org.eclipse.jetty.server.HttpConnection$SendCallback@12a22f9[PROCESSING][i=null,cb=Blocker@8fca22{null}] generate: DONE (null,[p=165,l=165,c=32768,r=0],false)@COMMITTED

2017-11-27 00:54:53.373 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!

2017-11-27 00:54:53.376 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!

2017-11-27 00:54:53.376 [DEBUG] [org.eclipse.jetty.server.HttpChannel] - sendResponse info=null content=DirectByteBuffer@1e8de52[p=0,l=162,c=32768,r=162]={<<<event: message\nda...mStateEvent"}\n\n>>>}\n\nONE\\"}","type"...\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00} complete=false committing=false callback=Blocker@8fca22{null}

2017-11-27 00:54:53.379 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'fritzdect200_office_energy' in database with statement 'INSERT INTO Item22 (TIME, VALUE) VALUES(?,?) ON DUPLICATE KEY UPDATE VALUE=?;': No operations allowed after connection closed.

2017-11-27 00:54:53.382 [DEBUG] [.eclipse.jetty.server.HttpConnection] - org.eclipse.jetty.server.HttpConnection$SendCallback@12a22f9[PROCESSING][i=null,cb=Blocker@8fca22{null}] generate: FLUSH (null,[p=0,l=162,c=32768,r=162],false)@COMMITTED

2017-11-27 00:54:53.383 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!

2017-11-27 00:54:53.386 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Error count exceeded 10. Disconnecting database.

2017-11-27 00:54:53.386 [DEBUG] [org.eclipse.jetty.io.ChannelEndPoint] - flushed 168 SelectChannelEndPoint@1bed1a0{/2003:de:bbfd:b600:cc70:2df8:44c9:2fab:49484<->8080,Open,in,out,-,W,21/30000,HttpConnection@164ef4d}{io=0/0,kio=0,kro=1}

2017-11-27 00:54:53.390 [DEBUG] [org.eclipse.jetty.io.WriteFlusher   ] - Flushed=true 6/6+1 WriteFlusher@3f42f0{WRITING}

2017-11-27 00:54:53.394 [DEBUG] [.eclipse.jetty.server.HttpConnection] - org.eclipse.jetty.server.HttpConnection$SendCallback@12a22f9[PROCESSING][i=null,cb=Blocker@8fca22{null}] generate: DONE (null,[p=162,l=162,c=32768,r=0],false)@COMMITTED

2017-11-27 00:54:53.404 [DEBUG] [org.eclipse.jetty.server.HttpChannel] - sendResponse info=null content=DirectByteBuffer@1e8de52[p=0,l=152,c=32768,r=152]={<<<event: message\nda...mStateEvent"}\n\n>>>eEvent"}\n\n}\n\nONE\\...\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00} complete=false committing=false callback=Blocker@8fca22{null}

2017-11-27 00:54:53.413 [DEBUG] [.eclipse.jetty.server.HttpConnection] - org.eclipse.jetty.server.HttpConnection$SendCallback@12a22f9[PROCESSING][i=null,cb=Blocker@8fca22{null}] generate: FLUSH (null,[p=0,l=152,c=32768,r=152],false)@COMMITTED

2017-11-27 00:54:53.422 [DEBUG] [org.eclipse.jetty.io.ChannelEndPoint] - flushed 158 SelectChannelEndPoint@1bed1a0{/2003:de:bbfd:b600:cc70:2df8:44c9:2fab:49484<->8080,Open,in,out,-,W,28/30000,HttpConnection@164ef4d}{io=0/0,kio=0,kro=1}

2017-11-27 00:54:53.426 [DEBUG] [org.eclipse.jetty.io.WriteFlusher   ] - Flushed=true 6/6+1 WriteFlusher@3f42f0{WRITING}

2017-11-27 00:54:53.429 [DEBUG] [.eclipse.jetty.server.HttpConnection] - org.eclipse.jetty.server.HttpConnection$SendCallback@12a22f9[PROCESSING][i=null,cb=Blocker@8fca22{null}] generate: DONE (null,[p=152,l=152,c=32768,r=0],false)@COMMITTED

2017-11-27 00:54:53.433 [DEBUG] [org.eclipse.jetty.server.HttpChannel] - sendResponse info=null content=DirectByteBuffer@1e8de52[p=0,l=159,c=32768,r=159]={<<<event: message\nda...mStateEvent"}\n\n>>>}\n\n}\n\nONE\\"}","ty...\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00} complete=false committing=false callback=Blocker@8fca22{null}

# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
url=jdbc:mysql://127.0.0.1:3306/OpenHAB

# the database user
user=openhab

# the database password
password=blablabla

# the reconnection counter
#reconnectCnt=

# the connection timeout (in seconds)
#waitTimeout=

# Use MySQL Server time to store item values (=false) or use openHAB Server time (=true).
# For new installations, its recommend to set "localtime=true".
# (optional, defaults to false)
#localtime=true

JohnnyX · November 27, 2017, 6:53pm

Assuming you followed the tutorial step by step:

Create a user for OpenHAB

CREATE USER 'openhab'@'localhost' IDENTIFIED BY 'YOURPASSWORD';
Grant the user permissions for the database

GRANT ALL PRIVILEGES ON OpenHAB.* TO 'openhab'@'localhost';

This would mean to connect to your Pi with its IP (hostname) with username “OpenHab” and “YOURPASSWORD”.

You open the workbench, click on the “+” in top left corner (next to MySQL connections), adding the IP of your PI and your credentials, then bottom right corner “test connection” - you are done.

riturrioz · November 29, 2017, 7:59pm

Hi guys!

I don’t know if anyone is going to find it useful, but I have created a bash script that automates the installation and configuration of a MySQL persistence setup. Here you have the Github repository with the installation instructions:

The script must be run in the openHAB host machine, so you can’t use to perform a remote installation, sorry.

If anyone has any question, don’t hesitate to ask!

Best regards,

Aitor

rlkoshak · November 29, 2017, 8:30pm

I recommend posting this as its own thread as well. It can easily get lost as the 46th posting to a more than year old thread.

RayMYP · December 6, 2017, 2:33am

If I’m reading those settings right then if @Multisaft7 or anyone else has followed the tutorial step by step then they wouldn’t be able to connect to the MySQL database as user ‘openhab’ from a remote machine be it using OH or Workbench. The user privileges are set to only allow the database user to connect from localhost, not external IPs to the server. You can change that if you want but it’s a good default setting for the tutorial as I suspect 98% will deploy on a single host.

Also I would hope your server firewall will need amending to allow access to MySQL, from single source IP only i.e. don’t open it wide to all.

hansvandamme · December 10, 2017, 12:26pm

I followed the steps in this tutorial, using the mysql persistence service. Just one table is created: items. Nothing else happens. I see no connection attempts to the database, no errors in the karaf logging. From the posts in this topic, I get the impression that mysql persistence sometimes just starts working, sometimes by choosing the mysql persistence, sometimes by using JDBC persistence. But of course I would like to understand why.

I wonder, for example, what I am supposed to put in the persistence section in /etc/openhab2/services/runtime.cfg
I currently have
> ################ PERSISTENCE ####################
>
> # The persistence service to use if no other is specified.
> #
> org.eclipse.smarthome.persistence:default=mysql

I have this very simple persist file:

Strategies {
everyMinute : “0 * * * * ?”
default = everyMinute
}

Items {
* : strategy = everyMinute
}

Is there a way to see mysql debug logging in karaf?

hansvandamme · December 10, 2017, 1:29pm

Breakthrough!

Initially my persistence config file was called “my.persist”. I now renamed it to “mysql.persist” and persistence starts working.

Jonas88 · January 9, 2018, 5:47pm

Hi! After updating to openHAB 2.2 it stopped working for me …
Is this a known issue?
How do i fix that? The error it plots me is:

2017-11-15 03:27:00.049 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-11-15 03:27:00.057 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-11-15 03:27:00.063 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item ‘Global_Thermostat_Heizen_Aus’ in database with statement ‘INSERT INTO Item12 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;’: No operations allowed after connection closed.

RayMYP · January 9, 2018, 6:22pm

I’m not aware of a mysql issue in general and I’m using it fine with 2.2.

From the first error message listed you need to go back over your mysql.cfg and validate all the settings. If you believe they are correct then also validate that you can connect manually to mysql with the same settings.

Jonas88 · January 9, 2018, 6:23pm

yeah. i deleted the user and the db and created it again so it worked …