I have a mysql slave (5.1) using row-based replication. The slave has stopped with the error HA_ERR_KEY_NOT_FOUND. OK fine, it's trying to update a record that doesn't exist for some reason. Using the master's binlog position, I use mysqlbinlog -vv to read the binlog... but the transaction in question updates at least 50 records! How can I tell specifically which record/PK is missing? MySQL has to know, but doesn't appear to tell me anywhere?
Add a comment
|
1 Answer
I think you should re-initialize your replication.
If the replication worked good, the rows should not be missing. That the rows are missing means there is some inconsistency between your master und your slave. I would not act on the assumption that this is the only error and if I'd fix it by hand everything is back fine again. Instead I would set up the replication from the start again and make sure it stays synced this time, to be sure to get rid of all inconsistencies.
Nevertheless there are some ways to identify which rows are missing. As a starting point you could take a look at this blogpost. This basically includes "decode the relay log" and "search for the current relay log position". One can decode the log for example with this statement:
mysqlbinlog mysql-relay-bin.000001 –base64-output=decode-rows -v > decoded.log
answered Mar 1, 2014 at 11:30
-
This may also be a bug. Just encountered the same thing after a mariadb upgrade with two way replication that had been stable for years. Looking at the table and the log, everything is perfectly fine. The query is valid and the supposedly missing row is there.– MantriurOct 16, 2022 at 12:18
-
@Mantriur You upgraded in the correct order? mariadb.com/kb/en/setting-up-replication/#versions Oct 17, 2022 at 4:11