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

by

The question:

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.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

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.

Method 2

Afaik you should not convert all tables to InnoDB.

catalogsearch_fulltext should stay MyISAM, because InnoDB has no fulltext search support, at least not until MySQL 5.6 (iirc).

For all other tables, though, it should be safe.

Method 3

I just changed the MySQL default engine to InnoDB and most of my Magento tables just miraculously transformed themselves to InnoDB (a few are still MyISAM and some are Memory).

Just thought I’d share this…


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment