ClickHouse DB::Exception: Cannot execute query in readonly mode

The "DB::Exception: Cannot execute query in readonly mode" error in ClickHouse indicates that a query attempted to modify data or settings, but the current session or user is configured as readonly. The READONLY error code is raised when INSERT, ALTER, CREATE, DROP, or other write operations are attempted in a session where readonly is set to a non-zero value.

Impact

All write and DDL operations are blocked for the affected session or user. SELECT queries continue to work normally. Applications that need to write data will fail until the readonly restriction is removed or the query is routed to a connection with write permissions. This is a common issue in mixed-use environments where read and write users have different profiles.

Common Causes

1. The user's settings profile has readonly = 1 (no writes, no settings changes) or readonly = 2 (no writes, but settings changes allowed)
2. The HTTP interface connection URL includes readonly=1 as a query parameter
3. The user was created or configured in users.xml with readonly mode enabled
4. A load balancer or proxy injects readonly mode for certain connection paths (e.g., read replicas)
5. The session was explicitly set to readonly by a SET readonly = 1 statement
6. ClickHouse Cloud or managed service restricts certain users to readonly by default

Troubleshooting and Resolution Steps

1. Check the current readonly setting for your session:

   SELECT name, value
   FROM system.settings
   WHERE name = 'readonly';
   

2. If connecting via HTTP, check the URL for a readonly parameter:

   -- This forces readonly mode:
   http://localhost:8123/?readonly=1
   -- Remove it or set to 0 for write access:
   http://localhost:8123/?readonly=0
   

3. Check the user's profile settings:

   SHOW GRANTS FOR your_user;
   SELECT * FROM system.settings_profiles;
   

4. If you have admin access, change the readonly setting for the user:

   ALTER SETTINGS PROFILE 'your_profile' MODIFY SETTINGS readonly = 0;
   

5. If defined in users.xml, update the configuration:

   <profiles>
       <your_profile>
           <readonly>0</readonly>
       </your_profile>
   </profiles>
   

   Then reload the configuration:

   sudo systemctl reload clickhouse-server
   # Or within ClickHouse:
   # SYSTEM RELOAD CONFIG;
   

6. For applications that need both read and write access, ensure they connect with a user that has the appropriate profile:

   clickhouse-client --user=write_user --password=...
   

7. If you intentionally want readonly for most operations but need occasional writes, create separate users:

   CREATE USER reader SETTINGS PROFILE 'readonly_profile';
   CREATE USER writer SETTINGS PROFILE 'readwrite_profile';
   

Best Practices

• Use dedicated read-only users for dashboards, reporting tools, and ad-hoc analysts to prevent accidental data modifications.
• Use dedicated read-write users for ETL pipelines and applications that need to insert or modify data.
• Prefer readonly = 2 over readonly = 1 when you want to restrict writes but still allow users to change query-level settings like max_threads or max_execution_time.
• Configure readonly mode at the profile level rather than the session level for consistent enforcement.
• Document which users and profiles are readonly so that application teams use the correct credentials.
• In distributed setups, route read queries to read replicas (with readonly users) and write queries to the primary nodes.

Frequently Asked Questions

Q: What is the difference between readonly = 1 and readonly = 2?
A: With readonly = 1, the user cannot modify data and cannot change any settings. With readonly = 2, the user still cannot modify data, but can change settings that are not restricted by constraints. This is useful for analysts who need to tune query performance without risking data modifications.

Q: Can a readonly user create temporary tables?
A: With readonly = 1, all write operations are blocked, including temporary table creation. With readonly = 2, SET and CREATE TEMPORARY TABLE are explicitly permitted in addition to read queries, so a readonly = 2 user can create temporary tables.

Q: Why am I getting this error in ClickHouse Cloud?
A: ClickHouse Cloud may assign readonly profiles to certain default or limited users. Check your service's user management console to verify the user's permissions, or contact your ClickHouse Cloud administrator to adjust the access level.