MySQL slave Error_code: 1032 | MySQL slave drift | HA_ERR_KEY_NOT_FOUND

MySQL slave Error_code: 1032 | MySQL slave drift:

With several MySQL, instance with master slave replication, I have one analytics MySQL, environment which is larger in terabytes, compared to other MySQL instances in the environment. Other MySQL instances with terabytes of data are running fine master, slave replication. But this analytics environment get started generating slave Error_code :1032.

mysql> show slave status;

Near relay log: Error_code: 1032; Can't find record in '<table_name>', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log <name>-bin.000047, end_log_pos 5255306

Near master section:
Could not execute Update_rows event on table <db_name>.<table_name>; Can't find record in '<table_name>', Error_code: 1032; Can't find record in '<table_name>', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log <name>-bin.000047, end_log_pos 5255306

Retrieved_Gtid_Set: 08a4856c-47a4-11ea-93aa-0050569a2a89:54-3732
Executed_Gtid_Set: 08a4856c-47a4-11ea-93aa-0050569a2a89:1-252

MySQL error log:

[ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log '<name>-bin.000047' position 195
[Warning] [MY-010584] [Repl] Slave: Can't find record in '<table_name>' Error_code: MY-001032

Identify statements using mysqlbinlog:

Identify statement breaking slave replication using mysqlbinlog utility

$mysqlbinlog -v --include-gtids='08a4856c-47a4-11ea-93aa-0050569a2a89:54-3732' /mysql/ binlogs/<name>-bin.000047 --stop-position=5255306 | more

Here I notices huge no insert statements and huge no. of update statements for the same table in the same database.

### INSERT INTO `<db_1>`.`<ta
ble_1>`
### UPDATE `<db_1>`.`<table_1>`BINLOG '

$ mysqlbinlog -v --include-gtids='08a4856c-47a4-11ea-93aa-0050569a2a89:54-3732' /mysql/binlogs/<name>-bin.000047 --stop-position=195 | more

Here I was able to notice the same message in “show slave status” in executed GTID set.
Previous-GTIDs # 08a4856c-47a4-11ea-93aa-0050569a2a89:1-252
Retrieve Table Situation:
mysql> SHOW CREATE TABLE <table_name> \G
mysql> SHOW INDEX FROM <table_name> \G 
mysql> SHOW TABLE STATUS WHERE NAME='<table_name>' \G

Conclusion:

In this situation, I realized the table does not have primary key and application design does not suit with the use of primary key, so in absence of primary key on table slave was not able to catch up with master, slave was always remain behind with huge gap that is noticeable from the “show slave status”. In this situation on slave DML statement has to scan entire table, each row of the table, to find out the matching rows. That is the reason why slave is not able to catch up master for the table having millions of rows.

Retrieved_Gtid_Set: 08a4856c-47a4-11ea-93aa-0050569a2a89:54-3732
Executed_Gtid_Set: 08a4856c-47a4-11ea-93aa-0050569a2a89:1-252
Explore Primary Key Check implementation at slave to avoid Error 1032 because of absence of primary key for table on slave. 

Multiple reasons for Error_code 1032:

• Error_code: 1032; Can't find record in '<table_name>', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
• slave has already deleted that row
  slave is waiting for the correct portion of the Binary Log to arrive in order to create that row
• slave never owned a copy of that row and never will
• slave is build using inconsistent backup
• replication started with wrong  log position
  

Explore more:

https://severalnines.com/blog/mysql-tutorial-troubleshooting-mysql-replication-part-2 https://bugs.mysql.com/bug.php?id=55508
https://jfg-mysql.blogspot.com/2017/08/danger-no-pk-with-RBR-and-mariadb-protection.html
https://www.percona.com/blog/2018/05/03/slave-performance-too-slow-with-row-based-events-causes-and-workarounds/