Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL refuses writes: 'running with the --read-only option'

ErrorERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

Short 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.

  • Applies to MySQL 8.0, MySQL 8.4 LTS
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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');
  2. 2Match what you see, then jump to its fix

    1. 1read_only = ON in the query above, and your user is a normal one.read_only or super_read_only is ONFix
    2. 2innodb_force_recovery is 4, 5 or 6.InnoDB was started in forced-recovery modeFix

What does the error look like?

  • ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
  • ERROR 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

Did this fix it?

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.cnf

2Fix

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 mysql

3Verify

The variable reads 0 and writes work.

SHOW GLOBAL VARIABLES LIKE 'innodb_force_recovery';

Confirmed by dev.mysql.com

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.