24

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?

Bento
  • 725
  • 2
  • 8
  • 22
  • 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 :) – Hackerman Jul 22 '16 at 17:27
  • 1
    Yeah don't really want to do that. Must be an easier way. – Bento Jul 23 '16 at 08:36
  • @BentodeGier, have you solved the problem? I just got this error after update – RedGiant Jul 27 '16 at 16:13
  • Check this answer: https://stackoverflow.com/a/69450228/3881354 and follow all the mentioned steps, but on the `Ubunto` you should work with `AppArmor` not `SELinux` – Elyas Hadizadeh Oct 06 '21 at 09:34

7 Answers7

64

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
Mathieu Rodic
  • 6,637
  • 2
  • 43
  • 49
Thomas
  • 656
  • 6
  • 2
  • 4
    in ubuntu 17.04 the file seems to have moved here /lib/systemd/system/ – wuppi Apr 18 '17 at 11:08
  • 3
    this seems to be reset some times. any idea to prevent this? – wuppi Aug 06 '17 at 19:43
  • 5
    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" – user1077915 Oct 29 '17 at 12:10
  • 2
    This apparently doesn't work anymore. Mariadb just updated this morning and it completely ignores ProtectHome=false. – Jestep Feb 07 '18 at 15:50
18

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.

zuim
  • 1,039
  • 8
  • 10
  • This answer works on Ubuntu 18.04, and is the approach recommended by Mariadb. See https://mariadb.com/kb/en/library/systemd/#configuring-access-to-home-directories For other stuff that might cause problems (I for example, was also missing an execute flag on a parent directory), see https://mariadb.com/kb/en/library/what-to-do-if-mariadb-doesnt-start/#cant-create-test-file – Giles B Nov 18 '19 at 18:54
  • Works like a charm! – xangr Jan 20 '20 at 21:56
8

On debian 9 you need to change ProtectHome to false in both /lib/systemd/system/mariadb.service and /lib/systemd/system/mariadb@.service 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

Neo
  • 474
  • 6
  • 13
  • Worked for opensuse tumbleweed too, /usr/lib/systemd/... and also mariadb.service and mysql.service are symlinked, mariadb@.service and mysql@.service likewise. – MaKR Mar 16 '20 at 15:29
7

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

mount --bind /home/mysql/var/lib/mysql /var/lib/mysql
Peter Grond
  • 79
  • 2
  • 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` – wuppi Oct 31 '17 at 15:54
4

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.

karel
  • 41
  • 2
3

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

Bento
  • 725
  • 2
  • 8
  • 22
0

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.

Hiren Parghi
  • 1,795
  • 1
  • 21
  • 30