Materialized views with inner tables fail to propagate to new nodes

[johnny]

We have materialized views created with the shorthand

CREATE MATERIALIZED VIEW db.m1_local
            ON CLUSTER '{cluster}'
            ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/{cluster}/tables/{shard}/${database}/{table}/{uuid}', '{replica}' )
                PARTITION BY toYYYYMMDD(time_ms)
                ORDER BY (device_id, time_ms)
            POPULATE
AS
SELECT
....

This creates an implicit inner table. The syncing mechanism first creates the inner table.
Then it propagates the materialized view which again tries to create an inner table. The inner table already exists and the materialized view fails to propagate.

E0811 11:49:54.001881       1 connection.go:200] Exec():FAILED Exec(http://***:***@clickhouse.svc.cluster.local:8123/) clickhouse: Code: 57. DB::Exception: Table db.`.inner_id.480893b7-4fbb-4d9d-9de3-479f9b74379e` already exists. (TABLE_ALREADY_EXISTS) (version 22.7.3.5 (official build))
 for SQL: CREATE MATERIALIZED VIEW db.m1_local UUID '480893b7-4fbb-4d9d-9de3-479f9b74379e' TO INNER UUID '598dc070-8df2-4ac9-8046-1ed31cfe08e7'  ....

We use operator version 0.19.0

[Slach]

@sunsingerus i think we could try to use ATTACH MATERIALIZED VIEW instead of CREATE MATERIALIZED VIEW ...

[alex-zaitsev]

This is weird, because operator filters out '.inner.' tables exactly for this reason. Maybe this is something new specific to 22.7?

@johnny , do you have Distributed table on top of inner table by any chance?

[johnny]

We have Distributed tables on top of some of the materialized views directly and we have distributed tables on top of views on top of materialized views in the other cases.
We don't define Distributed tables directly on the inner tables.

We are running 22.7.3.5

Edit:

We noticed that the operator creates the inner table directly, since the first attempt to create the tables fails when the DNS isn't setup, yet:

E0811 11:49:45.422970       1 connection.go:192] Exec():FAILED connect(http://***:***@clickhouse-0-1.clickhouse.svc.cluster.local:8123/) for SQL: CREATE TABLE IF NOT EXISTS db.`.inner_id.0e992972-3986-4259-aebf-9398b8274af5` UUID 'a3e5f961-37c2-412c-8b8c-abcf354f26c3' ...
E0811 11:49:45.424705       1 connection.go:105] connect():FAILED Ping(http://***:***@clickhouse-0-1.clickhouse.svc.cluster.local:8123/). Err: dial tcp: lookup clickhouse-0-1.clickhouse.svc.cluster.local on 169.254.20.10:53: no such host

[Mit0x2]

Maybe this is something new specific to 22.7?

@alex-zaitsev tried that, did not work with any 22 version either. Even tried 21.9, did not work, unfortunately.

Edit: Also newer versions than 22.7 did not work

[alex-zaitsev]

@Mit0x2 , thanks, your log record helped to detect the problem. MaterializedViews are stored differently in Atomic database, we did not have a test for that.

We will release a fix in 0.19.1

[Mit0x2]

While trying out different Versions of the clickhouse server with the clickhouse operator, we noticed another issue in sort of the same context.

When adding a second replica we get the following issue for "normal" tables. I exchanged the actual DB and Table names with placeholders like DATABASE_NAME.

E0819 09:50:51.497250       1 connection.go:200] Exec():FAILED Exec(http://***:***@clickhouse-0-1.svc.cluster.local:8123/) clickhouse: Code: 62. DB::Exception: Macro 'uuid' and empty arguments of ReplicatedMergeTree are supported only for ON CLUSTER queries with Atomic database engine. (SYNTAX_ERROR) (version 22.7.3.5 (official build))
 for SQL: CREATE TABLE IF NOT EXISTS TABLE_NAME UUID 'c4aad9d7-83b4-4d92-b6df-9601c843bf27' (...) ENGINE = ReplicatedMergeTree('/clickhouse/{cluster}/tables/{shard}/DATABASE_NAME/TABLE_NAME/{uuid}', '{replica}') PARTITION BY tuple() ORDER BY FIELD_NAME SETTINGS index_granularity = 8192

The scenario is, adding an additional replica to an already existing cluster. So replication of the tables seems to be broken?
Issue definitively present in 22.7 and following versions.

[alex-zaitsev]

@Mit0x2 , 22.7+ has deprecated Ordinary databases, so all databases are Atomic now. It is weird you are seeing this error though, it is supposed to work in 0.19.0. We will double test on 22.7

[alex-zaitsev]

Fixed in 0.19.1