MySQL refuses writes: 'running with the --read-only option'
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statementShort answer
Error 1290 means the server is in read-only mode. Either read_only or super_read_only is ON, which is normal on a replica, or InnoDB was started with innodb_force_recovery at 4 or higher, which makes it read-only. Check these variables first, then turn off the one that is set.
Fix it fast
1Find out which cause you have
Read the three switches in one query; the one that is ON or above 3 is your cause.
SHOW GLOBAL VARIABLES WHERE Variable_name IN ('read_only','super_read_only','innodb_force_recovery');2Match what you see, then jump to its fix
What does the error look like?
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statementERROR 1036 (HY000): Table 'orders' is read only
Cause 1 of 2
read_only or super_read_only is ON
1Check
With read_only enabled the server accepts no client updates except from accounts with CONNECTION_ADMIN (or the deprecated SUPER). super_read_only blocks even those. Setting super_read_only to ON forces read_only ON, and setting read_only to OFF forces super_read_only OFF.
SHOW GLOBAL VARIABLES LIKE '%read_only';
SELECT @@hostname;2Fix
If this is a primary that should accept writes, switch it off. If the host is a replica, write on the primary instead. Also remove read_only from the option file, or it returns after a restart.
SET GLOBAL read_only = OFF;3Verify
A write now succeeds.
SHOW GLOBAL VARIABLES LIKE '%read_only';Confirmed by dev.mysql.com
Cause 2 of 2
InnoDB was started in forced-recovery mode
1Check
A value of 4 or greater places InnoDB in read-only mode. The manual says to set it above 0 only in an emergency, to start InnoDB and dump your tables.
SHOW GLOBAL VARIABLES LIKE 'innodb_force_recovery';
grep -R "innodb_force_recovery" /etc/mysql/ /etc/my.cnf2Fix
Dump the data you need while the server is up in this mode. Then remove the innodb_force_recovery line from the option file and restart, ideally onto a clean restore.
# in the [mysqld] section of the option file, delete:
# innodb_force_recovery = 4
sudo systemctl restart mysql3Verify
The variable reads 0 and writes work.
SHOW GLOBAL VARIABLES LIKE 'innodb_force_recovery';Confirmed by dev.mysql.com
How do I stop it coming back?
- Keep read_only = ON in the option file of replicas only, and write to the primary from applications.
- After any emergency start with innodb_force_recovery, remove the setting the same day.
- Point application connection strings at the primary, not at a replica, for write paths.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix