Problem with replicated and distributed tables. Error code 371

[Boris Granveaud]

Hello,

I have an error that I don't understand when using replicated and distributed tables. Here is my test case:

2 nodes configured like this:

<vscluster>
    <shard>
 <internal_replication>true</internal_replication>
 <replica>
 <host>node1</host>
 </replica>
 <replica>
 <host>node2</host>
 </replica>
 </shard>
</vscluster>

with macros:

node1:

 

<macros>
 <shard>s1</shard>
 <replica>node1</replica>
</macros>

node2:

<macros>
 <shard>s1</shard>
 <replica>node2</replica>
</macros>

This is what I execute on any of the nodes (I'm using ON CLUSTER because I want to support configurations with multiple shards):

CREATE DATABASE vs ON CLUSTER vscluster

CREATE TABLE IF NOT EXISTS vs.t1 ON CLUSTER vscluster (ts DATETIME,c1 VARCHAR) ENGINE=ReplicatedMergeTree('/clickhouse/tables/{shard}/t1','{replica}') PARTITION BY toStartOfYear(ts) ORDER BY tuple()

CREATE TABLE IF NOT EXISTS vs.t1_distrib ON CLUSTER vscluster (ts DATETIME,c1 VARCHAR) ENGINE=Distributed('vscluster','vs','t1')

ALTER TABLE vs.t1 ON CLUSTER vscluster ADD COLUMN c2 VARCHAR

ALTER TABLE vs.t1_distrib ON CLUSTER vscluster ADD COLUMN c2 VARCHAR

=> error

Code: 371, e.displayText() = DB::Exception: Table t1_distrib isn't replicated, but shard #1 is replicated according to its cluster definition, e.what() = DB::Exception.

Did I miss something on the way the replicated / distributed tables are supposed to work?

Thanks,

Boris.

[Boris Granveaud]

I forgot to give Clickhouse version:

Connected to ClickHouse server version 18.12.17 revision 54407.

Note that it works if I execute the last statement on each node without ON CLUSTER.

Boris.

[Denis Zhuravlev]

Technically it's a bug, because create and alter have different behavior.


internal_replication = true, so CH tries to execute alter only on the first replica but t1_distrib is not replicated, and you get this error.




When you execute "ALTER TABLE vs.t1 ON CLUSTER vscluster" CH checks that internal_replication = true and sends alter to only one replica and other replica performs this alter using table (replicated) engine.





Workarounds




1. drop / create distrib table each time, BTW you can use sharded table as columns definition


CREATE TABLE IF NOT EXISTS vs.t1_distrib ON CLUSTER vscluster as vs.t1 ENGINE=Distributed('vscluster','vs','t1')


2. create one more cluster which covers all nodes

<vscluster_all>
    <shard>

      <replica>
         <host>node1</host>
      </replica>

    </shard>

    <shard>  

      <replica>
         <host>node2</host>
      </replica>
    </shard>
</vscluster>

and execute

ALTER TABLE vs.t1_distrib ON CLUSTER vscluster_all ADD COLUMN c2 VARCHAR

[Boris Granveaud]

Workaround #1 is not very convenient because I don't want to have a select returning an error when we are between drop and create. BTW, the problem is the same with views that you can only drop/recreate, it would be nice to be able to add/drop a column.

Workaround #2 doesn't seem to work either:

Code: 371, e.displayText() = DB::Exception: Table t1_distrib  is replicated, but shard #2 isn't replicated according to its cluster definition. Possibly <internal_replication>true</internal_replication> is forgotten in the cluster config.

B.

[Boris Granveaud]

sorry, there was a problem in my configuration, #2 is working!

B.