title: Error trying to convert tables to InnoDB
body: <p>I have a Percona 5.1 server in production that uses MyISAM tables in our production database. To support DB transactions I need to update the tables to InnoDB. We're currently using MySQL 5.5 in development and the migration script runs fine with simple <code>ALTER TABLE xyz ENGINE=InnoDB;</code> queries. However in production tests (against a copy of the production database) we're getting an error:</p>
  
  <pre><code>mysql&gt; ALTER TABLE `xyz` ENGINE=InnoDB;
  ERROR 1005 (HY000): Can't create table 'InnoTest.#sql-644_dd133' (errno: 1478)
  </code></pre>
  
  <p>On our development server, using the same database dump as our production tests:</p>
  
  <pre><code>mysql&gt; ALTER TABLE `xyz` ENGINE=InnoDB;
  Query OK, 0 rows affected, 2 warnings (0.04 sec)
  Records: 0  Duplicates: 0  Warnings: 2
  
  mysql&gt; show warnings;
  +---------+------+------------------------------------------------------------+
  | Level   | Code | Message                                                    |
  +---------+------+------------------------------------------------------------+
  | Warning | 1478 | InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. |
  | Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT.                       |
  +---------+------+------------------------------------------------------------+
  </code></pre>
  
  <p>And the stats:</p>
  
  <pre><code>mysql&gt; SHOW VARIABLES LIKE "%version%";
  +-------------------------+-------------------------------------------+
  | Variable_name           | Value                                     |
  +-------------------------+-------------------------------------------+
  | innodb_version          | 5.1.73-14.11                              |
  | protocol_version        | 10                                        |
  | version                 | 5.1.73-rel14.11-log                       |
  | version_comment         | Percona Server (GPL), 14.11, Revision 603 |
  | version_compile_machine | x86_64                                    |
  | version_compile_os      | unknown-linux-gnu                         |
  +-------------------------+-------------------------------------------+
  
  mysql&gt; SHOW VARIABLES LIKE "%version%";
  +-------------------------+-------------------------+
  | Variable_name           | Value                   |
  +-------------------------+-------------------------+
  | innodb_version          | 5.5.38                  |
  | protocol_version        | 10                      |
  | slave_type_conversions  |                         |
  | version                 | 5.5.38-0ubuntu0.12.04.1 |
  | version_comment         | (Ubuntu)                |
  | version_compile_machine | x86_64                  |
  | version_compile_os      | debian-linux-gnu        |
  +-------------------------+-------------------------+
  </code></pre>
  
  <p>Debugging for gloomy.penguin:</p>
  
  <pre><code>mysql&gt; ALTER TABLE `xyz` ENGINE=InnoDB;
  ERROR 1005 (HY000): Can't create table 'InnoTest.#sql-644_df08c' (errno: 1478)
  mysql&gt; show errors;
  +-------+------+------------------------------------------------------------+
  | Level | Code | Message                                                    |
  +-------+------+------------------------------------------------------------+
  | Error | 1005 | Can't create table 'InnoTest.#sql-644_df08c' (errno: 1478) |
  +-------+------+------------------------------------------------------------+
  1 row in set (0.00 sec)
  
  mysql&gt; show create table visit;
  | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | xyz | CREATE TABLE `xyz` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `some_field` int(11) DEFAULT NULL,
    `some_field` tinyint(2) DEFAULT '0',
    `some_field` enum('a','b') DEFAULT 'b',
    `some_field` varchar(200) DEFAULT NULL,
    `some_field` date DEFAULT NULL,
    `some_field` time DEFAULT NULL,
    `some_field` datetime DEFAULT NULL,
    `some_field` text,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
  1 row in set (0.02 sec)
  
  mysql&gt; SELECT @@GLOBAL.sql_mode;
  +-------------------+
  | @@GLOBAL.sql_mode |
  +-------------------+
  |                   |
  +-------------------+
  1 row in set (0.00 sec)
  
  mysql&gt; SELECT @@SESSION.sql_mode;
  +--------------------+
  | @@SESSION.sql_mode |
  +--------------------+
  |                    |
  +--------------------+
  1 row in set (0.00 sec)
  
  mysql&gt; ALTER TABLE `xyz` ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
  ERROR 1005 (HY000): Can't create table 'InnoTest.#sql-644_df08c' (errno: 1478)
  mysql&gt; ALTER TABLE `xyz` ENGINE=InnoDB ROW_FORMAT=COMPACT;
  Query OK, 0 rows affected (0.13 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  </code></pre>
  
  <p>The development server doesn't have any InnoDB settings in <strong>my.cnf</strong> (default Ubuntu 12.04 mysql-server install), production has these:</p>
  
  <pre><code>innodb                         = FORCE
  innodb_strict_mode             = 1
  innodb_flush_method            = O_DIRECT
  innodb_log_files_in_group      = 2
  innodb_log_file_size           = 64M
  innodb_flush_log_at_trx_commit = 1
  innodb_file_per_table          = 1
  innodb_buffer_pool_size        = 592M
  </code></pre>