Cannot execute replicated DDL query on leader

Clickhouse 执行DDL删一个字段的时候报错：Cannot execute replicated DDL query on leader

A01-R04-I133-136-9W54352.JD.LOCAL :) alter table w_local  on cluster shwx_test_cluster drop column pit;

ALTER TABLE w_local ON CLUSTER shwx_test_cluster
    DROP COLUMN pit


┌─host───────────┬─port─┬─status─┬─error─────────────────────────────────────────┬─num_hosts_remaining─┬─num_hosts_active─┐
│ 172.28.133.131 │ 9000 │     48 │ Cannot execute replicated DDL query on leader │                   3 │                3 │
└────────────────┴──────┴────────┴───────────────────────────────────────────────┴─────────────────────┴──────────────────┘
┌─host───────────┬─port─┬─status─┬─error─────────────────────────────────────────┬─num_hosts_remaining─┬─num_hosts_active─┐
│ 172.28.133.136 │ 9000 │     48 │ Cannot execute replicated DDL query on leader │                   2 │                2 │
└────────────────┴──────┴────────┴───────────────────────────────────────────────┴─────────────────────┴──────────────────┘
┌─host──────────┬─port─┬─status─┬─error─────────────────────────────────────────┬─num_hosts_remaining─┬─num_hosts_active─┐
│ 172.28.114.85 │ 9000 │     48 │ Cannot execute replicated DDL query on leader │                   1 │                1 │
└───────────────┴──────┴────────┴───────────────────────────────────────────────┴─────────────────────┴──────────────────┘
┌─host───────────┬─port─┬─status─┬─error─────────────────────────────────────────┬─num_hosts_remaining─┬─num_hosts_active─┐
│ 172.20.130.107 │ 9000 │     48 │ Cannot execute replicated DDL query on leader │                   0 │                0 │
└────────────────┴──────┴────────┴───────────────────────────────────────────────┴─────────────────────┴──────────────────┘
↘ Progress: 3.00 rows, 308.00 B (0.24 rows/s., 24.72 B/s.) █████████████████████████████████████████████████████████████████████████████████                            74%[A01-R04-I133-136-9W54352.JD.LOCAL] 2020.11.02 14:12:15.470005 [ 1343 ] {fd28dca1-91a0-4a23-ba80-0ab05c731c9e} <Error> executeQuery: Code: 48, e.displayText() = DB::Exception: There was an error on [172.28.133.131:9000]: Cannot execute replicated DDL query on leader (version 20.5.4.40) (from 127.0.0.1:39046) (in query: alter table w_local on cluster shwx_test_cluster drop column pit;), Stack trace (when copying this message, always include the lines below):

0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0xcc3083c in /usr/bin/clickhouse
1. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x5a54619 in /usr/bin/clickhouse
2. DB::DDLQueryStatusInputStream::readImpl() @ 0x9cb9f9a in /usr/bin/clickhouse
3. DB::IBlockInputStream::read() @ 0x9b854ed in /usr/bin/clickhouse
4. DB::AsynchronousBlockInputStream::calculate() @ 0x9b7d7b0 in /usr/bin/clickhouse
5. ? @ 0x9b7dbd4 in /usr/bin/clickhouse
6. ThreadPoolImpl<ThreadFromGlobalPool>::worker(std::__1::__list_iterator<ThreadFromGlobalPool, void*>) @ 0x5a5eb8d in /usr/bin/clickhouse
7. ThreadFromGlobalPool::ThreadFromGlobalPool<void ThreadPoolImpl<ThreadFromGlobalPool>::scheduleImpl<void>(std::__1::function<void ()>, int, std::__1::optional<unsigned long>)::'lambda1'()>(void&&, void ThreadPoolImpl<ThreadFromGlobalPool>::scheduleImpl<void>(std::__1::function<void ()>, int, std::__1::optional<unsigned long>)::'lambda1'()&&...)::'lambda'()::operator()() const @ 0x5a5f273 in /usr/bin/clickhouse
8. ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>) @ 0x5a5e12d in /usr/bin/clickhouse
9. ? @ 0x5a5c7af in /usr/bin/clickhouse
10. start_thread @ 0x7dd5 in /usr/lib64/libpthread-2.17.so
11. __clone @ 0xfdead in /usr/lib64/libc-2.17.so


Received exception from server (version 20.5.4):
Code: 48. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: There was an error on [172.28.133.131:9000]: Cannot execute replicated DDL query on leader. 

4 rows in set. Elapsed: 12.459 sec. 

error.log日志显示： 

3.131:9000]: Cannot execute replicated DDL query on leader (version 20.5.4.40) (from 127.0.0.1:39046) (in query: alter table w_local on cluster shwx_test_cluster drop column pit;), Stack trace (when copying this message, always include the lines below):
0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0xcc3083c in /usr/bin/clickhouse
1. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x5a54619 in /usr/bin/clickhouse
2. DB::DDLQueryStatusInputStream::readImpl() @ 0x9cb9f9a in /usr/bin/clickhouse
3. DB::IBlockInputStream::read() @ 0x9b854ed in /usr/bin/clickhouse
4. DB::AsynchronousBlockInputStream::calculate() @ 0x9b7d7b0 in /usr/bin/clickhouse
5. ? @ 0x9b7dbd4 in /usr/bin/clickhouse
6. ThreadPoolImpl<ThreadFromGlobalPool>::worker(std::__1::__list_iterator<ThreadFromGlobalPool, void*>) @ 0x5a5eb8d in /usr/bin/clickhouse
7. ThreadFromGlobalPool::ThreadFromGlobalPool<void ThreadPoolImpl<ThreadFromGlobalPool>::scheduleImpl<void>(std::__1::function<void ()>, int, std::__1::optional<unsigned long>)::'lambda1'()>(void&&, void ThreadPoolImpl<ThreadFromGlobalPool>::scheduleImpl<void>(std::__1::function<void ()>, int, std::__1::optional<unsigned long>)::'lambda1'()&&...)::'lambda'()::operator()() const @ 0x5a5f273 in /usr/bin/clickhouse
8. ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>) @ 0x5a5e12d in /usr/bin/clickhouse
9. ? @ 0x5a5c7af in /usr/bin/clickhouse
10. start_thread @ 0x7dd5 in /usr/lib64/libpthread-2.17.so
11. __clone @ 0xfdead in /usr/lib64/libc-2.17.so
 

没有看到其他更多有用的信息，最后发现是要DROP的字段pit，被定义在了ORDER BY里面了；

感觉CK的日志可读性不好，查了日志也不知道是啥原因，只能自己慢慢想

 

那么如果想要DROP一个ORDER BY中定义的COLUMN，应该如何操作呢？两种方法：

第一种方法， 可以重新以新的表结构建表；

第二种方法， 先修改ORDER BY 定义，再进行DROP COLUMN操作；

ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY ORDER BY new_expression