[BUG] Replication Fails When Adding New Table

[harishgreddy]

Bug Description

Replication Fails When Adding New Table

Steps to Reproduce

1. Allow for initial replication to complete
2. During normal realtime replication create a table in MySQL
3. Replication fails with Exception: unknown mysql type ""

Expected Behavior

Replication creates new table in Clickhouse and start replicating data.

Actual Behavior

Replication fails with below error
[dbrepl 2025-06-16 10:25:23,726 ERROR] unhandled exception Traceback (most recent call last): File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 196, in run self.run_realtime_replication() File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 224, in run_realtime_replication self.realtime_replicator.run_realtime_replication() File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator_realtime.py", line 73, in run_realtime_repli self.handle_event(event) File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator_realtime.py", line 94, in handle_event event_handlers[event.event_type](event) File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator_realtime.py", line 176, in handle_query_even self.handle_create_table_query(query, event.db_name) File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator_realtime.py", line 188, in handle_create_tab mysql_structure, ch_structure = self.replicator.converter.parse_create_table_query(query) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/converter.py", line 982, in parse_create_table_query ch_table_structure = self.convert_table_structure(mysql_table_structure) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/converter.py", line 404, in convert_table_structure clickhouse_field_type = self.convert_field_type(field.field_type, field.parameters) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/converter.py", line 392, in convert_field_type clickhouse_type = self.convert_type(mysql_type, mysql_parameters) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/converter.py", line 386, in convert_type raise Exception(f'unknown mysql type "{mysql_type}"') Exception: unknown mysql type ""

Environment

• mysql_ch_replicator version: 0.0.82
• Operating System: Amazon Linux 2023
• Python version: 3.11.1

MySQL Configuration

gtid_mode = on
enforce_gtid_consistency = 1
binlog_expire_logs_seconds = 864000
max_binlog_size            = 500M
binlog_format              = ROW

Replicator Configuration

mysql:
  host: '127.0.0.1'
  port: 3306
  user: 'replicator'
  password: 'changeit'

clickhouse:
  host: '127.0.0.1'
  port: 8123
  user: 'default'
  password: 'changeit'

binlog_replicator:
  data_dir: '/binlog/'
  records_per_file: 100000

databases: ['mysql_db']

exclude_tables: ['ignored_table_one']

target_databases:
  mysql_db: ch_db

Additional Information

Replication fails when for below table creation in MySQL

create table test_table
(
    id    bigint          not null,
    col_a datetime(6)     not null,
    col_b datetime(6)     null,
    col_c varchar(255)    not null,
    col_d varchar(255)    not null,
    col_e int             not null,
    col_f decimal(20, 10) not null,
    col_g decimal(20, 10) not null,
    col_h datetime(6)     not null,
    col_i date            not null,
    col_j varchar(255)    not null,
    col_k varchar(255)    not null,
    col_l bigint          not null,
    col_m varchar(50)     not null,
    col_n bigint          null,
    col_o decimal(20, 1)  null,
    col_p date            null,
    primary key (id, col_e)
);

[bakwc]

Fixed in PR #163

The issue was caused by multiple spaces in CREATE TABLE statements. Parser was splitting on single spaces which created empty strings, making field type extraction fail.

[bakwc]

🎉 Fixed and available in v0.0.86!

The parsing bug has been fixed and merged to master. I've created a new tag v0.0.86.

Please update and test:

pip install mysql-ch-replicator==0.0.86

Can you test with your original CREATE TABLE statement and let me know if it works now? The issue was caused by multiple spaces in your DDL statement that were breaking the parser.