NO_ENGINE_SUBSTITUTION 禁用且 INNODB 未激活,这是一个糟糕的组合
- 到 MySql 5.5 为止,sqlmode 默认值为空字符串,这意味着默认情况下未设置 sqlmode NO_ENGINE_SUBSTITUTION
根据 MySql 文档(参见https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_no_engine_substitution https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_no_engine_substitution)这就是sqlmode NO_ENGINE_SUBSTITUTION的含义:
控制默认存储引擎的自动替换
CREATE TABLE 或 ALTER TABLE 等语句指定存储
引擎被禁用或未编译。
因为存储引擎在运行时是可插拔的,不可用
引擎的处理方式相同:
禁用 NO_ENGINE_SUBSTITUTION 时,对于 CREATE TABLE 使用默认值
使用引擎,如果所需引擎被使用,则会出现警告
不可用。对于 ALTER TABLE,出现警告并且表不存在
改变了。
启用 NO_ENGINE_SUBSTITUTION 后,会发生错误并且表
如果所需的引擎不可用,则不会创建或更改。
- 所以:如果 NO_ENGINE_SUBSTITUTION 被禁用并且 INNODB 被关闭,如果您在 CREATE TABLE 语句中指定 INNODB,MySql 也会切换到 MYISAM。
- 如果您正在创建的表适用于 MYISAM,您只会收到一条警告,并且该表将被创建。这不是你的情况,你的创建语句中包含的索引超出了MYISAM的1000字节限制,那么创建失败,错误1071报告MYISAM错误。那是因为工作引擎是MYISAM,而不是INNODB。
PROOF
MySql 版本 5.1.56 社区
Case 1:
Options in my.cnf
sql-mode=""
default-storage-engine=MYISAM
skip-innodb uncommented (without#)
Return on execution of your create statement:
Error Code: 1071. Specified key was too long; max key length is 1000 bytes
Explanation: INNODB is not active, the engine is automatically switched to MYISAM
that returns this error as they key is longer than MYISAM 1000 bytes limit.
The key length is:
2 fields x 255 char x 3 bytes utf8 encoding + 2 x 1 length byte = 1532 bytes
Case 2:
Options in my.cnf
sql-mode="NO_ENGINE_SUBSTITUTION"
default-storage-engine=MYISAM
skip-innodb uncommented (without#)
Return on execution of your create statement:
Error Code: 1286. Unknown table engine 'INNODB'
Explanation: INNODB is not active but the engine substitution is not permitted
by sql mode therefore the DB returns an error about the attempt of using a disabled engine.
Case 3:
Options in my.cnf
sql-mode="NO_ENGINE_SUBSTITUTION"
default-storage-engine=MYISAM
skip-innodb commented (with#)
Return on execution of your create statement:
Table creation OK!
Explanation: INNODB is active (skip-innodb commented) and it is used also if
the default engine is MYISAM.
要重现测试,请在 my.cnf 中的每次更改后重新启动 MySql。
由于MySql 5.6版本的sqlmode默认不再为空,并且包含NO_ENGINE_SUBSTITUTION,而且INNODB是默认引擎,因此该错误很难满足。
其他测试
没有其他方法可以重现该错误:
Error Code: 1071. Specified key was too long; max key length is 1000 bytes
在尝试创建INNODB表时发现。
在 INNODB 中,有两种 ERROR 1071:
Error Code: 1071. Specified key was too long; max key length is 767 bytes
这与 innodb_large_prefix ON 或 OFF 无关,而仅与用作索引的单个 VARCHAR 列的大小有关。
Mysql 存储 varchar utf8,其中 3 个字节加上 1 个字节,长度最多为 255 个字符,后面为 2 个字节(请参阅:http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html),所以如果您尝试使用 VARCHAR(256) utf8 设置密钥,您会得到:
256 x 3 + 2 = 770 bytes
您会收到之前的错误,因为 InnoDB 表的单个列的最大键长度为 767 字节。 VARCHAR(255) 是可以的,因为:
255 x 3 + 1 = 766 bytes
我在四个版本的 Mysql(版本 5.1.56、5.5.33、5.6 和 5.7)上进行了测试,结果得到证实。使用 VARCHAR(255) 的查询没有问题,使用 VARCHAR(256) 的查询有问题:
Error Code: 1071. Specified key was too long; max key length is 767 bytes
正如你所看到的,消息是不同的,因为它是一条 INNODB 消息而不是 MYISAM 消息!
INNODB 表的 ERROR 1071 的另一种类型是:
Error Code: 1071. Specified key was too long; max key length is 3072 bytes
这与具有多列的键有关。要启用这些键,您需要将 innodb_large_prefix 设置为 on。
无论如何,如果你尝试运行这样的东西:
CREATE TABLE `phppos_modules_actions` (
`action_id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`module_id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`action_name_key` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`action_name_key1` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`action_name_key2` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`sort` INT NOT NULL ,
PRIMARY KEY ( `action_id` , `module_id`, `action_name_key`, `action_name_key1`, `action_name_key2` )
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
使用 5 个 VARCHAR(255) utf8 列(即 3830 字节)的键,您将遇到:
Error Code: 1071. Specified key was too long; max key length is 3072 bytes
奇异的假设
在寻找原因的过程中,我提出并测试了不同且相当奇怪的假设:
行格式
测试过 REDUNDANT、COMPACT、COMPRESS、DYNAMIC:对使用语句创建表没有影响。
文件格式
经过测试的羚羊和梭子鱼:对您的语句创建表没有影响。
MySql 搭建
测试了 32 位和 64 位 MySql:对使用语句创建表没有影响。
其他类似故障
在这里你可以发现相同情况下的相同错误:
https://www.drupal.org/node/2466287 https://www.drupal.org/node/2466287
我在 PROOF 中列出的 3 个测试情况中测试了该语句,它再现了与您完全相同的行为,所以我可以说问题是相同的。在这种情况下,他们切换到其他数据库,但问题是设置的混合,而不是数据库版本。
参考
这里给出了一篇关于 INNODB 索引的非常好的文章:
http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/ http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/
警告:在创建索引超过 1000 的 INNODB 表后,通过取消 my.cnf 中的skip-innodb 注释来禁用 INNODB 将不允许启动 MySql 服务
Regards