Community
OpenEnergyMonitor

Community

Emoncms fails to connect to database after upgrade

For no good reason I upgraded Raspbian on my emonBase yesterday.

I ran sudo apt upgrade and after it finished I rebooted. Now emoncms is not working. If I try to connect to its website, it says:

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

/var/log/emoncms.log contains:

2020-06-13 09:51:53.603|ERROR|feedwriter.php|Starting feedwriter script
2020-06-13 09:51:53.820|ERROR|feedwriter.php|Can't connect to database:Connection refused

And systemsctl status mysql shows:

● mariadb.service - MariaDB 10.1.44 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Sat 2020-06-13 09:50:48 UTC; 11min ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 1079 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
  Process: 993 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && sys
  Process: 922 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 896 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
 Main PID: 1079 (code=exited, status=1/FAILURE)
   Status: "MariaDB server is down"

Jun 13 09:49:40 emonpi systemd[1]: Starting MariaDB 10.1.44 database server...
Jun 13 09:49:51 emonpi mysqld[1079]: 2020-06-13  9:49:51 1996259120 [Note] /usr/sbin/mysqld (mysqld 10.1.44-MariaDB-0+deb9u1) starting as p
Jun 13 09:49:55 emonpi mysqld[1079]: 2020-06-13  9:49:55 1996259120 [Warning] Can't create test file /home/pi/data/mysql/emonpi.lower-test
Jun 13 09:49:55 emonpi mysqld[1079]: [95B blob data]
Jun 13 09:49:55 emonpi mysqld[1079]: 2020-06-13  9:49:55 1996259120 [ERROR] Aborting
 Jun 13 09:50:48 emonpi systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Jun 13 09:50:48 emonpi systemd[1]: Failed to start MariaDB 10.1.44 database server.
Jun 13 09:50:48 emonpi systemd[1]: mariadb.service: Unit entered failed state.
Jun 13 09:50:48 emonpi systemd[1]: mariadb.service: Failed with result 'exit-code'.

The mysql logs are empty and emonhub.log just contains:

2020-06-13 09:51:15,206 INFO     MainThread EmonHub emonHub emon-pi variant v2.1.1
2020-06-13 09:51:15,207 INFO     MainThread Opening hub...
2020-06-13 09:51:19,227 WARNING  MainThread Setting emoncmsorg apikey: obscured

Anybody have any idea what’s going on and how to fix it?

I’m sure this has happened before - have a search of the forum.

Try this… Mariadb cannot connect after dist-upgrade

BUT don’t edit it just yet. What is the contents of that file?

If you do need to change it do

sudo systemctl edit mariadb

and add the line… You need to include the right section - [service] I expect.

ProtectHome=false

[edit]

Actually my service file is as below before any update and ProtectHome =true

Compare this to yours.

[email protected]:~ $ cat /lib/systemd/system/mariadb.service
#
# /etc/systemd/system/mariadb.service
#
# This file is free software; you can redistribute it and/or modify it
# under the terms of the GNU Lesser General Public License as published by
# the Free Software Foundation; either version 2.1 of the License, or
# (at your option) any later version.
#
# Thanks to:
# Daniel Black
# Erkan Yanar
# David Strauss
# and probably others

[Unit]
Description=MariaDB 10.3.17 database server
Documentation=man:mysqld(8)
Documentation=https://mariadb.com/kb/en/library/systemd/
After=network.target

[Install]
WantedBy=multi-user.target
Alias=mysql.service
Alias=mysqld.service


[Service]

##############################################################################
## Core requirements
##

Type=notify

# Setting this to true can break replication and the Type=notify settings
# See also bind-address mysqld option.
PrivateNetwork=false

##############################################################################
## Package maintainers
##

User=mysql
Group=mysql

# To allow memlock to be used as non-root user if set in configuration
CapabilityBoundingSet=CAP_IPC_LOCK

# Prevent writes to /usr, /boot, and /etc
ProtectSystem=full

# Doesn't yet work properly with SELinux enabled
# NoNewPrivileges=true

PrivateDevices=true

# Prevent accessing /home, /root and /run/user
ProtectHome=true

# Execute pre and post scripts as root, otherwise it does it as User=
PermissionsStartOnly=true

ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld

# Perform automatic wsrep recovery. When server is started without wsrep,
# galera_recovery simply returns an empty string. In any case, however,
# the script is not expected to return with a non-zero status.
# It is always safe to unset _WSREP_START_POSITION environment variable.
# Do not panic if galera_recovery script is not available. (MDEV-10538)
ExecStartPre=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
ExecStartPre=/bin/sh -c "[ ! -e /usr/bin/galera_recovery ] && VAR= || \
 VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] \
 && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1"

# Needed to create system tables etc.
# ExecStartPre=/usr/bin/mysql_install_db -u mysql

# Start main service
# MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
# Use the [Service] section and Environment="MYSQLD_OPTS=...".
# This isn't a replacement for my.cnf.
# _WSREP_NEW_CLUSTER is for the exclusive use of the script galera_new_cluster
ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION
ExecStartPost=/etc/mysql/debian-start

# Unset _WSREP_START_POSITION environment variable.
ExecStartPost=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"

KillSignal=SIGTERM

# Don't want to see an automated SIGKILL ever
SendSIGKILL=no

# Restart crashed server only, on-failure would also restart, for example, when
# my.cnf contains unknown option
Restart=on-abort
RestartSec=5s

UMask=007

##############################################################################
## USERs can override
##
##
## by creating a file in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
## and adding/setting the following under [Service] will override this file's
## settings.

# Useful options not previously available in [mysqld_safe]

# Kernels like killing mysqld when out of memory because its big.
# Lets temper that preference a little.
# OOMScoreAdjust=-600

# Explicitly start with high IO priority
# BlockIOWeight=1000

# If you don't use the /tmp directory for SELECT ... OUTFILE and
# LOAD DATA INFILE you can enable PrivateTmp=true for a little more security.
PrivateTmp=false

##
## Options previously available to be set via [mysqld_safe]
## that now needs to be set by systemd config files as mysqld_safe
## isn't executed.
##

# Number of files limit. previously [mysqld_safe] open-file-limit
LimitNOFILE=16364

# Maximium core size. previously [mysqld_safe] core-file-size
# LimitCore=

# Nice priority. previously [mysqld_safe] nice
# Nice=-5

# Timezone. previously [mysqld_safe] timezone
# Environment="TZ=UTC"

# Library substitutions. previously [mysqld_safe] malloc-lib with explicit paths
# (in LD_LIBRARY_PATH) and library name (in LD_PRELOAD).
# Environment="LD_LIBRARY_PATH=/path1 /path2" "LD_PRELOAD=

# Flush caches. previously [mysqld_safe] flush-caches=1
# ExecStartPre=sync
# ExecStartPre=sysctl -q -w vm.drop_caches=3

# numa-interleave=1 equalivant
# Change ExecStart=numactl --interleave=all /usr/sbin/mysqld......

# crash-script equalivent
# FailureAction=

[edit2]

I striped out all the comments

[email protected]:~ $ grep -E "^[^#]" /lib/systemd/system/mariadb.service
[Unit]
Description=MariaDB 10.3.17 database server
Documentation=man:mysqld(8)
Documentation=https://mariadb.com/kb/en/library/systemd/
After=network.target
[Install]
WantedBy=multi-user.target
Alias=mysql.service
Alias=mysqld.service
[Service]
Type=notify
PrivateNetwork=false
User=mysql
Group=mysql
CapabilityBoundingSet=CAP_IPC_LOCK
ProtectSystem=full
PrivateDevices=true
ProtectHome=true
PermissionsStartOnly=true
ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld
ExecStartPre=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
ExecStartPre=/bin/sh -c "[ ! -e /usr/bin/galera_recovery ] && VAR= || \
 VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] \
 && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1"
ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION
ExecStartPost=/etc/mysql/debian-start
ExecStartPost=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
KillSignal=SIGTERM
SendSIGKILL=no
Restart=on-abort
RestartSec=5s
UMask=007
PrivateTmp=false
LimitNOFILE=16364

@djh, Dave, what base Image are you on?

If an old one, I suspect your SQL database is on in the home folder - this has been moved partly because of this issue.

So back to my first fix!

Hi @borpin, thanks Brian. I’m on low-write 9.8.8 and yes my SQL DB is in /home/pi/data/mysql I gather the problem is something to do with that but I don’t understand what. I’m confused about what is wrong and what I need to do about it.

I tried creating /etc/systemd/system/mariadb.service.d/override.conf with the contents:

[service]
ProtectHome=false

but it doesn’t seem to make any difference when I restart the DB:

Jun 13 13:55:51 emonpi mysqld[8396]: 2020-06-13 13:55:51 1995755312 [Warning] Can't create test file /home/pi/data/mysql/emonpi.lower-test

Firstly it is a bad idea to create the file manually especially in the /etc/systemd/ folder. Note the source file is in lib. Hence the suggestion of mine to use sudo systemctl edit.

Secondly, did you reload the daemon?

I did create the override file using systemctl edit

Reload what daemon?

Great (you did say you created the file :))

When you restarted the service did you get a warning?

You are basically starting the service as it was before, it doesn’t yet know to include the override. If you do a systemctl status mariadb.service it will tell you if you are using the override file.

Try that command then

sudo systemctl daemon-reload

Then the status again. I expect it will list the override file then (and it may start!).

[edit]
You should see something like this

  Drop-In: /etc/systemd/system/mariadb.service.d
           └─override.conf

Thanks @borpin, that worked. :grin:

I don’t understand how it worked before the upgrade now? Not that it matters.

Probably the default service file was changed on update - we have seen it before.

I strongly suggest you consider updating to Buster from Stretch - it does ideally mean a new card, but the USB import makes it relatively painless. Also there are lots of underlying fixes (such as moving data out of home) introduced since 9.x.

At some point, something will break that cannot be fixed.

Well, stretch is just about to start its two years of LTS so I think upgrading to buster on a system I’d like to stay as stable as possible is premature. What worked should keep working. I trust Debian / Raspberry Pi OS to produce sensible updates within the release, and applications built on it should continue to run. As regards emoncms, I don’t believe my changes to the flot library have been pulled into upstream yet (ever?) so I will have to reapply those whenever I do upgrade, and I have logging sorted to my satisfaction, so I’m pretty happy with what I have.

I think there have been some changes to the graphics library so those changes may well no longer be compatible.

If you want stable, stick to security updates only!

Well, that’s pretty much what LTS is, I think?

PS Everything seems to be working again now, so thanks for the help Brian.