Error running DDL Query - when trying to create a new ReplicatedReplacingMergeTree table

[Selfeer]

When creating tables with statements from https://github.com/Altinity/clickhouse-sink-connector/blob/develop/sink-connector-lightweight/sql/init_mysql.sql file in MySQL, the tables with the data seem to be correctly replicated in ClickHouse but when trying to create a new table in MySQL after that, for example,

CREATE TABLE test (id INT);

The new table is not replicated in ClickHouse anymore and we get the following error in sink-connector logs:

clickhouse-sink-connector-lt_1  | 70446 2024-03-18 16:35:20.614 [Sink connector Debezium Event Thread] INFO com.altinity.clickhouse.debezium.embedded.cdc.DebeziumChangeEventCapture  - Executed Source DB DDL: CREATE TABLE `test` (
clickhouse-sink-connector-lt_1  |   `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
clickhouse-sink-connector-lt_1  |   `id` int DEFAULT NULL,
clickhouse-sink-connector-lt_1  |   PRIMARY KEY (`my_row_id`)
clickhouse-sink-connector-lt_1  | ) Snapshot:false
clickhouse-sink-connector-lt_1  | 70446 2024-03-18 16:35:20.614 [Sink connector Debezium Event Thread] INFO com.altinity.clickhouse.debezium.embedded.cdc.DebeziumChangeEventCapture  - ClickHouse DDL: CREATE TABLE `test`(`my_row_id` Int64 NOT NULL ,`id` Nullable(Int32),`_version` UInt64,`is_deleted` UInt8)Engine=ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/', '{replica}', _version, is_deleted) ORDER BY (`my_row_id`)
clickhouse-sink-connector-lt_1  | 70455 2024-03-18 16:35:20.623 [Sink connector Debezium Event Thread] ERROR com.altinity.clickhouse.debezium.embedded.cdc.DebeziumChangeEventCapture  - Error running DDL Query: java.sql.SQLException: Code: 47. DB::Exception: Missing columns: 'customerNumber' while processing query: 'customerNumber', required columns: 'customerNumber' 'customerNumber'. (UNKNOWN_IDENTIFIER) (version 23.8.5.16 (official build))
clickhouse-sink-connector-lt_1  | , server ClickHouseNode [uri=http://clickhouse:8123/test, options={custom_settings=allow_experimental_object_type=1,insert_allow_materialized_columns=1,client_name=Client_1}]@174802180

Here are the configurations:

auto.create.tables: "true"
auto.create.tables.replicated: "true"
enable.snapshot.ddl: "true"
snapshot.mode: "initial"

sink-connector build: altinityinfra/clickhouse-sink-connector:497-15f5a0397ecf52f9b5e381eb6097b7da03c34ec7-lt

[Selfeer]

Related to #473

[subkanthi]

The query that is executed does not have the type column, for some reason the server expects Type column.

73176 [Sink connector Debezium Event Thread] INFO com.altinity.clickhouse.debezium.embedded.cdc.DebeziumChangeEventCapture  - Executed Source DB DDL: create table test_mysql_table (id int, name varchar(255)) Snapshot:false
73176 [Sink connector Debezium Event Thread] INFO  com.altinity.clickhouse.debezium.embedded.cdc.DebeziumChangeEventCapture  - ClickHouse DDL: CREATE TABLE test_mysql_table(id Nullable(Int32),name Nullable(String),`_sign` Int8,`_version` UInt64)Engine=ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/', '{replica}', _version) ORDER BY tuple()
7

[subkanthi]

This looks like its related to not adding on CLUSTER '{cluster}' in the table schema.
related to #540