Mysql error table is read only

You can get the following error in your Apache ‘s error logs if you’re using PHP as a means to retrieve data from your database.

PHP Warning:  Table “tbl_name” is read only

query: [SQL query]

Try the following to fix it. First, see if your user with which you’re connecting has enough privileges.

mysql> SHOW GRANTS FOR CURRENT_USER;

+-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-—-+

| Grants for dbuser@%                                                          |

+-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-—-+

| GRANT USAGE ON *.* TO ‘dbuser’@’%’ IDENTIFIED BY PASSWORD ‘randomhex’ |

| GRANT ALL PRIVILEGES ON `dbname`.* TO ‘dbuser’@’%’                         |

+-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-–-—-+

2 rows in set (0.00 sec)

You should at least have the necessary privileges on your specific table. If that’s the case, try to repair the table (it may have crashed).

mysql> REPAIR TABLE tbl_name;

+-–-–-–-–-–-—+-–-—-+-–-–-—+-–-–-–-–-–-–-–-–-–-–-–-–-—+

| Table             | Op     | Msg_type | Msg_text                               |

+-–-–-–-–-–-—+-–-—-+-–-–-—+-–-–-–-–-–-–-–-–-–-–-–-–-—+

| dbuser.tbl_name| repair | error    | Table ‘dbname.tbl_name’ is read only |

+-–-–-–-–-–-—+-–-—-+-–-–-—+-–-–-–-–-–-–-–-–-–-–-–-–-—+

1 row in set, 1 warning (0.00 sec)

If you’re still getting “read only” messages, check the file permissions in /var/lib/mysql/dbname/tbl_name (assuming your database is in /var/lib/mysql).

# ls -alh /var/lib/mysql/dbname/

total 209M

drwx-–-–   2 mysql mysql  16K Jul 20 10:42 .

drwxr-xr-x 187 mysql mysql 4.0K Jul 11 12:22 ..

-rw-r–r–   1 root  root 1.0K Jun 28 06:14 tbl_name.MYI

-rw-r–r–   1 root  root 8.4K Jun 28 06:14 tbl_name.frm

The files should be owned by “mysql” with the group “mysql”, if your MySQL is running as the mysql user. Stop the MySQL daemon, change the ownership and restart your MySQL.

# /etc/init.d/mysqld stop

Stopping MySQL:                                            [  OK  ]

# chown mysql.mysql /var/lib/mysql/dbname/*

# /etc/init.d/mysqld start

Starting MySQL:                                            [  OK  ]

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.