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 )这就是sqlmode NO_ENGINE_SUBSTITION的含义:

当 语句(如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。

证明

MySql 5.1.56版社区

案例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   

案例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. 

案例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中,您有两种错误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 ),因此,如果您尝试使用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)查询有问题:

错误代码:1071。指定的密钥太长;最大密钥长度为767字节

正如你所看到的,这条消息是不同的,因为它是INNODB消息,而不是MYISAM消息!

INNODB表的错误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个字节,您将运行到:

错误代码:1071。指定的密钥太长;最大密钥长度为3072字节

奇异假说

在寻找原因的过程中,我提出并测试了不同的、非常奇怪的假设:

ROW格式

测试REDUNDANT、COMPACT、COMPRESS、DYNAMIC:语句对表的创建没有影响。

FILE格式

经过测试的羚羊和梭鱼:您的声明对表的创建没有影响。

MySql已生成

测试了32位和64位MySql:语句对表创建没有影响。

其他类似故障

在这里,您可以在相同的情况下找到相同的错误:

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/

警告:在创建索引超过1000的INNODB表后,通过在my.cnf中取消注释skip INNODB来禁用INNODB将不允许启动MySql服务

当做

我现在不知道你有多大的灵活性,但这里有一些选择:

• 将MySQL版本升级到最新版本(我在 MySQL 5.5.25,没有错误。)

• 从utf8更改为latin1。

• 减小组成主键的字段的大小。 创建一个单独的auto_increment主键字段来替换现有的键。在第一个字段上创建单独的索引 action_id (但不是第二个字段)

除了这些选项之外,您几乎陷入了困境,因为MySQL中没有可以更改的设置来启用大于1000字节的索引键。

在MySQL 5.6.3+中,有一些限制(需要 ROW_FORMAT=DYNAMIC , innodb_file_format=BARRACUDA 和 innodb_file_per_table=true )您可以启用 innodb_large_prefix 对于3072字节的密钥长度限制。

你(意外地?)关闭了InnoDB。它正在将表创建为MyISAM 全部的 密钥超过1000字节。

检查是否有InnoDB的变量,以及是否存在“no_engine_substitution”(如果早在5.1.56中就存在)也要检查 mysqld.err 查看它在启动时是否有一些相关错误。

它似乎与存储引擎有关。我的最终用户正在使用 MyISAM 其以不同的方式处理导致该错误的数据的存储。

正如所说,你的索引的总长度太长了。

简单的答案是,你不应该索引这么长 VARCHAR 列,因为索引将非常庞大且效率低下。

最好的做法是使用前缀索引,这样您就只对数据的左子串进行索引。无论如何,您的大部分数据都将比255个字符短得多。

767字节是规定的前缀限制 InnoDB 表-其1000字节长用于 MyISAM 表。

根据对此问题的响应,您可以通过指定列的子集而不是整个数量来获得要应用的键。意味着您可以在定义索引时声明每列的前缀长度。

例如:

KEY `key_name` (`action_id`(50),`module_id`(50))

下面的例子可以更清楚地解释它:我创建了一个表,并在其中插入了一些数据。

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 ,
  `sort` INT NOT NULL ,
  PRIMARY KEY ( `action_id`(50) , `module_id`(50) )
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

但是,给定列的最佳前缀长度是多少?以下是一种查找方法:

SELECT
 ROUND(SUM(LENGTH(`action_id`)<10)*100/COUNT(*),2) AS pct_length_10,
 ROUND(SUM(LENGTH(`action_id`)<20)*100/COUNT(*),2) AS pct_length_20,
 ROUND(SUM(LENGTH(`action_id`)<50)*100/COUNT(*),2) AS pct_length_50,
 ROUND(SUM(LENGTH(`action_id`)<100)*100/COUNT(*),2) AS pct_length_100
FROM `phppos_modules_actions`;


+---------------+---------------+---------------+----------------+
| pct_length_10 | pct_length_20 | pct_length_50 | pct_length_100 |
+---------------+---------------+---------------+----------------+
|         42.86 |         80.20 |        100    |         100    |
+---------------+---------------+---------------+----------------+

这告诉您80%的字符串少于20个字符,并且所有字符串都少于50个字符。因此,不需要索引超过50的前缀长度,当然也不需要索引255个字符的完整长度。

根据需要进行调整以获得应用密钥,但我想知道是否值得查看您关于该实体的数据模型,看看是否有改进可以让您在不受MySQL限制的情况下实现预期的业务规则。

关于innodb中的设置,可以在 http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

正式文件5.6

限制 创新数据库 表格

InnoDB内部最大密钥长度为3500字节,但MySQL本身 将其限制为3072字节。此限制适用于 多列索引中的组合索引键。

http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

这个 我的ISAM 存储引擎

最大密钥长度为1000字节。这也可以通过 更改源代码并重新编译。对于钥匙长度超过 250字节,比默认的1024字节更大的密钥块大小是 习惯于

http://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html

然而,您的表被声明为InnoDB。所以我不知道该怎么想

最后还有一条线索 old bug

如果你需要这个,你应该看看MySQL 5.5和从5.5.14(2011年7月)起提供的innodb_large_prefix选项,因为它可能会满足你的需求:

“启用此选项可允许索引键前缀超过767字节 (最多3072字节),用于使用DYNAMIC和 压缩的行格式。(创建此类表格还需要 选项值innodb_file_format=梭鱼和 innodb_file_per_table=true。)参见第13.3.15节,InnoDB的限制 与索引键前缀关联的相关最大值的表格 在各种设置下。

最大密钥长度为1000字节。这也可以通过更改源代码和重新编译来更改。对于密钥长度超过250字节的情况,将使用比默认1024字节更大的密钥块大小。

在我的案例中,MySQL是在没有InnoDB支持的情况下启动的。在数据库备份导入过程中,尝试创建MyISAM表。

同时,MySQL重启期间控制台中没有显示任何错误。
只有在检查MySQL日志文件时才发现这一点。

顺便说一句,错误与 innodb_log_file_size = 4G ,仅当将其更改为 innodb_log_file_size = 1G -已启用InnoDB支持。