Type mismatch error during MySQL to ClickHouse replication: DataError: Invalid None value in non-Nullable column (email)

[VladimirDVV]

Hi team,
I've encountered a replication issue when syncing data from MySQL (Percona 8.0.43) to ClickHouse 25.8.
The Issue:
The source table in MySQL has a Nullable column, while the destination table in ClickHouse uses a non-Nullable String type with a DEFAULT ''. This is necessary because we use ngrambf_v1 Bloom Filter indexes in ClickHouse, which do not support Nullable types.
When the sync process encounters a NULL value in MySQL, the clickhouse_connect driver (or the integration logic) fails with the following error:
clickhouse_connect.driver.exceptions.DataError: Invalid None value in non-Nullable column, column name: email
Source (MySQL):

CREATE TABLE table (
        ......
	`email` varchar(255) DEFAULT NULL
) 
ENGINE=InnoDB

Destination (ClickHouse):

CREATE TABLE table
(
 ...
 `email` String DEFAULT '',
 `_version`  UInt64,
 INDEX idx_email email TYPE ngrambf_v1(2048, 4, 1, 0) GRANULARITY 2
)
ENGINE = ReplacingMergeTree(_version)
        ORDER BY (id)
        PRIMARY KEY (id)
        SETTINGS index_granularity = 8192, allow_nullable_key = 1

mysql_ch_replicator_log.txt

[bakwc]

We could add a global config option:
convert_null_strings_to_empty: true
Will it work for you? But it will convert all string in all tables to empty.

[VladimirDVV]

A global toggle is fine, but it might lead to accidental data loss in tables where NULL actually has a specific meaning. If possible, it would be better to limit this behavior to columns that are explicitly defined as NOT NULL in ClickHouse.

[bakwc]

AFAIK we should already handle it in regular flow. Are you using it with skip_initial_replication and create CH structures yourself?

[VladimirDVV]

Yes, I am creating the ClickHouse tables manually before starting the replication.

[bakwc]

Ok, for this scenario will check existing CH structure and add conversion if mysql null, and CH not null

[bakwc]

Fixed in v0.1.12