Persistent MySQL Connection Issues in OpenHAB

General
1

I’ve been experiencing a recurring issue with MySQL connectivity in my OpenHAB setup, and I’m hoping the community can offer some insights or solutions. Here’s what I’ve observed and the steps I’ve taken so far:

The Problem

After approximately a week of uptime, OpenHAB stops connecting to MySQL, even though I can manually connect to the database without any issues. This results in data not being stored or retrieved, which disrupts my energy monitoring setup. The logs consistently show the following errors:

text
2016-01-28 22:02:31.182 [ERROR] [.p.m.i.MysqlPersistenceService] - mySQL: Connection is not valid!
2016-01-28 22:02:31.183 [ERROR] [.p.m.i.MysqlPersistenceService] - mySQL: Could not store item ‘Weather_Temperature’ in database with statement ‘INSERT INTO Item2 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;’: No operations allowed after connection closed.

What I’ve Tried

  1. Restarting Services: I’ve restarted both OpenHAB and the MySQL service multiple times. This temporarily resolves the issue, but it returns after a week.
  2. Checking Credentials: I’ve verified that the MySQL credentials in OpenHAB are correct and have not expired.
  3. Inspecting Logs: The logs don’t indicate any unusual activity or errors aside from the connection issues.
  4. Testing Connectivity: I can manually connect to MySQL using the same credentials, which confirms that the database is reachable.
  5. Updating Software: I’ve ensured that both OpenHAB and MySQL are running the latest stable versions.

Possible Solutions

  • Connection Timeout Settings: I’m considering adjusting the connection timeout settings in MySQL to see if this prevents the connection from dropping.
  • Persistent Connection Management: I wonder if there’s a way to configure OpenHAB to automatically re-establish the connection when it detects that the current one has been lost.
  • Alternative Databases: As a last resort, I’m exploring the possibility of switching to a different database solution, though I’d prefer to stick with MySQL if possible.

Community Input

Has anyone else encountered similar issues with MySQL in OpenHAB? I’d greatly appreciate any advice on troubleshooting or resolving this problem. I’m particularly interested in whether others have implemented successful workarounds or if there are known fixes for this specific issue.

Thank you in advance for your help and insights!