How to fix ERROR 1036: Table is read only in MySQL

First, verify the ownership of your MySQL data directory. SSH into your server and run:

ls -la /var/lib/mysql/

The output should show that the mysql user owns the directory. If it shows a different user (like root or your username), this is the problem. Look for entries like:

drwx------ 5 mysql mysql 4096 Dec 22 10:15 .

Note: If your MySQL data directory is in a different location (check with SHOW VARIABLES LIKE 'datadir'; in MySQL), adjust the path accordingly.

If the ownership is wrong, fix it by running:

sudo chown -R mysql:mysql /var/lib/mysql

This recursively changes all files and directories under /var/lib/mysql to be owned by the mysql user. If your data directory is elsewhere, replace the path accordingly.

You can verify the change worked by running the ls command again.

Ensure the mysql user has proper read/write permissions. Run:

sudo chmod -R 755 /var/lib/mysql

For more restrictive permissions (often better for security), use:

sudo chmod -R 700 /var/lib/mysql

For specific database directories:

sudo chmod -R 660 /var/lib/mysql/database_name/*

Verify with ls -la to confirm write permissions are present (look for rw in the permissions).

After fixing ownership and permissions, restart MySQL to ensure it detects the changes:

sudo systemctl restart mysql

Or for older systems:

sudo service mysql restart

On macOS with Homebrew:

brew services restart mysql

Wait for the service to fully restart before attempting queries.

Connect to MySQL and check if global read_only mode is enabled:

SHOW GLOBAL VARIABLES LIKE 'read_only';

If the result shows ON, the global read-only setting is enabled. Disable it with:

SET GLOBAL read_only = OFF;

Note: Only users with SUPER privilege can modify this setting. Make sure this change is intentional, as it may have been set to protect your database.

Test that the error is resolved by attempting a simple write operation:

INSERT INTO your_table (column1) VALUES ('test_value');

If successful, the issue is resolved. If the error persists, verify that:
1. You're connected as a user with write permissions
2. You're attempting to write to the correct table
3. The service restart completed successfully

Check MySQL error logs for more details:

tail -50 /var/log/mysql/error.log