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**
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:
pi@emonpi:/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'.
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.
pi@emonpi:~ $ 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
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:
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.
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.
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?
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?