MySQL

MySQL Transaction Read-Only Error on Secondary Replica Database

A "read-only" error when attempting to write to a MySQL database usually means your application connected to a replica (secondary) server rather than the primary — replicas are intentionally configured as read-only specifically to prevent writes that would conflict with the replication stream coming from the actual primary server.

The Problem

An INSERT, UPDATE, or DELETE statement fails with:

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

The same operation might work fine at other times, or from a different part of the application, pointing at a routing or configuration inconsistency rather than a permissions issue affecting every connection uniformly.

Why It Happens

In a typical primary-replica MySQL setup, replicas are deliberately configured with read_only (or the stricter super_read_only) enabled, since allowing direct writes to a replica would create data that's never replicated back to the primary, causing the replica's data to diverge from the primary in a way that replication has no mechanism to reconcile. This is intentional, correct behavior — the error surfaces specifically when:

  • Application code (or a specific code path, background job, or migration script) is inadvertently connecting to a replica's connection string when it should be using the primary for write operations
  • A load balancer or connection pooler distributing database traffic across multiple servers isn't correctly distinguishing between read and write operations, sending some writes to a replica by mistake
  • A failover occurred (the former primary became a replica, or vice versa) and application configuration wasn't updated to reflect the new topology, continuing to send writes to what is now a replica
  • A read replica connection is being reused for what should be a separate, primary-only database session, due to an environment configuration or connection pooling setup that doesn't clearly separate the two

The Fix

First, confirm which server you're actually connected to, and its current replication role:

SELECT @@hostname, @@read_only, @@super_read_only;
SHOW REPLICA STATUS\G

If SHOW REPLICA STATUS returns results (rather than an empty result), you're connected to a replica — confirming the diagnosis, and pointing you toward checking your application's connection configuration next.

Review your application's database connection configuration to confirm write operations are explicitly routed to the primary server's connection string, not a replica's:

# Example: separate read/write connection configuration
DATABASE_WRITE_URL=mysql://user:pass@primary-host:3306/mydb
DATABASE_READ_URL=mysql://user:pass@replica-host:3306/mydb

Ensure your application code (or ORM configuration) consistently uses the write connection for any INSERT/UPDATE/DELETE operation, and only uses the read connection for genuine, safe-to-be-slightly-stale SELECT queries where reading from a replica is actually appropriate:

# Example: many ORMs support explicit read/write connection routing
class Order(Model):
    class Meta:
        # writes always use the 'default' (primary) connection
        # explicit .using('replica') only for read-heavy, replica-safe queries
        pass

Order.objects.using('replica').filter(status='shipped')  # safe read from replica
Order.objects.create(customer_id=123)  # uses default (primary) connection for the write

If a recent failover changed which server is currently the primary, update your application's configuration (or, ideally, ensure your infrastructure uses a mechanism like a virtual IP, DNS record, or proxy that automatically points to the current primary) so a future failover doesn't require manual application reconfiguration each time:

# Using a proxy/router that automatically tracks the current primary
# is more resilient than hardcoding a specific server's hostname directly
DATABASE_WRITE_URL=mysql://user:pass@db-proxy:3306/mydb

If you're using a connection pooler or load balancer in front of multiple MySQL servers, confirm it's actually configured to distinguish between read and write traffic correctly — tools like ProxySQL support explicit read/write splitting rules based on query type, and misconfigured routing rules there are a common source of this exact error appearing inconsistently across an application.

Still Not Working?

If you've confirmed the connection is correctly routed to what should be the primary, but still receive the read-only error, double check whether super_read_only specifically is enabled, since this stricter setting also blocks writes from accounts that would normally bypass a plain read_only setting (like accounts with the SUPER privilege) — if this is unexpectedly enabled on what you believe to be your primary server, it may indicate the server's actual replication role isn't what your infrastructure's configuration assumes it to be, worth investigating at the infrastructure/orchestration level rather than assuming it's purely an application-side connection routing issue.

You might also like