[BUG] Error after adding modified field in mysql

[q876625596]

Bug Description

After adding a new column sale_type_list in MySQL (initially defined as NULL), I later altered it to be NOT NULL. Since then, mysql_ch_replicator consistently throws errors during real-time replication. The error indicates that ClickHouse cannot add the column because it already exists. It seems the replicator is attempting to run ALTER TABLE ADD COLUMN again instead of handling the column modification properly.

Steps to Reproduce

1. Add a new column sale_type_list in a MySQL table, initially allowing NULL.
2. Alter the column to be NOT NULL in MySQL.
3. Observe the error.

Expected Behavior

The replicator should detect that the column already exists in ClickHouse and only update its definition (or ignore the change if unsupported), rather than attempting to add the column again.

Actual Behavior

[dbrepl x_crm 2025-12-09 04:44:03,844    ERROR] unhandled exception

Traceback (most recent call last):

  File "/app/mysql_ch_replicator/db_replicator.py", line 216, in run

    self.run_realtime_replication()

  File "/app/mysql_ch_replicator/db_replicator.py", line 260, in run_realtime_replication

    self.realtime_replicator.run_realtime_replication()

  File "/app/mysql_ch_replicator/db_replicator_realtime.py", line 73, in run_realtime_replication

    self.handle_event(event)

  File "/app/mysql_ch_replicator/db_replicator_realtime.py", line 94, in handle_event

    event_handlers[event.event_type](event)

  File "/app/mysql_ch_replicator/db_replicator_realtime.py", line 185, in handle_query_event

    self.handle_alter_query(query, event.db_name)

  File "/app/mysql_ch_replicator/db_replicator_realtime.py", line 199, in handle_alter_query

    self.replicator.converter.convert_alter_query(query, db_name)

  File "/app/mysql_ch_replicator/converter.py", line 696, in convert_alter_query

    self.__convert_alter_table_add_column(db_name, table_name, tokens)

  File "/app/mysql_ch_replicator/converter.py", line 851, in __convert_alter_table_add_column

    self.db_replicator.clickhouse_api.execute_command(query)

  File "/app/mysql_ch_replicator/clickhouse_api.py", line 124, in execute_command

    self.client.command(query)

  File "/usr/local/lib/python3.12/site-packages/clickhouse_connect/driver/httpclient.py", line 351, in command

    response = self._raw_request(payload, params, headers, method, fields=fields, server_wait=False)

               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/usr/local/lib/python3.12/site-packages/clickhouse_connect/driver/httpclient.py", line 462, in _raw_request

    self._error_handler(response)

  File "/usr/local/lib/python3.12/site-packages/clickhouse_connect/driver/httpclient.py", line 385, in _error_handler

    raise OperationalError(err_str) if retried else DatabaseError(err_str) from None

clickhouse_connect.driver.exceptions.DatabaseError: HTTPDriver for http://clickhouse:8123 received ClickHouse error code 15

 Code: 15. DB::Exception: Cannot add column `sale_type_list`: column with this name already exists. (DUPLICATE_COLUMN) (version 25.9.2.1 (official build))

Environment

• mysql_ch_replicator version: 0.1.4
• Operating System: docker FROM fippo/mysql-ch-replicator:0.1.4
• Python version: docker FROM fippo/mysql-ch-replicator:0.1.4

[q876625596]

There are other error logs,the sale_type_list type is json.

Traceback (most recent call last):

  File "/app/main.py", line 6, in <module>

    main()

  File "/app/mysql_ch_replicator/main.py", line 202, in main

    run_db_replicator(args, config)

  File "/app/mysql_ch_replicator/main.py", line 116, in run_db_replicator

    db_replicator.run()

  File "/app/mysql_ch_replicator/db_replicator.py", line 216, in run

    self.run_realtime_replication()

  File "/app/mysql_ch_replicator/db_replicator.py", line 260, in run_realtime_replication

    self.realtime_replicator.run_realtime_replication()

  File "/app/mysql_ch_replicator/db_replicator_realtime.py", line 73, in run_realtime_replication

    self.handle_event(event)

  File "/app/mysql_ch_replicator/db_replicator_realtime.py", line 94, in handle_event

    event_handlers[event.event_type](event)

  File "/app/mysql_ch_replicator/db_replicator_realtime.py", line 185, in handle_query_event

    self.handle_alter_query(query, event.db_name)

  File "/app/mysql_ch_replicator/db_replicator_realtime.py", line 199, in handle_alter_query

    self.replicator.converter.convert_alter_query(query, db_name)

  File "/app/mysql_ch_replicator/converter.py", line 711, in convert_alter_query

    self.__convert_alter_table_modify_column(db_name, table_name, tokens)

  File "/app/mysql_ch_replicator/converter.py", line 881, in __convert_alter_table_modify_column

    column_type_ch = self.convert_field_type(column_type_mysql, column_type_mysql_parameters)

                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/app/mysql_ch_replicator/converter.py", line 493, in convert_field_type

    clickhouse_type = self.convert_type(mysql_type, mysql_parameters)

                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/app/mysql_ch_replicator/converter.py", line 487, in convert_type

    raise Exception(f'unknown mysql type "{mysql_type}"')

Exception: unknown mysql type "bit(32)"

[q876625596]

The errors disappeared after the reboot,The data structure is also successfully synchronized