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

Je connais des questions avec ce titre ont déjà été répondu, mais s’il vous plaît lisez la suite. J’ai lu attentivement toutes les autres questions / réponses sur cette erreur avant de poster.

Je reçois l’erreur ci-dessus pour la requête suivante:

CREATE TABLE IF NOT EXISTS `pds_core_menu_items` ( `menu_id` varchar(32) NOT NULL, `parent_menu_id` int(32) unsigned DEFAULT NULL, `menu_name` varchar(255) DEFAULT NULL, `menu_link` varchar(255) DEFAULT NULL, `plugin` varchar(255) DEFAULT NULL, `menu_type` int(1) DEFAULT NULL, `extend` varchar(255) DEFAULT NULL, `new_window` int(1) DEFAULT NULL, `rank` int(100) DEFAULT NULL, `hide` int(1) DEFAULT NULL, `template_id` int(32) unsigned DEFAULT NULL, `alias` varchar(255) DEFAULT NULL, `layout` varchar(255) DEFAULT NULL, PRIMARY KEY (`menu_id`), KEY `index` (`parent_menu_id`,`menu_link`,`plugin`,`alias`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Est-ce que quelqu’un a une idée pourquoi et comment le réparer? Le problème est que cette même requête fonctionne parfaitement sur ma machine locale et fonctionne aussi bien sur mon hôte précédent. Btw.it provient d’un projet mature – phpdevshell – alors je suppose que ces gars-là savent ce qu’ils font, même si vous ne le savez jamais.

Tout indice apprécié.

J’utilise phpMyAdmin.

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

La réponse courte est que vous ne devriez pas indexer de telles colonnes VARCHAR si longues, car l’index sera très volumineux et inefficace.

La meilleure pratique consiste à utiliser des index de préfixe pour indexer uniquement une sous-chaîne gauche des données. La plupart de vos données seront beaucoup plus courtes que 255 caractères.

Vous pouvez déclarer une longueur de préfixe par colonne lorsque vous définissez l’index. Par exemple:

 ... KEY `index` (`parent_menu_id`,`menu_link`(50),`plugin`(50),`alias`(50)) ... 

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

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

Il vous indique la proportion de lignes n'ayant pas plus d'une longueur de chaîne donnée dans la colonne menu_link . Vous pourriez voir la sortie comme ceci:

 +---------------+---------------+---------------+----------------+ | pct_length_10 | pct_length_20 | pct_length_50 | pct_length_100 | +---------------+---------------+---------------+----------------+ | 21.78 | 80.20 | 100.00 | 100.00 | +---------------+---------------+---------------+----------------+ 

Cela vous indique que 80% de vos chaînes ont moins de 20 caractères et que toutes vos chaînes ont moins de 50 caractères. Il n'y a donc pas besoin d'indexer plus d'une longueur de préfixe de 50, et certainement pas d'indexer la longueur totale de 255 caractères.

PS: Les types de données INT(1) et INT(32) indiquent un autre malentendu à propos de MySQL. L'argument numérique n'a aucun effet lié au stockage ou à la plage de valeurs autorisée pour la colonne. INT est toujours de 4 octets et autorise toujours les valeurs comsockets entre -2147483648 et 2147483647. L'argument numérique concerne les valeurs de remplissage lors de l'affichage, ce qui n'a d'effet que si vous utilisez l'option ZEROFILL .

Cette erreur signifie que la longueur de l’index d’ index est supérieure à 1000 octets. MySQL et les moteurs de stockage peuvent avoir cette ressortingction. J’ai une erreur similaire sur MySQL 5.5 – ‘La clé spécifiée était trop longue; La longueur maximale de la clé est de 3072 octets lors de l’exécution de ce script:

 CREATE TABLE IF NOT EXISTS test_table1 ( column1 varchar(500) NOT NULL, column2 varchar(500) NOT NULL, column3 varchar(500) NOT NULL, column4 varchar(500) NOT NULL, column5 varchar(500) NOT NULL, column6 varchar(500) NOT NULL, KEY `index` (column1, column2, column3, column4, column5, column6) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

UTF8 est multi-octets et la longueur de la clé est calculée de cette manière – 500 * 3 * 6 = 9 000 octets.

Mais notez que la prochaine requête fonctionne!

 CREATE TABLE IF NOT EXISTS test_table1 ( column1 varchar(500) NOT NULL, column2 varchar(500) NOT NULL, column3 varchar(500) NOT NULL, column4 varchar(500) NOT NULL, column5 varchar(500) NOT NULL, column6 varchar(500) NOT NULL, KEY `index` (column1, column2, column3, column4, column5, column6) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

… parce que j’ai utilisé CHARSET = latin1, dans ce cas la longueur de la clé est 500 * 6 = 3000 octets.

Exécutez cette requête avant de créer ou de modifier une table.

SET @@global.innodb_large_prefix = 1;

cela définira la longueur maximale de la clé à 3072 octets

Cette limite de taille d’index semble être plus grande sur les versions 64 bits de MySQL.

Je rencontrais cette limitation en essayant de vider notre firebase database de développement et de la charger sur une VMWare locale. Finalement, je me suis rendu compte que le serveur de développement distant était 64 bits et que j’avais créé un virt 32 bits. Je viens de créer un virt 64 bits et j’ai pu charger la firebase database localement.

J’ai eu ce problème et résolu en suivant:

Référence :

Cause

Il y a un bug connu avec MySQL lié à MyISAM, le jeu de caractères UTF8 et les index que vous pouvez vérifier ici.

Résolution

  • Assurez-vous que MySQL est configuré avec le moteur de stockage InnoDB.

  • Modifiez le moteur de stockage utilisé par défaut pour que les nouvelles tables soient toujours créées de manière appropriée:

    set GLOBAL storage_engine='InnoDb';

  • Pour MySQL 5.6 et versions ultérieures, utilisez ce qui suit:

    SET GLOBAL default_storage_engine = 'InnoDB';

  • Et enfin, assurez-vous de suivre les instructions fournies dans Migration vers MySQL .

Je viens de faire un contournement de cette erreur en changeant simplement les valeurs de la “longueur” dans la firebase database d’origine sur le total d’environ “1000” en modifiant sa structure, puis en exportant la même chose vers le serveur. 🙂

J’étais confronté au même problème, utilisé ci-dessous pour le résoudre.

Lors de la création de la firebase database, vous pouvez utiliser l’encodage utf-8

par exemple. create database my_db character set utf8 collate utf8_bin;