MySQL row-based-replication HA_ERR_KEY_NOT_FOUND… which record is it

binlogMySQLreplication

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?

Best 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

Related Solutions

Mysql – Error reading MySQL 5.5 binary log using thesqlbinlog

Since the binary log on the Master is corrupt, there is nothing else you can do. My condolences. Just skip to the next binlog:

STOP SLAVE;
CHANGE MASTER TO master_log_file='ibm-pr-slglcd-01.000076',master_log_pos=107;
START SLAVE;

Of course, 107 is the start position for MySQL 5.5 binlogs. If the Master is 5.1 (or 5.0.95), use 106. If the Master is prior to 5.1, use 98.

If Master BinLog Corruption is frequent, you may need to consider using a smaller size for the binary logs on the Master (perhaps 128M instead of the default 1G):

[mysqld]
max_binlog_size=128M

This will not stop corruption, but lowering the size it will minimize data loss from 1G to 128M.

MySQL Replication – pt-table-checksum and Row-Based Replication

I do not understand why an application requires the MIXED replication format, as MIXED is equal to STATEMENT-based, but changing to ROW format when executing "unsafe" queries. So, a MIXED-compatible application should be compatible with both STATEMENT and ROW.

As you can see here, if the the format detected (server-side) is ROW or MIXED, it tries to change it dynamically for the session only. In your case, it complains about one of the slaves here (it requires SUPER privileges).

Assuming you do not have a 3-node chain replication, or you use the slave's binlog for anything, you can ignore the warning with --no-check-binlog-format. No need to change the my.cnf. The question is, will the session-only change to statement affect your "application that requires MIXED"? pt-table-checksum requires its own calculations to be done as statements, as a hash has to be calculated independently on the master and the slave independently and synchronously (not in time, but in relation with other queries).

Best Answer

Related Solutions

Mysql – Error reading MySQL 5.5 binary log using thesqlbinlog

MySQL Replication – pt-table-checksum and Row-Based Replication

Related Question