[BUG] Initial replication can loop forever on composite string primary keys with MySQL collations

[KamikotoTkach]

Bug Description

Initial replication can get stuck in an infinite loop for a table with a composite string primary key when MySQL uses a collation whose ordering differs from Python string ordering, for example utf8mb4_0900_ai_ci.

The replicator keeps reading/re-inserting the same rows, and the replicated N records counter grows far beyond the actual number of rows in the source table.

The suspected cause is that initial replication updates the pagination cursor with Python max() over primary key values, while the rows are ordered by MySQL using MySQL collation. For string primary keys, these orderings can differ.

Steps to Reproduce

1. Create a MySQL table with a composite string primary key and a case-insensitive MySQL 8 collation:

CREATE TABLE `balances` (
    `user_id` VARCHAR(16) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `currency` VARCHAR(8) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `balance` BIGINT(19) NOT NULL DEFAULT '0',
    `version` INT(10) UNSIGNED NOT NULL DEFAULT '1',
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`user_id`, `currency`) USING BTREE,
    INDEX `idx_updated_at` (`updated_at`) USING BTREE,
    CONSTRAINT `chk_balance_non_negative` CHECK (`balance` >= 0)
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB;

2. Insert rows where MySQL collation order differs from Python string order, for example values containing both uppercase and lowercase usernames:

INSERT INTO balances (user_id, currency, balance) VALUES
('admin', 'crystal', 100),
('metalgentle', 'mora', 200),
('Tkachenko', 'primogem', 300);

3. Run mysql_ch_replicator initial replication for this database/table.

Expected Behavior

Initial replication should read all rows once and finish the initial copy.

For the example above, the balances table should finish after copying the actual number of rows from MySQL.

Actual Behavior

Initial replication does not finish. The worker keeps reporting progress for the same table, and the replicated record count grows far beyond the actual row count.

Example logs from a table that had only 26 rows:

[dbrepl economy 2026-05-16 20:42:30,017     INFO] Still waiting for 3 workers to complete
[dbrepl economy 2026-05-16 20:42:30,117     INFO] Still waiting for 3 workers to complete
[dbrepl economy worker_1 table_balances 2026-05-16 20:43:27,786     INFO] replicating balances, replicated 6070 records, primary key: ['admin', 'crystal']
[dbrepl economy worker_0 table_balances 2026-05-16 20:43:27,786     INFO] replicating balances, replicated 3041 records, primary key: ['Tkachenko', 'primogem']
[dbrepl economy worker_3 table_balances 2026-05-16 20:43:27,786     INFO] replicating balances, replicated 15228 records, primary key: ['metalgentle', 'mora']
[dbrepl economy 2026-05-16 20:43:30,093     INFO] Still waiting for 3 workers to complete
...
[dbrepl economy worker_3 table_balances 2026-05-16 20:45:27,950     INFO] replicating balances, replicated 18593 records, primary key: ['metalgentle', 'mora']
[dbrepl economy worker_1 table_balances 2026-05-16 20:45:27,950     INFO] replicating balances, replicated 7416 records, primary key: ['admin', 'crystal']
[dbrepl economy worker_0 table_balances 2026-05-16 20:45:27,950     INFO] replicating balances, replicated 3714 records, primary key: ['Tkachenko', 'primogem']

Environment

• mysql_ch_replicator version: 0.1.15 / Docker image fippo/mysql-ch-replicator:0.1.15
• Operating System: Linux container
• Python version: Python 3.12

MySQL Configuration

[mysqld]
binlog_format = ROW
log_bin = /var/log/mysql/mysql-bin.log
server_id = 1
default_authentication_plugin = mysql_native_password

The relevant table/column collation is:

COLLATE='utf8mb4_0900_ai_ci'

Example affected table:

CREATE TABLE `balances` (
    `user_id` VARCHAR(16) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `currency` VARCHAR(8) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `balance` BIGINT(19) NOT NULL DEFAULT '0',
    `version` INT(10) UNSIGNED NOT NULL DEFAULT '1',
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`user_id`, `currency`) USING BTREE,
    INDEX `idx_updated_at` (`updated_at`) USING BTREE,
    CONSTRAINT `chk_balance_non_negative` CHECK (`balance` >= 0)
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB;

Replicator Configuration

mysql:
  host: mysql
  port: 3306
  user: mysql_replicator
  password: REDACTED
  charset: utf8mb4

clickhouse:
  host: clickhouse
  port: 8123
  user: mysql_replicator
  password: REDACTED
  connection_timeout: 30
  send_receive_timeout: 300

binlog_replicator:
  data_dir: /app/data
  records_per_file: 100000
  binlog_retention_period: 43200

databases:
  - economy

tables: '*'

initial_replication_threads: 4
log_level: info
optimize_interval: 86400
enable_optimize_final: true
mysql_timezone: UTC

Additional Information

I believe the issue is caused by this logic in mysql_ch_replicator/db_replicator_initial.py:

for record in records:
    record_primary_key = [record[key_idx] for key_idx in primary_key_ids]
    if max_primary_key is None:
        max_primary_key = record_primary_key
    else:
        max_primary_key = max(max_primary_key, record_primary_key)

This uses Python ordering to choose the pagination cursor. However, records are fetched from MySQL using ORDER BY on the primary key, and MySQL applies the column collation.

For string primary keys with collations like utf8mb4_0900_ai_ci, Python max() can choose a different key than the last row returned by MySQL.

For example, with rows ordered by MySQL collation:

records = [
    ('admin', 'crystal', 100),
    ('metalgentle', 'mora', 200),
    ('Tkachenko', 'primogem', 300),
]

Python may choose:

['metalgentle', 'mora']

as the max key, while the last row returned by MySQL is:

['Tkachenko', 'primogem']

Then the next pagination query starts from the wrong cursor and can return already processed rows again.

A possible fix is to use the primary key of the last returned record, since MySQL already returned the batch in the correct order:

max_primary_key = [records[-1][key_idx] for key_idx in primary_key_ids]

I tested this change locally in a patched Docker image, and it fixed the infinite initial replication loop for the affected table.