Tables with multiple primary keys are breaking the replicator

[Stanpol]

In the converter.py the function parse_mysql_table_structure when stumble upon the following create table statement ends up with exception:

--- processing statement:
 CREATE TABLE `test` (
  `key_a_id` bigint NOT NULL,
  `key_b_id` bigint NOT NULL,
  PRIMARY KEY (`key_a_id`, `key_b_id`),
  KEY `FK_test` (`key_b_id`),
  CONSTRAINT `test_ibfk_1` FOREIGN KEY (`key_a_id`) REFERENCES `a` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `test_ibfk_2` FOREIGN KEY (`key_b_id`) REFERENCES `b` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Exception:

=== processing line key_a_id bigint NOT NULL
=== processed line key_a_id bigint NOT NULL
=== processing line key_b_id bigint NOT NULL
=== processed line key_b_id bigint NOT NULL
=== processing line PRIMARY KEY (key_a_id ,key_b_id)
[dbrepl 2024-11-20 19:27:38,547 ERROR] unhandled exception
Traceback (most recent call last):
File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 193, in run
self.create_initial_structure()
File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 203, in create_initial_structure
self.create_initial_structure_table(table)
File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 210, in create_initial_structure_table
mysql_structure = self.converter.parse_mysql_table_structure(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/converter.py", line 462, in parse_mysql_table_structure
result = pattern.parseString(line)
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/.direnv/python-3.11.2/lib/python3.11/site-packages/pyparsing/util.py", line 256, in _inner
return fn(self, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/.direnv/python-3.11.2/lib/python3.11/site-packages/pyparsing/core.py", line 1197, in parse_string
raise exc.with_traceback(None)
pyparsing.exceptions.ParseException: Expected ')', found ',' (at char 23), (line:1, col:24)

You probably want to try something like this to match multiple keys:

        pattern = Suppress('PRIMARY KEY (') + delimitedList(Word(alphanums + '_`')) + Suppress(')')
        try:
            result = pattern.parseString(line)
            structure.primary_keys = [strip_sql_name(col) for col in result]
        except Exception as e:
            print("Failed to parse PRIMARY KEY:", e)

[bakwc]

Will fix on weekend (not so quick fix unfortunately).

[bakwc]

Fixed in 0.0.36. Could you please update and check if it works?

[Stanpol]

Unfortunately, new release introduced bunch of problems - it didn't fix the compound primary key issue for me, but also broke the previously working table sync.

Error 1. For a single table with compound primary key. Temporary database not existing:
Clickhouse is empty, no databases or tables preexisting.

mysql_ch_replicator --config replicator.yaml run_all
[runner 2024-11-26 10:54:54,125     INFO] running replication for test (initial replication not finished - waiting)
[binlogrepl 2024-11-26 10:54:54,934     INFO] state start position: ('mysql-bin.000030', 67736584)
[binlogrepl 2024-11-26 10:54:54,935     INFO] connection_settings: {'host': 'localhost', 'port': 3306, 'user': '***', 'passwd': '***', 'charset': 'utf8'}
[binlogrepl 2024-11-26 10:54:54,935     INFO] resume_stream: True
[binlogrepl 2024-11-26 10:54:54,935     INFO] allowed_events_in_packet: [event, row_event, event, event, event, event, event, event, event, event, event, event, event, row_event, event, event, event, row_event, row_event, event, row_event, event, event, event]
[binlogrepl 2024-11-26 10:54:54,935     INFO] server_id: 3047475605
[binlogrepl 2024-11-26 10:54:54,935     INFO] log_pos: 67736584
[binlogrepl 2024-11-26 10:54:54,936     INFO] log_file: mysql-bin.000030
[dbopt None 2024-11-26 10:54:55,589     INFO] running optimizer
[dbrepl test 2024-11-26 10:54:55,748     INFO] launched db_replicator
[binlogrepl 2024-11-26 10:54:55,748    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000030', 67736584)
[binlogrepl 2024-11-26 10:54:55,748    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000030', 67736584)
[dbrepl test 2024-11-26 10:54:55,839  WARNING] database test missing in CH
[dbrepl test 2024-11-26 10:54:55,843     INFO] running initial replication
[dbrepl test 2024-11-26 10:54:55,845     INFO] running initial replication for table test1
[dbrepl test 2024-11-26 10:54:55,846     INFO] skip table test1 - not matching any allowed table
[dbrepl test 2024-11-26 10:48:22,146     INFO] initial replication - swapping database
[dbrepl test 2024-11-26 10:48:22,225    ERROR] unhandled exception
Traceback (most recent call last):
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 180, in run
    self.perform_initial_replication()
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 251, in perform_initial_replication
    self.clickhouse_api.execute_command(
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/clickhouse_api.py", line 67, in execute_command
    self.client.command(query)
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/clickhouse_connect/driver/httpclient.py", line 350, in command
    response = self._raw_request(payload, params, headers, method, fields=fields, server_wait=False)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/clickhouse_connect/driver/httpclient.py", line 461, in _raw_request
    self._error_handler(response)
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/clickhouse_connect/driver/httpclient.py", line 384, in _error_handler
    raise OperationalError(err_str) if retried else DatabaseError(err_str) from None
clickhouse_connect.driver.exceptions.DatabaseError: HTTPDriver for http://localhost:8123 received ClickHouse error code 81
 Code: 81. DB::Exception: Database test_tmp does not exist. (UNKNOWN_DATABASE) (version 24.10.2.80 (official build))

Traceback (most recent call last):
  File "/.direnv/python-3.11.2/bin/mysql_ch_replicator", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/main.py", line 152, in main
    run_db_replicator(args, config)
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/main.py", line 98, in run_db_replicator
    db_replicator.run()
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 180, in run
    self.perform_initial_replication()
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 251, in perform_initial_replication
    self.clickhouse_api.execute_command(
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/clickhouse_api.py", line 67, in execute_command
    self.client.command(query)
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/clickhouse_connect/driver/httpclient.py", line 350, in command
    response = self._raw_request(payload, params, headers, method, fields=fields, server_wait=False)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/clickhouse_connect/driver/httpclient.py", line 461, in _raw_request
    self._error_handler(response)
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/clickhouse_connect/driver/httpclient.py", line 384, in _error_handler
    raise OperationalError(err_str) if retried else DatabaseError(err_str) from None
clickhouse_connect.driver.exceptions.DatabaseError: HTTPDriver for http://localhost:8123 received ClickHouse error code 81
 Code: 81. DB::Exception: Database test_tmp does not exist. (UNKNOWN_DATABASE) (version 24.10.2.80 (official build))

Error 2. For a table with a single primary key id. Primary key is not found.
This was working before, but now it's broken.

mysql_ch_replicator --config replicator.yaml run_all
[runner 2024-11-26 10:59:01,540     INFO] running replication for test (initial replication not finished - waiting)
[binlogrepl 2024-11-26 10:59:02,395     INFO] state start position: ('mysql-bin.000030', 67736584)
[binlogrepl 2024-11-26 10:59:02,396     INFO] connection_settings: {'host': 'localhost', 'port': 3306, 'user': '***', 'passwd': '***', 'charset': 'utf8'}
[binlogrepl 2024-11-26 10:59:02,396     INFO] resume_stream: True
[binlogrepl 2024-11-26 10:59:02,396     INFO] allowed_events_in_packet: [event, event, event, event, event, event, row_event, event, event, event, event, row_event, event, row_event, event, event, row_event, event, event, event, event, event, event, row_event]
[binlogrepl 2024-11-26 10:59:02,396     INFO] server_id: 797684290
[binlogrepl 2024-11-26 10:59:02,397     INFO] log_pos: 67736584
[binlogrepl 2024-11-26 10:59:02,397     INFO] log_file: mysql-bin.000030
[dbopt None 2024-11-26 10:59:03,129     INFO] running optimizer
[dbrepl test 2024-11-26 10:59:03,134     INFO] launched db_replicator
[binlogrepl 2024-11-26 10:59:03,228    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.RotateEvent'>, ('mysql-bin.000030', 67736584)
[binlogrepl 2024-11-26 10:59:03,230    DEBUG] received event <class 'mysql_ch_replicator.pymysqlreplication.event.FormatDescriptionEvent'>, ('mysql-bin.000030', 67736584)
[dbrepl test 2024-11-26 10:59:03,231  WARNING] database test missing in CH
[dbrepl test 2024-11-26 10:59:03,231     INFO] running initial replication
[dbrepl test 2024-11-26 10:59:03,237     INFO] running initial replication for table users
[dbrepl test 2024-11-26 10:59:03,237     INFO] replicating from scratch
[dbrepl test 2024-11-26 10:59:03,239    DEBUG] mysql table structure: TableStructure(fields=[TableField(name='id', field_type='bigint', parameters='NOT NULL AUTO_INCREMENT'), TableField(name='first_name', field_type='varchar(255)', parameters='CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL'), TableField(name='last_name', field_type='varchar(255)', parameters='CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL'), TableField(name='phone_number', field_type='varchar(255)', parameters='CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL'), TableField(name='email', field_type='varchar(255)', parameters='CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL'), TableField(name='user_settings_id', field_type='bigint', parameters='NOT NULL'), TableField(name='created_at', field_type='datetime', parameters='NOT NULL DEFAULT CURRENT_TIMESTAMP'), TableField(name='updated_at', field_type='datetime', parameters='NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'), TableField(name='deleted_at', field_type='datetime', parameters='DEFAULT NULL')], primary_keys='', primary_key_ids=0, table_name='users')
[dbrepl test 2024-11-26 10:59:03,239    DEBUG] clickhouse table structure: TableStructure(fields=[TableField(name='id', field_type='Int64', parameters=''), TableField(name='first_name', field_type='Nullable(String)', parameters=''), TableField(name='last_name', field_type='Nullable(String)', parameters=''), TableField(name='phone_number', field_type='Nullable(String)', parameters=''), TableField(name='email', field_type='Nullable(String)', parameters=''), TableField(name='user_settings_id', field_type='Int64', parameters=''), TableField(name='created_at', field_type='DateTime64', parameters=''), TableField(name='updated_at', field_type='DateTime64', parameters=''), TableField(name='deleted_at', field_type='Nullable(DateTime64)', parameters='')], primary_keys='', primary_key_ids=0, table_name='users')
[dbrepl test 2024-11-26 10:59:03,239    ERROR] unhandled exception
Traceback (most recent call last):
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 180, in run
    self.perform_initial_replication()
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 239, in perform_initial_replication
    self.perform_initial_replication_table(table)
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 285, in perform_initial_replication_table
    primary_key_types = [field_types[key_idx] for key_idx in primary_key_ids]
                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
TypeError: 'int' object is not iterable
Traceback (most recent call last):
  File "/.direnv/python-3.11.2/bin/mysql_ch_replicator", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/main.py", line 152, in main
    run_db_replicator(args, config)
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/main.py", line 98, in run_db_replicator
    db_replicator.run()
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 180, in run
    self.perform_initial_replication()
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 239, in perform_initial_replication
    self.perform_initial_replication_table(table)
  File "/.direnv/python-3.11.2/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 285, in perform_initial_replication_table
    primary_key_types = [field_types[key_idx] for key_idx in primary_key_ids]
                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
TypeError: 'int' object is not iterable

[bakwc]

Could you please restart replication from scratch? Updated version changed internal format unfortunately, so you need to remove state and restart replication.
Need to remove directory that is set in config, (binlog_replicator data_dir)

binlog_replicator:
  data_dir: "YOUR_DIR"

[Stanpol]

My bad, removing the binlog directory solved the problem. Updated my CI/CD to clean the Persistent Volume for the app between releases.

Thanks a lot for your help!

[bakwc]

Updated my CI/CD to clean the Persistent Volume for the app between releases.

Usually you don't need to clean it, as restarting replication could take some time to copy all the data. For the future we will keep it backward-compatible to avoid issues like this.

[Stanpol]

You can write to the binlog directory the app version and if it is compatible with the running version (does the directory need to be cleaned or not). So the app can show warning to the user or clean the directory by itself.