MariaDB: Fatal error: Table './mysql/host.MYI' is read only

MariaDB upgrade fails on Debian 8.x with Directadmin, and here is a possible solution. Found the issue with upgrading from 10.0.35 to 10.0.36.

Can't connect to local MySQL server through socket

SQL server is not available with the following error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/usr/local/mysql/data/mysql.sock' (2)

And if you see the similar lines in the server error log:

180909 17:04:07 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
180909 17:04:07 [ERROR] Can't open and lock privilege tables: Table './mysql/servers.MYI' is read only
180909 17:04:07 [Note] Server socket created on IP: '127.0.0.1'.
180909 17:04:07 [ERROR] Fatal error: Can't open and lock privilege tables: Table './mysql/host.MYI' is read only
180909 17:04:07 mysqld_safe mysqld from pid file /usr/local/mysql/data/mysqld.pid ended

Have no issue with MariaDB 10.0.35 (if to downgrade back to the version). The SQL server starts, and no issue is found. Hence it can not be an issue with files/folders permissions. 

And none of master/slave replication is enabled.

Is that because of Selinux?

The server has selinux disabled

Probably AppArmor?

AppArmor does not seem to be enabled.

root@hosting:/etc/apparmor.d# service apparmor status
● apparmor.service
   Loaded: not-found (Reason: No such file or directory)
   Active: inactive (dead)
root@hosting:/etc/apparmor.d#

If to do a fresh installation?

A fresh installed MariaDB 5.5.x fails the same way even after:

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/

it installs all the tables and fails to start afterwards.

Nothing helps?

So we can't start any other MariaDB version, but 10.0.35.

Reasons?

The reason for this is that newer MariaDB versions need read access all the way down the path (not too sure why, but they do), eg:

openat(-1, "/home", O_RDONLY|O_NOFOLLOW|O_PATH) = -1 EACCES (Permission denied)
openat(-1, "/home", O_RDONLY|O_NOFOLLOW|O_PATH) = -1 EACCES (Permission denied)
write(2, "180910 16:04:47 [ERROR] Fatal error: Can't open and lock privilege tables: Table './mysql/host.MYI' is read onlyn", 113180910 16:04:47 [ERROR] Fatal error: Can't open and lock privilege tables: Table './mysql/host.MYI' is read only 

and we cannot change /home to 755 for security reasons.

/home should be 711 so it's contents cannot be read by Users.

Let's move datadir then...

Change/add datadir= in /etc/my.cnf from /home/mysql/ and move databases to /var/lib/mysql/:

datadir = /var/lib/mysql/

and MariaDB 10.0.36 should start fine on your Debian 8 server.

Complete upgrade with:

/usr/local/mysql/bin/mysql_upgrade

And what about Directadmin?

New installs of Directadmin (since version 1.54) will address these changes in MariaDB and install MySQL data into /var/lib/mysql. 

• Debian: MySQL/MariaDB will install data to /var/lib/mysql from /home/mysql
  

That's it.

If you don't know how to do it, contact us for a paid service.