Replication handler error HA_ERR_KEY_NOT_FOUND and Memory Tables In Replication.

Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Update_rows event on table mamc.id_findloc; Can't find record in 'id_findloc', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log 1.000221, end_log_pos 2256175

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.

In this blog post will discuss support for MEMORY storage engine table with MySQL Replication and few examples with a possible solution. As per official MySQL DOC,  support for replication with memory storage engine is “LIMITED”.

The reason is limited because of the MEMORY storage engine functionality, which stores all data in memory other than table metadata and structure. And this data will get flushed from the memory at the time of MySQL server restart.

Possible Solutions to bring backup replication in sync: [ Without downtime]

Option 1: Set slave_exec_mode to IDEMPOTENT It will suppress duplicate-key and no-key-found errors. Default slave_exec_mode is STRICT  means no such suppression takes place.

mysql> stop slave;

Query OK, 0 rows affected (0.01 sec)

mysql> Set global slave_exec_mode=IDEMPOTENT;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Option 2: Skip memory tables from replication using replication filter “replicate_ignore_table”. From 5.7.3 we can set replication filters dynamically without mysql service restart.

Option 3: If you want to keep data persistently then better to covert this table from MEMORY to InnoDB storage engine type using ALTER TABLE… ENGINE=Innodb command.

Selecting one of the options is depending on what we really need. For example, rather than skipping transaction one by one we can set Set slave_exec_mode to IDEMPOTENT  for the time being to get replication in sync and after that set it back to STRICT again.