[MySQL] OH2 doesn't reconnect dropped MySQL connections

[binderth]

Expected Behavior

If for some reason the connection to the MySQL server is dropped (in my case after an update my Synology, which provides the MySQL/MariaDB instance), OH2 should reconnect to the MySQL server, if it's up again.

Current Behavior

If the connection is dropped, it's not reestablished, but there are only errors in the logfile:

2017-01-14 03:32:44.029 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-01-14 03:32:44.032 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-01-14 03:32:44.041 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'Sensoren_Lux_S_VDD' in database with statement 'INSERT INTO Item122 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': No operations allowed after connection closed.
2017-01-14 03:32:44.075 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-01-14 03:32:44.077 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-01-14 03:32:44.081 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'Sensoren_Lux_S_lux' in database with statement 'INSERT INTO Item123 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': No operations allowed after connection closed.
2017-01-14 03:32:44.118 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!

this goes on, until the MySQL binding is restartet (bundle restart xxx in the OH2 console, e.g.)

Possible Solution

OH2 should check, if the MySQL server is up again and reconnect to it automatically. This could be done e.g. with every time, there's an update to a persisted item or in a (configurable) interval. The first one would be the best, I think.

Steps to Reproduce (for bugs)

1. configura MySQL as persistance and configure items to be persisted
2. restart MySQL server
3. the errors start filling up the log

Context

I'd like to have a persistant persistance! ;) At present I'm either pressed to update my Synology manually (and reconnect MySQL after the update) or I have to look in the logs regularly (or install some log analyzer).

Your Environment

• Version used: OH2 #710, openHAB mySQL Persistence bündle 1.9.0.201701090210
• Environment name and version (e.g. Chrome 39, node.js 5.4, Java 8, ...): openHABian, JAVA8
• Operating System and version (desktop or mobile, Windows 10, Raspbian Jessie, ...): Raspbian Jessie (openHABian)

[binderth]

I didn't want to re-open old issues and PRs, but there were already some old ones, I'm not sure, if they relate here, but I'll link them:

• https://github.com/openhab/openhab/pull/2472 (didn't find the errReconnectThresholdconfiguration, though)
• https://github.com/openhab/openhab/issues/2035

[DanielMalmgren]

Just for the record: It seems this can happen also when mysql server hasn't been restarted. I'm sure I haven't restarted my mysql server (which is on the same server as openhab) for a very long time but I suddenly got those errors today. Solved it using a bundle restart.

[brutevinch]

Same here.