我在生产中使用Percona 5.1服务器,在我们的生产数据库中使用MyISAM表.为了支持数据库事务,我需要将表更新为InnoDB.我们目前正在开发中使用MySQL 5.5,并且迁移脚本可以通过简单的ALTER TABLE xyz ENGINE = InnoDB运行良好;查询.但是在生产测试中(针对生产数据库的副本),我们收到了一个错误：
mysql> ALTER TABLE `xyz` ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table 'InnoTest.#sql-644_dd133' (errno: 1478)
在我们的开发服务器上,使用与生产测试相同的数据库转储：
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. |
+---------+------+------------------------------------------------------------+
和统计数据：
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 |
+-------------------------+-------------------------+
调试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
开发服务器在my.cnf中没有任何InnoDB设置(默认Ubuntu 12.04 mysql-server安装),生产有以下几种：
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 …所以OP没有回应.这很酷.这是关于该错误的mysql文档… http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-compression-syntax-warnings.html
>运行ALTER TABLE xyz ENGINE = InnoDB;再次刺激.
>然后显示错误;
>做一个show create table xyz;
>看看你是否处于innodb严格模式……(我会在prod和non-prod都这样做,看看有什么区别)SELECT @@ GLOBAL.sql_mode; SELECT @@ SESSION.sql_mode;
>来自docs：如果您在InnoDB严格模式下运行,KEY_BLOCK_SIZE与除COMPRESSED以外的任何ROW_FORMAT的组合会生成错误,而不是警告,并且不会创建表.
>使用您获得的信息和链接中的信息来确定如何设置key_block_size和row_format以使其获取它.
非prod数据库的工作原理是：
>使用任何其他ROW_FORMAT指定KEY_BLOCK_SIZE会生成警告,您可以使用SHOW WARNINGS查看.但是,该表是非压缩的;指定的KEY_BLOCK_SIZE被忽略). (在非innodb严格模式下)
如果你想要更多的帮助,发布你得到的信息,我绝对可以建议…这可能是改变表xyz engine = innodb ROW_FORMAT = COMPRESSED;和/或使innodb模式设置等于非prod数据库设置的设置.
more than you ever wanted to know on row formats
虽然真的,但听起来像prod是在innodb严格模式而非prod不是.
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.
你的一个版本高于5.5.5,另一个低于.那个默认可能是差异……