Connection errors after MySQL server reboot

Tags: #<Tag:0x00007f616eb9d030> #<Tag:0x00007f616eb9cf68> #<Tag:0x00007f616eb9cdd8>

I use the MySQL persistence. Yesterday night my Synology updated to a new DSM version, which resulted in a reboot of the NAS. I run the MySQL instance on that NAS. After a short time, the NAS was up again - MySQL Connections were working again as soon as the server was started.
but in my log there were these Errors:

2016-12-12 03:39:50.676 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2016-12-12 03:39:50.677 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2016-12-12 03:39:50.686 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'Astro_MoonAzimuth' in database with statement 'INSERT INTO Item139 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': No operations allowed after connection closed.
2016-12-12 03:39:50.743 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2016-12-12 03:39:50.747 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2016-12-12 03:39:50.752 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'Astro_SunElevation' in database with statement 'INSERT INTO Item134 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': No operations allowed after connection closed.
2016-12-12 03:39:50.765 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2016-12-12 03:39:50.767 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2016-12-12 03:39:50.769 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'Astro_MoonElevation' in database with statement 'INSERT INTO Item138 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': No operations allowed after connection closed.
2016-12-12 03:39:50.791 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2016-12-12 03:39:50.793 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2016-12-12 03:39:50.796 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'Astro_SunAzimuth' in database with statement 'INSERT INTO Item135 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': No operations allowed after connection closed.
2016-12-12 03:45:03.887 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2016-12-12 03:45:03.889 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2016-12-12 03:45:03.892 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'Sensoren_Temp_OutN' in database with statement 'INSERT INTO Item49 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': No operations allowed after connection closed.
(...)

I resaved the mysql.cfg without success, only a bundle:restart in the openHAB console reset and reconnected the connection.
I’m using OH2 (build #637) and 1.9.0.201612110211 openHAB mySQL Persistence bundle
Is this a known bug? https://github.com/openhab/openhab/issues/4504 refers to pre 1.9 if I can see correctly?

As my Synology (which provides the MySQL-server) updated yesterday and rebooted, my openHAB persistance stopped working again.
I guess, OH2 just opens a Connection and never checks it or reconnects, if there’s an error?

This issue is still alive with snapshot #798, this time even without rebooting the mySQL-Server. I get again the “Connection is not valid” ERRORs.

Sorry for pushing this thread up, but since it is still without result i didn’t want to open a new.

Did you solve the Problem?
I have recognized the same behaviour with my mysql databse on my QNAP NAS.
After configuring the optional reconnectCnt parameter it seems to work now.

If its already working for you, we should maybe add a solution mark here, for other users who may have the same problem.

2 Likes

You’re right. I added the reconnectCnt parameter also and wanted to wait for the outcome. It works for me since - and I forgot to come back again… Thanks for bringing it up!

1 Like