Check if persistence is working

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.

Thanks
Patrick

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…

1 Like

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:
mysql:reconnectCnt=3 mysql:waitTimeout=20

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

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.

I think I got an easy fix which should be usable for every persistence service.
Persistence Heartbeat How-To
Conditions:

  • An Item which changes frequently (e.g. an powermeter measuring the power offtake of my proxmox"cluster" (In my case it changes at least every 10 seconds with the exception the server is not running which makes this procedure obsolet anyway :wink: )
  • 2 Items for the ruleset

Items:

//Does provide a switch if notification in case of unavailability is wanted
Switch Persistence_Check_Mon 
//Does wait up t0 5 Minutes before resending notification
Switch Persistence_Check          {expire="5m,command=OFF"}

The following rule checks if a state was saved into the persistence db and sends a notification if the service-request was not successful:

rule "Check if data was written to db"
when
    Item Computer_Power changed
then
    var letztes_Update = Computer_Power.lastUpdate
    //If call fails check if notification is wanted
    if(letztes_Update === null){
        if(Persistence_Check.state != ON && Persistence_Check_Mon.state == ON){
            sendNotification("test@mail.com", "InfluxDB not reachable")
            Persistence_Check.sendCommand(ON)
        }
    }
    else{
        if(Persistence_Check.state == ON){
            sendNotification("test@mail.com", "InfluxDB reachable again")
            Persistence_Check.sendCommand(OFF)
        }
    }

end

From there on you might trigger actions solving the lost connection issue (restarting services…)

Hope this might help