Despite promising performance results in testing, we stopped developing this branch since Nov 2024 after running into intermittent data inconsistency problems in internal replica tests. I believe I've tracked down the source of this issue. Below is the investigation for anyone interested.

Investigation

The data inconsistency appeared intermittently on several different ghost testing replicas running the MTR version.
I was able to reproduce the error locally using a dockerlocaltest with sysbench write load. The data inconsistency happens pretty reliably with ~900 trx/sec on the table.

The test table looks like this:

CREATETABLE `sbtest1` (`id`intNOT NULL AUTO_INCREMENT,`k`intNOT NULL DEFAULT'0',`c`char(120)NOT NULL DEFAULT'',`pad`char(60)NOT NULL DEFAULT'',PRIMARY KEY (`id`),  KEY`k_1` (`k`));

The testing result is checksum mismatch (usually only one row). In this case the row withid=5025. The second columnk is wrong. I ran the test withgeneral_log='ON' on the primary and replica to see what is going on. Here are the last two transactions onsbtest1 from sysbench affecting row5025:

-- 2025-04-12T03:28:44.209273Z        25 ExecuteBEGIN;DELETEFROM sbtest1WHERE id=5025;INSERT INTO sbtest1 (id, k, c, pad)VALUES (5025,5046,'55585975399-51936995975-90609908571-88981758242-41639509045-49015163211-63909390173-09873895014-17528416149-59787710722','90699347551-90936038435-69760642136-45340328341-67205199431');COMMIT;-- 2025-04-12T03:28:44.209695Z        28 ExecuteUPDATE sbtest1SET k=k+1WHERE id=5025;

And corresponding binlog events on the replica

last_committed=89053    sequence_number=89058DELETE FROM `test`.`sbtest1`WHERE  @1=5025  @2=4993  @3='72162371109-65711525437-30164254657-02236716337-47638530925-52423543892-06270192544-11372615750-04017656641-19388264173'  @4='44029122667-48848103638-83352868135-91599152925-97809617080'# at 145248382INSERT INTO `test`.`sbtest1`SET  @1=5025  @2=5046  @3='55585975399-51936995975-90609908571-88981758242-41639509045-49015163211-63909390173-09873895014-17528416149-59787710722'  @4='90699347551-90936038435-69760642136-45340328341-67205199431'# at 145248672...last_committed=89062    sequence_number=89065UPDATE `test`.`sbtest1`WHERE  @1=5025  @2=5046  @3='55585975399-51936995975-90609908571-88981758242-41639509045-49015163211-63909390173-09873895014-17528416149-59787710722'  @4='90699347551-90936038435-69760642136-45340328341-67205199431'SET  @1=5025  @2=5047  @3='55585975399-51936995975-90609908571-88981758242-41639509045-49015163211-63909390173-09873895014-17528416149-59787710722'  @4='90699347551-90936038435-69760642136-45340328341-67205199431'# at 145258107

So, the correct value isk=5047. But on_sbtest1_gho the order of the transactions is switched. First gh-ost updates the row tok=5047 then deletes and reinserts it withk=5046, resulting in wrong final valuek=5046.

We can look at the dependency (sub)graph for the original transactions onsbtest1 (sequence numbers89058 and89065). It looks like this:

graph LR;     89058--> 89053;     89053--> 89050;     89065--> 89062;     89062--> 89053;

This means once transaction89053 and89062 finish, the coordinator may schedule89058 and89065 concurrently.

Comparing to what the MySQL replication applier coordinator does (sql/rpl_rli_pbd.cc), I realized that a transaction should be scheduled if and only iflowWaterMark >= lastCommitted.

ThelastCommitted of a transaction is itsmost recent (i.e. greatest) dependent transaction. ThelowWaterMark is a global variable that maintains the invariant that all sequence numbers <= lowWaterMark are complete. Therefore if we schedule a transaction whenlastCommitted > lowWaterMark it is possible it has dependent transactions that haven't completed, even iflastCommitted is complete.

In the example,89065 must wait untillowWaterMark >= 89062, at which point it's guaranteed that89058 completed.

Fix

In our Coordinator, the culprit is this line inWaitForTransaction:

After removing these lines the sysbench localtest is consistently passing.

Hi@meiji163, my team has been running into the exact limits you describe in this PR, so we were very excited to see the progress already made here. However, over the last 2 weeks I've run some tests with this PR and still notice a data inconsistency issue.

We use gh-ost on AWS Aurora, and my testing has been with a production-like setup. On the table we're trying to migrate the only interesting queries (not SELECTs) are fairly simple: we insert rows and update some of them fairly quickly to remove a timestamp value.

The inconsistency I see is caused by some UPDATEs not being applied to the ghost table. This happens infrequently but consistently throughout the tests (50 rows affected out of ~2 million).
Here's an example problematic row:

#250516 13:06:46 server id 1951482084  end_log_pos 26539240 CRC32 0x364830a9   Anonymous_GTID  last_committed=1822 sequence_number=1824    rbr_only=yes    original_committed_timestamp=1747393606597218   immediate_commit_timestamp=1747393606597218 transaction_length=3944# at 26539538### INSERT INTO `DB_ACTIVE`.`message`### SET###   @1=39181433--#250516 13:06:46 server id 1951482084  end_log_pos 26589750 CRC32 0x2b5cf4fe   Anonymous_GTID  last_committed=1867 sequence_number=1868    rbr_only=yes    original_committed_timestamp=1747393606648774   immediate_commit_timestamp=1747393606648774 transaction_length=7447# at 26590057### UPDATE `DB_ACTIVE`.`message`### WHERE###   @1=39181433###   @52=1747480006593### SET###   @1=39181433###   @52=NULL--#250516 13:06:46 server id 1951482084  end_log_pos 28141453 CRC32 0xd8e3f39c   Anonymous_GTID  last_committed=2029 sequence_number=2030    rbr_only=yes    original_committed_timestamp=1747393606747371   immediate_commit_timestamp=1747393606747371 transaction_length=3949# at 28141756### INSERT INTO `DB_ACTIVE`.`_message_gho`### SET###   @1=39181433

It doesn't quite look like the same issue you ran into and described above because the dependency graph looked fairly linear:

last_committed=1822 sequence_number=1824 -- INSERTlast_committed=1824 sequence_number=1825last_committed=1825 sequence_number=1826last_committed=1826 sequence_number=1828last_committed=1828 sequence_number=1832last_committed=1832 sequence_number=1836last_committed=1836 sequence_number=1837last_committed=1837 sequence_number=1840last_committed=1840 sequence_number=1844last_committed=1844 sequence_number=1854last_committed=1854 sequence_number=1856last_committed=1856 sequence_number=1857last_committed=1857 sequence_number=1858last_committed=1858 sequence_number=1862last_committed=1862 sequence_number=1863last_committed=1863 sequence_number=1866last_committed=1866 sequence_number=1867last_committed=1867 sequence_number=1868 -- UPDATE

Do you have any ideas what could be the issue, and is there anything else I can provide?

Thank you!

Thanks for reporting this@TomKnaepen, I also ran into more data consistency issues after the latest changes, but I haven't had time to investigate further. We thought it might be related to the binlogsyncer connection being closed and reopened. Is there any error in your gh-ost output?

I don't recall seeing any errors at the time; I ran some new tests on a smaller scale this week to see if I could find anything but there's no errors or anything noteworthy in the gh-ost output.
Tested both revisions2eacc78 and7c3032f since I thought you might be referring to that commit, but both resulted in inconsistent data.