MariaDB cannot start after update: [Warning] Can't create test file /home/mysql/beta.lower-test

Question

I've just updated MariaDB using apt-get dist-upgrade. Now it won't start using service mysql start anymore.

I can however, run it as root or do: sudo -u mysql mysqld_safe then MariaDB starts up fine. The folder /home/mysql is owned by the mysql user and group.

I've found the error to be thrown in this function: https://github.com/MariaDB/server/blob/7ff44b1a832b005264994cbdfc52f93f69b92cdc/sql/mysqld.cc#L9865

I can't figure out what to do next. Any pointers?

Talaria

Just revert your database system to the previous version...or backup your databases(mysqldump), remove every single trace of MariaDB and then install the latest version from scratch :) – Machinate

Yeah don't really want to do that. Must be an easier way. – Talaria

@BentodeGier, have you solved the problem? I just got this error after update – Eulau

Check this answer: https://mcmap.net/q/135187/-how-to-change-mysql-data-directory and follow all the mentioned steps, but on the Ubunto you should work with AppArmor not SELinux – Lubalubba

Answer 1

To run MariaDB SQL from /home, in the file /usr/lib/systemd/system/mariadb.service or /lib/systemd/system/mariadb.service, just change :

ProtectHome=true

to :

ProtectHome=false

Malachite

in ubuntu 17.04 the file seems to have moved here /lib/systemd/system/ – Muscarine

this seems to be reset some times. any idea to prevent this? – Muscarine

For debian 9 the file is at /etc/systemd/system/multi-user.target.wants/mariadb.service. And then before starting the service it must be run "systemctl daemon-reload" – Yaw

This apparently doesn't work anymore. Mariadb just updated this morning and it completely ignores ProtectHome=false. – Requital

Answer 2

The answer by Thomas is correct, but get's reset by updates every few months. So here is a permanent solution:

Use systemctl edit mariadb to create a file overwritting the default settings of the mariadb service. (In debian it's located in /etc/systemd/system/mariadb.service.d/override.conf)

Set the same setting Thomas changed in the file:

[Service]
ProtectHome=false

Use systemctl daemon-reload to reload the systemctl config.

Earthenware

This answer works on Ubuntu 18.04, and is the approach recommended by Mariadb. See mariadb.com/kb/en/library/systemd/… For other stuff that might cause problems (I for example, was also missing an execute flag on a parent directory), see mariadb.com/kb/en/library/what-to-do-if-mariadb-doesnt-start/… – Wuhsien

Works like a charm! – Smectic

Answer 3

On debian 9 you need to change ProtectHome to false in both /lib/systemd/system/mariadb.service and /lib/systemd/system/[email protected] then run sudo systemctl daemon-reload.

Maybe also run sudo grep -Ri "protecthome" /lib/systemd/system to find other instances of ProtectHome in mysql-related files if this still isn't working

Innards

Worked for opensuse tumbleweed too, /usr/lib/systemd/... and also mariadb.service and mysql.service are symlinked, [email protected] and [email protected] likewise. – Ioved

Answer 4

The workaround for this is mounting the directory in home as /var/lib/mysql:

mount --bind /home/mysql/var/lib/mysql /var/lib/mysql

Profusive

Thanks. I used the solution above, but this gets permanently removed by updtes. Thus I hope this is the long-term workaround: I put this in the fstab /home/share/mysqldb /var/lib/mysql none bind 0 0 – Muscarine

Answer 5

Same situation after update on Debian 8 (Jessie) and 9 (Stretch). After "apt-get upgrade" the command

• service mysql start

the server fails to start and logs error:

[Warning] Can't create test file /home/johndoe/UserDatabases/mypcname.lower-test

the solution is to change in file /lib/systemd/system/mariadb.service the value:

ProtectHome=true

to

ProtectHome=false

as described above.

Ralleigh

Answer 6

@RedGiant yes I solved it. Forgot to post here.

Apparently after the .1 release you cannot run SQL from /home anymore. There's probably a way around this but haven't found it.

I can run MySQL from any location except /home. What I did was unmount /home (I had my SSD RAID mounted to /home) and re-mount my disk as /ssd. Changed my paths in the config and it worked right away.

I did not run SELinux or Apparmor.

Talaria

Yes you can use /home. it's just a bad idea and needs extra conf (centos/fedora) – Jaclynjaco

Answer 7

I have faced the same issue as mentioned in the question. In my case, I wanted to move the MariaDB Data Directory to the /home/mysql folder but after changing the my.cnf file, MariaDB service was failing to start.

I have made the following changes in the file /lib/systemd/system/mariadb.service

ProtectHome=true to ProtectHome=false

It asked me to reload the daemon while starting the Mariadb service again. However, i have rebooted the server and started the MariaDB service without any issue.

Tashinatashkent