I have MySQL 5.5.19 on both master and slave in a simple master->slave replication setup using MIXED binlog mode. Slave has nothing that writes to the replicated InnoDB database. Isolation is the default REPEATABLE-READ.
Every once a short while I get the 1032 error code, HA_ERR_KEY_NOT_FOUND, on the slave for DELETE operation. After digging into the relay log for the problematic log position, I can verify that the record in question was indeed non-existent in the table. I can also verify that the record had an INSERT query earlier in the binlog, however I don’t know how to verify that it either took place or not just from reading the relay log. Due to that I also cannot tell if the 1032 error was because the record got deleted somehow previously or was never inserted in the first place.
I have tried re-sync the master and slave with fresh dump, lock, reset logs, start position, etc.,… I also checked the problematic table and found the keys and rows are exactly the same on master and slave after the re-synch. However the error still pops up after some hours of normal operation. The servers were up all the time with no other errors or shutdowns.
Is there any way to track how this might have happened? Also is there is way to tell if the particular replication was done in ROW or STATEMENT mode?
This is a fairly new setup so I don’t rule out having less-than-optimal settings in my.cnf… What setting could affect replication to drop/miss?
Thank you in advance.