dbstore s7 has showed strange problems recently (T104471)

That was not a "strange problem", that was a misconfiguration error due to wrong filtering.

Also, dbstore100[12] issues where different, where a stop slave; start slave; fixed replication because false duplicate key errors.

Possibilities:

• If the delete happened at the time of the previous import, it could be just a re-population error
• If the delete happened just before, maybe there is a b) another misconfiguration (replication filtering) b) a bug (default parallel replication)/upgrade error.

Are we sure this only happens on multi-source hosts? I would say T105879 is a better related error (duplicate key) on a host that was recently updated/restarted.

@Springle Did you do any out-of-band changes to the table after the second replication error? (I do not care if you did, *I did out of band changes to correct the first issue*). Because if you confirm me that you didn't, I can confirm that the DELETE was not executed or it had no effect despite being on the relay log. Current status:

SELECT count(*) FROM `site_identifiers` WHERE si_site IN ('1','2','3','4',...,'879','883') and si_site > '203';
+----------+
| count(*) |
+----------+
|      440 |
+----------+
1 row in set (0.00 sec)

I think we can discard the population issues, at least for this error. Random things that should work but that I throw here as potential offenders:

• filtering (because it happened in the past)
• multisource (because lol)
• strings literals used for in fields (not a reason)
• Package size/max number of "in" items (again, something that should fail, not ignore things silently)

@jcrespo, no, I did not out-of-band change or use skip counter. I found the machine exactly as you described on IRC, and only did research by dumping logs to get the query examples shown in ticket description.

Good point, I had forgotten T105879. The last working theory we had for db1022 on IRC was related to replication starting before mysql_upgrade back on June 29, or something else dto o with the skip counter usage on that day. If we could disprove those theories and definitively link these two issues...

It's strange that this is happening suddenly despite 10.0.16 packages running for months. If there is a replication bug, multi-source is most suspicious ...and the checksum tests become very important priority.

Tests I would do:

• What happens if we do a CHANGE MASTER to the most recent DELETE and START REPLICATION UNTIL the following event id? We could even activate the binary log/general log to check it. But we need to do that soon before the binlogs on the master disappear.
• Can we try again with full disabled parallel replication?
• What are all differences in configuration between these 2 hosts an the others? (pt-config-diff or manually)
• Can we try again with: GTID, filtering disabled, innodb-table-based replication control, ROW replication (that last is problematic, as the master executed the events long time ago).

It's strange that this is happening suddenly despite 10.0.16 packages running for months.

I agree, I restart these 3 machines not a long time ago and then start having issues? My main thesis is conf-related. Replication issues with these hosts started when you restarted these 2 for maintenance the last time, too. Its master was also recently restarted, I think.

checksum tests become very important priority

I couldn't agree more.

Importing now dbstore2002, all connections on the server will have lag while this happens.

Import on dbstore2002 finished. We have a 3-day lag on dbstore2002, but I prefer that than performing another manual import because it will be less error-prone and this way it will validate both its correctness and performance.

Things that were not solved on this ticket that will be tracked separately:

• Drift detection: checked by doing checksums T104459
• Drift solution: done by migrating to ROW based replication T109179
• TokuDB issues: T109069
• Filtering issues: not a concern right now
• Multisource replication issues: not a concern right now