[BUG] Exception: unknown mysql type "`idx_adgroup_id_type_time` (`adgroup_id`,`type`,`time`)"

[liandong00]

Bug Description

Exception: unknown mysql type "idx_adgroup_id_type_time (adgroup_id,type,time)"

Steps to Reproduce

After I added some table indexes, the log error is as follows

[dbrepl youtube_data 2025-05-06 11:37:42,041     INFO] launched db_replicator
[dbrepl youtube_data 2025-05-06 11:37:42,052     INFO] running realtime replication from the position: ('ON.000037', 1016234912)
[dbrepl youtube_data 2025-05-06 11:37:46,363     INFO] set position to /data/youtube_data/28.bin
[dbrepl youtube_data 2025-05-06 11:37:47,162     INFO] found transaction ('ON.000037', 1016234912) inside /data/youtube_data/28.bin
[dbrepl youtube_data 2025-05-06 11:37:47,163    DEBUG] processing event ('ON.000037', 1016235172), 3,
[dbrepl youtube_data 2025-05-06 11:37:47,163    DEBUG] processing query event: ('ON.000037', 1016235172), query: ALTER TABLE `adgroup_data` ADD  KEY `idx_adgroup_id_type_time` (`adgroup_id`,`type`,`time`)
[dbrepl youtube_data 2025-05-06 11:37:47,171    DEBUG] upload records, to insert: 0, to delete: 0
[dbrepl youtube_data 2025-05-06 11:37:47,174    ERROR] unhandled exception
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator.py", line 196, in run
    self.run_realtime_replication()
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator.py", line 224, in run_realtime_replication
    self.realtime_replicator.run_realtime_replication()
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_realtime.py", line 73, in run_realtime_replication
    self.handle_event(event)
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_realtime.py", line 94, in handle_event
    event_handlers[event.event_type](event)
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_realtime.py", line 174, in handle_query_event
    self.handle_alter_query(query, event.db_name)
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_realtime.py", line 185, in handle_alter_query
    self.replicator.converter.convert_alter_query(query, db_name)
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/converter.py", line 587, in convert_alter_query
    self.__convert_alter_table_add_column(db_name, table_name, tokens)
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/converter.py", line 699, in __convert_alter_table_add_column
    column_type_ch = self.convert_field_type(column_type_mysql, column_type_mysql_parameters)
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/converter.py", line 392, in convert_field_type
    clickhouse_type = self.convert_type(mysql_type, mysql_parameters)
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/converter.py", line 386, in convert_type
    raise Exception(f'unknown mysql type "{mysql_type}"')
Exception: unknown mysql type "`idx_adgroup_id_type_time` (`adgroup_id`,`type`,`time`)"
[dbrepl youtube_data 2025-05-06 11:37:49,053     INFO] launched db_replicator
[dbrepl youtube_data 2025-05-06 11:37:49,064     INFO] running realtime replication from the position: ('ON.000037', 1016234912)

Expected Behavior

Actual Behavior

Environment

• mysql_ch_replicator version: v0.0.82
• Operating System: Debian GNU/Linux 11 (bullseye)
• Python version: 3.10.16

MySQL Configuration

server_id = 1
log_bin=ON
expire_logs_days = 10
max_binlog_size = 1024M
binlog_format = ROW
gtid_mode = on
log_slave_updates = ON
enforce_gtid_consistency = 1

Replicator Configuration

mysql:
  host: 'mysql'
  port: 3306
  user: 'root'
  password: ''

clickhouse:
  host: 'clickhouse'
  port: 8123
  user: 'default'
  password: ''
  connection_timeout: 30        # optional
  send_receive_timeout: 300     # optional

binlog_replicator:
  data_dir: '/data'
  records_per_file: 1000000

databases: 'youtube_data'
log_level: 'debug'
initial_replication_threads: 4

Additional Information

[bakwc]

Fixed in v0.0.83

[liandong00]

After upgrading to v0.0.83, the above error seems to be resolved. But when I tried to reinitialize and synchronize the data, the following error occurred.

[dbrepl youtube_data worker_0 table_ad_rate_key 2025-05-07 11:41:37,890     INFO] launched db_replicator
[dbrepl youtube_data worker_2 table_ad_rate_key 2025-05-07 11:41:37,894     INFO] Processing specific table: ad_rate_key
[dbrepl youtube_data worker_2 table_ad_rate_key 2025-05-07 11:41:37,894     INFO] Worker 2/4 using state file: /data/youtube_data/state_worker_2_of_4_05fd72847e74aa17.pckl
[dbrepl youtube_data worker_2 table_ad_rate_key 2025-05-07 11:41:37,894     INFO] Worker 2 focusing only on table: ad_rate_key
[dbrepl youtube_data worker_0 table_ad_rate_key 2025-05-07 11:41:37,895     INFO] running initial replication
[dbrepl youtube_data worker_0 table_ad_rate_key 2025-05-07 11:41:37,896     INFO] running initial replication for table ad_rate_key
[dbrepl youtube_data worker_0 table_ad_rate_key 2025-05-07 11:41:37,896     INFO] continue from primary key None
[dbrepl youtube_data worker_0 table_ad_rate_key 2025-05-07 11:41:37,896    DEBUG] mysql table structure: TableStructure(fields=[TableField(name='rate_id', field_type='bigint(20)', parameters='NOT NULL', additional_data=None), TableField(name='key', field_type='varchar(64)', parameters="NOT NULL DEFAULT ''", additional_data=None), TableField(name='adgroup_id', field_type='varchar(64)', parameters='NOT NULL', additional_data=None), TableField(name='adgroup_type', field_type='varchar(5)', parameters='NOT NULL', additional_data=None), TableField(name='label', field_type='varchar(255)', parameters="NOT NULL DEFAULT ''", additional_data=None)], primary_keys=['rate_id', 'key'], primary_key_ids=[0, 1], table_name='ad_rate_key', charset='utf8', charset_python='utf_8', if_not_exists=False)
[dbrepl youtube_data worker_0 table_ad_rate_key 2025-05-07 11:41:37,896    DEBUG] clickhouse table structure: TableStructure(fields=[TableField(name='rate_id', field_type='Int64', parameters='', additional_data=None), TableField(name='key', field_type='String', parameters='', additional_data=None), TableField(name='adgroup_id', field_type='String', parameters='', additional_data=None), TableField(name='adgroup_type', field_type='String', parameters='', additional_data=None), TableField(name='label', field_type='String', parameters='', additional_data=None)], primary_keys=['rate_id', 'key'], primary_key_ids=[0, 1], table_name='ad_rate_key', charset='', charset_python='', if_not_exists=True)
[dbrepl youtube_data worker_0 table_ad_rate_key 2025-05-07 11:41:37,897    ERROR] unhandled exception
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/mysql/connector/connection_cext.py", line 772, in cmd_query
    self._cmysql.query(
_mysql_connector.MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key, ''))) % 4 = 0 ORDER BY rate_id,key LIMIT 50000' at line 1

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator.py", line 199, in run
    self.initial_replicator.perform_initial_replication()
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 102, in perform_initial_replication
    self.perform_initial_replication_table(table)
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 179, in perform_initial_replication_table
    records = self.replicator.mysql_api.get_records(
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/mysql_api.py", line 118, in get_records
    self.cursor.execute(query)
  File "/usr/local/lib/python3.10/site-packages/mysql/connector/cursor_cext.py", line 356, in execute
    self._connection.cmd_query(
  File "/usr/local/lib/python3.10/site-packages/mysql/connector/opentelemetry/context_propagation.py", line 97, in wrapper
    return method(cnx, *args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/mysql/connector/connection_cext.py", line 781, in cmd_query
    raise get_mysql_exception(
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key, ''))) % 4 = 0 ORDER BY rate_id,key LIMIT 50000' at line 1
[dbrepl youtube_data worker_2 table_ad_rate_key 2025-05-07 11:41:37,925     INFO] launched db_replicator
[dbrepl youtube_data worker_2 table_ad_rate_key 2025-05-07 11:41:37,929     INFO] running initial replication
[dbrepl youtube_data worker_2 table_ad_rate_key 2025-05-07 11:41:37,930     INFO] running initial replication for table ad_rate_key
[dbrepl youtube_data worker_2 table_ad_rate_key 2025-05-07 11:41:37,930     INFO] continue from primary key None
[dbrepl youtube_data worker_2 table_ad_rate_key 2025-05-07 11:41:37,930    DEBUG] mysql table structure: TableStructure(fields=[TableField(name='rate_id', field_type='bigint(20)', parameters='NOT NULL', additional_data=None), TableField(name='key', field_type='varchar(64)', parameters="NOT NULL DEFAULT ''", additional_data=None), TableField(name='adgroup_id', field_type='varchar(64)', parameters='NOT NULL', additional_data=None), TableField(name='adgroup_type', field_type='varchar(5)', parameters='NOT NULL', additional_data=None), TableField(name='label', field_type='varchar(255)', parameters="NOT NULL DEFAULT ''", additional_data=None)], primary_keys=['rate_id', 'key'], primary_key_ids=[0, 1], table_name='ad_rate_key', charset='utf8', charset_python='utf_8', if_not_exists=False)
[dbrepl youtube_data worker_2 table_ad_rate_key 2025-05-07 11:41:37,930    DEBUG] clickhouse table structure: TableStructure(fields=[TableField(name='rate_id', field_type='Int64', parameters='', additional_data=None), TableField(name='key', field_type='String', parameters='', additional_data=None), TableField(name='adgroup_id', field_type='String', parameters='', additional_data=None), TableField(name='adgroup_type', field_type='String', parameters='', additional_data=None), TableField(name='label', field_type='String', parameters='', additional_data=None)], primary_keys=['rate_id', 'key'], primary_key_ids=[0, 1], table_name='ad_rate_key', charset='', charset_python='', if_not_exists=True)
[dbrepl youtube_data worker_2 table_ad_rate_key 2025-05-07 11:41:37,931    ERROR] unhandled exception
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/mysql/connector/connection_cext.py", line 772, in cmd_query
    self._cmysql.query(
_mysql_connector.MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key, ''))) % 4 = 2 ORDER BY rate_id,key LIMIT 50000' at line 1

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator.py", line 199, in run
    self.initial_replicator.perform_initial_replication()
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 102, in perform_initial_replication
    self.perform_initial_replication_table(table)
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 179, in perform_initial_replication_table
    records = self.replicator.mysql_api.get_records(
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/mysql_api.py", line 118, in get_records
    self.cursor.execute(query)
  File "/usr/local/lib/python3.10/site-packages/mysql/connector/cursor_cext.py", line 356, in execute
    self._connection.cmd_query(
  File "/usr/local/lib/python3.10/site-packages/mysql/connector/opentelemetry/context_propagation.py", line 97, in wrapper
    return method(cnx, *args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/mysql/connector/connection_cext.py", line 781, in cmd_query
    raise get_mysql_exception(
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key, ''))) % 4 = 2 ORDER BY rate_id,key LIMIT 50000' at line 1
[dbrepl youtube_data 2025-05-07 11:41:38,063    ERROR] Worker process 0 failed with exit code 1
[dbrepl youtube_data 2025-05-07 11:41:38,064    ERROR] unhandled exception
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator.py", line 199, in run
    self.initial_replicator.perform_initial_replication()
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 102, in perform_initial_replication
    self.perform_initial_replication_table(table)
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 139, in perform_initial_replication_table
    self.perform_initial_replication_table_parallel(table_name)
  File "/usr/local/lib/python3.10/site-packages/mysql_ch_replicator/db_replicator_initial.py", line 375, in perform_initial_replication_table_parallel
    raise Exception(f"Worker process failed with exit code {exit_code}")
Exception: Worker process failed with exit code 1