Ответ 1

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_SUBSTITUTION:

Управление автоматической заменой механизма хранения по умолчанию, когда такой как CREATE TABLE или ALTER TABLE, указывает хранилище который отключен или не скомпилирован.

Поскольку механизмы хранения могут быть подключаемы во время выполнения, недоступны двигатели обрабатываются одинаково:

С отключенным NO_ENGINE_SUBSTITUTION для CREATE TABLE значение по умолчанию используется двигатель, и появляется предупреждение, если требуемый двигатель недоступен. Для ALTER TABLE появляется предупреждение, и таблица не изменено.

При включенном NO_ENGINE_SUBSTITUTION возникает ошибка, и таблица не создается или не изменяется, если желаемый движок недоступен.

• Итак: если NO_ENGINE_SUBSTITUTION отключен и INNODB отключен, MySql переключится на MYISAM также, если вы укажете INNODB в инструкции CREATE TABLE.
• Если созданная вами таблица в порядке для MYISAM, вы получите предупреждение, и таблица будет создана. Это не ваш случай, ваш оператор создания включает индекс, который превышает ограничение на 1000 байтов MYISAM, тогда создание завершилось с ошибкой 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.

Чтобы воспроизвести тесты, перезапустите MySql после каждого изменения my.cnf.

Так как 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), поэтому, если вы попытаетесь установить ключ с VARCHAR (256) utf8 вы получаете:

256 x 3 + 2 = 770 bytes

и вы получите предыдущую ошибку, так как максимальная длина ключа для одного столбца составляет 767 байт для таблицы InnoDB. 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!

Другой тип ERROR 1071 для таблиц INNODB:

Error Code: 1071. Specified key was too long; max key length is 3072 bytes

Это связано с ключами с несколькими столбцами. Чтобы эти ключи были включены, вам необходимо установить innodb_large_prefix для включения. В любом случае, если вы попытаетесь запустить что-то вроде этого:

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 столбцов utf8 VARCHAR (255), то есть 3830 байт, вы столкнетесь с:

Error Code: 1071. Specified key was too long; max key length is 3072 bytes

Экзотическая гипотеза

Во время поиска причины я сформулировал и протестировал разные и довольно странные гипотезы:

Формат ROW

Протестировано REDUNDANT, COMPACT, COMPRESS, DYNAMIC: не влияет на создание таблицы с вашим выражением.

Формат файла

Протестированная Antelope и Barracuda: никакого влияния на создание таблицы с вашим выражением.

MySql построен

Протестировано 32-битное и 64-битное MySql: не влияет на создание таблицы с вашим выражением.

Другие аналогичные отказы

Здесь вы можете найти ту же ошибку в той же ситуации:

https://www.drupal.org/node/2466287

Я тестировал этот оператор в трех тестовых ситуациях, перечисленных в PROOF, и он воспроизводил точно такое же поведение, как ваше, поэтому я могу сказать, что проблема такая же. В этом случае они переключились на другую БД, но проблема заключается в сочетании настроек, а не версии БД.

Ссылки

Здесь очень хорошая статья индексации с INNODB:

http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

ПРЕДУПРЕЖДЕНИЕ: отключив INNODB, раскомментировав skip-innodb в my.cnf после создания таблицы INNODB с индексом более 1000, не разрешит запуск службы MySql

Привет

Ответ 2

Теперь у меня нет такой гибкости, но вот несколько вариантов:

• Обновите версию MySQL до последней версии (я проверил код на MySQL 5.5.25 и не получил ошибок.)

• Изменить с utf8 на latin1.

• Уменьшите размер полей, составляющих первичный ключ.     Создайте отдельное поле первичного ключа auto_increment, чтобы заменить существующий ключ. Создайте отдельный индекс в первом поле action_id (но не во втором поле)

Кроме этих параметров, вы довольно сильно застряли, так как в MySQL нет параметров, которые могут включать индексный ключ размером более 1000 байт.

Ответ 3

В MySQL 5.6.3+ с некоторыми ограничениями (нужны ROW_FORMAT=DYNAMIC, innodb_file_format=BARRACUDA и innodb_file_per_table=true) вы можете включить innodb_large_prefix для ограничения длины ключа в 3072 байта.

Ответ 4

Кажется, что это связано с механизмом хранения. Мой конечный пользователь использовал MyISAM, который обрабатывает данные хранения по-разному, вызывая эту ошибку.

Ответ 5

У вас (случайно?) InnoDB отключен. Он создает таблицу как MyISAM, а весь ключ - более 1000 байт.

Проверьте переменные, есть ли у вас InnoDB и существует ли "no_engine_substitution" (если это было в обратном порядке в 5.1.56). Также проверьте mysqld.err, чтобы увидеть, есть ли у него некоторые соответствующие ошибки при запуске.

Ответ 6

Как говорится, общая длина вашего индекса слишком длинная.

Короткий ответ заключается в том, что вы не должны индексировать такие длинные столбцы VARCHAR в любом случае, потому что индекс будет очень громоздким и неэффективным.

Лучшей практикой является использование префиксных индексов, поэтому вы только индексируете левую подстроку данных. Большинство ваших данных будут в любом случае короче 255 символов.

767 bytes - это префиксное ограничение для таблиц 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/

Ответ 7

Официальный документ 5.6

Ограничения на InnoDB Таблицы

Внутренняя максимальная длина ключа InnoDB составляет 3500 байт, но сама MySQL ограничивает это до 3072 байт. Этот предел относится к длине комбинированный индексный индекс в индексе с несколькими столбцами.

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

Механизм хранения MyISAM

Максимальная длина ключа - 1000 байт. Это также можно изменить изменение источника и перекомпиляция. Для случая ключа дольше, чем 250 байт, больший размер блока клавиш, чем значение по умолчанию 1024 байта, б.

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

Однако ваша таблица объявлена ​​как InnoDB. Поэтому я не знаю, что думать

В конце этой старой ошибки есть подсказка

Если вам это нужно, вы должны действительно посмотреть на MySQL 5.5 и параметр innodb_large_prefix, который доступен в версии 5.5.14 (июль 2011), потому что он, вероятно, делает то, что вы ищете:

"Включите эту опцию, чтобы разрешить префиксы ключевых слов до 767 байт (до 3072 байт), для таблиц InnoDB, использующих DYNAMIC и Сжатые строки. (Для создания таких таблиц также требуется значения опций innodb_file_format = barracuda и innodb_file_per_table = true.) См. раздел 13.3.15," Ограничения на InnoDB Таблицы "для соответствующих максимумов, связанных с префиксами ключевых слов индекса в различных настройках.

Ответ 8

Максимальная длина ключа - 1000 байт. Это также может быть изменено путем изменения источника и перекомпиляции. В случае ключа длиной более 250 байтов используется больший размер блока клавиш, чем значение по умолчанию 1024 байта.