Replication Failure On MySQL Schema Changes

[harishgreddy]

Hi,

I ran alter commands in MySQL for adding new columns, but the replication fails when schema changes with index out of range. I tried restarting and also manually creating tables directly in clickhouse, but it wasn't helpful. To resolve this I killed the replication process clear state and binlog files and restarted replication.

During this schema change initial replication was successfully complete and normal binlog replication was taking place.

I have a huge data in my DB and the initial replication takes around 4 hours to complete, my users will see stale data during this process. Is there a way to continue replication with existing data when new schema changes are done in MySQL ?

MySQL Alter Commands:

ALTER TABLE production
ADD COLUMN number_of_batches DECIMAL(20, 2) NOT NULL;

ALTER TABLE sales_invoice
MODIFY COLUMN gst_in VARCHAR(50) NULL;

ALTER TABLE daily_transaction_item
DROP COLUMN return_quantity,
DROP COLUMN return_stock_id;

ALTER TABLE production
DROP COLUMN end_time;

Error Logs:-

Apr 04 14:40:09 : records = self.converter.convert_records(event.records, mysql_table_structure, clickhouse_tab
Apr 04 14:40:09 : ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : clickhouse_record = self.convert_record(
Apr 04 14:40:09 : ^^^^^^^^^^^^^^^^^^^^
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : mysql_field_type = mysql_field_types[idx]
Apr 04 14:40:09 : ~~~~~~~~~~~~~~~~~^^^^^
Apr 04 14:40:09 : IndexError: list index out of range
Apr 04 14:40:09 : Searching for transaction ('binlog.000093', 90862479) in files: [1, 2]
Apr 04 14:40:09 : Checking file 1...
Apr 04 14:40:09 : Checking file 2...
Apr 04 14:40:09 : Traceback (most recent call last):
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/bin/mysql_ch_replicator", line 8, in <module>
Apr 04 14:40:09 : sys.exit(main())
Apr 04 14:40:09 : ^^^^^^
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : self.run_realtime_replication()
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : self.handle_event(event)
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : event_handlers[event.event_type](event)
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : records = self.converter.convert_records(event.records, mysql_table_structure, clickhouse_tab
Apr 04 14:40:09 : ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : clickhouse_record = self.convert_record(
Apr 04 14:40:09 : ^^^^^^^^^^^^^^^^^^^^
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : mysql_field_type = mysql_field_types[idx]
Apr 04 14:40:09 : ~~~~~~~~~~~~~~~~~^^^^^
Apr 04 14:40:09 : IndexError: list index out of range
Apr 04 14:40:09 : Searching for transaction ('binlog.000093', 90862479) in files: [1, 2]
Apr 04 14:40:09 : Checking file 1...
Apr 04 14:40:09 : Checking file 2...
Apr 04 14:40:09 : Traceback (most recent call last):
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/bin/mysql_ch_replicator", line 8, in <module>
Apr 04 14:40:09 : sys.exit(main())
Apr 04 14:40:09 : ^^^^^^
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : run_db_replicator(args, config)
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : db_replicator.run()
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : self.run_realtime_replication()
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : self.handle_event(event)
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : event_handlers[event.event_type](event)
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : records = self.converter.convert_records(event.records, mysql_table_structure, clickhouse_tab
Apr 04 14:40:09 : ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : clickhouse_record = self.convert_record(
Apr 04 14:40:09 : ^^^^^^^^^^^^^^^^^^^^
Apr 04 14:40:09 : File "/home/ec2-user/.pyenv/versions/3.11.1/lib/python3.11/site-packages/mysql_ch_replicator/
Apr 04 14:40:09 : mysql_field_type = mysql_field_types[idx]
Apr 04 14:40:09 : ~~~~~~~~~~~~~~~~~^^^^^
Apr 04 14:40:09 : IndexError: list index out of range

[bakwc]

Most schema changes supported without interrupting replication, but not all. I will double check commands you provided and let you know.

There are a few hints for the future:

1. Write an auto-test for migrations in your CI/CD (a. apply all your migrations, b. try to insert new record in mysql, c. ensure it appearing in clickhouse.
2. Recently we introduced accelerated realtime replication: initial_replication_threads config option, it should help to recover much faster in case of any emergency.

[bakwc]

For me it's not reproducing, i created a few tables and applied the migrations you provided, and migrations applied without replication restart.

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

[harishgreddy]

It was during realtime replication.

[bakwc]

Do you have steps to reproduce it? I tried multiple ways, but could not reproduce it unfortunately, during realtime phase it should work without any issue.

[harishgreddy]

Let me create a minimal reproducible example and share it with you

[bakwc]

Issue #141 has a similar report. We deployed a fix in version v0.0.80, please update and check if it helps with your case.

[harishgreddy]

I tried with 0.0.80, still facing same issue.

pip show mysql_ch_replicator
Name: mysql-ch-replicator
Version: 0.0.80
Summary: Tool for replication of MySQL databases to ClickHouse
Home-page:
Author: Filipp Ozinov
Author-email: filipp@bakanov.su
License: MIT
Location: /home/ec2-user/.pyenv/versions/3.11.11/lib/python3.11/site-packages
Requires: clickhouse-connect, fastapi, mysql-connector-python, packaging, pymysql, pyparsing, pyyaml, requests, sqlparse, uvicorn
Required-by:

Tried with this config

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

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

binlog_replicator:
  data_dir: 'binlog/'
  records_per_file: 100000

databases: 'test_replication'

target_databases:
  test_replication: test_replication_ch

I noticed the issue when target_databases is added.

Create sample database and add table

create table test_replication.replication_data
(
    code                      varchar(255)   not null
        primary key,
    val_1  varchar(255)   not null
);

insert into test_replication.replication_data(code, val_1) value ("test-1", "1");

After creating this DB & table I ran

mysql_ch_replicator --config config.yaml run_all

When the initial sync is completed, I ran below SQL commands in MySQL.

update test_replication.replication_data set val_1 = "1200";
update test_replication.replication_data set val_2 = "100";

Traceback (most recent call last):
  File "/home/ec2-user/.pyenv/versions/3.11.11/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.11/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.11/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator_realtime.py", line 73, in run_realtime_replication
    self.handle_event(event)
  File "/home/ec2-user/.pyenv/versions/3.11.11/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.11/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator_realtime.py", line 135, in handle_insert_event
    records = self.replicator.converter.convert_records(event.records, mysql_table_structure, clickhouse_table_structure)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ec2-user/.pyenv/versions/3.11.11/lib/python3.11/site-packages/mysql_ch_replicator/converter.py", line 422, in convert_records
    clickhouse_record = self.convert_record(
                        ^^^^^^^^^^^^^^^^^^^^
  File "/home/ec2-user/.pyenv/versions/3.11.11/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
Traceback (most recent call last):
  File "/home/ec2-user/.pyenv/versions/3.11.11/bin/mysql_ch_replicator", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/home/ec2-user/.pyenv/versions/3.11.11/lib/python3.11/site-packages/mysql_ch_replicator/main.py", line 179, in main
    run_db_replicator(args, config)
  File "/home/ec2-user/.pyenv/versions/3.11.11/lib/python3.11/site-packages/mysql_ch_replicator/main.py", line 113, in run_db_replicator
    db_replicator.run()
  File "/home/ec2-user/.pyenv/versions/3.11.11/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.11/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.11/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator_realtime.py", line 73, in run_realtime_replication
    self.handle_event(event)
  File "/home/ec2-user/.pyenv/versions/3.11.11/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.11/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator_realtime.py", line 135, in handle_insert_event
    records = self.replicator.converter.convert_records(event.records, mysql_table_structure, clickhouse_table_structure)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/ec2-user/.pyenv/versions/3.11.11/lib/python3.11/site-packages/mysql_ch_replicator/converter.py", line 422, in convert_records
    clickhouse_record = self.convert_record(
                        ^^^^^^^^^^^^^^^^^^^^
  File "/home/ec2-user/.pyenv/versions/3.11.11/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

[bakwc]

Hm.. when you run this command update test_replication.replication_data set val_2 = "100"; - the column val_2 does not yet exists in MySQL. I am trying to reproduce with your steps but I unable to run update on the non-exists column:

mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'val_2' in 'field list'

[harishgreddy]

Sorry, I forgot to add the alter statement. I run this alter command after the initial sync is completed.

ALTER TABLE replication_data
ADD COLUMN val_2 DECIMAL(20, 2);

I run update alter.

update test_replication.replication_data set val_1 = "1200";
update test_replication.replication_data set val_2 = "100";

[bakwc]

It reproduced thanks for the steps! Prepared the fix in v0.0.81

[harishgreddy]

Working, Thank you.