Database MySQL Backup – Is It Safe to Convert All Magento Tables to InnoDB?

backupdatabasedatabase-updateMySQLtable

I am using AWS RDS Read Replica. It constantly has issues with Magento's Memory engine tables. For backup and read replicas RDS loves InnoDB. Can I safely change all tables to InnoDB?

In addition I get the following warning from AWS:

DB Instance magento-monin-prod-db contains MyISAM tables that have not been migrated to InnoDB. These tables can impact your ability to perform point-in-time restores. Consider converting these tables to InnoDB. Please refer to http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.CommonDBATasks.html#MySQL.CommonDBATasks.Tables

Plausible Answer

Still interested in feedback. I will add this as an answer if I don't find any issues within the next 24 hours. The steps I took below appear to be safe, so far. My biggest concern was Magento's Memory Engine tables (tables ending in_tmp) and the impact it might have on indexing.

Here is what I did:

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

    • For me this returned mostly temporary index tables and magento module tables, so not a lot of critical core tables to be concerned about and few enough tables that I can easily execute another alter table if stuff hits the fan.

  2. For each table returned I executed: Alter table {table-name} ENGINE=InnoDB;

I'd be nervous to try this if none of your tables are InnoDB. But, as I said before, there were only a few core tables on my instance that needed to be modified.

Best Answer

It is fine to change the data type to InnoDB, assuming one of the following is true:

  1. You are using MySQL 5.6.4+ where the InnoDB storage engine supports fulltext search
  2. You are not using the default Magento Search functionality which relies on underlying MyISAM fulltext search capabilities. That functionality is troublesome to begin with and the feature set provided in default Magento Search leaves a lot to be desired so I'd suggest using Lucene or Sphinx or best yet Algolia hosted search.

Personally i'd recommend doing this with the Magento DB Repair Tool to minimize risk and also check for any other DB configuration drift or problems. InnoDB is the ideal engine, it's fulltext limitations notwithstanding.

Related Topic