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 ALTER TABLE xyz ENGINE=InnoDB; queries. However in production tests (against a copy of the production database) we're getting an error:
mysql> ALTER TABLE `xyz` ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table 'InnoTest.#sql-644_dd133' (errno: 1478)
On our development server, using the same database dump as our production tests:
mysql> ALTER TABLE `xyz` ENGINE=InnoDB;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 2
mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------+
| Warning | 1478 | InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_per_table. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |
+---------+------+------------------------------------------------------------+
And the stats:
mysql> 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> 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 |
+-------------------------+-------------------------+
Debugging for gloomy.penguin:
mysql> ALTER TABLE `xyz` ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table 'InnoTest.#sql-644_df08c' (errno: 1478)
mysql> 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> 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> SELECT @@GLOBAL.sql_mode;
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
| |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT @@SESSION.sql_mode;
+--------------------+
| @@SESSION.sql_mode |
+--------------------+
| |
+--------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE `xyz` ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
ERROR 1005 (HY000): Can't create table 'InnoTest.#sql-644_df08c' (errno: 1478)
mysql> ALTER TABLE `xyz` ENGINE=InnoDB ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
The development server doesn't have any InnoDB settings in my.cnf (default Ubuntu 12.04 mysql-server install), production has these:
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
k... so OP isn't responding. that's cool. this is the mysql documentation on that error... http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-compression-syntax-warnings.html
run ALTER TABLE xyz ENGINE=InnoDB; in prod again.
then do show errors;.
do a show create table xyz;
see if you're in innodb strict mode... (i would do this in both prod and non-prod to see any difference) SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;
from docs: If you are running in InnoDB strict mode, the combination of a KEY_BLOCK_SIZE with any ROW_FORMAT other than COMPRESSED generates an error, not a warning, and the table is not created.
use that info you got and the info at the link to determine how you should set your key_block_size and row_format to get it to take it.
the non-prod db works because:
KEY_BLOCK_SIZE with any other ROW_FORMAT generates a warning that you can view with SHOW WARNINGS. However, the table is non-compressed; the specified KEY_BLOCK_SIZE is ignored). (in non-innodb-strict mode) if you want more help, post the info you get and i can definitely suggest things... which would probably be alter table xyz engine=innodb ROW_FORMAT=COMPRESSED; and/or making the innodb mode setting equal to what your non-prod db is set with.
more than you ever wanted to know on row formats
for real, though... it sounds like prod is in innodb strict mode and non-prod isn't.
innodb_strict_mode:
The innodb_strict_mode option controls whether InnoDB operates in strict mode,
where conditions that are normally treated as warnings, cause errors instead
(and the underlying statements fail).
This mode is the default setting in MySQL 5.5.5 and higher.
One of your versions is above 5.5.5 and the other is below. That default might be the discrepancy....