Mariadb fails after upgrade

borpin · 2019-05-01T16:36:41.324Z

Ok so the ‘Stretch’ version.

I found a suggestion to delete the file /var/lib/mysql/tc.log and reboot.

bgrattan · 2019-05-01T18:24:26.059Z

Brian,
I have no file by that name:

root@emonpiB3:/home/pi# ll /var/lib/mysql
total 0
-rw-r–r-- 1 root root 0 Apr 30 18:06 debian-10.1.flag
root@emonpiB3:/home/pi#

root@emonpiB3:/home/pi# find / -name tc.log
root@emonpiB3:/home/pi#

borpin · 2019-05-01T18:52:30.291Z

Apologies, I checked on the wrongs system.

It will be in /home/pi/data/mysql/

What is in the file /var/log/mysql/error.log

My problem really is that there is nothing in the journal to indicate what has caused it not to load.

borpin · 2019-05-01T19:07:53.568Z

bgrattan:

Process: 9092 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited,

Bob, in the status line above, the end of this line is cut off. Could you widen the SSH terminal, restart the service and do a status output again please

bgrattan · 2019-05-01T19:08:13.342Z

Brian,
/var/log/MySQL/error.log is empty

bgrattan · 2019-05-01T19:12:32.012Z

● mariadb.service - MariaDB 10.1.38 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─override.conf
   Active: failed (Result: exit-code) since Wed 2019-05-01 15:09:48 EDT; 11s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 1717 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=1/FAILURE)

borpin · 2019-05-01T19:15:15.151Z

Ah progress.

Check permissions

 ls -la /var/run/my*

Mine is

pi@emonpi:/var/log/mysql $ ls -la /var/run/my*
total 4
drwxr-xr-x  2 mysql root   60 May  1 19:09 .
drwxr-xr-x 29 root  root  960 May  1 15:55 ..
-rw-rw----  1 mysql mysql   6 May  1 19:09 mysqld.pid

[edit]
I note you are running as root. That can upset permissions if you run scripts etc as root rather than as pi and sudo. I wonder if that is the issue. But what particular permissions are wrong will be tricky to discover.

bgrattan · 2019-05-01T19:23:30.201Z

I don’t seem to have a mysqld.pid. Does this mean it’s not running?

bgrattan · 2019-05-01T19:30:15.298Z

Maybe I should load a new image and move the data over? My last backup is a couple of weeks old so I have to lose all the data.

borpin · 2019-05-01T19:54:14.269Z

bgrattan:

I don’t seem to have a mysqld.pid. Does this mean it’s not running?

What are the folder permissions. I suspect the service cannot create the PID.

They should be

drwxr-xr-x  2 mysql       root          60 May  1 19:16 mysqld

bgrattan · 2019-05-01T21:37:59.330Z

Brian,

I have a new image, new SD 32, even a new pi B+3. The problem is still there…
Here is basically the same error:

emonSD-30Oct18
pi@emonpi:~ $ uname -a
Linux emonpi 4.19.37-v7+ #1214 SMP Tue Apr 30 17:20:43 BST 2019 armv7l GNU/Linux
pi@emonpi:~ $ service mariadb status
● mariadb.service - MariaDB 10.1.38 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Wed 2019-05-01 21:20:32 UTC; 50s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 840 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
  Process: 757 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemc
  Process: 752 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 750 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
 Main PID: 840 (code=exited, status=1/FAILURE)
   Status: "MariaDB server is down"
May 01 21:20:30 emonpi systemd[1]: Starting MariaDB 10.1.38 database server...
May 01 21:20:31 emonpi mysqld[840]: 2019-05-01 21:20:31 1995697968 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB-0+deb9u1) starting as proces
May 01 21:20:32 emonpi mysqld[840]: 2019-05-01 21:20:32 1995697968 [Warning] Can't create test file /home/pi/data/mysql/emonpi.lower-test
May 01 21:20:32 emonpi mysqld[840]: [95B blob data]
May 01 21:20:32 emonpi mysqld[840]: 2019-05-01 21:20:32 1995697968 [ERROR] Aborting
May 01 21:20:32 emonpi systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
May 01 21:20:32 emonpi systemd[1]: Failed to start MariaDB 10.1.38 database server.
May 01 21:20:32 emonpi systemd[1]: mariadb.service: Unit entered failed state.
May 01 21:20:32 emonpi systemd[1]: mariadb.service: Failed with result 'exit-code'.
pi@emonpi:~ $

Before update/upgrade/rpi-update I checked and MariaDB was running, Now I get the errors as before,
I have no inputs or feeds setup, It’s just a completely new system,
Any ideas? It must have something to do with the updates.
Your help is much appreciated and I’m sorry to take up so much of your time.
I’m going for a beer,!

Bob

pb66 · 2019-05-01T22:24:58.855Z

bgrattan:

Before update/upgrade/rpi-update I checked and MariaDB was running, Now I get the errors as before,
. . .
It must have something to do with the updates.

May I ask why are you doing a rpi-update?

Can you try just doing the recommended update procedure of update + dist-upgrade without the rpi-update to see if that runs ok?

The rpi-update will update the RPi’s firmware to the latest cutting edge firmware, the RPi firmware is already updated as part of the (dist-)upgrade to the latest stable release, you shouldn’t need to use rpi-update. Whereas dist-upgrade includes an “upgrade”, it upgrades all the packages that are being used rather than holding back upgrades to some packages (which the upgrade without “dist” does).

So your chosen update commands seem to be ultra conservative in one aspect and living life on the edge in another aspect, the use of “upgrade” and “rpi-update” seems inconsistent in that you don’t trust tested packages to all be upgraded automatically but are willing to run firmware that hasn’t yet been deemed “stable” enough for inclusion in a “routine” update.

For the record IIRC the emonpi update routine doesn’t include any apt-get (dist-)upgrade at all, so the image has only been tested on the Raspian image as supplied as part of the emonSD image, So even a more standard update + dist-upgrade might raise issues we haven’t seen as yet, but using rpi-update adds another even greater level of uncertainty.

borpin · 2019-05-01T22:47:42.258Z

pb66:

May I ask why are you doing a rpi-update?

Grrr, missed that bit of info first time. I did an apt update/upgrade here and it was still fine for me. I was planning on a dist-upgrade to see what happened (as it is the ‘approved’ upgrade method by Raspbian). I’ll do further updates tomorrow to replicate the issue. It must be a mariadb settings issue.

[edit]

pb66:

For the record IIRC the emonpi update routine doesn’t include any apt-get (dist-)upgrade at all

I think that is a weakness and a security risk personally.

bgrattan · 2019-05-01T23:06:14.069Z

Sorry, I’m a novice with all of this and thought it was always good to be up to date for security reasons. Once the latest image is installed, should I only do updates from the admin page? I’ve been doing upgrades and rpi-update for more than a year and never had a problem but will now heed your warning, Paul.
Tomorrow I will re-install without the upgrades to see what happens.
Is it possible to back out of the latest rpi-update? That would save me from a month’s data loss.
Thanks.

Bob

pb66 · 2019-05-01T23:06:16.323Z

borpin:

I think that is a weakness and a security risk personally.

You’ll hear no disagreement from me on that front.

pb66 · 2019-05-01T23:22:47.249Z

bgrattan:

Once the latest image is installed, should I only do updates from the admin page?

Good question, that’s what @borpin and I have just commented about.

Yes you should only update via the admin button (ideally!) and Yes you should run all the latest (stable) versions, how you would achieve that when the update button doesn’t (currently) do that for you I’m not sure. The current opinion is that you should have no reason to SSH in to use the command line, so I’m guessing the emonSD image assumes you will not update the OS and packages etc.

The update procedure is being reviewed currently, so hopefully this will get resolved.

Just to be clear “latest version” is a loaded term, yes you want the latest stable versions, sometimes there is a need to use a later version to get a particular fix or feature, but the “very latest” softwares are usually in testing for a while before general release, you can run those, but they can be buggy and are not for the feint-hearted when used on a live system, they are really for the developers and early adopter that are happy to “fiddle and tweak” to keep their systems running to get the absolute latest softwares asap.

I think @borpin is going to test dist-upgrade tomorrow, I suspect if “upgrade” worked, dist-upgrade might well be fine (but that’s not guaranteed). I believe you can back out of an rpi-update, I’m not sure how offhand but I think you would need to define what FW you want (ie the current stable) but i worry that you may then be stuck on that revision as I do not know for sure if it will return to the control of apt-get if you define it via rpi-update. You would need to do some research, personally, i would want to start clean or if things don’t go to plan, we might be having a similar discussion when you have substantially more data. You could backup/restore if that months data is that important to you I guess.

borpin · 2019-05-02T12:36:30.730Z

@bgrattan I see the error you got there is different so I think my fix proposed here will work

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

pb66 · 2019-05-02T16:19:11.747Z

ProtectHome=false

could be concidered a security or privacy risk, that is the purpose of that setting. (from systemd.exec man page)

ProtectHome=
Takes a boolean argument or “read-only”. If true, the directories
/home, /root and /run/user are made inaccessible and empty for
processes invoked by this unit. If set to “read-only”, the three
directories are made read-only instead. It is recommended to
enable this setting for all long-running services (in particular
network-facing ones), to ensure they cannot get access to private
user data, unless the services actually require access to the
user’s private data. This setting is implied if DynamicUser= is
set. For this setting the same restrictions regarding mount
propagation and privileges apply as for ReadOnlyPaths= and
related calls, see above.

A better fix for this is to not have the mysql folder in the user pi’s homedir. Hopefully the next image will not put everything in the pi’s home dir. But in the interim there are a number of other fixes, move the mysql folder back and symlink it so that it can be found via both paths and use the original path for mysql or mount the 3rd partition as /data and access the same folder via /data/mysql/ . . . and symlink /home/pi/data to /data.

Many of the “optimisations” are no longer required since the latest image is not RO, however they were just carried forward to the last image rather than “undoing” them. There are no special requirements to run emoncms on mysql, it’s only the RO OS that has led to “tweeks”, I had a rather long discussion about mysql “tweaks” on the last image elsewhere not so long ago, I will link if I recall/find it.

It would appear that the changes you propose were already in place and “done properly” (assuming the oct 18 image was built along the same lines as the previous image as we have no build guide) as it is documented at the end of the “Move MYSQL database” section in the install docs. Commited by Glyn back in Aug 18

github.com/emoncms/emoncms

update mariadb move SQL location for Stretch

committed 03:30PM - 17 Aug 18 UTC

glynhudson

+22 -2

pb66 · 2019-05-02T17:29:48.546Z

pb66:

I had a rather long discussion about mysql “tweaks” on the last image elsewhere not so long ago, I will link if I recall/find it.

It was in connection with logrotation errors due to some redundant “RO tweaks” carried forward into the non-RO Oct 2018 image.

Log Rotate Errors Getting Started

Paul … Thanks for the 24 hours explanation. My unix_socket is ACTIVE per below … | unix_socket | ACTIVE | AUTHENTICATION | NULL | GPL | However, unlike you, I do not have the required /var/run/mysqld/mysqld.sock pi@watchman:/var/run/mysqld $ ls -la /var/run/mysqld/mysqld.sock ls: cannot access '/var/run/mysqld/mysqld.sock': No such file or directory Lucky googling - I stumbled across this … EDIT: A really good way of finding the location of the socket file …

borpin · 2019-05-02T18:58:58.488Z

pb66:

A better fix for this is to not have the mysql folder in the user pi’s homedir. Hopefully the next image will not put everything in the pi’s home dir.

I am running a system on an EmonPi created using @TrystanLea’s install scripts and I can confirm the mod to the setting is not required as everything is in `/opt/var’. Even got log2ram on it although I am still not getting a coherent set of emonhub logs even though emonhub is now being called with a log file parameter. For another thread…

I think the future intention is not to create an image rather to have an install process/script.