ClickHouse "Table is in readonly mode": Causes and How to Fix It

Your ingestion pipeline starts failing, dashboards still load, and the logs say "Table is in readonly mode". In ClickHouse this almost always concerns a ReplicatedMergeTree table, and it means the replica has lost its connection to the coordination service (ClickHouse Keeper or ZooKeeper) or cannot find its own metadata there. Data on disk is normally fine. The job is to work out which of the two cases you are in, because the fix is different.

Code: 242. DB::Exception: Table is in readonly mode
(replica path: /clickhouse/tables/01/events/replicas/ch1). (TABLE_IS_READ_ONLY)

What read-only means here

The error code is 242, TABLE_IS_READ_ONLY, as listed in the ClickHouse error codes. A replicated table needs Keeper for everything that changes the shared state: inserts, merges, mutations, ALTERs and the replication queue. The storage engine source throws this same error when it cannot update the queue, assign merges or finalize mutations while read-only.

Reads are different. The replication documentation says ZooKeeper is not used in SELECT queries, so queries on the table keep working. That is why this incident often shows up as "inserts broken, everything else looks fine". Be aware that the replica stops receiving new data from its peers while it is in this state, so results on it can get stale.

Why a replica goes read-only

According to the system.replicas documentation, read-only mode is turned on when the config has no Keeper section, when an unknown error happens while reinitializing the Keeper session, and during the session reinitialization itself. The source adds two more situations: a table stays read-only after server start until its initialization finishes, and a table whose metadata is not found in Keeper is read-only too. In practice that gives four causes:

1. Keeper is down or has lost quorum. Every replicated table on every server that uses it goes read-only at about the same time.
2. The session expired. Typical triggers are network problems or an overloaded or stalled Keeper or ClickHouse server, which can let the session time out. ClickHouse then reinitializes it, and the tables are read-only while that happens.
3. The metadata in Keeper is gone. Someone deleted the path, Keeper was rebuilt from scratch, or the table points to a path that never existed. The documentation has a classic example: if the Keeper path uses the {table} macro and you rename the table, the macro expands to a different path that does not exist, and the table goes read-only.
4. No Keeper in the config. After a config change or on a freshly built server, existing replicated tables will be read-only, and you cannot create new ones.

Step 1: see which tables and since when

SELECT database, table, is_readonly, readonly_start_time,
       is_session_expired, active_replicas, total_replicas,
       zookeeper_exception
FROM system.replicas
WHERE is_readonly
ORDER BY readonly_start_time;

readonly_start_time tells you when it started; line it up with Keeper and network events. zookeeper_exception holds the last error from Keeper and is often the fastest pointer to the cause. is_session_expired is, in the docs' own words, "basically the same as is_readonly", so do not read too much into it. The error message may also say it directly: "Table is in readonly mode since table metadata was not found in zookeeper" means case 3.

A pattern is useful too. If every replicated table on every server went read-only at the same minute, it is Keeper (case 1). If one server is affected, look at that server's connection (case 2). If one table is affected and the others are fine, suspect its metadata or its path (case 3).

Step 2: check the connection to Keeper

SELECT name, host, port, connected_time,
       session_uptime_elapsed_seconds, is_expired
FROM system.zookeeper_connection;

SELECT value FROM system.metrics WHERE metric = 'ReadonlyReplica';

system.zookeeper_connection shows which Keeper node this server is connected to, since when, and whether the connection is expired. The ReadonlyReplica metric is the number of replicated tables that are currently read-only because of re-initialization after a session loss or a start without Keeper configured. It is the one number worth alerting on.

Then ask Keeper itself, using the four-letter commands on its client port (9181 by default in the Keeper examples):

echo ruok | nc keeper1 9181          # imok = process is up (not proof of quorum)
echo mntr | nc keeper1 9181 | grep zk_server_state   # leader / follower
echo isro | nc keeper1 9181          # rw or ro

One documented trap: imok from ruok only means the process is running and listening. It does not mean the node has joined the quorum. Check zk_server_state in mntr: in a healthy ensemble there is exactly one leader and the rest are followers.

Fix A: Keeper is back, the table is still read-only

Once Keeper is healthy, replicas normally reinitialize their sessions on their own and ReadonlyReplica goes back to zero. If a table stays read-only, reinitialize it by hand:

SYSTEM RESTART REPLICA analytics.events;
-- or every replicated table on this server
SYSTEM RESTART REPLICAS;

The SYSTEM statement documentation describes SYSTEM RESTART REPLICA as reinitializing the Keeper session state for the table: it compares the local state with Keeper as the source of truth and adds tasks to the queue if needed, the same way as on ATTACH TABLE. For a short time the table is unavailable for any operations, so do it table by table on a busy server rather than with RESTART REPLICAS in the middle of peak load.

Fix B: the metadata in Keeper is lost

If the path for the replica, or for the whole table, no longer exists in Keeper, a restart will not help: the replica has nothing to reconnect to. For this ClickHouse has SYSTEM RESTORE REPLICA:

SYSTEM RESTART REPLICA analytics.events;
SYSTEM RESTORE REPLICA analytics.events;
-- run on every replica, or:
SYSTEM RESTORE REPLICA analytics.events ON CLUSTER main;

What the documentation says about it:

• It works only on read-only ReplicatedMergeTree tables, and it is meant for loss of the Keeper root, of the /replicas path or of a single replica's path.
• The replica attaches the parts it finds locally and sends information about them to Keeper. Parts that were present before the loss are not fetched again from other replicas unless they are outdated, so a restore does not mean downloading all the data over the network.
• Parts in all states are moved to the detached/ folder, and the parts that were active before the loss are attached back.
• In the documented example the command is run on every replica, or once with ON CLUSTER.

If the cause was a renamed table with a {table} macro in the path, the cleaner fix may be to rename it back, so that the table points at its original, intact path again. Check what exists in Keeper before you restore anything.

Preventing the next one

• Run Keeper as a proper ensemble: three or five nodes on their own disks, not co-located with a ClickHouse server that is busy with heavy merges. Monitor mntr output (leader present, latency, outstanding requests).
• Alert on ReadonlyReplica above zero for more than a minute or two. That catches all four causes, usually before the ingestion team notices.
• Treat Keeper paths as part of the schema. Be careful with renames when the path contains {table} or {database} macros, and never delete paths in Keeper by hand on a live cluster.
• Keep write pressure sane. Every insert block creates Keeper entries; the replication docs estimate about ten per block. Huge numbers of tiny inserts load Keeper as well as the merge process; our article on "Too many parts" covers how to batch them.
• Watch memory on the ClickHouse side too. A server under heavy memory pressure can stall long enough to miss Keeper heartbeats; see our guide to "Memory limit exceeded" for the limits to set.