Replication stops with missing transaction; need option to reload data without schema drop

[simplecrm-github]

Replication between MySQL and ClickHouse using mysql_ch_replicator fails with
transaction not found error, even though the corresponding binlog file still exists in the MySQL data directory.

When replication stops due to this error, data goes missing in ClickHouse. I do not want to recreate ClickHouse table structures because I have already modified them, but I want to re-sync data, even from scratch, also fine, but without modifying the database and underlying tables in ClickHouse.

---

Steps to Reproduce

1. Start mysql_ch_replicator with replication configured.

2. Allow replication to run until MySQL binlog rotation occurs.

3. Restart or resume the replicator.

4. Observe error:

   ERROR transaction not found ...

---

Expected Behavior

Replication should continue seamlessly as long as binlog files exist.
Ability to resync data from MySQL without dropping/recreating existing ClickHouse table structures.

---

Actual Behavior

Replication fails with transaction not found.
Data is missing in ClickHouse if replication is stopped.
Current recovery requires resync from scratch, including the schema with data, which is not ideal.

could you please suggest a way if data can resync without modifying the tables in clickhouse?

[bakwc]

Allow replication to run until MySQL binlog rotation occurs.

Yeah, probably we don't handle the case when binlog order changes in mysql, and this could probably happens from time to time. We will add handling this case better. We have met similar case but maybe once in a half a year, and we just restarted replication from scratch.

if data can resync without modifying the tables in clickhouse?

We have to create temporary databases and rename them to avoid having downtime for user-facing data etc. So we have to be able to create all tables by script. Could you please describe your manual schema modifications? We could try to support having this schema modifications in config so they will be automatically applied during recovery process.
Also - having all schema stored in config is more reliable then having some manual non saved changes.

[simplecrm-github]

We are getting the following transaction not found eventhough it exists in mysql.

raise Exception('transaction not found', transaction_id)
Oct 03 11:43:19 FHLCINCRMARCHDB mysql_ch_replicator[1577598]: Exception: ('transaction not found', ('binlog.000161', 233563394))
Oct 03 11:43:19 FHLCINCRMARCHDB mysql_ch_replicator[1577525]: [runner 2025-10-03 11:43:19,812 WARNING] Process dead (exit code: 1), restarting: < /usr/local/bin/mysql_ch_replicator --config /datadisk/settings.yaml --db fortis_live db_replicator >

in mysql we have the following binlogs retain for 10 days.

-rw-r----- 1 mysql mysql 501M Sep 23 18:59 binlog.000148
-rw-r----- 1 mysql mysql 95M Sep 23 22:03 binlog.000149
-rw-r----- 1 mysql mysql 501M Sep 24 23:20 binlog.000150
-rw-r----- 1 mysql mysql 501M Sep 25 21:05 binlog.000151
-rw-r----- 1 mysql mysql 501M Sep 26 18:13 binlog.000152
-rw-r----- 1 mysql mysql 501M Sep 27 15:18 binlog.000153
-rw-r----- 1 mysql mysql 501M Sep 28 18:02 binlog.000154
-rw-r----- 1 mysql mysql 501M Sep 29 17:35 binlog.000155
-rw-r----- 1 mysql mysql 501M Sep 30 16:34 binlog.000156
-rw-r----- 1 mysql mysql 501M Oct 1 14:45 binlog.000157
-rw-r----- 1 mysql mysql 501M Oct 1 20:50 binlog.000158
-rw-r----- 1 mysql mysql 501M Oct 2 02:04 binlog.000159
-rw-r----- 1 mysql mysql 519M Oct 2 06:14 binlog.000160
-rw-r----- 1 mysql mysql 501M Oct 2 07:14 binlog.000161
-rw-r----- 1 mysql mysql 501M Oct 2 11:27 binlog.000162
-rw-r----- 1 mysql mysql 501M Oct 2 16:39 binlog.000163
-rw-r----- 1 mysql mysql 501M Oct 3 02:04 binlog.000164
-rw-r----- 1 mysql mysql 426M Oct 3 11:57 binlog.000165
-rw-r----- 1 mysql mysql 288 Oct 3 02:04 binlog.index

Kindly help?

[bakwc]

You also need to increase binlog storage time for mysql_binlog_replicator itself:

...
binlog_replicator:
  ...
  binlog_retention_period: 43200  # optional, how long to keep binlog files in seconds, default 12 hours
  ...

Currently it's only 12 hours, so if you had downtime for some reason more than 12 hours, it would not find old transaction unfortunately.

[simplecrm-github]

Hi, I dont have any downtime. The sync has been running for a few days, but it's shown this error - transaction not found error without any reason, even though the binlog files exist in mysql

How to fix this?

[simplecrm-github]

If I remove files inside /binlog/ can they sync everything without dropping the existing tables?

[bakwc]

You should find the previous error in logs. Transaction not exist error can happen in following cases:

1. There was some Error / Exception that prevented some binlog entry from being applied. You need to find this error in logs and share it here. Probably there was some unsupported migration or anything else.
2. There was some downtime.

can they sync everything without dropping the existing tables?

It sync into temporary database and then renamed into target database.

[simplecrm-github]

Log shows only the following error

Oct 03 16:29:29 FHLCINCRMARCHDB mysql_ch_replicator[1623002]: File "/usr/local/lib/python3.11/site-packages/mysql_ch_replicator/binlog_replicator.py", line 186, in get_file_with_transaction
Oct 03 16:29:29 FHLCINCRMARCHDB mysql_ch_replicator[1623002]: raise Exception('transaction not found', transaction_id)
Oct 03 16:29:29 FHLCINCRMARCHDB mysql_ch_replicator[1623002]: Exception: ('transaction not found', ('binlog.000161', 233563394))

can this help to identify the root cause?

[bakwc]

No, this is what happen when replication failing for more than 12 hours. Please share the first error from logs.
I expect there is a lot of errors about missing transaction, but before the first error should be another. Please share the part of log where there were success logs, and then suddenly some error appeared.

[simplecrm-github]

(version 25.6.4.12 (official build))
2025.10.03 14:50:49.435147 [ 1606093 ] {d6887a4a-6e54-4527-83cf-214e6e16de19} executeQuery: Code: 62. DB::Exception: Syntax error: failed at position 119 (where) (line 2, col 1): where aos_quotes.deleted=0. Expected one of: Query, Query with output, EXPLAIN, EXPLAIN, SELECT query, possibly with UNION, list of union elements, SELECT query, subquery, possibly with UNION, SELECT subquery, SELECT query, WITH, FROM, SELECT, SHOW CREATE QUOTA query, SHOW CREATE, SHOW [FULL] [TEMPORARY] TABLES|DATABASES|CLUSTERS|CLUSTER|MERGES 'name' [[NOT] [I]LIKE 'str'] [LIMIT expr], SHOW, SHOW COLUMNS query, SHOW ENGINES query, SHOW ENGINES, SHOW FUNCTIONS query, SHOW FUNCTIONS, SHOW INDEXES query, SHOW SETTING query, SHOW SETTING, EXISTS or SHOW CREATE query, EXISTS, DESCRIBE FILESYSTEM CACHE query, DESCRIBE, DESC, DESCRIBE query, SHOW PROCESSLIST query, SHOW PROCESSLIST, CREATE TABLE or ATTACH TABLE query, CREATE, ATTACH, REPLACE, CREATE DATABASE query, CREATE VIEW query, CREATE DICTIONARY, CREATE LIVE VIEW query, CREATE WINDOW VIEW query, ALTER query, ALTER TABLE, ALTER TEMPORARY TABLE, ALTER DATABASE, RENAME query, RENAME DATABASE, RENAME TABLE, EXCHANGE TABLES, RENAME DICTIONARY, EXCHANGE DICTIONARIES, RENAME, DROP query, DROP, DETACH, TRUNCATE, UNDROP query, UNDROP, CHECK ALL TABLES, CHECK TABLE, KILL QUERY query, KILL, OPTIMIZE query, OPTIMIZE TABLE, WATCH query, WATCH, SHOW ACCESS query, SHOW ACCESS, ShowAccessEntitiesQuery, SHOW GRANTS query, SHOW GRANTS, SHOW PRIVILEGES query, SHOW PRIVILEGES, BACKUP or RESTORE query, BACKUP, RESTORE, INSERT query, INSERT INTO, USE query, USE, SET ROLE or SET DEFAULT ROLE query, SET ROLE DEFAULT, SET ROLE, SET DEFAULT ROLE, SET query, SET, SYSTEM query, SYSTEM, CREATE USER or ALTER USER query, ALTER USER, CREATE USER, CREATE ROLE or ALTER ROLE query, ALTER ROLE, CREATE ROLE, CREATE QUOTA or ALTER QUOTA query, ALTER QUOTA, CREATE QUOTA, CREATE ROW POLICY or ALTER ROW POLICY query, ALTER POLICY, ALTER ROW POLICY, CREATE POLICY, CREATE ROW POLICY, CREATE SETTINGS PROFILE or ALTER SETTINGS PROFILE query, ALTER SETTINGS PROFILE, ALTER PROFILE, CREATE SETTINGS PROFILE, CREATE PROFILE, CREATE FUNCTION query, DROP FUNCTION query, CREATE WORKLOAD query, DROP WORKLOAD query, CREATE RESOURCE query, DROP RESOURCE query, CREATE NAMED COLLECTION, DROP NAMED COLLECTION query, Alter NAMED COLLECTION query, ALTER, CREATE INDEX query, DROP INDEX query, DROP access entity query, MOVE access entity query, MOVE, GRANT or REVOKE query, REVOKE, GRANT, CHECK GRANT, CHECK GRANT, EXTERNAL DDL query, EXTERNAL DDL FROM, TCL query, BEGIN TRANSACTION, START TRANSACTION, COMMIT, ROLLBACK, SET TRANSACTION SNAPSHOT, Delete query, DELETE, Update query, UPDATE. (SYNTAX_ERROR) (version 25.6.4.12 (official build)) (from [::ffff:172.32.4.25]:50734) (in query: -- Metabase:: userID: 3 queryType: native queryHash: 19aa310132e3bdc8fc8b236c22685e2a351e27bc82f6c7b7c3186771e82ff4cd
where aos_quotes.deleted=0), Stack trace (when copying this message, always include the lines below):

I can see some issues with syntax errors as attached above. Can this help?

[bakwc]

This looks like clickhouse exception, we need full exception from mysql_ch_replication log.

[simplecrm-github]

[dbrepl fortis_live 2025-10-02 15:03:28,071 ERROR] unhandled exception
Traceback (most recent call last):
File "/usr/local/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 202, in run
self.run_realtime_replication()
File "/usr/local/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator.py", line 241, in run_realtime_replication
self.realtime_replicator.run_realtime_replication()
File "/usr/local/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator_realtime.py", line 73, in run_realtime_replication
self.handle_event(event)
File "/usr/local/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator_realtime.py", line 94, in handle_event
event_handlersevent.event_type
File "/usr/local/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator_realtime.py", line 185, in handle_query_event
self.handle_alter_query(query, event.db_name)
File "/usr/local/lib/python3.11/site-packages/mysql_ch_replicator/db_replicator_realtime.py", line 199, in handle_alter_query
self.replicator.converter.convert_alter_query(query, db_name)
File "/usr/local/lib/python3.11/site-packages/mysql_ch_replicator/converter.py", line 629, in convert_alter_query
self.__convert_alter_table_add_column(db_name, table_name, tokens)
File "/usr/local/lib/python3.11/site-packages/mysql_ch_replicator/converter.py", line 725, in __convert_alter_table_add_column
raise Exception('wrong tokens count', tokens)
Exception: ('wrong tokens count', ['INDEX(date_entered)'])
[dbrepl fortis_live 2025-10-02 15:03:29,511 INFO] launched db_replicator
[dbrepl fortis_live 2025-10-02 15:03:29,522 INFO] running realtime replication from the position: ('binlog.000161', 233563394)
[dbrepl fortis_live 2025-10-02 15:03:30,982 INFO] set position to /binlog/fortis_live/182.bin
[dbrepl fortis_live 2025-10-02 15:03:31,048 INFO] found transaction ('binlog.000161', 233563394) inside /binlog/fortis_live/182.bin
[dbrepl fortis_live 2025-10-02 15:03:31,063 ERROR] unhandled exception

pls find the log

[bakwc]

Yeah, this is the issue. Exception: ('wrong tokens count', ['INDEX(date_entered)']). Will try to reproduce and prepare a fix.
Anyway, currently the only way is to restart replication from scratch.

To avoid issues like this - always check the migrations you apply to mysql on some dev environment first:

WARNING. While 95% of operations supported, there could be still some unhandled operations. We try to support all of them, but for your safety, please write the CI/CD test that will check your migrations. Test should work a following way:

Aplly all your mysql migrations
Try to insert some record into mysql (to any table)
Check that this record appears in ClickHouse

[simplecrm-github]

can you please fix this issue immediately as i need this applied in production.