Clickhouse: DB::Exception: Existing table metadata in ZooKeeper differs in TTL

[erste]

Describe the bug
Performed the update from version 20.1.2.4 to 20.3.5.21, after restart clickhouse does not start.
Tried to upgrade to 20.1.6.30 or 20.1.9.54 versions, clickhouse also does not start but errors changed.

How to reproduce

• Version: 20.1.2.4
• OS Version: Ubuntu 18.04.1 LTS
• CREATE TABLE statements for all tables involved

CREATE DATABASE database ENGINE = Ordinary;
CREATE TABLE database.table_rpl ('mdate' Date) ENGINE = ReplicatedMergeTree('/clickhouse/tables/database/{shard}/table_rpl', '{replica}') PARTITION BY mdate ORDER BY mdate TTL mdate + toIntervalMonth(6) TO VOLUME 'old_data_volume' SETTINGS storage_policy = 'main_policy', index_granularity = 8192;

• Update according to documentation: https://clickhouse.tech/docs/en/operations/update/#clickhouse-update

Expected behavior
Clickhouse updated and restarted.

Error message and/or stacktrace
After update from 20.1.2.4 to 20.3.5.21:

2020.04.10 16:49:40.116410 [ 6785 ] {} <Error> Application: DB::Exception: Existing table metadata in ZooKeeper differs in TTL. Stored in ZooKeeper: , local: mdate + toIntervalMonth(6) TO VOLUME 'old_data_volume': Cannot attach table `database`.`table_rpl` from metadata file /var/lib/clickhouse/metadata/database/table_rpl.sql from query ATTACH TABLE table_rpl (`mdate` Date) ENGINE = ReplicatedMergeTree('/clickhouse/tables/database/{shard}/table_rpl', '{replica}') PARTITION BY mdate ORDER BY mdate TTL mdate + toIntervalMonth(6) TO VOLUME 'old_data_volume' SETTINGS storage_policy = 'main_policy', index_granularity = 8192

And from 20.1.2.4 to 20.1.6.30 or 20.1.9.54:

2020.04.10 16:46:04.323697 [ 1 ] {} <Error> Application: Caught exception while loading metadata: Code: 62, e.displayText() = DB::Exception: Empty query, Stack trace (when copying this message, always include the lines below):
2020.04.10 16:46:05.238882 [ 1 ] {} <Error> Application: DB::Exception: Empty query

Full logs: https://gist.github.com/erste/75c06f232225e03ae6fe29e1ba796381

Additional context
Clickhouse cluster configuration:

    <remote_servers incl="clickhouse_remote_servers" >
        <production>
            <shard>
                <weight>1</weight>
                <internal_replication>true</internal_replication>
                <replica>
                    <host>10.1.200.1</host>
                    <port>9000</port>
                </replica>
            </shard>
            <shard>
                <weight>1</weight>
                <internal_replication>true</internal_replication>
                <replica>
                    <host>10.1.200.2</host>
                    <port>9000</port>
                </replica>
            </shard>
            <shard>
                <weight>1</weight>
                <internal_replication>true</internal_replication>
                <replica>
                    <host>10.1.200.3</host>
                    <port>9000</port>
                </replica>
            </shard>
        </production>
    </remote_servers>

    <zookeeper>
      <node index="1">
        <host>10.1.200.4</host>
        <port>2181</port>
      </node>
    </zookeeper>

    <macros>
        <replica>clickhouse1</replica>
        <shard>1</shard>
    </macros>

Zookeeper configuration:

tickTime=2000
initLimit=30000
syncLimit=10
dataDir=/var/lib/zookeeper
clientPort=2181
server.1=10.1.200.4:2888:3888
preAllocSize=131072
snapCount=3000000
leaderServes=yes
maxClientCnxns=2000
maxSessionTimeout=60000000
autopurge.snapRetainCount=10
autopurge.purgeInterval=1
standaloneEnabled=false

Zookeeper metadata:

[zk: localhost:2181(CONNECTED) 16] get /clickhouse/tables/database/1/table_rpl/replicas/clickhouse1/metadata
metadata format version: 1
date column:
sampling expression:
index granularity: 8192
mode: 0
sign column:
primary key: mdate
data format version: 1
partition key: mdate
move ttl: mdate + toIntervalMonth(6) TO VOLUME 'old_data_volume'
granularity bytes: 10485760

[Zer0Divis0r]

@filimonov , could you please give a brief explanation of this issue? I am having a similar problem, wondering if this is somehow related.

In my case I have performed ALTER TABLE MODIFY TTL on a replica. For reasons not worth mentioning, I made DROP TABLE on that replica, and then made CRATE TABLE again to have it pulled from other replicas. But no matter what I specify in CREATE TABLE's TTL clause, it says:

Existing table metadata in ZooKeeper differs in TTL. Stored in ZooKeeper: reporting_date + toIntervalMonth(3) TO VOLUME 'cold', local: .
...as if I specified and empty value in my CREATE TABLE query. CH version is 20.3.8.53.

[filimonov]

I hope @excitoon can do that.

[shuttie]

Experienced the same issue while upgrading from 20.1.4.14. We also did ALTER TABLE MODIFY TTL, but never dropped and recreated tables afterwards.

[oik741]

Faced the same problem after upgrade from 20.1 to 20.3.10.75

[PhantomPhreak]

v20.5, also have thin problem after running

alter table <table> MODIFY TTL <column> + interval 6 month;

[PhantomPhreak]

I found a root cause of the problem:
in my example

alter table <table> MODIFY TTL <column> + interval 6 month;

table <table> have been created using old (deprecated) syntax, when TTL setting supported when using new syntax only.
I've re-created that table using new syntax with TTL and it works.

Currently, this bug causing unability of clickhouse-server to start up, which is quite bad (can be pretty surprising) and require manual hacks.
This can be fixed by supporting TTL expressions for tables, created in the old format, or explicitly forbidding to add TTL expressions for them.

[Zer0Divis0r]

@PhantomPhreak , that's a great finding! when you say you have recreated the table, how exactly was that done?
Let's say I want to "convert" the table and preserve the data, will that be possible without having to do INSERT FROM SELECT?

[PhantomPhreak]

@Zer0Divis0r AFAIK, it’s impossible without re-inserting a data. So, i just created a new one, using new syntax, and then copied the table’s content.

[oik741]

@PhantomPhreak checked my table, according to show create table it was created using new syntax
I think that it's more related to _format_version_ of table
In my case format_version is 1, according to code latest is 4

[simPod]

@oik741 how do you find table format_version and how to eventually bump it?

[oik741]

@simPod sry mate, my assumption was wrong, just created new MergeTree table - format_version is 1, looks like other version dedicated for something else
btw answer on your question: in data folder (eg /var/lib/clickhouse/DB_NAME/TABLE_NAME/format_version.txt) or in ZK(example in issue description)