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:

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'