Memory tables do not play well with replication.
The Problem
After upgrading MySQL server from 5.6 to 5.7, we noticed that Master/Slave replication started to fail with the following error:
Could not execute Delete_rows event on table my_database.my_table; Can’t find record in ‘my_table’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log bin-log.003023, end_log_pos 552195868
If we restart the slave, we lose content of our MEMORY tables, and MySQL replication breaks.
Working Towards the Solution
MySQL Binary Logging: MySQL 5.6 vs MySQL 5.7
Prior to MySQL 5.7.7, the default binlog_format was STATEMENT. That’s what we used before the upgrade.
In MySQL 5.7.7 and later, the default is ROW. This is what we have after the upgrade.
Now, on MySQL 5.6, STATEMENT replication will often continue to run, with contents of the table just being different as there is a little checks whenever statements produce the same results on the slave.
ROW replication, however, will complain about a non-existent ROW for UPDATE or DELETE operation.
Workaround: use SQL_SLAVE_SKIP_COUNTER
When replication is broken because a row was not found and it cannot be deleted, we can do the following:
STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
This will skip the offending statement and resume replication. Be careful with it! In our case it’s fine, because the application logic is such that the contents of MEMORY tables can be safely lost (the table in question is used for caching).
Note that this approach is not a solution, because our relication will get broken as soon as there is another update or delete statement that affects MEMORY tables.
Solution: do not replicate MEMORY tables
If we don’t need MEMORY tables on the slave, then we can stop replicating them.
We need to create a replication filter which keeps the slave thread from replicating a statement in which any table matches the given wildcard pattern.
In our case, we would use the following:
--replicate-wild-ignore-table="my_database.my_table"
If we have more than one database that has this problem, we can use a wildcard:
--replicate-wild-ignore-table="%.my_table"
The above will not replicate updates that use a table where the database name is any, and the table matches “my_table”.
This can be done on the fly as well:
STOP SLAVE;
CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('%.my_table');
START SLAVE;
References
https://www.percona.com/blog/2010/10/15/replication-of-memory-heap-tables/
https://dev.mysql.com/doc/mysql-replication-excerpt/5.7/en/replication-features-memory.html