In clickhouse-server.err.log every second an error appears:
<Error> virtual bool DB::MutatePlainMergeTreeTask::executeStep(): Code: 47. DB::Exception: Missing columns: 'purchase_number' while processing query: 'SELECT purchase_number FROM rad_reports_api_release_2.purchase_notices_v1 WHERE region = 'Test'', required columns: 'purchase_number' 'region', maybe you meant: 'region': While processing (purchase_number IN ((SELECT purchase_number FROM rad_reports_api_release_2.purchase_notices_v1 WHERE region = 'Test' ) AS _subquery8297539)) OR (purchase_number IN ((SELECT purchase_number FROM rad_reports_api_release_2.purchase_notices_v1 WHERE region = 'Test') AS _subquery8297540)) OR (purchase_number IN ((SELECT registration_number FROM rad_reports_api_release_2.purchase_ notices_v1 WHERE region = 'Test') AS _subquery8297541) ).(UNKNOWN_IDENTIFIER)
However, in the purchase_notices_v1 table there is no 'purchase_number' field at all. Please tell me what is the reason and how to fix this problem?
Updating the clickhouse version did not help. Current version is 23.11.1.2711
The error message is because you have an unfinished mutation ALTER table ... UPDATE. And in this mutation, the query reference an non existing column purchase_number.
CREATE TABLE example_table
(
`Date` Int64,
`name` String
)
ENGINE = MergeTree()
ORDER BY Date;
-- Here nameccc is not existing.
ALTER TABLE example_table
UPDATE name = 'John Doe'
WHERE Date in (SELECT nameccc from example_table);
-- Since this query is executed, the error log of Missing column is repeated as you described.
The way to get rid of this is to find the mutation causing the issue, and then kill it.
select * from system.mutations
Row 2:
──────
database: default
table: example_table
mutation_id: mutation_3.txt
command: UPDATE name = 'John Doe' WHERE Date IN (SELECT nameccc FROM default.example_table)
create_time: 2024-03-12 20:35:49
block_numbers.partition_id: ['']
block_numbers.number: [3]
parts_to_do_names: ['all_1_1_0_2']
parts_to_do: 1
is_done: 0
latest_failed_part: all_1_1_0_2
latest_fail_time: 2024-03-12 20:41:37
latest_fail_reason: Code: 47. DB::Exception: Missing columns: 'nameccc' while processing query: 'SELECT nameccc FROM default.example_table', required columns: 'nameccc': While pro
cessing Date IN ((SELECT nameccc FROM default.example_table) AS _subquery1637). (UNKNOWN_IDENTIFIER) (version 23.11.1.1)
KILL MUTATION WHERE mutation_id = 'mutation_3.txt' ASYNC
Query id: 3e210be1-880d-4952-bce7-b9f52aefa08f
┌─kill_status─┬─database─┬─table─────────┬─mutation_id────┬─command────────────────────────────────────────────────────────────────────────────┐
│ waiting │ default │ example_table │ mutation_3.txt │ UPDATE name = 'John Doe' WHERE Date IN (SELECT nameccc FROM default.example_table) │
└─────────────┴──────────┴───────────────┴────────────────┴────────────────────────────────────────────────────────────────────────────────────┘
1 row in set. Elapsed: 0.019 sec.