[SOLVED] After reboot, no connection to mysql database

I’m getting these errors after a reboot. They repeat every few seconds. Everything was working just fine before:

2019-03-11 21:43:04.670 [WARN ] [sql.internal.MysqlPersistenceService] - mySQL: No connection to database. Cannot persist item 'Living_Room_Volts (Type=NumberItem, State=119.12)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:0
2019-03-11 21:43:07.638 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://localhost:3306/openhab, user=openhab
java.sql.SQLException: The server time zone value 'CDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

I’ve restarted both services, but continue to get these errors. OH seems to be working just fine otherwise. I last rebooted this past Saturday and the system came up just fine.
I’m running on a RPi3 OH 2.4 and mariadb.
Any ideas?

Uninstalling and then reinstalling the jdbc persistence seemed to fix the problem. I’m not getting the errors anymore.

Found solution here https://github.com/openhab/openhab1-addons/issues/5706
Need to add ?serverTimezone parameter to connection url

url=jdbc:mysql://localhost:3306/openhab?serverTimezone=EST (Eastern Standard Time)

2 Likes

Thanks, I got that as well.

“My Break-Dancing days are over, but there’s always the Funky Chicken” – The Full Monty

Ok, so I believe OH is connecting to the DB now, but it nothing is being read from or written to the DB, it seems. I’m getting errors like this after putting the jdbc binding into debug mode:

JDBC::query: query for Humidity returned 0 rows in 14 ms
JDBC::query: query for Inside_Humidity returned 0 rows in 16 ms
JDBC::query: query for TempDisplay returned 0 rows in 13 ms

Here is my mysql.persist:

 Strategies {
    default = everyChange
 }

Items {
* : strategy = everyChange, restoreOnStartup

Inside_* : strategy = everyChange, restoreOnStartup
PorchLight_Switch: strategy = everyChange, restoreOnStartup
GarageLight_Switch: strategy = everyChange, restoreOnStartup
PatioLight_Switch: strategy = everyChange, restoreOnStartup
LRWindowLight_Switch: strategy = everyChange, restoreOnStartup
Desk_Light_Switch: strategy = everyChange, restoreOnStartup
Utility_Switch: strategy = everyChange, restoreOnStartup
Humidity : strategy = everyChange, restoreOnStartup
PressureDisplay : strategy = everyChange, restoreOnStartup
TempDisplay : strategy = everyChange, restoreOnStartup
Sunrise : strategy = everyChange, restoreOnStartup
Sunset : strategy = everyChange, restoreOnStartup
Daylength : strategy = everyChange, restoreOnStartup
Daylength2 : strategy = everyChange, restoreOnStartup
stair_light_level: strategy = everyChange, restoreOnStartup
back_door_1_level: strategy = everyChange, restoreOnStartup
window_light_level: strategy = everyChange, restoreOnStartup
DeskMoteBattery_level: strategy = everyChange, restoreOnStartup
DoorSensor_Battery: strategy = everyChange, restoreOnStartup
WallMote_Battery: strategy = everyChange, restoreOnStartup
luminance_check_started: strategy = everyChange, restoreOnStartup
max_luminance: strategy = everyChange, restoreOnStartup
max_luminance_time: strategy = everyChange, restoreOnStartup
Barrier_State: strategy = everyChange, restoreOnStartup
Hallway_Humidity: strategy = everyChange, restoreOnStartup
Car_Battery_Level: strategy = everyChange, restoreOnStartup
CarBattery_Update: strategy = everyChange, restoreOnStartup
max_luminance: strategy = everyChange, restoreOnStartup
max_luminance_time: strategy = everyChange, restoreOnStartup
Hallway_Temp: strategy = everyChange, restoreOnStartup
nano_mote_quad_battery: strategy = everyChange, restoreOnStartup
}

Here is my mysql.config:

url=jdbc:mysql://localhost:3306/openhab
user=xxxxx
password=xxxxxx
mysql:sqltype.string=VARCHAR(20000)

I have the JDBC Persistence MySQL 1.13.0 persistence driver installed.

I think I found the answer. The timezone needed to be set. So in mysql.cfg, I set my connection url to:

url=jdbc:mysql://127.0.0.1:3306/openhab?serverTimezone=America/Chicago

Now things seem to be working and my charts are again displaying data.

3 Likes

Thank you your message saves my Openhab installation after a blackout. Openhab didn’t want to connect to mysql and your message was the solution.

+1

Great, glad to hear it.

Brilliant! It’s working for me as well.
I used: serverTimezone=Europe/Berlin

Cheers, Rainer

1 Like