Mariadb cannot connect after dist-upgrade

borpin · 2019-02-12T12:36:04.683Z

[Warning] Can't create test file /home/pi/data/mysql/emonpi.lower-test

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.

Tockley · 2019-02-12T23:26:34.866Z

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/

 pi@emonpi:~ $ cd /home/pi/data/
pi@emonpi:~/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/:

pi@emonpi:~/data $ cd /home/pi/data/mysql/
pi@emonpi:~/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

borpin · 2019-02-13T05:57:42.817Z

Can't connect to local emoncms

[Warning] Can’t create test file /home/pi/data/mysql/emonpi.lower-test

Try this suggestion ubuntu - MariaDB cannot start after update: [Warning] Can't create test file /home/mysql/beta.lower-test - Stack Overflow

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).

Tockley · 2019-02-13T09:28:11.193Z

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

image1861×245 19.5 KB

image1667×302 27.7 KB

borpin · 2019-02-13T10:35:39.824Z

~~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’.

pb66 · 2019-02-13T11:24:42.544Z

borpin:

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.

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.

Tockley · 2019-02-14T02:07:25.292Z

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.

pi@emonpi:/ $ 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.

pi@emonpi:/ $ sudo service feedwriter restart
pi@emonpi:/ $ 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.

pi@emonpi:/ $ 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.

Tockley · 2019-02-14T06:05:29.651Z

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.

borpin · 2019-02-14T07:29:13.599Z

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

github.com

emoncms/emoncms/blob/0a74cda9c3d0b1fbf02a68b3c53b7bbdddf7612f/default.emonpi.settings.php#L25-L30

    
      
          $mqtt_server = array( 'host'     => 'localhost',
                                'port'     => 1883,
                                'user'     => 'emonpi',
                                'password' => 'emonpimqtt2016',
                                'basetopic'=> 'emon',
                                'client_id' => 'emoncms'

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.

Tockley · 2019-02-14T08:48:24.362Z

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

Restored default password, now working.

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

borpin:

Change in file /lib/systemd/system/mariadb.service the value:
ProtectHome=true
to
ProtectHome=false

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

borpin · 2019-02-14T09:01:37.594Z

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.

https://github.com/emoncms/emoncms/issues/1202

whatsupskip · 2019-02-15T03:23:41.005Z

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

borpin · 2019-02-15T07:14:29.322Z

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

whatsupskip · 2019-02-16T00:22:53.727Z

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.

borpin · 2019-02-16T08:22:28.133Z

whatsupskip:

I just created a new image.

Without doing a dist-upgrade I am guessing?

whatsupskip · 2019-02-17T03:06:16.611Z

Sorry, should have said. No dist-upgrade.

glyn.hudson · 2019-05-03T14:09:29.217Z

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:

github.com/emoncms/emoncms

Mariadb will not start after a dist-upgrade

opened 10:43AM - 13 Feb 19 UTC

closed 07:12PM - 03 May 19 UTC

borpin

Discussion https://community.openenergymonitor.org/t/mariadb-cannot-connect-afte…r-dist-upgrade/10071/4 Solution; Change in file /lib/systemd/system/mariadb.service the value: ``` ProtectHome=true ``` to ``` ProtectHome=false ``` It may be that the dist-upgrade has introduced a new service file that now has the value as **true** or else this file is edited in the EmonSD build (which is still a closely guarded secret). Editing **any** service file is a **bad idea**. Instead; ``` sudo systemctl edit mariadb.service ``` then add ``` [Service] ProtectHome=false ``` Save file then; ``` sudo systemctl daemon-reload sudo systemctl restart mariadb.service ``` This change is then protected if the service file is updated.

WARNING - Do not do apt-get dist-upgrade Emoncms

I’ve just tested running sudo apt-get update & sudo apt-get upgrade on a stock emonSD-30Oct18 and mariadb was still running and not effected by any updates. The issue should only effect dist-upgrade as reported: https://community.openenergymonitor.org/t/mariadb-cannot-connect-after-dist-upgrade/10071 Maridb will fail to start after running sudo apt-get dist-upgrade to fix edit: /lib/systemd/system/mariadb.service the value: ProtectHome=true to ProtectHome=false I would not recommend runn…

Bill.Thomson · 2019-05-03T16:34:12.941Z

glyn.hudson:

Instead running sudo apt-get update && sudo apt-get update

Is the second update supposed to be upgrade?

glyn.hudson · 2019-05-03T18:38:26.700Z

Thanks, been fixed

glyn.hudson · 2019-05-03T18:39:02.376Z

Solution to this issue has been posted by @pb66 on this thread:

WARNING - Do not do apt-get dist-upgrade Emoncms

NO PLEASE DO NOT DO THIS! It is the reason the fault occurred in the first place. The correct “fix” is to sudo systemctl edit mariadb.service then to that blank file add [Service] ProtectHome=false and save with ctrl-x, y, enter. Then reload the service and restart sudo systemctl daemon-reload sudo systemctl restart mariadb This will ensure the same thing doesn’t happen again next time there is an upgrade to the default service unit, regardless of the upgrade command you chose.