Error
Error Code:
1036
MariaDB Error 1036: Cannot Write to Table
Description
This error indicates that an attempt was made to modify a MariaDB table that is currently in a read-only state. This prevents any INSERT, UPDATE, or DELETE operations, often due to underlying file system permissions, server configuration, or explicit table attributes.
Error Message
Table '%s' is read only
Known Causes
3 known causesInsufficient File System Permissions
The operating system permissions on the table's data files (e.g., .frm, .ibd, .MYD) prevent MariaDB from writing to them.
Server or Database Read-Only Mode
The MariaDB server (e.g., `super_read_only` variable) or a specific database is configured to disallow write operations.
Table Explicitly Set to Read-Only
The table has been intentionally set to a read-only state using an `ALTER TABLE ... READ ONLY` statement, often for maintenance or data archival.
Solutions
3 solutions available1. Check Table Storage Engine and Permissions easy
Verify the table's storage engine and the user's write permissions.
1
Connect to your MariaDB server as a user with sufficient privileges (e.g., root).
mysql -u root -p2
Select the database where the problematic table resides.
USE your_database_name;3
Check the storage engine of the table. Some storage engines (like MEMORY) are inherently read-only or have limitations.
SHOW CREATE TABLE your_table_name;4
If the storage engine is inappropriate for writes (e.g., MEMORY and you need persistence), consider converting it. For example, to convert a MEMORY table to InnoDB:
ALTER TABLE your_table_name ENGINE=InnoDB;5
Verify that the user attempting to write to the table has the necessary privileges (e.g., INSERT, UPDATE, DELETE).
SHOW GRANTS FOR 'your_user'@'localhost';6
If privileges are missing, grant them. For example, to grant all privileges on the table to 'your_user':
GRANT ALL PRIVILEGES ON your_database_name.your_table_name TO 'your_user'@'localhost';7
Apply the privilege changes.
FLUSH PRIVILEGES;2. Examine MariaDB System Variables medium
Inspect and adjust MariaDB system variables that might enforce read-only modes.
1
Connect to your MariaDB server as a user with super privileges.
mysql -u root -p2
Check the `read_only` system variable. If it's set to `ON`, the entire server is in read-only mode.
SHOW GLOBAL VARIABLES LIKE 'read_only';3
If `read_only` is `ON`, you can temporarily disable it for the current session (requires SUPER privilege):
SET GLOBAL read_only = OFF;4
If you want to permanently disable `read_only`, you need to modify the MariaDB configuration file (e.g., `my.cnf` or `mariadb.conf.d/50-server.cnf`). Locate the `[mariadb]` or `[mysqld]` section and ensure `read_only` is not set or is set to `OFF`.
# Example in my.cnf
[mariadb]
read_only = OFF5
Restart the MariaDB service after modifying the configuration file for the changes to take effect.
sudo systemctl restart mariadb6
Also, check the `super_read_only` variable. It's similar to `read_only` but cannot be changed by non-super users.
SHOW GLOBAL VARIABLES LIKE 'super_read_only';7
If `super_read_only` is `ON`, you'll need to disable it in the configuration file and restart the server, similar to `read_only`.
# Example in my.cnf
[mariadb]
super_read_only = OFF3. Investigate Table Corruption or Disk Issues advanced
Rule out underlying table corruption or disk space problems.
1
Connect to your MariaDB server.
mysql -u root -p2
Select the database.
USE your_database_name;3
Check the status of the table. Look for any error messages or unusual flags.
SHOW TABLE STATUS LIKE 'your_table_name';4
If the table uses MyISAM, check for corruption and attempt to repair it. For InnoDB, this is less common but can still occur.
REPAIR TABLE your_table_name;5
If `REPAIR TABLE` fails or doesn't resolve the issue, consider using `CHECK TABLE` to diagnose further.
CHECK TABLE your_table_name;6
Examine the MariaDB error log for any disk-related errors or I/O issues that might be preventing writes.
# The location of the error log varies, common locations include:
# /var/log/mysql/error.log
# /var/log/mariadb/mariadb.log7
Verify that the disk where MariaDB stores its data files has sufficient free space.
df -h8
If the disk is full, free up space or expand the storage. Ensure MariaDB has write permissions to the data directory.
sudo chown -R mysql:mysql /var/lib/mysql/