[SOLVED] Need help troubleshooting mysql

  • Platform information: Raspberry Pi Nano
    • Hardware: Raspberry Pi Nano
    • OS: Rasbian image from openHABian
    • Java Runtime Environment:Zulu Embedded OpenJDK Java 8
    • openHAB version: 2.3.0
  • Issue of the topic: Need help troubleshooting mysql.

I setup mqsql persistence using this guide and it worked great for 10 months and then stopped working.

I see log entries like this:

2018-11-17 13:23:15.027 [WARN ] [sql.internal.MysqlPersistenceService] - mySQL: No connection to database. Cannot persist item ‘HUE_LightLevel_Kitchen (Type=NumberItem, State=20873)’! Will retry connecting to database when error count:0 equals errReconnectThreshold:0

I can access the tables using MySQL workbench. And my config files all look correct. Not sure where to start.

You are running openhab2 AND mysql of a raspberry zero?
And it’s fast enough?

Back to your problem
It could be a sign of SD card corruption

no changes happened during these 10 months? (btw: OH 2.3.0 Stable or another release?)

where is your mysql server? on localhost or across the LAN?

post your mysql.cfg, enable DEBUG on the persistence service addon from the console and monitor openhab.log

log:set DEBUG org.openhab.persistence.mysql

check also for free space on your SD Card (just in case): df -h

MySQL is running on same host as OpenHab (OH 2.3.0)

# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)


# the database user

# the database password

# the reconnection counter

# the connection timeout (in seconds)

# Use MySQL Server time to store item values (=false) or use openHAB Server time (=true).
# For new installations, its recommend to set "localtime=true".
# (optional, defaults to false)


root@raspberrypi:/var/log/openhab2# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/root 5.9G 4.6G 1.1G 82% /
devtmpfs 213M 0 213M 0% /dev
tmpfs 218M 0 218M 0% /dev/shm
tmpfs 218M 8.9M 209M 5% /run
tmpfs 5.0M 4.0K 5.0M 1% /run/lock
tmpfs 218M 0 218M 0% /sys/fs/cgroup
/dev/mmcblk0p6 66M 21M 45M 32% /boot
tmpfs 44M 0 44M 0% /run/user/1000
/dev/mmcblk0p5 30M 398K 28M 2% /media/pi/SETTINGS3

config file looks good

enable debug and check log entries

OK, figured this out. The password some how changed. Once I set to what was in the mysql.cfg, things started working.

1 Like