openHAB2 & MySQL persistence setup

Right :slight_smile:

1 Like

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

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

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 :smiley:

Edit:

I have no clue why I did not get any connection
I did step by step your tutorial
:frowning:
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

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.

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! :wink:

Best regards,

Aitor

5 Likes

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.

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.

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?

Breakthrough!

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

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.

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.

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


If I remeber correctly I had the same problem in the past and needed to restart the mysql service.

Hi guys,

maybe a Stupid question, i am not a linux crack at all.
If I install mysql server as described by @Christian_V in the first post on my openhabian machine (Raspberry pi 3 with oepnhab 2.2) is it installed that way that it starts up automatically on reboot? And what about the start up order? Do I need to ensure somehow that the MySQL server is up before OpenHab starts?

Thank you for some feedback
Xerx

Yes, mysql server should start automatically.
It’s possible to disable the automatic startup of the server, checkout through

systemctl status mysql.service

(Maybe mysqld.service)
If it’s disabled, you can enable autostart with

sudo systemctl enable mysql.service

and manually start the service (regardless if disabled) with

sudo systemctl start mysql.service

systemd takes care about startup order.

I am getting such an error. Where am I making mistakes?

2018-02-01 14:19:50.697 [WARN ] [sql.internal.MysqlPersistenceService] - mySQL: No connection to database. Can not persist item ‘OdaTemp (Type=NumberItem, State=23.30)’! Will retry connecting to database when error count:0 equals errReconnectThreshold:1
==> /var/log/openhab2/events.log <==

mysql.cfg

mysql.persist

Are you hosting it all on the same machine? So 192.168.100.155 is the actual local IP address with openHAB and mysql on it?

You might find using ‘localhost’ rather than ‘192.168.100.155’ is required depending on what restrictions your mysql setup has, quite often it only allows connections to localhost not the ‘externally’ facing IP address.

Hey guys,
I have another Problem than this above, but I think it’s the right thread for my question.

I install new (second) openhab2 on a new system and I want to correct all my old mistakes. If I understand right, openhab creates tables in MySQL with a ID number and behind the correct item name like they’re called in myhome.items. But when I take a look into my SQL database it looks like this:

image

And not like this:

image

How can I change this, or is there no oppertunity to change this. In general the database is working, but it would be nice to see directly the item names in SQL.

Maybe somone can help me, thank you and best regards
Manfred

This is very simple if using the jdbc persistence service. You have to set

tableUseRealItemNames = true

in jdbc.cfg.

Please be aware that you will lose old data. Or at least you will have to move old data to new table manually, after openHAB has created the new table.
Please keep in mind that you have to restart openHAB after changing the parameter to take effect.