mysql - 尝试将表转换为 InnoDB 时出错

Question

我在生产环境中有一台 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

Answer 1

k... 所以 OP 没有响应。这很酷。这是关于该错误的 mysql 文档... http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-compression-syntax-warnings.html

• 运行 ALTER TABLE xyz ENGINE=InnoDB;再次生产。

• 然后执行 show errors; .

• 做一个show create table xyz;

• 查看您是否处于 innodb 严格模式...(我会在产品和非产品中执行此操作以查看任何区别)SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;

• 来自文档:如果您在 InnoDB 严格模式下运行， KEY_BLOCK_SIZE 的组合与任何 ROW_FORMAT除了COMPRESSED生成错误，而不是警告，并且不会创建表。

• 使用您获得的信息和链接中的信息来确定您应该如何设置 key_block_size和 row_format得到它来接受它。

非产品数据库之所以有效，是因为:

• 指定 KEY_BLOCK_SIZE与任何其他ROW_FORMAT生成一个警告，您可以使用 SHOW WARNINGS 查看该警告.但是，该表未压缩；指定的 KEY_BLOCK_SIZE被忽略)。 (在非 innodb-strict 模式下)

如果您需要更多帮助，请发布您获得的信息，我绝对可以提出建议...可能是 alter table xyz engine=innodb ROW_FORMAT=COMPRESSED;和/或使 innodb 模式设置等于您的非产品数据库的设置。

key_block_size

more than you ever wanted to know on row formats

但实际上...听起来 prod 处于 innodb 严格模式而 non-prod 不是。

---

found this:

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以上，另一个在5.5.5以下。该默认值可能是差异....