Why mysqldump is converting my tables from InnoDB to MyISAM?

First of all: mysqldump is not converting tables. It is something else. Here is the story:

One of my clients had a case when they were migrating to a new mysql server: they used mysqldump to export data from the old server (all InnoDB) and imported it to the new server. When finished, all the tables became MyISAM on the new server. So they asked me this question:
“Why mysqldump is converting my tables from InnoDB to MyISAM?”

First of all we made sure that the tables are InnoDB on the old server. It was true.
Second we run “show engines” on the new server:

+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+


As we see, there is no InnoDB in the list. So, InnoDB was not started.
Next we look into the error log to find out, why InnoDB was not started. And we saw this:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 134217728 bytes!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[Note] Event Scheduler: Loaded 0 events
[Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.51-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

So, InnoDB was not stared as the size of log files was changed in the my.cnf and the old log files were not moved. Although, mysql server was started, but without InnoDB. In this case mysql restored the tables, but the storage engine was substituted from InnoDB to MyISAM. For example if we create a table with non-existing storage engine, MySQL will use MyISAM instead:


mysql> create table aaa(i int) engine=non_existing_engine;
Query OK, 0 rows affected, 2 warnings (0.16 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1286 | Unknown table engine 'non_existing_engine' |
| Warning | 1266 | Using storage engine MyISAM for table 'aaa' |
+---------+------+---------------------------------------------+
2 rows in set (0.00 sec)

That was happened: mysql used MyISAM instead of InnoDB, produced warnings, but they are usually ignored.

The fix was easy: restart mysql using this instructions (http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html) and upload the dump again (or convert myisam to innodb manually).

UPDATE: To prevent this in the future you can do 2 things (thanks Shantanu for pointing this out):

  1. If using innodb plugin and mysql 5.1 add this to my.cnf: innodb=FORCE. In this case MySQL will not start if InnoDB failed to start:

    InnoDB: Error: log file ./ib_logfile0 is of different size 0 536870912 bytes
    InnoDB: than specified in the .cnf file 0 53477376 bytes!
    [ERROR] Plugin 'InnoDB' init function returned error.
    [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    [ERROR] Failed to initialize plugins.
    [ERROR] Aborting

  2. use sql_mode=NO_ENGINE_SUBSTITUTION:


    mysql> set sql_mode=NO_ENGINE_SUBSTITUTION;
    Query OK, 0 rows affected (0.00 sec)

    mysql> create table aaa(i int) engine=non_existing_engine;
    ERROR 1286 (42000): Unknown table engine 'non_existing_engine'

4 comments to Why mysqldump is converting my tables from InnoDB to MyISAM?

  • Start mysql with strict sql mode.

    sql_mode=NO_ENGINE_SUBSTITUTION

    This seems to be related to a bug already reported and closed.

    http://bugs.mysql.com/bug.php?id=19027

  • Shantanu,

    Thank you for your comment. Exactly, I forgot to add this bit, see update.

  • There is a better way to avoid such issues.

    I changed the default engine to InnoDB in my.cnf file and now mysql won’t start if InnoDB is disabled due to some reason.
    # default-storage-engine=InnoDB

  • Shantanu,

    “default-storage-engine=InnoDB” is a good option. In one case thou it can affect performance of your queries:
    If your queries will create temporary tables with “create temporary table aaa (i int … )” without specifying storage engine, InnoDB will be used for temporary tables. Innodb for temp tables can be slow, especially with InnoDB_file_per_table option (when each temporary table will create its own tablespace).