For supporting foreign-key constraints better, we allow disabling checking the key integrity: this allows to temporarily violate constraints. The assumption is that if the data in the source DB is valid, we can copy it to the target without verification in chunks (e.g. one table after another) and the constraints will eventually be satisfied.
In the presence of partitioned tables with uniqueness constraints across different shards, this can cause problems, however. Consider table
table test
PK: (col A, col B)
Partition: col B
with a sequence of commands of
timestamp 1: insert into test (A, B) values (1,2);
timestamp 2: <time passes>
timestamp 3: update test set B=3 where A=1;
That is, the application inserts into one partition and then moves the row into a different partition.
If the binlog writer has a replication lag (that is, the time spent at time timestamp 2 above is actually a while), it is possible that there is a race where the data iterator/writer inserts and the binlog writer.
The data iterator will select the data from the source at time timestamp 3 and insert the data into partition B=3. Later, the binlog writer catches up and inserts the value into partition B=2. This is valid, because the checking is not enforced. Later, the binlog writer attempts to update the row and move it from partition B=2 to partition B=3. This fails, as the constraint is enforced.
In addition to the backlog, I think there is an additional requirement that ghostferry is interrupted and does not store the resume date for the table yet, meaning that the second invocation will make the table copy resume at a later point than the initial invocation (which triggers the copy of a row that is "older" than what the binlog writer considers and that data is copied in reverse order
For supporting foreign-key constraints better, we allow disabling checking the key integrity: this allows to temporarily violate constraints. The assumption is that if the data in the source DB is valid, we can copy it to the target without verification in chunks (e.g. one table after another) and the constraints will eventually be satisfied.
In the presence of partitioned tables with uniqueness constraints across different shards, this can cause problems, however. Consider table
with a sequence of commands of
That is, the application inserts into one partition and then moves the row into a different partition.
If the binlog writer has a replication lag (that is, the time spent at time
timestamp 2above is actually a while), it is possible that there is a race where the data iterator/writer inserts and the binlog writer.The data iterator will select the data from the source at time
timestamp 3and insert the data into partitionB=3. Later, the binlog writer catches up and inserts the value into partitionB=2. This is valid, because the checking is not enforced. Later, the binlog writer attempts to update the row and move it from partitionB=2to partitionB=3. This fails, as the constraint is enforced.In addition to the backlog, I think there is an additional requirement that ghostferry is interrupted and does not store the resume date for the table yet, meaning that the second invocation will make the table copy resume at a later point than the initial invocation (which triggers the copy of a row that is "older" than what the binlog writer considers and that data is copied in reverse order