ClickHouse异常处理Metadata on replic

背景

给表添加字段的时候提示如下错误。

ALTER TABLE buffer_device ADD COLUMN IF NOT EXISTS `iot_id` String After `id`;

异常信息如下:

2021.08.26 15:28:39.460681 [ 27604 ] {7311abed-3e64-474e-926c-d9258a2d589e} <Error> executeQuery: Code: 517, e.displayText() = DB::Exception: Metadata on replica is not up to date with common metadata in Zookeeper. Cannot alter (version 20.8.6.6 (official build)) (from 172.30.104.193:33606) (in query: ALTER TABLE buffer_device ADD COLUMN IF NOT EXISTS `iot_id` String After `id`), 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) @ 0x18bbfa10 in /usr/bin/clickhouse

1. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0xe61532d in /usr/bin/clickhouse

2. ? @ 0x15e798b3 in /usr/bin/clickhouse

3. DB::InterpreterAlterQuery::execute() @ 0x15863edb in /usr/bin/clickhouse

4. ? @ 0x15bf2ce2 in /usr/bin/clickhouse

5. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, DB::Context&, std::__1::function<void (std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&)>) @ 0x15bf4a69 in /usr/bin/clickhouse

6. DB::HTTPHandler::processQuery(DB::Context&, Poco::Net::HTTPServerRequest&, HTMLForm&, Poco::Net::HTTPServerResponse&, DB::HTTPHandler::Output&) @ 0x16232b99 in /usr/bin/clickhouse

7. DB::HTTPHandler::handleRequest(Poco::Net::HTTPServerRequest&, Poco::Net::HTTPServerResponse&) @ 0x1623664b in /usr/bin/clickhouse

8. Poco::Net::HTTPServerConnection::run() @ 0x18a9f663 in /usr/bin/clickhouse

9. Poco::Net::TCPServerConnection::start() @ 0x18add87b in /usr/bin/clickhouse

10. Poco::Net::TCPServerDispatcher::run() @ 0x18addd0b in /usr/bin/clickhouse

11. Poco::PooledThread::run() @ 0x18c5c7e6 in /usr/bin/clickhouse

12. Poco::ThreadImpl::runnableEntry(void*) @ 0x18c57be0 in /usr/bin/clickhouse

13. start_thread @ 0x7e25 in /usr/lib64/libpthread-2.17.so

14. __clone @ 0xf834d in /usr/lib64/libc-2.17.so

查看zookeeper元数据信息

登录zookeeper，查看数据版本信息：

stat /clickhouse/tables/01-01/buffer_device/metadata

stat /clickhouse/tables/01-01/buffer_device/replicas/cluster01-01-01/metadata

stat /clickhouse/tables/01-01/buffer_device/replicas/cluster01-01-02/metadata

发现副本的dataVersion版本与上级metadata版本不一致。导致无法加字段。尝试了重启clickhouse未解决。

问题解决

由于dataVersion版本zookeeper无法直接修改，随着数据变化自动增加的，可以尝试手动设置副本的metadata。

set /clickhouse/tables/01-01/buffer_device/replicas/cluster01-01-01/metadata

metadata format version: 1

date column:

sampling expression:

index granularity: 8192

mode: 5

sign column:

primary key: id, product_key, device_key

data format version: 1

partition key:

granularity bytes: 10485760

设置好后，就可以添加字段了。设置的时候我们发现可以随意设置个值，保证副本的dataVersion比上级的大即可。

set /clickhouse/tables/01-01/buffer_device/replicas/cluster01-01-02/metadata /