TABLE_IS_READ_ONLY ClickHouse error¶

This error occurs when you try to modify a table that is in read-only mode. This can happen due to system maintenance, permissions, or table engine constraints.

The TABLE_IS_READ_ONLY error in ClickHouse (and Tinybird) happens when you attempt to perform write operations (INSERT, UPDATE, DELETE, ALTER) on a table that is currently in read-only mode. This can occur due to various reasons including system maintenance, insufficient permissions, table engine limitations, or intentional configuration.

What causes this error¶

You'll typically see it when:

Attempting to INSERT into a read-only table
Trying to UPDATE or DELETE from a read-only table
Running ALTER statements on read-only tables
System maintenance mode is active
Insufficient user permissions
Table engine doesn't support writes
Table is in a read-only state due to configuration

Read-only tables can still be queried with SELECT statements. Only write operations are blocked.

Example errors¶

Fails: INSERT into read-only table

INSERT INTO read_only_table (id, name) VALUES (1, 'test')
-- Error: Table is in readonly mode

Fails: UPDATE read-only table

UPDATE read_only_table SET name = 'updated' WHERE id = 1
-- Error: Table is in readonly mode

Fails: DELETE from read-only table

DELETE FROM read_only_table WHERE id = 1
-- Error: Table is in readonly mode

Fails: ALTER read-only table

ALTER TABLE read_only_table ADD COLUMN new_column String
-- Error: Table is in readonly mode

How to fix it¶

Check table status¶

Verify if the table is actually read-only:

Check table engine and status

SELECT
    database,
    name,
    engine,
    engine_full,
    total_rows,
    total_bytes
FROM system.tables
WHERE database = currentDatabase()
  AND name = 'your_table_name'

Check table permissions¶

Verify your user permissions:

Check user permissions

SELECT
    user,
    database,
    table,
    grantee,
    privilege_type
FROM system.grants
WHERE table = 'your_table_name'

Check system settings¶

Look for read-only configurations:

Check read-only settings

SELECT
    name,
    value,
    description
FROM system.settings
WHERE name LIKE '%readonly%'

Verify table engine¶

Check if the table engine supports writes:

Check table engine capabilities

SELECT
    name,
    engine,
    engine_full
FROM system.tables
WHERE database = currentDatabase()

Common patterns and solutions¶

Temporary read-only state¶

Wait for maintenance to complete:

Check if maintenance is active

SELECT
    database,
    table,
    partition,
    name,
    active,
    modification_time
FROM system.parts
WHERE database = currentDatabase()
  AND table = 'your_table_name'

Permission issues¶

Request proper permissions or use appropriate user:

Check current user

SELECT currentUser() as current_user

Engine limitations¶

Use appropriate table engines for write operations:

Create writable table

CREATE TABLE writable_table (
    id UInt64,
    name String,
    created_at DateTime
) ENGINE = MergeTree()
ORDER BY id

System maintenance¶

Check system status and wait if needed:

Check system processes

SELECT
    query_id,
    user,
    query,
    state,
    start_time
FROM system.processes

Tinybird-specific notes¶

In Tinybird, TABLE_IS_READ_ONLY errors often occur when:

Data Sources are in maintenance mode
Workspace permissions are restricted
Data Sources are configured as read-only
System maintenance is in progress
API rate limits are exceeded

To debug in Tinybird:

Check Data Source status in the UI
Verify workspace permissions
Check if maintenance is scheduled
Contact support if the issue persists

In Tinybird, Data Sources can be temporarily read-only during maintenance or when rate limits are exceeded.

Best practices¶

Table design¶

Choose appropriate table engines for your use case
Plan for maintenance windows
Use proper permissions and access controls
Document table write requirements

Maintenance planning¶

Schedule maintenance during low-traffic periods
Communicate read-only periods to users
Implement proper backup strategies
Test maintenance procedures

Error handling¶

Implement retry logic for temporary read-only states
Use appropriate error messages for users
Monitor for read-only table states
Have fallback strategies for write operations

Troubleshooting steps¶

Step 1: Verify table state¶

Check table status

SELECT
    database,
    name,
    engine,
    total_rows,
    total_bytes
FROM system.tables
WHERE database = currentDatabase()
  AND name = 'your_table_name'

Step 2: Check permissions¶

Verify permissions

SELECT
    user,
    database,
    table,
    privilege_type
FROM system.grants
WHERE table = 'your_table_name'

Step 3: Check system status¶

System status check

SELECT
    name,
    value
FROM system.settings
WHERE name IN ('readonly', 'readonly_reason')

Step 4: Verify engine support¶

Engine capabilities

SELECT
    name,
    engine,
    engine_full
FROM system.tables
WHERE database = currentDatabase()

Alternative solutions¶

Use different table¶

Create a writable table for temporary operations:

Temporary writable table

CREATE TABLE temp_writable (
    id UInt64,
    data String
) ENGINE = Memory

-- Insert into temporary table
INSERT INTO temp_writable (id, data) VALUES (1, 'test')

-- Later merge with main table when it's writable
INSERT INTO main_table SELECT * FROM temp_writable

Batch operations¶

Wait for write access and perform batch operations:

Batch operations when available

-- Check if table is writable
SELECT count() FROM system.tables
WHERE database = currentDatabase()
  AND name = 'your_table_name'

-- If writable, perform batch insert
INSERT INTO your_table_name (id, name, timestamp) VALUES
(1, 'batch1', now()),
(2, 'batch2', now()),
(3, 'batch3', now())

Read-only alternatives¶

Use read-only operations while waiting:

Read operations while waiting

-- Analyze data without writing
SELECT
    COUNT(*) as total_records,
    COUNT(DISTINCT user_id) as unique_users,
    MIN(timestamp) as earliest_record,
    MAX(timestamp) as latest_record
FROM read_only_table
WHERE timestamp >= '2024-01-01'