Victor's room

Overview & Root Cause Summary: The error ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement occurs in MySQL and MariaDB when a client application, migration script, or developer attempts to execute a write operation (INSERT, UPDATE, DELETE, or CREATE TABLE), but the database instance is operating under read-only or super-read-only enforcement. This commonly happens when connecting to a read replica instead of the primary writer, during incomplete failover transitions, after cloud hosting providers lock instances due to low disk space, or from persistent configuration directives in my.cnf.

Understanding the Root Causes

• Application Routed to Read Replica: The application’s database connection string or load balancer pool mistakenly routes write queries to a secondary read replica rather than the primary read-write cluster node.
• Automatic Read-Only Disk Space Safeguard: Cloud providers (such as AWS RDS, GCP Cloud SQL, or DigitalOcean Managed Databases) automatically flip instances to read_only = ON when storage capacity drops below critical thresholds (typically < 5% free disk) to prevent tablespace corruption.
• Unfinished High Availability / Replication Failover: Following an automated failover (via Orchestrator, MHA, or ProxySQL), a newly promoted master node remains locked in read-only mode because the post-failover promotion script failed to execute.
• Static Configuration in my.cnf: The server configuration file explicitly declares read_only = 1 or super_read_only = 1 under the [mysqld] section.

Step 1: Quick Fix (Inspect & Disable read_only Dynamically)

Connect with administrative privileges and toggle off the read-only safety locks at runtime.

# 1. Connect to MySQL as an administrative user:
mysql -u root -p

# 2. Check the current status of read_only and super_read_only:
SHOW VARIABLES LIKE '%read_only%';

# Example output:
# +------------------+-------+
# | Variable_name    | Value |
# +------------------+-------+
# | read_only        | ON    |
# | super_read_only  | ON    |
# +------------------+-------+

# 3. Disable super_read_only first, then disable read_only:
SET GLOBAL super_read_only = OFF;
SET GLOBAL read_only = OFF;

# 4. Confirm both settings are now OFF:
SHOW VARIABLES LIKE '%read_only%';

Step 2: Check Disk Thresholds & Manage Persistent my.cnf Settings

Ensure that low disk space did not trigger the lock and remove persistent read-only directives from configuration files.

# 1. Inspect server storage utilization:
df -h
# If the partition hosting /var/lib/mysql is > 95% full, free up space immediately:
# Remove or rotate old binary logs (inside MySQL):
# PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;

# 2. Open the MySQL configuration file:
# Ubuntu/Debian: /etc/mysql/mysql.conf.d/mysqld.cnf
# CentOS/RHEL:   /etc/my.cnf
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# 3. Search for and comment out or remove any read-only directives:
# [mysqld]
# read_only = 1
# super_read_only = 1

# 4. If modified, restart MySQL:
sudo systemctl restart mysql

Step 3: Resolve Replication Topology & Application Connection Routing

Align application connection pools with your database cluster architecture to ensure writes reach the write-capable master.

# --- Check Replication Node Role ---
# In MySQL console, check if the server is acting as a replication slave:
SHOW REPLICA STATUS\G
# (Or on older MySQL 5.7 / MariaDB):
# SHOW SLAVE STATUS\G

# If this node is intended to be a dedicated replica, DO NOT disable read_only!
# Instead, update your application connection string (.env) to point to the Master IP:
# WRONG (pointing writes to read replica endpoint):
# DATABASE_URL="mysql://app_user:pass@db-replica-01.internal:3306/mydb"

# CORRECT (pointing writes to master writer endpoint):
DATABASE_URL="mysql://app_user:pass@db-primary.internal:3306/mydb"

Verification & Testing Steps

Confirm that the MySQL instance accepts write transactions cleanly without throwing Error 1290.

# 1. Test creating and inserting into a temporary verification table:
mysql -u root -p mydb -e "CREATE TABLE IF NOT EXISTS _write_test (id INT PRIMARY KEY); INSERT INTO _write_test VALUES (1); DROP TABLE _write_test;"
# Expected output: (exits cleanly with zero errors)

# 2. Check user privileges:
mysql -u app_user -p mydb -e "SELECT current_user(), @@read_only;"

Summary Comparison Table

Root Cause	Diagnostic Indicator	Immediate Remediation	Architectural Solution
Read Replica Routing	SHOW REPLICA STATUS shows active slave IO	Point client to Primary writer node	Configure read/write splitting (ProxySQL)
Low Disk Trigger	df -h shows disk > 95% full	Purge binlogs & expand storage volume	Configure automated disk auto-scaling
Failed Promotion	Server is primary but read_only=ON	SET GLOBAL read_only = OFF;	Fix post-failover orchestrator hooks
Static Config Flag	Reboots reset variable to ON	Remove read_only=1 in my.cnf	Standardize configuration templates