`_sign` is used although `replacingmergetree.delete.column = is_deleted`

[congvmit]

Currently, I want to use is_deleted column for Replacing Enngine Table in clickhouse. So , I set replacingmergetree.delete.column = is_deleted in config.yaml file. Then I start the system.

Below is the log.
I notice that _sign column is created although I explicitly used is_deleted in the configuration file. The system I used is built from here image: registry.gitlab.com/altinity-public/container-images/clickhouse_debezium_embedded:latest

Clickhouse: v23.4.5.22

java.sql.SQLException: Code: 390. DB::Exception: Table `carts` doesn't exist. (CANNOT_GET_CREATE_TABLE_QUERY) (version 23.4.5.22 (official build))
, server ClickHouseNode [uri=http://localhost:8123/test, options={custom_settings=allow_experimental_object_type=1,client_name=Agent_1}]@-1243253706
        at com.clickhouse.jdbc.SqlExceptionUtils.handle(SqlExceptionUtils.java:85)
        at com.clickhouse.jdbc.SqlExceptionUtils.create(SqlExceptionUtils.java:31)
        at com.clickhouse.jdbc.SqlExceptionUtils.handle(SqlExceptionUtils.java:90)
        at com.clickhouse.jdbc.internal.ClickHouseStatementImpl.getLastResponse(ClickHouseStatementImpl.java:120)
        at com.clickhouse.jdbc.internal.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:474)
        at com.altinity.clickhouse.sink.connector.db.DBMetadata.getTableEngineUsingShowTable(DBMetadata.java:107)
        at com.altinity.clickhouse.sink.connector.db.DBMetadata.getTableEngine(DBMetadata.java:53)
        at com.altinity.clickhouse.sink.connector.db.DbWriter.<init>(DbWriter.java:102)
        at com.altinity.clickhouse.sink.connector.executor.ClickHouseBatchRunnable.getDbWriterForTable(ClickHouseBatchRunnable.java:133)
        at com.altinity.clickhouse.sink.connector.executor.ClickHouseBatchRunnable.processRecordsByTopic(ClickHouseBatchRunnable.java:148)
        at com.altinity.clickhouse.sink.connector.executor.ClickHouseBatchRunnable.run(ClickHouseBatchRunnable.java:101)
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
        at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305)
        at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: java.io.IOException: Code: 390. DB::Exception: Table `carts` doesn't exist. (CANNOT_GET_CREATE_TABLE_QUERY) (version 23.4.5.22 (official build))

        at com.clickhouse.client.http.HttpUrlConnectionImpl.checkResponse(HttpUrlConnectionImpl.java:184)
        at com.clickhouse.client.http.HttpUrlConnectionImpl.post(HttpUrlConnectionImpl.java:227)
        at com.clickhouse.client.http.ClickHouseHttpClient.send(ClickHouseHttpClient.java:124)
        at com.clickhouse.client.AbstractClient.execute(AbstractClient.java:280)
        at com.clickhouse.client.ClickHouseClientBuilder$Agent.sendOnce(ClickHouseClientBuilder.java:282)
        at com.clickhouse.client.ClickHouseClientBuilder$Agent.send(ClickHouseClientBuilder.java:294)
        at com.clickhouse.client.ClickHouseClientBuilder$Agent.execute(ClickHouseClientBuilder.java:349)
        at com.clickhouse.client.ClickHouseClient.executeAndWait(ClickHouseClient.java:1056)
        at com.clickhouse.client.ClickHouseRequest.executeAndWait(ClickHouseRequest.java:2154)
        at com.clickhouse.jdbc.internal.ClickHouseStatementImpl.getLastResponse(ClickHouseStatementImpl.java:118)
        ... 13 more
10220 2023-10-23 09:33:12.104 [pool-1-thread-10] INFO com.altinity.clickhouse.sink.connector.db.DbWriter  - **** Task(0), AUTO CREATE TABLE (carts) *** 
10223 2023-10-23 09:33:12.107 [pool-1-thread-10] INFO com.altinity.clickhouse.sink.connector.db.operations.ClickHouseAutoCreateTable  - **** AUTO CREATE TABLE CREATE TABLE carts(`id` Int32 NOT NULL,`user_id` Int32 NULL,`first_name` String NULL,`last_name` String NULL,`phone` String NULL,`contact_phone` String NULL,`address_id` Int32 NULL,`province_id` Int32 NULL,`district_id` Int32 NULL,`address` String NULL,`full_address` String NULL,`note` String NULL,`payment_method` Int32 NULL,`ip` String NULL,`card_processor` String NULL,`discount_code` String NULL,`is_freeship` Int16 NULL,`is_gift` Int16 NULL,`gift_message` String NULL,`discount_price` Int32 NULL,`promotions_price` Int32 NULL,`shipping_price` Int32 NULL,`services_price` Int32 NULL,`gift_price` Int32 NULL,`subtotal_price` Int32 NULL,`balance_used` Int32 NULL,`deposit_used` Int32 NULL,`reward_used` Int32 NULL,`cashback_used` Int32 NULL,`cashback_rebate` Int32 NULL,`total_price` Int32 NULL,`number` String NULL,`created_at` DateTime64(3) NOT NULL,`updated_at` DateTime64(3) NOT NULL,`guest` Int16 NULL,`status` String NULL,`subtotal_coins` Int32 NULL,`total_coins` Int32 NULL,`coins_used` Int32 NULL,`shipping_package` String NULL,`onepay_ref` String NULL,`coin_bonus` Int32 NULL,`coin_bonus_ratio` Float32 NULL,`platform` String NULL,`mobile_referral_code` String NULL,`referral_code` String NULL,`applied_scheme_id` Int32 NULL,`ward_id` Int32 NULL,`payment_platform` String NULL,`warehouse_id` Int32 NULL,`user_device_id` Int32 NULL,`invoice_requested` Int16 NULL,`accompanies_count` Int32 NULL,`apply_balance` Int16 NOT NULL,`applicable_balance` Int32 NULL,`cashback_scheme_id` Int32 NULL,`_sign` Int8,`_version` UInt64) ENGINE = ReplacingMergeTree(_version) PRIMARY KEY(id) ORDER BY(id)

[aadant]

indeed this config should create table without the _sign and with the is_deleted.

replacingmergetree.delete.column: "is_deleted"

@subkanthi : I noticed that the version comparison is not great : as 23.10.1 < 23.2.1. Any explanation for the above behavior ?

@congvmit : this looks like a bug. A workaround is to create the schema / data using the clickhouse_loader.py that supports all those options. You need to dump the schema using mysqlsh see this discussion for example https://github.com/Altinity/clickhouse-sink-connector/discussions/180

[subkanthi]

@aadant The replacingmergetree version with is_deleted column is defined as REPLACING_MERGE_TREE_VERSION_WITH_IS_DELETED = "23.2";

So any version that is greater than 23.2 is expected to have the is_deleted column.

Not sure where you noticed the bug with 23.10.1 < 23.2.1 The following test checks if 23.10.1 > 23.2

    @ParameterizedTest
    @CsvSource({
            "23.2, true",
            "23.1, false",
            "23.2.1, true",
            "23.1.1, false",
            "23.0.1, false",
            "23.3, true",
            "33.1, true",
            "23.10.1, true"
    })
    public void testIsRMTVersionSupported(String clickhouseVersion, boolean result) throws SQLException {
        Assert.assertTrue(new DBMetadata().checkIfNewReplacingMergeTree(clickhouseVersion) == result);
    }