AWS Read Replica fails to sync magento MEMORY-engine table

Question

Our Magento 1.9 database is hosted on AWS RDS. For improving database performance, we created an Amazon Read Replica.

Unfortunately, the read replica runs out of sync. Error messages indicate, that replication tries to delete records from

catalog_product_index_eav_tmp

... but these records don't exist.

Further investigation showed, that this table uses the MEMORY engine, and tables using the MEMORY engine cannot be replicated properly.

https://www.percona.com/blog/2010/10/15/replication-of-memory-heap-tables/ suggests to prevent these tables from being replicated by using the option replicate-ignore-table=db.memory_table . But this option seems to be unavailable in Amazon RDS.

Does anyone have a suggestion of how to get the read replica running in our environment?

Answer 1

After some research it seems that converting those tables using engine "MEMORY" to engine "InnoDB" might work. Although this is not supported, and there seems to be no "official answer", all known facts seem to favour the approach:

Some Magento tables are not InnoDB, is it safe to convert all tables to InnoDB?

Similar problems on stackexchange have no answers either (I found this one only later), hence there might be few (published) experiences regarding the use of read replica:

https://stackoverflow.com/questions/39043787/magento-reindexing-uses-tmp-tables-which-break-mysql-replication

Tables could be found like this:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE (ENGINE = 'Memory'  OR ENGINE='MyIsam') AND TABLE_SCHEMA='magento'

I post this as an answer here (converting the tables), although I am unsure about it - whoever can improve it, can add some experiences or contribute knowledge about disadvantages, feel free to do so!

Answer 2

Workaround to establish replication of deletion on non-exist data in replica

1) Get all memory engine table in the databases: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE (ENGINE = 'Memory') AND TABLE_SCHEMA='';

2) Truncate those memory tables i.e. - truncate table catalog_product_index_eav_tmp

3) Create the new Read Replica