Why is My MySQL Table Read-Only and How Can I Fix It?

Error 1036: Table is 'Read Only' Solution

When attempting to execute update queries on a table and encountering the "1036 - Table data is read only" error, it's evident that the table is in a restricted state. To rectify this issue, follow the steps outlined below:

1. Examine MySQL Configuration:

Check the MySQL configuration file (/etc/my.cnf or /etc/mysql/my.cnf) for the following setting:

innodb_force_recovery

If this parameter is set to 1, it forces the database to operate in read-only mode. Comment out or remove this setting to enable write operations.

2. Check Table Attributes:

Ensure that the table's file permissions on the server filesystem are not read-only. The table attributes should be set to 777 (full permissions) for both the user and the group owning the files.

3. Optimize and Repair Table:

Try using the OPTIMIZE TABLE command to reclaim unused space and improve performance. Additionally, run the REPAIR TABLE command to check for errors and repair any inconsistencies.

4. Check InnoDB Recovery Setting:

In cases where the previous steps do not resolve the issue, check the InnoDB recovery setting in the MySQL database. Execute the following query:

SHOW INNODB STATUS\G

Look for the line that starts with innodb_use_sys_read_only_table. If this value is set to 1, it indicates that the table is being forced into read-only mode. Set it to 0 to disable this restriction.

5. Restart MySQL:

After making the necessary changes, restart the MySQL service for the updates to take effect.

The above is the detailed content of Why is My MySQL Table Read-Only and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!