Erreur : La clé spécifiée était trop longue ; la longueur maximale de la clé est de 1000 octets

Question

Erreur :

1071 - La clé spécifiée était trop longue ; la longueur maximale de la clé est de 1000 octets

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` , `module_id` )
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Je sais que l'erreur se produit en raison de 255x2x3 (3 octets par caractère)

Cela ne se produit pas sur toutes les installations. Quel paramètre puis-je modifier ?

Answer 1

Désactivation de NO_ENGINE_SUBSTITUTION avec INNODB non actif, une mauvaise combinaison

• Jusqu'à MySql 5.5, le mode sqlmode par défaut était une chaîne vide, ce qui signifie que le mode sqlmode NO_ENGINE_SUBSTITUTION n'était pas défini par défaut

Conformément à la documentation de MySql (voir https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_no_engine_substitution), voici la signification du mode sqlmode NO_ENGINE_SUBSTITUTION :

Contrôle de la substitution automatique du moteur de stockage par défaut lorsqu'une déclaration comme CREATE TABLE ou ALTER TABLE spécifie un moteur de stockage qui est désactivé ou non compilé.

Étant donné que les moteurs de stockage peuvent être ajoutés à l'exécution, les moteurs indisponibles sont traités de la même manière :

Avec NO_ENGINE_SUBSTITUTION désactivé, pour CREATE TABLE le moteur par défaut est utilisé et un avertissement survient si le moteur désiré n'est pas disponible. Pour ALTER TABLE, un avertissement survient et la table ne est pas modifiée.

Avec NO_ENGINE_SUBSTITUTION activé, une erreur survient et la table n' est pas créée ou modifiée si le moteur désiré est indisponible.

• Donc : si NO_ENGINE_SUBSTITUTION est désactivé ET INNODB est désactivé, MySql basculera également vers MYISAM même si vous spécifiez INNODB dans votre instruction CREATE TABLE.
• Si la table que vous créez est adaptée à MYISAM, vous recevrez simplement un avertissement et la table sera créée. Ce n'est pas votre cas, votre instruction de création inclut un index qui dépasse la limite de 1000 octets de MYISAM, alors la création échoue avec l'erreur 1071 signalant l'erreur de MYISAM. Cela est dû au fait que le moteur en cours est MYISAM, et non INNODB.

PREUVE

Version de MySql 5.1.56 communauté

Cas 1 :

Options dans my.cnf  
sql-mode=""
default-storage-engine=MYISAM  
skip-innodb en commentaire (sans #) 

Résultat de l'exécution de votre instruction de création :
Code d'erreur : 1071. Clé spécifiée trop longue ; longueur maximale de la clé est de 1000 octets

Explication : INNODB n'est pas actif, le moteur bascule automatiquement en MYISAM  
ce qui retourne cette erreur car la clé est plus longue que la limite de 1000 octets de MYISAM.  
La longueur de la clé est : 
2 champs x 255 caractères x 3 octets (encodage utf8) + 2 x 1 octet (longueur) = 1532 octets   

...

Answer 2

Je ne sais pas quelle est la flexibilité dont vous disposez, mais voici quelques options :

• Mettre à jour la version de MySQL à la dernière version (j'ai testé le code sur MySQL 5.5.25 et je n'ai eu aucune erreur.)

• Passer de utf8 à latin1.

• Réduire la taille des champs qui composent la clé primaire. Créez un champ de clé primaire auto_increment séparé pour remplacer la clé existante. Créez un index séparé sur le premier champ action_id (mais pas sur le deuxième champ)

À part ces options, vous êtes plus ou moins bloqué car il n'y a aucun paramètre que vous pouvez modifier dans MySQL pour permettre une clé d'index supérieure à 1000 octets.

Answer 3

Dans MySQL 5.6.3+ avec certaines limitations (besoin de ROW_FORMAT=DYNAMIC, innodb_file_format=BARRACUDA et innodb_file_per_table=true), vous pouvez activer innodb_large_prefix pour une limite de longueur de clé de 3072 octets.

Answer 4

Vous avez (accidentellement?) désactivé InnoDB. Il crée la table en tant que MyISAM et la clé entière fait plus de 1000 octets.

Vérifiez les variables pour voir si vous avez InnoDB, et s'il y a "no_engine_substitution" (si cela existait il y a longtemps dans la version 5.1.56). Vérifiez également le fichier mysqld.err pour voir s'il y a des erreurs pertinentes au démarrage.

Answer 5

Il semble que cela soit lié au moteur de stockage. Mon utilisateur final utilisait MyISAM qui gère le stockage des données différemment, provoquant ainsi cette erreur.