Schema Evolution not working

[rajshtech]

Hello,

I am replicating the table from MySql to ClickHouse in real time sync process. I have altered the table by adding new column, but the Schema changes are not replicated to the ClickHouse table and running without fail. When I inserted a value to that column and failed with list index out of range as below. Then I deleted the state.pckl file, for which it started replication from scratch.

File "/home/anaconda/miniconda3/envs/mysql_ch_repli/lib/python3.11/site-packages/mysql_ch_replicator/converter.py", line 439, in convert_record
mysql_field_type = mysql_field_types[idx]
~~~~~~~~~~~~~~~~~^^^^^
IndexError: list index out of range

Similarly, for other cases like rename, drop column, modify datatype.

Please let me know if anything missing for schema evolution config.

Another Scenario: Dropped a column and added values to the existing columns, then failed with below error.

File "/home/anaconda/miniconda3/envs/mysql_ch/lib/python3.12/site-packages/clickhouse_connect/driver/insert.py", line 94, in data
raise ProgrammingError('Insert data column count does not match column names')
clickhouse_connect.driver.exceptions.ProgrammingError: Insert data column count does not match column names

Here is the config file using to run the replicator:

'mysql':
host: 'localhost'
port: 8306
user: 'root'
password: 'root'
clickhouse:
host: 'localhost'
port: 8323
user: 'default'
password: 'default'
binlog_replicator:
data_dir: '/home/user/binlog/'
records_per_file: 100000
databases: 'intranet'
tables: ['table_name']
target_databases:
mysql_db_name: clickhouse_db_name
log_level: 'info'

Environment Information
mysql_ch_replicator version: v0.0.73
MySQL version: 8.0.32
ClickHouse version: 25.3.2.39
Python version: Python 3.11.9 on linux

Thank you

[bakwc]

Investigating, thank you for the report. Could you please share the exact alter table query that you used?

[rajshtech]

Here it is,

ALTER TABLE mysql_db_name.table_name ADD COLUMN added_new_column char;

ALTER TABLE mysql_db_name.table_name RENAME COLUMN added_new_column TO rename_column_name;

ALTER TABLE mysql_db_name.table_name modify rename_column_name varchar(5);

ALTER TABLE mysql_db_name.table_name DROP COLUMN rename_column_name;

[bakwc]

Did you applied migrations during initial replication process, or after initial replication finished and realtime replication started?

[rajshtech]

yes, I applied after initial replication finished and realtime replication started.

[bakwc]

Found one issue with rename operation, ALTER TABLE mysql_db_name.table_name RENAME COLUMN added_new_column TO rename_column_name;, will prepare a fix for it.

[rajshtech]

Thank you! but also, other alter statements like add column also not replicated.

[bakwc]

For other cases could not reproduce. Could you reliably reproduce it on some simple table? Could you share your mysql server settings (my.cnf) file?
Also, is there a chance you using transactions and apply migrations at the same time in mysql?

[rajshtech]

I just tried with table of 2 columns with 10 rows, for which I added a new column but didn't replicated and nothing noticed in logs also, then if I insert a value to the new column then failing with list out of index.

I could share mysql server settings (my.cnf) file on Monday from admin. Is there any setting that missing could cause this?

Also, is there a chance you using transactions and apply migrations at the same time in mysql?
Currently, I am just testing on simple test table, so nothing like that.

[bakwc]

I just tried with table of 2 columns with 10 rows, for which I added a new column but didn't replicated and nothing noticed in logs also

We have a lot of tests for this case, also i tried manually and could not reproduce (

Is there any setting that missing could cause this?

It should not, i double check usually it's either nothing replicated or everything.
I will create additional branch with much more logs to help find out what's going on.

Also, is there a chance you using transactions and apply migrations at the same time in mysql?

We do it one by one, not in parallel, so the new records should only be inserted after migration applied.

[bakwc]

Could you please try following:

1. Checkout branch more_logs_alter_debug
2. Set log level to INFO: log_level: 'info' inside replicator config
3. Post all logs that happens when you run altering table in mysql (5 seconds before and 15 seconds after)

[rajshtech]

I found the issue seems, so when database mapping b/w mysql to clickhouse added in the config file, then schema evolution is not working. The below one,

target_databases:
mysql_db_name: clickhouse_db_name

[bakwc]

Thank you! Will try to reproduce with mappings.

[bakwc]

Fixed in v0.0.81.

• Added renames handling
• Fix for your case with target_databases mappings

Please check if it helped