Is there a way to check if MySQL is running and openhab can actually store values in it?
The reason is, that i have openhab running for quite some time now and I store some values in a MySQL database. When playing around with an analysis of the temperature values, I now realised that my MySQL server must have been down for over a month!
Of course I could build an external linux script that checks MySQL and alerts me if something is wrong, but I was wondering if there’s any way to do it from within openhab as I already have the config to MySQL (user,pass) and notification options.
You might take a look here:
Continuing the discussion from How Do I know if Persistance is working?:
Thanks, I actually saw that discussion but it’s not exactly the same topic. Persistence is working great for me, but I’m looking for a way to monitor the MySQL service from within openhab.
I just discovered the Network Health Binding. Could I use that to monitor the MySQL database ?
You need to provide an ip adress and a (optional) port.
So if ip adress and port are exclusiv for your mysql database it should work.
But you could also try monit, or take a look here:
Continuing the discussion from Automatic reboot at zwave stick problem:
Thanks! Didn’t know about monit, will probably also take a look at that.
I faced a similar problem recently. Happily I had monit running, so got to know that something happened to MySQL.
The problem is, that by default openhab obviously opens only one connection and if that errored, it can’t access MySQL anymore.
In my case the automatic update mechanism of Ubuntu updated and restarted MySQL, so it wasn’t available for a short period of time, but then was running fine again.
But openhab didn’t reconnect. So check the MySQL config and set the connectionCount (or similar, don’t remember the exact name) and another option. Maybe this should be made more clear on the wiki page or even should be default setting…
Thanks a lot @Maniac, that’s very interesting. In this case, maybe even Monit wouldn’t catch a short interruption but Openhab wouldn’t reconnect afterwards?
It’s very likely that I had the same issue, the server is kept up-to-date so I can’t guarantee that I haven’t restarted MySQL once.
I now set the following options as you proposed:
Not sure if the 20 seconds is a good value.
And I totally agree that this should be a default. Maybe I also missed it in the documentation though
monit can monitor the hash of the binary of monitored services. So due to the update, it sent me an email that the binary isn’t the same anymore. That’s why I guessed that the update caused the problem.
I have set the reconnectionCount to 1 like explained in wiki, so it should try a reconnection right after the first error.
Also I have set the waitTimeout to 28800 which results in 8 hours, also like in the wiki.
These settings seem to work, thanks again. Had an update of MySQL scheduled anyway so i had a restart of the MySQL server and checked the openhab log.
Got the following message (and some stacktraces):
2016-02-06 17:25:00.093 [WARN ] [.p.m.i.MysqlPersistenceService] - mySQL: No connection to database. Can not persist item 'Weather_Humidity (Type=NumberItem, State=65)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:1
I didn’t get another log message that the connection was successfully re-established, but new items are saved after that again so the reconnect did work.