Community
OpenEnergyMonitor

Community

MySQL server has gone away

Tags: #<Tag:0x00007f56bf170670>

I powered down my emonbase today to move it and when I started it again the input list would not load and the feeds were not being updated.
The following error is being reported in many places:

Can't connect to database, please verify credentials/configuration in settings.php
Error message:  **Connection refused**
Server Information

Server Information

Services

  • emonhub :- Active Running
  • emoncms_mqtt :- Activating Auto-restart
  • feedwriter :- Active Running - sleep 60s 0 feed points pending write
  • service-runner :- Active Running
  • emonPiLCD :- Active Exited
  • redis-server :- Active Running
  • mosquitto :- Active Running

Emoncms

  • Version :- low-write 10.1.13
  • Modules :- Administration | App v2.0.9 | Backup v2.1.7 | EmonHub Config v2.0.4 | Dashboard v2.0.5 | Device v2.0.3 | EventProcesses | Feed | Graph v2.0.7 | Input | Postprocess v2.1.2 | CoreProcess | Schedule | Network Setup v1.0.0 | sync | Time | User | Visualisation | WiFi v2.0.2
  • Git :-

Server

  • OS :- Linux 4.14.71-v7+
  • Host :- emonpi | emonpi | (192.168.1.2)
  • Date :- 2020-01-11 12:22:55 UTC
  • Uptime :- 12:22:55 up 7:59, 1 user, load average: 0.17, 0.16, 0.17

Memory

  • RAM :- Used: 22.38%
    • Total :- 976.74 MB
    • Used :- 218.55 MB
    • Free :- 758.19 MB
  • Swap :- Used: 0.00%
    • Total :- 100 MB
    • Used :- 0 B
    • Free :- 100 MB
      Write Load Period

Disk

  • / :- Used: 47.67%
    • Total :- 3.81 GB
    • Used :- 1.82 GB
    • Free :- 1.82 GB
    • Write Load :- 341.88 B/s (6 hours 57 mins)
  • /boot :- Used: 51.69%
    • Total :- 42.52 MB
    • Used :- 21.98 MB
    • Free :- 20.54 MB
    • Write Load :- 0 B/s (6 hours 57 mins)
  • /home/pi/data :- Used: 1.56%
    • Total :- 10.11 GB
    • Used :- 160.99 MB
    • Free :- 9.44 GB
    • Write Load :- 35.53 KB/s (6 hours 57 mins)

HTTP

  • Server :- Apache/2.4.25 (Raspbian) HTTP/1.1 CGI/1.1 80

MySQL

  • Version :- 5.5.5-10.1.23-MariaDB-9+deb9u1
  • Host :- localhost:6379 (127.0.0.1)
  • Date :- 2020-01-11 12:22:54 (UTC 00:00‌​)
  • Stats :- Uptime: 3 Threads: 3 Questions: 31 Slow queries: 0 Opens: 20 Flush tables: 1 Open tables: 14 Queries per second avg: 10.333

Redis

  • Version :-
    • Redis Server :- 3.2.6
    • PHP Redis :- 4.1.1
  • Host :- localhost:6379
  • Size :- 51 keys (755.05K)
  • Uptime :- 0 days

MQTT Server

  • Version :- Mosquitto 1.4.10
  • Host :- localhost:1883 (127.0.0.1)

PHP

  • Version :- 7.0.30-0+deb9u1 (Zend Version 3.0.0)
  • Modules :- apache2handler | calendar v7.0.30-0+deb9u1 | Core v7.0.30-0+deb9u1 | ctype v7.0.30-0+deb9u1 | curl v7.0.30-0+deb9u1 | date v7.0.30-0+deb9u1 | dom v20031129 | exif v7.0.30-0+deb9u1 | fileinfo v1.0.5 | filter v7.0.30-0+deb9u1 | ftp v7.0.30-0+deb9u1 | gd v7.0.30-0+deb9u1 | gettext v7.0.30-0+deb9u1 | hash v1.0 | iconv v7.0.30-0+deb9u1 | igbinary v2.0.1 | json v1.4.0 | libxml v7.0.30-0+deb9u1 | mbstring v7.0.30-0+deb9u1 | mcrypt v7.0.30-0+deb9u1 | mosquitto v0.4.0 | mysqli v7.0.30-0+deb9u1 | mysqlnd vmysqlnd 5.0.12-dev - 20150407 - $Id: b5c5906d452ec590732a93b051f3827e02749b83 $ | openssl v7.0.30-0+deb9u1 | pcre v7.0.30-0+deb9u1 | PDO v7.0.30-0+deb9u1 | pdo_mysql v7.0.30-0+deb9u1 | Phar v2.0.2 | posix v7.0.30-0+deb9u1 | readline v7.0.30-0+deb9u1 | redis v4.1.1 | Reflection v7.0.30-0+deb9u1 | session v7.0.30-0+deb9u1 | shmop v7.0.30-0+deb9u1 | SimpleXML v7.0.30-0+deb9u1 | sockets v7.0.30-0+deb9u1 | SPL v7.0.30-0+deb9u1 | standard v7.0.30-0+deb9u1 | sysvmsg v7.0.30-0+deb9u1 | sysvsem v7.0.30-0+deb9u1 | sysvshm v7.0.30-0+deb9u1 | tokenizer v7.0.30-0+deb9u1 | wddx v7.0.30-0+deb9u1 | xml v7.0.30-0+deb9u1 | xmlreader v7.0.30-0+deb9u1 | xmlwriter v7.0.30-0+deb9u1 | xsl v7.0.30-0+deb9u1 | Zend OPcache v7.0.30-0+deb9u1 | zlib v7.0.30-0+deb9u1

Pi

  • Model :- Raspberry Pi 3 Model B+ Rev 1.3 - 1GB (Sony UK)

  • Serial num. :- B4F44840

  • Temperature :- 49.39°C - 49.9°C

  • emonpiRelease :- emonSD-30Oct18

  • File-system :- read-write

Client Information

Client Information

HTTP

  • Browser :- Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.88 Safari/537.36
  • Language :- en-US,en;q=0.9

Window

  • Size :- 1903 x 888

Screen

  • Resolution :- 1920 x 1080
LAST ENTRIES ON THE LOG FILE
#7 {main}
2020-01-11 12:09:13.814|WARN|emoncms_mqtt.php|Not connected, retrying connection
2020-01-11 12:09:13.843|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Accepted.: code: 0
2020-01-11 12:09:14.055|ERROR|emoncms_mqtt.php|ErrorException: mysqli::query(): MySQL server has gone away in /var/www/emoncms/Modules/input/input_model.php:697
Stack trace:
#0 [internal function]: exceptions_error_handler(2, 'mysqli::query()...', '/var/www/emoncm...', 697, Array)
#1 /var/www/emoncms/Modules/input/input_model.php(697): mysqli->query('SELECT id,useri...')
#2 /var/www/emoncms/Modules/input/input_model.php(197): Input->load_to_redis(1)
#3 /var/www/emoncms/Modules/input/input_model.php(183): Input->redis_get_inputs(1)
#4 /var/www/emoncms/scripts/services/emoncms_mqtt/emoncms_mqtt.php(318): Input->get_inputs(1)
#5 [internal function]: message(Object(Mosquitto\Message))
#6 /var/www/emoncms/scripts/services/emoncms_mqtt/emoncms_mqtt.php(137): Mosquitto\Client->loop()
#7 {main}
2020-01-11 12:10:15.403|WARN|emoncms_mqtt.php|Not connected, retrying connection
2020-01-11 12:10:15.435|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Accepted.: code: 0
2020-01-11 12:10:15.675|ERROR|emoncms_mqtt.php|ErrorException: mysqli::query(): MySQL server has gone away in /var/www/emoncms/Modules/input/input_model.php:697
Stack trace:
#0 [internal function]: exceptions_error_handler(2, 'mysqli::query()...', '/var/www/emoncm...', 697, Array)
#1 /var/www/emoncms/Modules/input/input_model.php(697): mysqli->query('SELECT id,useri...')
#2 /var/www/emoncms/Modules/input/input_model.php(197): Input->load_to_redis(1)
#3 /var/www/emoncms/Modules/input/input_model.php(183): Input->redis_get_inputs(1)
#4 /var/www/emoncms/scripts/services/emoncms_mqtt/emoncms_mqtt.php(318): Input->get_inputs(1)
#5 [internal function]: message(Object(Mosquitto\Message))
#6 /var/www/emoncms/scripts/services/emoncms_mqtt/emoncms_mqtt.php(137): Mosquitto\Client->loop()
#7 {main}

The emoncms_mqtt service appears to be having difficulty starting. I have tried restarting the service to no avail. Status of the service is as follows:

[email protected]:/var/www/html/emoncms $ sudo service emoncms_mqtt status
 emoncms_mqtt.service - Emoncms emoncms_mqtt script
   Loaded: loaded (/var/www/emoncms/scripts/services/emoncms_mqtt/emoncms_mqtt.service; enabled; vendor preset: enabled)
   Active: activating (auto-restart) (Result: exit-code) since Sat 2020-01-11 12:25:41 UTC; 10s ago
     Docs: https://github.com/emoncms/emoncms/blob/master/docs/RaspberryPi/MQTT.md
  Process: 24086 ExecStart=/usr/bin/php /var/www/emoncms/scripts/services/emoncms_mqtt/emoncms_mqtt.php (code=exited, status=255)
  Process: 24083 ExecStartPre=/bin/chmod 666 ${LOG_PATH}/emoncms.log (code=exited, status=0/SUCCESS)
  Process: 24080 ExecStartPre=/bin/touch ${LOG_PATH}/emoncms.log (code=exited, status=0/SUCCESS)
  Process: 24077 ExecStartPre=/bin/chown ${USER} ${LOG_PATH} (code=exited, status=0/SUCCESS)
  Process: 24074 ExecStartPre=/bin/mkdir -p ${LOG_PATH} (code=exited, status=0/SUCCESS)
 Main PID: 24086 (code=exited, status=255)
   CGroup: /system.slice/emoncms_mqtt.service

Jan 11 12:25:41 emonpi systemd[1]: emoncms_mqtt.service: Failed with result 'exit-code'.

Can anyone advise?

Edit - formatted for readability. BT, Moderator

Hello @Jaddache I assume you tried restarting the emonbase to see if it came up ok after a second power cycle? What do you see if you try:

$ mysql -u emoncms -p emoncms (password: emonpiemoncmsmysql2016)

and

$ sudo service mysql status

you could try:

$ mysqlcheck -A --auto-repair -uemoncms -p

Hi Trystan,
Yes I have tried restarting the emonbase a number of times - with no improvement.

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0

Copyright © 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mariadb.service - MariaDB database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─override.conf
Active: active (running) since Sun 2020-01-12 02:46:39 UTC; 49s ago
Process: 1551 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS
Process: 1548 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Process: 1425 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=/usr/bin/galera_recovery; [
Process: 1420 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 1418 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Main PID: 1520 (mysqld)
Status: “Taking your SQL requests now…”
CGroup: /system.slice/mariadb.service
└─1520 /usr/sbin/mysqld

Jan 12 02:46:37 emonpi systemd[1]: Starting MariaDB database server…
Jan 12 02:46:38 emonpi mysqld[1520]: 2020-01-12 2:46:38 1988280320 [Note] /usr/sbin/mysqld (mysqld 10.1.23-MariaDB-9+deb
Jan 12 02:46:39 emonpi systemd[1]: Started MariaDB database server.
Warning: mariadb.service changed on disk. Run ‘systemctl daemon-reload’ to reload units.

[email protected]:~ $ mysqlcheck -A -uemoncms -p
Enter password:
emoncms.app_config OK
emoncms.dashboard OK
emoncms.device OK
emoncms.feeds OK
emoncms.graph OK
emoncms.input OK
emoncms.multigraph OK
emoncms.postprocess OK
emoncms.rememberme OK
emoncms.schedule OK
emoncms.setup OK
emoncms.sync OK
emoncms.users OK

How old is the SD Card?

From experience, odd happenings after reboots are the first sign of SD Card failure.

I’d use the new upgrade guide (go to the upgrade section) and start with a fresh image and SD Card, import your data and see where you end up.

It was supplied with the emonbase in October 2019 - so I have been assuming it is OK.

1 Like

Not sure what’s going wrong, seems that everything is in place. There’s one small possibility here:

You could try:

sudo systemctl daemon-reload
sudo systemctl restart mariadb.service

Then reboot the pi.

Could you check the emoncms log again after the reboot and check that there are new entries showing again mysql/mariadb related errors?

Hi Tristan,
Tried the two commands above and they appeared to complete successfully with no errors. After the reboot the emonbase remained unchanged to my observation.
The contents of the log file are a series of entries. This is the last set:

2020-01-14 01:53:05.559|WARN|emoncms_mqtt.php|Not connected, retrying connection
2020-01-14 01:53:05.593|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Accepted.: code: 0
2020-01-14 01:53:05.815|ERROR|emoncms_mqtt.php|ErrorException: mysqli::query(): MySQL server has gone away in /var/www/emoncms/Modules/input/input_model.php:697
Stack trace:
#0 [internal function]: exceptions_error_handler(2, 'mysqli::query()...', '/var/www/emoncm...', 697, Array)
#1 /var/www/emoncms/Modules/input/input_model.php(697): mysqli->query('SELECT id,useri...')
#2 /var/www/emoncms/Modules/input/input_model.php(197): Input->load_to_redis(1)
#3 /var/www/emoncms/Modules/input/input_model.php(183): Input->redis_get_inputs(1)
#4 /var/www/emoncms/scripts/services/emoncms_mqtt/emoncms_mqtt.php(318): Input->get_inputs(1)
#5 [internal function]: message(Object(Mosquitto\Message))
#6 /var/www/emoncms/scripts/services/emoncms_mqtt/emoncms_mqtt.php(137): Mosquitto\Client->loop()
#7 {main}

I have another sd card with the emonpi image on it. Is it worth trying this image?

@Jaddache, have you run an apt-update or apt-upgrade at all?

Hi Brian,
Not tried previously. Ran both this morning. Still no change to the error messages to my level of observation.
The original “connection refused” error is still being reported in many places and the emoncms_mqtt serine is still attempting to restart with no success.

I’m at a loss as to why this might have happened.

You could ensure your emoncms repository is not corrupted in some way by

cd /var/www/emoncms/
git reset --hard
git pull

Also check the contents of your settings.ini that the SQL details are correct and there are no stray characters.

Searching on that error (“MySQL server has gone away”), it seems that from a PHP perspective it thinks the database is somehow corrupted. The fact you can connect to it from the command line is odd. @TrystanLea I note there is no default.emonpi.settings.ini file, just a PHP one.

There are various suggested commands to try and repair the database. One is to use

mysqlcheck --all-databases -r

Other than that, all I can suggest is trying a new image and importing your current data. However, there is a risk this will import a corrputed database so you will be back where you started.

1 Like

Hi Brian,
No luck still.
Thank you Brian and Tristan for trying to help me solve this. I recognise the risk of importing a corrupted database, so I think I will now start again with a new sd card and a new image. The data lost is not much of an issue - I have already studied it closely and learned lots from it.

2 Likes

Out of interest @Jaddache when you did: git reset and git pull what did you see?
Do you have /var/www/emoncms/settings.ini in place?
What is the content of the mysql database settings, I assume defaults: username: emoncms, password: emonpiemoncmsmysql2016?

The thing is, the error is not a connection error but a “SQL Server has gone away”

Fair point, given that its possible to login directly to mysql via command line, perhaps that points to an error with the php mysql client.

github issue for tracking https://github.com/openenergymonitor/EmonScripts/issues/74

1 Like

For future ref I would recommend doing a sudo systemctl cat mariadb.service ahead of any unexpected need to daemon-reload to be able to see what changes are being made.

All the examples I just read (very briefly) seem to point to timeouts, that would better “fit the message” and explain the fact the error occurs part way through the code, not at the start.

The fact that the service unit had changed on disk must raise the question “did anything else get changed?” whilst there is no way to be sure, it might be worth checking any files that are edited by emoncms installation, eg php.ini or mysql.ini (or what ever the mysql settings are) since the Oct18 image would have edited package maintained settings files rather than adding drop-in files to a “.d” folder.

[edit - I am aware that some setting files are not forcibly overwritten when there are local changes, but in that instance, maybe there were new settings that were needed but were blocked by local changes?]

Also if you check the mysql/mariadb logs via journald you might see the connection being terminated (timing out) or not, to perhaps determine which end (mysql or php) is timing out, if that is indeed what is happening.

Would the Oct18 image have done an apt-get upgrade as part of the emonpi update? either this time (new updater?) or during a previous update (emonpi updater) and only just presented as an error due to the reboot?

1 Like