Last_SQL_Error: Could not execute _rows event on table .; Can’t find record in ‘’, Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event’s master log , end_log_pos.
This is one of the error for MySQL Replication when MASTER and SLAVE replica not in sync. The Reason for replication getting out of sync due to various reasons. But the common cause is particular table row or data not matched/missing on the slave while applying transaction received from MASTER. This transaction can be insert/update/delete type.
Skip MySQL GTID Replication Error
The classic method for skipping master-slave errors won’t work in this particular case as our replication is GTID.
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction
The error above is expected as we’re running a MySQL GTID replication setup.
Let’s have a look on the last transaction executed on the slave that failed:
Executed_Gtid_Set: b2c84f0c-5dcb-11e8-a550-005056847b4c:1-13, bdf3bf63-0cc9-11e8-89e8-000d3a365fa6:52044900-59506053
We know now that the transaction number
59506053 coming from our master server having the ID
Having now the record fixed manually on the slave we can carry on and insert an empty transaction in order to bypass the error. We’ll need to increment the transaction via a SQL query like shown below in order to bypass the error.
mysql> SET GTID_NEXT='bdf3bf63-0cc9-11e8-89e8-000d3a365fa6:59506054';
Now we’ll have to commit our change:
mysql> BEGIN; COMMIT; SET GTID_NEXT='AUTOMATIC';
Start MySQL Slave
Having everything in place like transaction number incremented and change committed we can try to start our GTID slave replication back with the next SQL query:
mysql> START SLAVE;
show slave status\G will look like this:
mysql> SHOW SLAVE STATUS \G ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ...
Check MySQL Slave Replication Status
We know that our slave is running now, receiving data from the master and we can check how far the slave is behind the master like this:
mysql> SHOW SLAVE STATUS \G ... Seconds_Behind_Master: 168356 ...
Seconds_Behind_Master value goes down to
0 seconds then we can say that our slave is fully synced.
mysql> SHOW SLAVE STATUS \G ... Seconds_Behind_Master: 0 ...
Thank you! It helped to resolve the problem on Percona 8.0.23-14. I love you.
Glad you solved the problem! 🙂