ClickHouse DB::Exception: Unknown mutation command

The "DB::Exception: Unknown mutation command" error in ClickHouse indicates that the server encountered a mutation command it does not recognize. Mutations in ClickHouse are operations like ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE that modify existing data. If the mutation syntax is malformed or uses an unsupported command, ClickHouse raises the UNKNOWN_MUTATION_COMMAND error.

Impact

The mutation is not created, and no data is modified. The table remains in its current state. This is a syntax or version-level error that does not affect data integrity or ongoing operations.

Common Causes

1. Typo in the mutation command -- writing ALTER TABLE ... UPDATES instead of UPDATE, or other misspellings.
2. Using syntax from a different database -- attempting standard SQL UPDATE table SET ... without the ALTER TABLE prefix that ClickHouse requires.
3. ClickHouse version too old -- some mutation types or features were added in later versions. Running them on an older server produces this error.
4. Incorrect ALTER syntax -- mixing DDL ALTER commands (like ADD COLUMN) with mutation commands in an unsupported way.
5. Attempting unsupported mutation operations -- some operations that look like mutations are not supported (e.g., ALTER TABLE ... UPSERT).

Troubleshooting and Resolution Steps

1. Verify the mutation syntax. ClickHouse mutations use ALTER TABLE, not standalone UPDATE/DELETE:

   -- Correct ClickHouse mutation syntax
   ALTER TABLE my_table UPDATE column = value WHERE condition;
   ALTER TABLE my_table DELETE WHERE condition;
   
   -- Wrong: standard SQL syntax
   UPDATE my_table SET column = value WHERE condition;
   DELETE FROM my_table WHERE condition;
   

2. Check for typos in the command:

   -- Correct
   ALTER TABLE my_table UPDATE status = 'inactive' WHERE last_seen < '2024-01-01';
   
   -- Wrong: misspelled
   ALTER TABLE my_table UPDAE status = 'inactive' WHERE last_seen < '2024-01-01';
   

3. Verify your ClickHouse version supports the mutation type:

   SELECT version();
   

   Mutations were introduced in ClickHouse 18.12.14. Lightweight deletes (DELETE FROM) were added in version 22.8.

4. Use the correct syntax for lightweight deletes (if available):

   -- Lightweight delete (ClickHouse 22.8+)
   DELETE FROM my_table WHERE condition;
   
   -- Traditional mutation delete
   ALTER TABLE my_table DELETE WHERE condition;
   

5. Do not combine mutation commands with DDL commands in a single ALTER:

   -- Wrong: mixing DDL and mutation
   ALTER TABLE my_table ADD COLUMN new_col String, UPDATE old_col = 'x' WHERE 1;
   
   -- Correct: separate statements
   ALTER TABLE my_table ADD COLUMN new_col String;
   ALTER TABLE my_table UPDATE old_col = 'x' WHERE 1;
   

Best Practices

• Use ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE for data mutations in ClickHouse. Do not expect standard SQL DML syntax to work.
• Check your ClickHouse version before using newer mutation features like lightweight deletes.
• Keep mutation commands simple and separate from schema DDL operations.
• Review the ClickHouse changelog when upgrading to understand newly supported mutation types.

Frequently Asked Questions

Q: Does ClickHouse support standard SQL UPDATE and DELETE?
A: ClickHouse supports DELETE FROM table WHERE ... as a lightweight delete starting from version 22.8. Standard UPDATE ... SET is not supported; you must use ALTER TABLE ... UPDATE ... WHERE .... These operations are asynchronous mutations that rewrite data parts.

Q: What is the difference between ALTER DELETE and DELETE FROM?
A: ALTER TABLE ... DELETE WHERE is a traditional mutation that rewrites parts asynchronously. DELETE FROM (lightweight delete) marks rows as deleted without immediately rewriting parts, making it faster but requiring the allow_experimental_lightweight_delete setting in some versions.

Q: Can I combine multiple UPDATE commands in one ALTER?
A: Yes, you can combine multiple mutations in a single ALTER statement: ALTER TABLE t UPDATE a = 1 WHERE x, UPDATE b = 2 WHERE y;. All mutations in the statement are processed together.

Q: Are mutations transactional?
A: No. Mutations are applied asynchronously, part by part. There is no rollback mechanism. If a mutation fails midway, some parts will have the mutation applied and others will not. You can monitor progress in system.mutations.