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

Comme dit, la longueur totale de votre index est trop longue.

La réponse courte est que vous ne devriez de toute façon pas indexer des colonnes VARCHAR aussi longues, car l'index sera très volumineux et inefficace.

La meilleure pratique est d'utiliser des index de préfixe afin de n'indexer qu'une sous-chaîne de données. La plupart de vos données seront de toute façon beaucoup plus courtes que 255 caractères.

767 octets est la limitation de préfixe indiquée pour les tables InnoDB - elle est de 1 000 octets de long pour les tables MyISAM.

En fonction de la réponse à ce problème, vous pouvez obtenir la clé à appliquer en spécifiant un sous-ensemble de la colonne plutôt que la quantité totale. Cela signifie que vous pouvez déclarer une longueur de préfixe par colonne lorsque vous définissez l'index.

Par exemple:

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

Voici un exemple pour expliquer cela plus clairement : J'ai créé une table et y ai inséré des données.

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;

Mais quelle est la meilleure longueur de préfixe pour une colonne donnée ? Voici une méthode pour le découvrir :

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    |
+---------------+---------------+---------------+----------------+

Cela vous indique que 80 % de vos chaînes sont inférieures à 20 caractères, et que toutes vos chaînes sont inférieures à 50 caractères. Il n'est donc pas nécessaire d'indexer plus qu'une longueur de préfixe de 50, et certainement pas nécessaire d'indexer sur la longueur totale de 255 caractères.

Ajustez comme vous en avez besoin pour que la clé s'applique, mais je me demande s'il ne serait pas utile de revoir votre modèle de données concernant cette entité pour voir s'il existe des améliorations qui vous permettraient de mettre en œuvre les règles métier prévues sans atteindre la limitation de MySQL.

Des alternatives aux paramètres dans InnoDB peuvent être trouvées sur http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

Answer 2

Documentation officielle 5.6

Limites des tables InnoDB

La longueur maximale de la clé interne d'InnoDB est de 3500 octets, mais MySQL lui-même limite cela à 3072 octets. Cette limite s'applique à la longueur de la clé d'index combinée dans un index multi-colonnes.

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

---

Le moteur de stockage MyISAM

La longueur maximale de la clé est de 1000 octets. Cela peut également être modifié en changeant la source et en recompilant. Dans le cas d'une clé de plus de 250 octets, une taille de bloc de clé plus grande que la valeur par défaut de 1024 octets est utilisée.

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

---

Cependant, votre table est déclarée comme InnoDB. Donc je ne sais pas quoi penser

Il y a aussi un indice à la fin de ce ancien bug

Si vous avez besoin de cela, vous devriez vraiment envisager MySQL 5.5 et l'option innodb_large_prefix qui est disponible à partir de 5.5.14 (juillet 2011) car c'est probablement ce que vous cherchez :

"Activez cette option pour permettre des préfixes de clé d'index plus longs que 767 octets (jusqu'à 3072 octets), pour les tables InnoDB qui utilisent les formats de ligne DYNAMIC et COMPRESSED. (La création de telles tables nécessite également les valeurs d'option innodb_file_format=barracuda et innodb_file_per_table=true.) Voir la Section 13.3.15, "Limites sur les tables InnoDB" pour les maximums pertinents associés aux préfixes de clé d'index selon les différents paramètres.

Answer 3

La longueur maximale de la clé est de 1000 octets. Cela peut également être modifié en changeant la source et en recompilant. Dans le cas d'une clé de plus de 250 octets, une taille de bloc de clé plus grande que la valeur par défaut de 1024 octets est utilisée.

Answer 4

Dans mon cas, MySQL a été démarré sans le support InnoDB. Et lors de l'importation de sauvegarde de la base de données - a essayé de créer des tables MyISAM.

En même temps, aucune erreur n'a été affichée dans la console lors du redémarrage de MySQL.
Seulement lorsque j'ai vérifié le fichier journal de MySQL - j'ai trouvé ceci.

En passant, une erreur était liée à innodb_log_file_size = 4G, seulement lorsque je l'ai changé en innodb_log_file_size = 1G - le support InnoDB a été activé.