Mariadb failed after moving userdata to external SSD

Ricardo-Simoes · 7 December 2022 22:48

After using drive-manager to move userdata to external SSD, mariadb fails to start… need help please.
Saw this post mariadb failed but I don’t know if it’s the same problem.

Required Information

DietPi version | 8.11.2 master MichaIng
Distro version | bullseye 1
Kernel version | Linux DietPi 5.15.76-v7+
SBC model | RPi 2 Model B (armv7l)

Some info from commands:

systemctl restart mariadb.service

Job for mariadb.service failed because the control process exited with error code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.

journalctl -u mariadb

-- Journal begins at Wed 2022-12-07 22:22:55 WET, ends at Wed 2022-12-07 22:39:01 WET. --
Dec 07 22:23:09 DietPi systemd[1]: Starting MariaDB 10.5.15 database server...
Dec 07 22:23:11 DietPi mariadbd[515]: 2022-12-07 22:23:11 0 [Note] /usr/sbin/mariadbd (mysqld 10.5.15-MariaDB-0+deb11u1) starting as process 515 ...
Dec 07 22:23:11 DietPi mariadbd[515]: 2022-12-07 22:23:11 0 [Warning] Can't create test file /var/lib/mysql/DietPi.lower-test
Dec 07 22:23:11 DietPi mariadbd[515]: [92B blob data]
Dec 07 22:23:11 DietPi mariadbd[515]: 2022-12-07 22:23:11 0 [ERROR] Aborting
Dec 07 22:23:11 DietPi systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Dec 07 22:23:11 DietPi systemd[1]: mariadb.service: Failed with result 'exit-code'.
Dec 07 22:23:11 DietPi systemd[1]: Failed to start MariaDB 10.5.15 database server.
Dec 07 22:36:47 DietPi systemd[1]: Starting MariaDB 10.5.15 database server...
Dec 07 22:36:47 DietPi mariadbd[1042]: 2022-12-07 22:36:47 0 [Note] /usr/sbin/mariadbd (mysqld 10.5.15-MariaDB-0+deb11u1) starting as process 1042 ...
Dec 07 22:36:47 DietPi mariadbd[1042]: 2022-12-07 22:36:47 0 [Warning] Can't create test file /var/lib/mysql/DietPi.lower-test
Dec 07 22:36:47 DietPi mariadbd[1042]: [92B blob data]
Dec 07 22:36:47 DietPi mariadbd[1042]: 2022-12-07 22:36:47 0 [ERROR] Aborting
Dec 07 22:36:47 DietPi systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Dec 07 22:36:47 DietPi systemd[1]: mariadb.service: Failed with result 'exit-code'.
Dec 07 22:36:47 DietPi systemd[1]: Failed to start MariaDB 10.5.15 database server.

cat /var/log/mysql/error.log
cat: /var/log/mysql/error.log: No such file or directory

readlink /var/lib/mysql
/mnt/dietpi_userdata/mysql

readlink -f /var/lib/mysql
/mnt/My_SSD/dietpi_userdata/mysql

Joulinar · 7 December 2022 23:18

can you share following

lsblk -o name,fstype,label,size,ro,type,mountpoint,partuuid,uuid
ls -la /var/lib/mysql/

Ricardo-Simoes · 7 December 2022 23:22

lsblk -o name,fstype,label,size,ro,type,mountpoint,partuuid,uuid

NAME     FSTYPE LABEL   SIZE RO TYPE MOUNTPOINT PARTUUID                             UUID
sda                   931.5G  0 disk                                                 
└─sda1   ext4         931.5G  0 part /mnt/My_SS d2d71b99-ffec-4f64-8371-6ecdf288019f cc184941-9c70-4a61-bbcf-997cdab6d1f2
mmcblk0                28.8G  0 disk                                                 
├─mmcblk0p1
│        vfat           128M  0 part /boot      8ad88797-01                          5041-9789
└─mmcblk0p2
         ext4          28.7G  0 part /          8ad88797-02                          287c1068-9e1a-4c88-a429-7e49026ef592

ls -la /var/lib/mysql/

NOTE: I’m using exa, hope it’s ok

Permissions Links Size User   Group  Date Modified Name
drwxr-xr-x      6    - mysql  mysql   7 Dec 21:48   ./
drwxrwxr-x      8    - dietpi dietpi 22 Nov 13:33   ../
drwx------      2    - mysql  mysql  18 Nov 23:13   mysql/
drwx------      2    - mysql  mysql  22 Nov 14:46   nextcloud/
drwx------      2    - mysql  mysql  18 Nov 23:13   performance_schema/
drwx------      2    - mysql  mysql  18 Nov 23:26   phpmyadmin/
.rw-rw----      1 32Ki mysql  mysql   7 Dec 21:48   aria_log.00000001
.rw-rw----      1   52 mysql  mysql   7 Dec 21:48   aria_log_control
.rw-r--r--      1    0 root   root   18 Nov 23:13   debian-10.5.flag
.rw-rw----      1 13Ki mysql  mysql   7 Dec 21:48   ib_buffer_pool
.rw-rw----      1 96Mi mysql  mysql   7 Dec 21:48   ib_logfile0
.rw-rw----      1 76Mi mysql  mysql   7 Dec 21:48   ibdata1
.rw-rw----      1    0 mysql  mysql  18 Nov 23:13   multi-master.info
.rw-r--r--      1   15 root   root   18 Nov 23:13   mysql_upgrade_info

Joulinar · 7 December 2022 23:27

is following working and creating the demo.file?

sudo -u mysql -g mysql touch /var/lib/mysql/demo.file
ls -la /var/lib/mysql/

what do you mean with this?

Ricardo-Simoes · 7 December 2022 23:33

I’m using exa as an alias for ls

sudo -u mysql -g mysql touch /var/lib/mysql/demo.file
touch: cannot touch '/var/lib/mysql/demo.file': Permission denied

ls -la /var/lib/mysql/

Permissions Links Size User   Group  Date Modified Name
drwxr-xr-x      6    - mysql  mysql   7 Dec 21:48   ./
drwxrwxr-x      8    - dietpi dietpi 22 Nov 13:33   ../
drwx------      2    - mysql  mysql  18 Nov 23:13   mysql/
drwx------      2    - mysql  mysql  22 Nov 14:46   nextcloud/
drwx------      2    - mysql  mysql  18 Nov 23:13   performance_schema/
drwx------      2    - mysql  mysql  18 Nov 23:26   phpmyadmin/
.rw-rw----      1 32Ki mysql  mysql   7 Dec 21:48   aria_log.00000001
.rw-rw----      1   52 mysql  mysql   7 Dec 21:48   aria_log_control
.rw-r--r--      1    0 root   root   18 Nov 23:13   debian-10.5.flag
.rw-rw----      1 13Ki mysql  mysql   7 Dec 21:48   ib_buffer_pool
.rw-rw----      1 96Mi mysql  mysql   7 Dec 21:48   ib_logfile0
.rw-rw----      1 76Mi mysql  mysql   7 Dec 21:48   ibdata1
.rw-rw----      1    0 mysql  mysql  18 Nov 23:13   multi-master.info
.rw-r--r--      1   15 root   root   18 Nov 23:13   mysql_upgrade_info

Joulinar · 7 December 2022 23:44

hmm permission seems to fit but still issues to access the folder.

let’s check following

sudo -u mysql -g mysql touch  /mnt/dietpi_userdata/mysql/demo.file1
sudo -u mysql -g mysql touch  /mnt/My_SSD/dietpi_userdata/mysql/demo.file2
ls -la /mnt/My_SSD/dietpi_userdata/

any kernel error messages

dmesg -l err,crit,alert,emerg

Ricardo-Simoes · 7 December 2022 23:50

sudo -u mysql -g mysql touch /mnt/dietpi_userdata/mysql/demo.file1
touch: cannot touch '/mnt/dietpi_userdata/mysql/demo.file1': Permission denied

sudo -u mysql -g mysql touch /mnt/My_SSD/dietpi_userdata/mysql/demo.file2
touch: cannot touch '/mnt/My_SSD/dietpi_userdata/mysql/demo.file2': Permission denied

ls -la /mnt/My_SSD/dietpi_userdata/

Permissions Links Size User     Group    Date Modified Name
drwxrwxr-x      8    - dietpi   dietpi   22 Nov 13:33   ./
drwxr-x---      8    - root     root      7 Dec 22:19   ../
drwxrwxr-x      2    - dietpi   dietpi   29 Sep 22:29   downloads/
drwxrwxr-x      2    - dietpi   dietpi   29 Sep 22:29   Music/
drwxr-xr-x      6    - mysql    mysql     7 Dec 21:48   mysql/
drwxrwx---      7    - www-data www-data 22 Nov 18:21   nextcloud_data/
drwxrwxr-x      2    - dietpi   dietpi   29 Sep 22:29   Pictures/
drwxrwxr-x      2    - dietpi   dietpi   29 Sep 22:29   Video/
.rw-r--r--      1    0 root     root     18 Nov 22:50   mariadb-database-backup.sql

dmesg -l err,crit,alert,emerg

[    8.487999] sd 0:0:0:0: [sda] No Caching mode page found
[    8.489533] sd 0:0:0:0: [sda] Assuming drive cache: write through

Ricardo-Simoes · 8 December 2022 00:10

Also systemctl status mariadb.service is a bit different now

mariadb.service - MariaDB 10.5.15 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; disabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since Thu 2022-12-08 00:07:08 WET; 11s ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 1610 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 1611 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 1613 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
    Process: 1661 ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
   Main PID: 1661 (code=exited, status=1/FAILURE)
     Status: "MariaDB server is down"
        CPU: 545ms

Dec 08 00:07:08 DietPi systemd[1]: Starting MariaDB 10.5.15 database server...
Dec 08 00:07:08 DietPi mariadbd[1661]: 2022-12-08  0:07:08 0 [Note] /usr/sbin/mariadbd (mysqld 10.5.15-MariaDB-0+deb11u1) starting as process 1661 ...
Dec 08 00:07:08 DietPi mariadbd[1661]: 2022-12-08  0:07:08 0 [Warning] Can't create test file /var/lib/mysql/DietPi.lower-test
Dec 08 00:07:08 DietPi mariadbd[1661]: [92B blob data]
Dec 08 00:07:08 DietPi mariadbd[1661]: 2022-12-08  0:07:08 0 [ERROR] Aborting
Dec 08 00:07:08 DietPi systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE
Dec 08 00:07:08 DietPi systemd[1]: mariadb.service: Failed with result 'exit-code'.
Dec 08 00:07:08 DietPi systemd[1]: Failed to start MariaDB 10.5.15 database server.

Ricardo-Simoes · 8 December 2022 12:33

Ok so I’ve done a little more digging since it’s becoming evident that this is a permissions problem related… So I cd into /mnt/My_SSD/dietpi_userdata/mysql and then can create demo.file2 with mysql user.

 ┌─────(root)─────(/) 
 └> $ cd /mnt/My_SSD/dietpi_userdata/mysql

 ┌─────(root)─────(/mnt/My_SSD/dietpi_userdata/mysql) 
 └> $ sudo -u mysql -g mysql touch demo.file2

 ┌─────(root)─────(/mnt/My_SSD/dietpi_userdata/mysql) 
 └> $ ls
Permissions Links Size User  Group Date Modified Name
drwx------      2    - mysql mysql 18 Nov 23:13   mysql/
drwx------      2    - mysql mysql 22 Nov 14:46   nextcloud/
drwx------      2    - mysql mysql 18 Nov 23:13   performance_schema/
drwx------      2    - mysql mysql 18 Nov 23:26   phpmyadmin/
.rw-rw----      1 32Ki mysql mysql  7 Dec 21:48   aria_log.00000001
.rw-rw----      1   52 mysql mysql  7 Dec 21:48   aria_log_control
.rw-r--r--      1    0 root  root  18 Nov 23:13   debian-10.5.flag
.rw-r--r--      1    0 mysql mysql  8 Dec 12:17   demo.file2
.rw-rw----      1 13Ki mysql mysql  7 Dec 21:48   ib_buffer_pool
.rw-rw----      1 96Mi mysql mysql  7 Dec 21:48   ib_logfile0
.rw-rw----      1 76Mi mysql mysql  7 Dec 21:48   ibdata1
.rw-rw----      1    0 mysql mysql 18 Nov 23:13   multi-master.info
.rw-r--r--      1   15 root  root  18 Nov 23:13   mysql_upgrade_info

However, while I’m still inside /mnt/My_SSD/dietpi_userdata/mysql and try to issue to the command with full path, again says permission denied.

 ┌─────(root)─────(/mnt/My_SSD/dietpi_userdata/mysql) 
 └> $ sudo -u mysql -g mysql touch  /mnt/My_SSD/dietpi_userdata/mysql/demo.file2
touch: cannot touch '/mnt/My_SSD/dietpi_userdata/mysql/demo.file2': Permission denied

So it seems that it can only create the file if it’s already inside the directory, if it’s outside it does not… I don’t know if this helps to narrow down the solution to the problem or not, but I don’t know what to do more

Joulinar · 8 December 2022 22:04

Lets check mount point of your HDD

ls -la /mnt/My_SSD

Ricardo-Simoes · 9 December 2022 00:57

ls -la /mnt/My_SSD

Permissions Links Size User     Group    Date Modified Name
drwxr-x---      8    - root     root      7 Dec 22:19  ./
drwxr-xr-x      7    - root     root      7 Dec 22:03  ../
drwxr-x---      4    - www-data www-data 10 Nov 23:03  Anabela/
drwxr-xr-x      3    - root     root     22 Nov 18:47  dietpi-backup/
drwxrwxr-x      8    - dietpi   dietpi    8 Dec 12:11  dietpi_userdata/
drwxr-x---      2    - root     root     10 Oct 20:09  lost+found/
drwxr-x---      4    - www-data www-data 22 Nov 15:29  Ricardo/
drwxr-x---      4    - www-data www-data 19 Oct 13:54  Tiago/

Joulinar · 9 December 2022 14:11

this might be the issue. Can you try following

chmod 755 /mnt/My_SSD
sudo -u mysql -g mysql touch /var/lib/mysql/demo.file

if this is working fine

systemctl restart mariadb.service

Ricardo-Simoes · 9 December 2022 22:43

Thank you so so much!!!

P.S: Just curious how could this have happened? I honestly don’t remember if I used another Linux machine to format/create the ext4 file system on this external SSD… I tried to replicate the problem on a VM machine, first installing everything on just one virtual disc, installed NextCloud, and then added another virtual disc and moved the userdata to this disc, and no problem whatsoever did occour.

Joulinar · 10 December 2022 06:47

That’s something we probably will not find out

MichaIng · 11 December 2022 10:00

Strange indeed. When not changing it manually, the default umask is 0022 which leads to 0755, i.e. world-readable directories and mounts, when just using mkdir, resp. mount or mount via dietpi-drive_manager . It shouldn’t depend on where the drive is formatted but on the system it is mounted to only.

Ricardo-Simoes · 13 December 2022 13:21

I see… well then the cause will remain to be discovered because like I said I couldn’t replicate it within a VM, and the only thing that I could have made different was indeed format/create ext4 fs on another machine, but it’s ok, let us not worry anymore. I really appreciate all of your help (just donated a small amount… like we say in Portugal “it’s small but it’s well intended”)