Right
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
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
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!
Best regards,
Aitor
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:
And not like this:
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.