clickhouse Mutations删除操作报错及解决方案

1. clickhouse 执行删除语句后查询数据仍旧存在，等待数分钟后依然存在

2. 查询 system.Mutations 表

sql select * from system.mutations whereis_done = 0;发现任务在突变表中状态一直是未执行

3. 查阅 /var/log/clickhouse-server/clickhouse-server.err.log 日志发现如下报错

2021.04.09 10:59:12.971073 [ 6363 ] {} <Error> void DB::BackgroundProcessingPool::workLoopFunc(): Code: 393, e.displayText() = DB::Exception: There is no query, Stack trace (when copying this message, always include the lines below):

0. DB::Context::getQueryContext() const @ 0xd87f6ae in /usr/bin/clickhouse
1. DB::InterpreterSelectWithUnionQuery::getSampleBlock(std::__1::shared_ptr<DB::IAST> const&, DB::Context const&) @ 0xdd71271 in /usr/bin/clickhouse
2. ? @ 0xded66ce in /usr/bin/clickhouse
3. DB::getDatabaseAndTablesWithColumns(std::__1::vector<DB::ASTTableExpression const*, std::__1::allocator<DB::ASTTableExpression const*> > const&, DB::Context const&) @ 0xded77c0 in /usr/bin/clickhouse
4. DB::JoinedTables::resolveTables() @ 0xdda3ad4 in /usr/bin/clickhouse
5. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, DB::Context const&, std::__1::shared_ptr<DB::IBlockInputStream> const&, std::__1::optional<DB::Pipe>, std::__1::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&) @ 0xdbc5b34 in /usr/bin/clickhouse
6. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, DB::Context const&, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0xdbc504d in /usr/bin/clickhouse
7. DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::__1::shared_ptr<DB::IAST> const&, DB::Context const&, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0xdd6fa64 in /usr/bin/clickhouse
8. DB::interpretSubquery(std::__1::shared_ptr<DB::IAST> const&, DB::Context const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&, DB::SelectQueryOptions const&) @ 0xdedd5cb in /usr/bin/clickhouse
9. DB::ActionsMatcher::makeSet(DB::ASTFunction const&, DB::ActionsMatcher::Data&, bool) @ 0xdc41089 in /usr/bin/clickhouse
10. DB::ActionsMatcher::visit(DB::ASTFunction const&, std::__1::shared_ptr<DB::IAST> const&, DB::ActionsMatcher::Data&) @ 0xdc3a7c8 in /usr/bin/clickhouse
11. DB::ActionsMatcher::visit(DB::ASTFunction const&, std::__1::shared_ptr<DB::IAST> const&, DB::ActionsMatcher::Data&) @ 0xdc3b96a in /usr/bin/clickhouse
12. DB::InDepthNodeVisitor<DB::ActionsMatcher, true, std::__1::shared_ptr<DB::IAST> const>::visit(std::__1::shared_ptr<DB::IAST> const&) @ 0xdc18b8a in /usr/bin/clickhouse
13. DB::ExpressionAnalyzer::getRootActions(std::__1::shared_ptr<DB::IAST> const&, bool, std::__1::shared_ptr<DB::ActionsDAG>&, bool) @ 0xdc18916 in /usr/bin/clickhouse
14. DB::SelectQueryExpressionAnalyzer::appendPrewhere(DB::ExpressionActionsChain&, bool, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0xdc1ba39 in /usr/bin/clickhouse
15. DB::ExpressionAnalysisResult::ExpressionAnalysisResult(DB::SelectQueryExpressionAnalyzer&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&, bool, bool, bool, std::__1::shared_ptr<DB::FilterInfo> const&, DB::Block const&) @ 0xdc232a0 in /usr/bin/clickhouse
16. DB::InterpreterSelectQuery::getSampleBlockImpl() @ 0xdbd0164 in /usr/bin/clickhouse
17. ? @ 0xdbcb025 in /usr/bin/clickhouse
18. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, DB::Context const&, std::__1::shared_ptr<DB::IBlockInputStream> const&, std::__1::optional<DB::Pipe>, std::__1::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&) @ 0xdbc662b in /usr/bin/clickhouse
19. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::__1::shared_ptr<DB::IAST> const&, DB::Context const&, std::__1::shared_ptr<DB::IStorage> const&, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&, DB::SelectQueryOptions const&) @ 0xdbc8802 in /usr/bin/clickhouse
20. DB::MutationsInterpreter::MutationsInterpreter(std::__1::shared_ptr<DB::IStorage>, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&, DB::MutationCommands, DB::Context const&, bool) @ 0xddbf6eb in /usr/bin/clickhouse
21. DB::StorageMergeTree::tryMutatePart() @ 0xe0d8564 in /usr/bin/clickhouse
22. DB::StorageMergeTree::mergeMutateTask() @ 0xe0da733 in /usr/bin/clickhouse
23. DB::BackgroundProcessingPool::workLoopFunc() @ 0xe294b53 in /usr/bin/clickhouse
24. ? @ 0xe295683 in /usr/bin/clickhouse
25. ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>) @ 0x7b8963d in /usr/bin/clickhouse
26. ? @ 0x7b8d153 in /usr/bin/clickhouse
27. start_thread @ 0x7e65 in /usr/lib64/libpthread-2.17.so
28. __clone @ 0xfe88d in /usr/lib64/libc-2.17.so
 (version 20.10.3.30 (official build))

观察日志发现clickhouse认为这个语句不是一个查询，应该是内部处理出现问题，考虑终止Mutations操作。

百度谷歌查询后未找到相应解决办法，计划查阅官方文档。

官方文档对于Mutations操作有如下描述

page 98 一旦mutation完成提交，就不能回退了，但是如果因为某种原因操作被卡住了，可以通过 KILL MUTATION操作来取消它的执行。

查找官方文档中 KILL MUTATION语法

KILL MUTATION KILL MUTATION [ON CLUSTER cluster] WHERE <where expression to SELECT FROM system.mutations query> [TEST] [FORMAT format] 
尝试取消和删除 突变 当前正在执行。 要取消的突变选自 system.mutations 表使用由指定的过滤器 WHERE 《公约》条款 KILL 查询。 测试查询 (TEST）仅检查用户的权限并显示要停止的查询列表。 
例:-- Cancel and remove all mutations of the single table: KILL MUTATION WHERE database = 'default' AND table = 'table' -- Cancel the specific mutation: KILL MUTATION WHERE database = 'default' AND table = 'table' AND mutation_id = 'mutation_3.txt' 
The query is useful when a mutation is stuck and cannot finish (e.g. if some function in the mutation query throws an exception when applied to the data contained in the table). 
已经由突变所做的更改不会回滚

终止突变

使用如下语句终止了突变

KILL MUTATION WHERE database = 'default' AND table = 'table_name';

再次查询 system.Mutations 表发现已提交的突变已经消失。
再次提交突变成功运行。

后记

clickhouse官方文档中对于mutations操作的建议性描述如下：
已有的表已经支持mutations操作（不需要转换）。但是在首次对表进行mutation操作以后，它的元数据格式变得和和之前的版本不兼容，并且不能回退到之前版本。

然后再给社区提个bug？

原文链接：https://blog.csdn.net/w4187402/article/details/115617531