Mariadb requires 'read' permission on parent folders (/var/lib)

[zanami]

I just upgraded Wheezy to Jessie using _WHEEZY_TO_JESSIE=YES
MariaDB was probably upgraded too and it couldn't start during 'barracuda up-stable'

Feb 26 16:57:20 boa mysqld: 2019-02-26 16:57:20 139948020463552 [Note] Plugin 'FEEDBACK' is disabled.
Feb 26 16:57:20 boa mysqld: 2019-02-26 16:57:20 139948020463552 [ERROR] Can't open and lock privilege tables: Table './mysql/servers.MYI' is read only
Feb 26 16:57:20 boa mysqld: 2019-02-26 16:57:20 139948020463552 [Note] Server socket created on IP: '::'.
Feb 26 16:57:20 boa mysqld: 2019-02-26 16:57:20 139948020463552 [ERROR] Fatal error: Can't open and lock privilege tables: Table './mysql/user.MYI' is read only
Feb 26 16:57:20 boa mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended

Long story short: this new MariaDB (mariadb-server-10.1) requires 'read' access on all folders down to its data files (/var/lib/mysql/...)

In my case the var folder was drwx--x--x 15 root root 4.0K Feb 26 15:13 var/

Now sure why MariaDB would want to read something above its datadir but it does.
chmod +r /var fixed the problem and mysqld finally started (4 hours spent).
Not easy to track down this issue because actually mysql user can read/write/delete ./mysql/*

I think barracuda should be checking for permissions if there's no real fix.

[omega8cc]

MariaDB 10.1 works perfectly well with current permissions. The problem must have been elsewhere.

I'm honestly surprised you were able to still upgrade Wheezy to Jessie, since Wheezy repositories for packages no longer exists, so it's not even possible to install/fix anything. A miracle, actually. See:

https://twitter.com/omega8cc/status/1000865515819208704

[zanami]

Wow, I guess I'm lucky

I'm not sure what 'current permissions' should be, in my case /var was drwx--x--x (root:root)
maybe it's because of my 'impossible' Wheezy to Jessie upgrade

To reproduce

chmod o-r /var
service mysql restart
tail -f /var/log/syslog
...
Feb 27 11:37:38 boa mysqld: 2019-02-27 11:37:38 140222578702272 [ERROR] Can't open and lock privilege tables: Table './mysql/servers.MYI' is read only
Feb 27 11:37:38 boa mysqld: 2019-02-27 11:37:38 140222578702272 [Note] Server socket created on IP: '::'.
Feb 27 11:37:38 boa mysqld: 2019-02-27 11:37:38 140222578702272 [ERROR] Fatal error: Can't open and lock privilege tables: Table './mysql/user.MYI' is read only
Feb 27 11:37:38 boa mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended
...

[zanami]

More important — barracuda up-stable sets that /var permissions back to drwx--x--x (711) here and here

Actually (I'm not sure) Maybe some maintenance script changes /var permissions back to drwx--x--x
which leads to 'read only' mariadb errors and non-functional sites

If mysql is running when permissions change, then it continues to run, just throws 'read only' errors.

some sites don't work until chmod +r /var
barracuda up-head & octopus up-head didn't help

My other Debian machines have drwxr-xr-x (755) permissions for /var

I can't find many issues like this but here's one.

[omega8cc]

These permissions are correct and they don’t interfere in any way. Your instance problem must be elsewhere, perhaps related to no longer supported major system upgrade or even file system issue. Please check ownership on your databases binary files and directories, not permissions.

[zanami]

I understand that you can’t reproduce my setup, but I’m certain it is permissions related.
chmod o-r /var - problem (mysql.* and other myisam (?) tables are read only)
chmod o+r /var - works fine
Everything else is the same.
What made mariadb to behave like that is another question.
I’ll try _SQL_FORCE_REINSTALL

[zanami]

I’ll try _SQL_FORCE_REINSTALL

Nope didn't help
I guess adding chmod to cron is the only option for me

[omega8cc]

Have you checked ownership, not just permissions? MariaDB doesn’t require any new permissions.

[zanami]

I compared permissions/ownership to my other systems, everything looks fine.

drwx--xr-x root root /var/ (o+r here by me)
drwxr-xr-x root root /var/lib/
drwxr-xr-x mysql mysql /var/lib/mysql/
drwx------ mysql mysql /var/lib/mysql/mysql/
-rw-rw---- 1 mysql mysql /var/lib/mysql/mysql/*

I can't use auditd (no support) so I can't check who/when tries to read /var
Here's the list of open files (common libs/data excluded)

lsof -r 1 -u mysql | grep -v -e '/var/lib/mysql/*' -e '/lib' -e '/usr/lib'
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 20968 mysql rtd DIR 0,35 4096 16517515 /
mysqld 20968 mysql txt REG 0,35 17661608 16520943 /usr/sbin/mysqld
mysqld 20968 mysql mem REG 253,1 16520943 /usr/sbin/mysqld (path dev=0,35)
mysqld 20968 mysql 0r CHR 1,3 0t0 979536290 /dev/null
mysqld 20968 mysql 1w FIFO 0,8 0t0 1027909318 pipe
mysqld 20968 mysql 2w FIFO 0,8 0t0 1027909318 pipe
mysqld 20968 mysql 11u REG 0,35 0 16650890 (deleted)/tmp/iboiVpiL
mysqld 20968 mysql 21u IPv6 1027909335 0t0 TCP *:mysql (LISTEN)
mysqld 20968 mysql 22u unix 0xffff880267fa9800 0t0 1027909336 /var/run/mysqld/mysqld.sock

When tables are open they work fine, no 'read only' errors even if /var is unreadable for mysql.
If I try to restart mysql service it just won't start.

This version is that I have problems with

boa:~# mysql --version
mysql  Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

This version doesn't care if /var is 755 or 711, it restarts fine with both. It's on another system so there may be other differences.

mysql --version
mysql  Ver 14.14 Distrib 5.5.47, for debian-linux-gnu (x86_64) using readline 6.2