Here's something about MySQL replication error. The exact error could be as below:
Could not execute Update_rows event on table DBName.tableName; Can't find record in 'tableName', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.01, end_log_pos 218
If SQL Thread on one of your slave stops with this error code, that just mean Master and this Slave are not in sync!! There must be an inconsistency.
In our case, slave got an Update event form master, but that particular record was not available in the slave. (Of course, that was deleted on Master n Slave separately)
You would get this error only if the binlog_format is set to ROW_BASED or MIXED mode.
So, now check that particular binlog at that position where replication stopped with this error.
When you convert the binlog with mysqlbinlog command, you may see some junk characters where you were expecting some DMLs which caused the error(In our case its an Update statement).
So you can use below command to translate binlog completely:
mysqlbinlog --base64-output=DECODE-ROWS --verbose mysql-bin.01 >mysql-bin.01.txt
It is not a good practice to do manual updates on slave server, it has to be from master through the IO thread. Because the order in which the slave executes the statement may get changed and would cause errors like this.
ReplyDeleteYes starting from 5.1, the ROW binlog was introduced, so binlog written with ROW format should be viewed by using "--base64-output=DECODE-ROWS", where as it is not the case in 5.0 which used to be only STATEMENT based binlog.
Oh okk.. That's a good piece of information..
DeleteThank uu..:)