Community
OpenEnergyMonitor

Community

Mariadb cannot connect after dist-upgrade

Tags: #<Tag:0x00007f6e0f77c110> #<Tag:0x00007f6e0f78bf70>

Sorry for the post size, but alas, I decided to do a sudo apt-get update & sudo apt-get dist-upgrade and have hit the original emoncms web connection refused that I posted. (Noted that during upgrade ‘mariadb’ seemed to have issues.) :confounded:

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

Checked settings.php, looked fine. Also tried restoring file from the default. Data still pushing to remote emoncms. Assuming that auth credentials are as expected:

//1 #### Mysql database settings
    $server   = "127.0.0.1";
    $database = "emoncms";
    $username = "emoncms";
    $password = "emonpiemoncmsmysql2016";
    $port     = "3306";
    // Skip database setup test - set to false once database has been setup.
    $dbtest = true;

//2 #### Redis
    $redis_enabled = true;
    $redis_server = array( 'host'   => 'localhost',
                           'port'   => 6379,
                           'auth'   => '',
                           'prefix' => '');

//3 #### MQTT
    // The 'subscriber' topic format is rx/* - where * is the emoncms input node number.
    // The 'publisher' topic format is user selectable from the 'Publish to MQTT' input process, for example power/solar
    $mqtt_enabled = true;            // Activate MQTT by changing to true
    $mqtt_server = array( 'host'     => 'localhost',
                          'port'     => 1883,
                          'user'     => 'emonpi',
                          'password' => 'emonpi2016',
                          'basetopic'=> 'emon'
                          );

I am not sure if something has been overwritten to now lock me out of emoncms local instance. Do I need to fix mariadb or reset mysql/mqtt credentials somehow - if so, how?

Also get the following if relevant:

[email protected]:/var/www/emoncms $ sudo systemctl start mysqld
Job for mariadb.service failed because the control process exited with error code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.

[email protected]:/var/www/emoncms $ sudo systemctl status mariadb.service
● mariadb.service - MariaDB 10.1.37 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Tue 2019-02-12 21:49:05 AEDT; 57s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 1808 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
  Process: 1725 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR
  Process: 1720 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 1718 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
 Main PID: 1808 (code=exited, status=1/FAILURE)
   Status: "MariaDB server is down"
      CPU: 519ms

Feb 12 21:49:03 emonpi systemd[1]: Starting MariaDB 10.1.37 database server...
Feb 12 21:49:05 emonpi mysqld[1808]: 2019-02-12 21:49:05 1988849664 [Note] /usr/sbin/mysqld (mysqld 10.1.37-MariaDB-0+deb9u1) starting as process 1808 ...
Feb 12 21:49:05 emonpi mysqld[1808]: 2019-02-12 21:49:05 1988849664 [Warning] Can't create test file /home/pi/data/mysql/emonpi.lower-test
Feb 12 21:49:05 emonpi systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Feb 12 21:49:05 emonpi systemd[1]: Failed to start MariaDB 10.1.37 database server.
Feb 12 21:49:05 emonpi systemd[1]: mariadb.service: Unit entered failed state.
Feb 12 21:49:05 emonpi systemd[1]: mariadb.service: Failed with result 'exit-code'.

It might be a permission issue.

Can you post the contents of /lib/systemd/system/mariadb.service please.

[edit] and a directory output for /home/pi/data/ and /home/pi/data/mysql/ please.

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.1.37 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=

/home/pi/data/

 [email protected]:~ $ cd /home/pi/data/
[email protected]:~/data $ ls -al
total 8652
drwxrwxrwx 12 pi       pi          1024 Jan 31 17:43 .
drwxr-xr-x 20 pi       pi          4096 Dec 18 18:09 ..
-rw-r--r--  1 root     root           0 Oct 31 04:00 dhcpd.leases
drwx--x--x 14 root     root        1024 Feb 12 21:40 docker
drwxrwxrwx  3 root     root        1024 Oct 31 05:34 @eaDir
-rw-r--r--  1 pi       pi       8750024 Jan 31 17:43 emoncms-backup-2019-01-31.tar.gz
-rw-rw-r--  1 pi       www-data       0 Oct 31 03:59 emoncms.conf
-rw-r--r--  1 pi       pi           968 Jan 31 17:43 emoncms-export.log
-rw-r--r--  1 pi       pi          1455 Dec 17 15:21 emoncms-import.log
-rw-r--r--  1 pi       pi         16070 Jan 31 17:43 emoncms.sql
-rw-r--r--  1 pi       pi           377 Dec 17 18:30 emoncms-wifiscan.log
-rw-rw-rw-  1 pi       www-data    7469 Dec 17 17:19 emonhub.conf
-rw-rw-rw-  1 pi       pi          7889 Dec 17 15:20 emonpiupdate.log
drwxr-xr-x  7 root     root        1024 Feb 13 10:24 homeassistant
drwxr-xr-x  2 pi       pi          1024 Dec 17 15:21 import
drwx------  2 pi       pi         12288 Aug 17 01:29 lost+found
drwxrwxrwx  5 mysql    mysql       1024 Feb 12 20:46 mysql
-rw-r--r--  1 pi       pi          7469 Oct 16 18:36 old.emonhub.conf
drwxr-xr-x  2 www-data root        1024 Oct 16 18:27 phpfina
drwxr-xr-x  2 www-data root        1024 Jun 25  2018 phptimeseries
drwxrwxrwt  2 root     root        1024 Oct 19 04:33 @tmp
drwxr-xr-x  2 www-data root        1024 Dec 17 15:21 uploads
-rw-r--r--  1 root     root         185 Feb 13 10:20 wificheck.log

and /home/pi/data/mysql/:

[email protected]:~/data $ cd /home/pi/data/mysql/
[email protected]:~/data/mysql $ ls -al
total 111053
drwxrwxrwx  5 mysql mysql     1024 Feb 12 20:46 .
drwxrwxrwx 12 pi    pi        1024 Jan 31 17:43 ..
-rw-rw----  1 mysql mysql    16384 Feb 12 20:46 aria_log.00000001
-rw-rw----  1 mysql mysql       52 Feb 12 20:46 aria_log_control
-rw-r--r--  1 mysql mysql        0 Aug 18 00:27 debian-10.1.flag
drwx------  2 mysql mysql     1024 Dec 17 15:21 emoncms
-rw-rw----  1 mysql mysql 12582912 Feb 12 20:45 ibdata1
-rw-rw----  1 mysql mysql 50331648 Feb 12 20:45 ib_logfile0
-rw-rw----  1 mysql mysql 50331648 Aug 18 00:28 ib_logfile1
-rw-rw----  1 mysql mysql        0 Aug 18 00:29 multi-master.info
drwx------  2 mysql mysql     4096 Feb 12 20:29 mysql
drwx------  2 mysql mysql     1024 Feb 12 20:45 performance_schema

Try this suggestion https://stackoverflow.com/questions/38529205/mariadb-cannot-start-after-update-warning-cant-create-test-file-home-mysql

Change in file /lib/systemd/system/mariadb.service the value:

ProtectHome=true

to

ProtectHome=false

If it works, we will need to do this a different way to make it permanent (and update will overwrite the change).

1 Like

Boom. That was it - changed to False and rebooted, local emoncms pages loads up fine. However now local emoncms is not picking up data feeds or publishing to app while remote emoncms is ticking along fine… Logs for emonhub seem fine?

2019-02-13 20:27:01,402 DEBUG    RFM2Pi     26 Sent to channel(start)' : ToEmonCMS
2019-02-13 20:27:01,403 DEBUG    RFM2Pi     26 Sent to channel(end)' : ToEmonCMS
2019-02-13 20:27:01,666 DEBUG    MQTT       Publishing: emon/emonpi/power1 588.5
2019-02-13 20:27:01,668 DEBUG    MQTT       Publishing: emon/emonpi/power2 -3.48
2019-02-13 20:27:01,671 DEBUG    MQTT       Publishing: emon/emonpi/power1pluspower2 547
2019-02-13 20:27:01,674 DEBUG    MQTT       Publishing: emon/emonpi/vrms 226.66
2019-02-13 20:27:01,677 DEBUG    MQTT       Publishing: emon/emonpi/t1 0
2019-02-13 20:27:01,680 DEBUG    MQTT       Publishing: emon/emonpi/t2 0
2019-02-13 20:27:01,682 DEBUG    MQTT       Publishing: emon/emonpi/t3 0
2019-02-13 20:27:01,685 DEBUG    MQTT       Publishing: emon/emonpi/t4 0
2019-02-13 20:27:01,688 DEBUG    MQTT       Publishing: emon/emonpi/t5 0
2019-02-13 20:27:01,690 DEBUG    MQTT       Publishing: emon/emonpi/t6 0


Did you just reboot or did you power down completely?

Try a complete power cycle (unplug).
[edit]
Can you post the service information from the admin page (just click the button and paste here).

I wonder if the Mosquito service or the mqtt-input service is running.

Anything in the emoncms log on receiving data via MQTT. You can change the log level in the settings.php to ‘Info’.

If there are no errors in emonhub regards MQTT then I think it is more likely to be the feedwriter service that isn’t running as that is dependent on mysql too.

This might not show on the server info as it always says “…loading” (unless fixed v.recently)

Try re starting the feedwriter service.

I’ve got remote SSH but unfortunately not remote local emoncms access to check admin page - I will do that when I get home from work to see if the local feeds are now updating. For now I have restarted feedwriter but looks same as prior to restart. Also included below is mqtt input status. Remote cms logigng still ticking along fine.

[email protected]:/ $ sudo systemctl status feedwriter
● feedwriter.service - LSB: feedwriter script daemon
   Loaded: loaded (/etc/init.d/feedwriter; generated; vendor preset: enabled)
   Active: active (running) since Wed 2019-02-13 17:47:02 AEDT; 19h ago
     Docs: man:systemd-sysv-generator(8)
  Process: 1317 ExecStart=/etc/init.d/feedwriter start (code=exited, status=0/SUCCESS)
      CPU: 694ms
   CGroup: /system.slice/feedwriter.service
           └─1425 /usr/bin/php -f /var/www/emoncms/scripts/feedwriter.php

Feb 13 17:46:55 emonpi systemd[1]: Starting LSB: feedwriter script daemon...
Feb 13 17:46:55 emonpi feedwriter[1317]: Log is turned off
Feb 13 17:46:55 emonpi feedwriter[1317]: Starting RPI
Feb 13 17:47:02 emonpi systemd[1]: Started LSB: feedwriter script daemon.

[email protected]:/ $ sudo service feedwriter restart
[email protected]:/ $ sudo service feedwriter status
● feedwriter.service - LSB: feedwriter script daemon
   Loaded: loaded (/etc/init.d/feedwriter; generated; vendor preset: enabled)
   Active: active (running) since Thu 2019-02-14 13:02:02 AEDT; 59s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 13573 ExecStop=/etc/init.d/feedwriter stop (code=exited, status=0/SUCCESS)
  Process: 13587 ExecStart=/etc/init.d/feedwriter start (code=exited, status=0/SUCCESS)
      CPU: 357ms
   CGroup: /system.slice/feedwriter.service
           └─13596 /usr/bin/php -f /var/www/emoncms/scripts/feedwriter.php

Feb 14 13:01:55 emonpi systemd[1]: Starting LSB: feedwriter script daemon...
Feb 14 13:01:55 emonpi feedwriter[13587]: Log is turned off
Feb 14 13:01:55 emonpi feedwriter[13587]: Starting RPI
Feb 14 13:02:02 emonpi systemd[1]: Started LSB: feedwriter script daemon.

[email protected]:/ $ sudo service mqtt_input status
● mqtt_input.service - Emoncms MQTT Input Script
   Loaded: loaded (/etc/systemd/system/mqtt_input.service; enabled; vendor preset: enabled)
   Active: active (running) since Wed 2019-02-13 17:47:02 AEDT; 19h ago
     Docs: https://github.com/emoncms/emoncms/blob/master/docs/RaspberryPi/MQTT.md
 Main PID: 1430 (php)
      CPU: 2h 28min 26.076s
   CGroup: /system.slice/mqtt_input.service
           └─1430 /usr/bin/php /var/www/emoncms/scripts/phpmqtt_input.php

Feb 13 17:47:02 emonpi systemd[1]: Started Emoncms MQTT Input Script.

Looks like an MQTT issue… what is the next step to authorise connection?

2019-02-14 06:03:45.004|WARN|phpmqtt_input.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5
2019-02-14 06:03:45.004|ERROR|phpmqtt_input.php|unexpected connection problem mqtt server:Connection Refused: not authorised.
2019-02-14 06:03:51.000|WARN|phpmqtt_input.php|Not connected, retrying connection
2019-02-14 06:03:51.003|WARN|phpmqtt_input.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5
2019-02-14 06:03:51.004|ERROR|phpmqtt_input.php|unexpected connection problem mqtt server:Connection Refused: not authorised.

Hi @Tockley, Can you remind me what your setup is please? Is the EmonSD in a Pi with an RFM board?

Have you changed the MQTT password? The default is (I think) this

You could test it using the command line

mosquitto_sub -v -u ‘emonpi’ -P ‘emonpimqtt2016’ -t ‘test/topic

You will not see anything but if it connects you know you have the right credentials.

1 Like

You nailed it @borpin, I altered the password during my initial troubleshooting (based on logic that currently escapes me…) :roll_eyes:

Restored default password, now working.

The root problem was the mariadb change on dist-upgrade, the fix was:

Thanks for everyone’s help and the learning - loving the community! :blush:

No bother, glad it is working.

Just one point, to ensure the service file is not overwritten by a future upgrade (which is highly likely, the package ‘owns’ that file), I have outlined the process for overriding the default setting that will survive updates.

1 Like

Thanks for this. I hope this can be sorted out at EMON level.

@whatsupskip, did you have the exactly the same issue, and did the solution fix it?

I had the same error message. As this was not a production machine I just created a new image. Not the solution that you would be looking for.

Without doing a dist-upgrade I am guessing?

Sorry, should have said. No dist-upgrade.

1 Like

Thanks for reporting.

I would not recommend running sudo apt-get dist-upgrade . Instead running sudo apt-get update && sudo apt-get upgrade will update all installed packages and apply any security fixes.

This issue is being discussed:

Is the second update supposed to be upgrade?

Thanks, been fixed