Replication

Replication handler error HA_ERR_KEY_NOT_FOUND and Memory Tables In Replication

 Last_SQL_Error: Could not execute <Update/insert/delete>_rows event on table <dbname>.<tablename>; Can’t find record in ‘<tablename>’, Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event’s master log <master binary log file name>, end_log_pos <master log file position>

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.

mem_rpl

Above diagram illustrating stages before and after mysqld service restart on the slave.

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.

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.

All set !!

Source link

Leave a Reply

Your email address will not be published. Required fields are marked *