Mysql – Why did the MySQL Storage Engine Change on Restore in one scenario

innodbmyisamMySQL

In my production database life, there was one tragic incident when the MySQL engine type was accidentally changed from InnoDB to MyISAM on restoring a recent backup. The result was that all referential integrity were lost.

What could have caused this incident? My production database is MySQL Server 5.1.

Best Answer

This brings to mind something I have done a dozen times (fortunately with small datasets less then 10GB). When restoring a mysqldump to a new server, you must make absolutely sure the InnoDB settings on the new server is identical to the InnoDB settings.

For a new server that you have opted to restore a mysqldump to, run this command:

SHOW ENGINES;

This will display all storage engines available in in the MySQL instance. Here is a sample from a server running MySQL 5.5.9:

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

Notice that InnoDB has DEFAULT in the Support Column. There are four possible values for the Support column:

  • YES
  • NO
  • DEFAULT
  • DISABLED

One day, a server I was restoring to had mismatched configurations on the innodb_buffer_pool_size. Because I had set innodb_buffer_pool_size=512G instead of 512M (obvious typo), InnoDB would come up DISABLED. When loading the data into the new server, every CREATE TABLE defaulted to MyISAM upon execution. I didn't notice anything until I saw dozens of DB Connections that were waiting to write to the same table, which was typical of heavy writes against MyISAM. The solution was simply to start over with the correct InnoDB settings and reload.

MORAL OF THE STORY

  1. Always make sure you can restore data to the same configuration as specfied in /etc/my.cnf. If the new server has less diskspace and/or less RAM than the server you mysqldump'd, they carefully configure /etc/my.cnf to the maximum settings that make the most sense for the new server you are restoring to.
  2. Always run SHOW ENGINES; to make sure every storage engine is functioning.
  3. Always read the error log and verify any anomalies with regard to startup, InnoDB crash recovery, fulltext stopword list declarations, MyISAM repair options, the amount of memory requested for buffers and DB Connections.
  4. Always make sure ib_logfile0 and ib_logfile1 are the same size as the one specified in the old server's /etc/my.cnf.
  5. Always use dev and staging DB servers to load old backups and test usability.
Related Question