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 ]