Issues with mysql after OH2 upgrade

Add-ons Persistence Services
1

Hi,
I am running a RP3 system and have recently upgraded to OH2. The database has not changed, only OH2 was upgraded. So I assume the database and connection itself should not be the problem for my issue - never had any problems on OH1.

I have following in log:

2017-11-10 08:19:45.279 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-11-10 08:19:45.280 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-11-10 08:19:45.282 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'roofTemp01' in database with statement 'INSERT INTO Item2 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': No operations allowed after connection closed.
2017-11-10 08:19:46.186 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-11-10 08:19:46.188 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!
2017-11-10 08:19:46.190 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'roofSunTemp01' in database with statement 'INSERT INTO Item3 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': No operations allowed after connection closed.

I am using the mysql binding (not the JDBC one - this did not work at all).

Two questions:
Does anyone know what that means?
How to increase the log level for mysql? - I can switch all to debug but that makes the log quite unreadable.

When checking the database content, I am missing some of the values. So it does not store them, even saying it is a duplicate entry.
I log that value anytime it changes, and at around the time of the issue, the value is missing in the database. a few minutes later, it saves most of the times again.

Thank you and best Regards,
SJ

2

openHAB has lost connection to MySQL.

3

Thanks Udo.
Do you also have an idea how to solve it?

4

No, sorry. I do not use mysql but jdbc (with mariadb) and I did not see any disconnecting yet.

You could set the mysql binding logger to debug level to see more details when connection is lost. Regarding to http://docs.openhab.org/addons/persistence/mysql/readme.html you can set reconnectCnt to 1 to reestablish the connection automatically though.

5

A quick update:
I changed the parameter

waitTimeout=28800

to 28800
Since then, not one issue and before I had it quite frequent. Not sure if this is a solution… at least it helped. Will let you know after a few more days.

Cheers,
SJ

1 Like
6

I ran into the same issue after setting VARCHAR(20000) in the mysql.cfg file and so I found this topic. It seems like mysql doesn’t really like this value, as the maximum should be 16383, but even that is only a theoretical value. I personally don’t need to store values with more than 500 characters, so I went for this and it works perfectly now on OpenHAB 2.5.6.

For me the solution was to set in /services/mysql.cfg:
mysql:sqltype.string=VARCHAR(500)