Erreur 1071 de mysql: Specified key was too long; max key length is 1000 bytes #4342

Open
opened 3 years ago by RealET · 19 comments
RealET commented 3 years ago

Bonjour,

Après avoir configuré MariaDB pour être en UTF8MB4, je n'ai pas pu installer un plugin (comme il y a aussi un bug SVP, je vais faire un autre ticket spécifique).
Pour être précis, un table du plugin n'a pas été créée et sql.log contient :

2019-05-25 16:23:59 78.205.175.37 (pid 16091) :Pri:ERREUR: Erreur 1071 de mysql: Specified key was too long; max key length is 1000 bytes
in ecrire/base/create.php L73 [sql_create(),creer_ou_upgrader_table(),alterer_base(),maj_tables(),serie_alter(),maj_while(),maj_plugin(),referer_spam_upgrade(),spip_plugin_install(),plugins_installer_dist(),installer_plugin(),do_install(),do_action(),one_action(),action_actionner_dist(),traiter_appels_actions()]
CREATE  TABLE IF NOT EXISTS `mutu_pro2spipf126`.spip_referer_spam (
		date DATE NOT NULL,
		referer VARCHAR (255) ,
		PRIMARY KEY (referer)) ENGINE=MyISAM

La documentation sur ce sujet est abondante.
Ceci m'a semblé un bon résumé du problème : https://stackoverflow.com/questions/6172798/mysql-varchar255-utf8-is-too-long-for-key-but-max-length-is-1000-bytes
Et la solution générale consiste à faire un index de seulement 191 : https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-conversion.html :

In an InnoDB table that uses COMPACT or REDUNDANT row format, these column and index definitions are legal:
col1 VARCHAR(500) CHARACTER SET utf8, INDEX (col1(255))

To use utf8mb4 instead, the index must be smaller:
col1 VARCHAR(500) CHARACTER SET utf8mb4, INDEX (col1(191))
Bonjour, Après avoir configuré MariaDB pour être en UTF8MB4, je n'ai pas pu installer un plugin (comme il y a aussi un bug SVP, je vais faire un autre ticket spécifique). Pour être précis, un table du plugin n'a pas été créée et sql.log contient : <pre> 2019-05-25 16:23:59 78.205.175.37 (pid 16091) :Pri:ERREUR: Erreur 1071 de mysql: Specified key was too long; max key length is 1000 bytes in ecrire/base/create.php L73 [sql_create(),creer_ou_upgrader_table(),alterer_base(),maj_tables(),serie_alter(),maj_while(),maj_plugin(),referer_spam_upgrade(),spip_plugin_install(),plugins_installer_dist(),installer_plugin(),do_install(),do_action(),one_action(),action_actionner_dist(),traiter_appels_actions()] CREATE TABLE IF NOT EXISTS `mutu_pro2spipf126`.spip_referer_spam ( date DATE NOT NULL, referer VARCHAR (255) , PRIMARY KEY (referer)) ENGINE=MyISAM </pre> La documentation sur ce sujet est abondante. Ceci m'a semblé un bon résumé du problème : https://stackoverflow.com/questions/6172798/mysql-varchar255-utf8-is-too-long-for-key-but-max-length-is-1000-bytes Et la solution générale consiste à faire un index de seulement 191 : https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-conversion.html : <pre> In an InnoDB table that uses COMPACT or REDUNDANT row format, these column and index definitions are legal: col1 VARCHAR(500) CHARACTER SET utf8, INDEX (col1(255)) To use utf8mb4 instead, the index must be smaller: col1 VARCHAR(500) CHARACTER SET utf8mb4, INDEX (col1(191)) </pre>
Poster

Voir #4343 pour le bug spécifique SVP

Voir #4343 pour le bug spécifique SVP
b_b commented 3 years ago
Owner

Comme indiqué plus bas dans le thread que tu pointes, ton problème vient du fait que tu utilises MyISAM et non InnoDB cf https://stackoverflow.com/a/50689604

J'hésite à répondre que c'est un bug de configuration de ton serveur SQL ou du plugin en question...
Statut changé à En cours

Comme indiqué plus bas dans le thread que tu pointes, ton problème vient du fait que tu utilises MyISAM et non InnoDB cf https://stackoverflow.com/a/50689604 J'hésite à répondre que c'est un bug de configuration de ton serveur SQL ou du plugin en question... **Statut changé à En cours**
Poster

Puisque tu parles de InoDB : Il n'est pas possible en l'état d'utilise SPIP avec InoDB : il force MyISAM...

Et #4277 fourni le patch pour résoudre ça (et fonctionne en production sur l'Académie de Rennes)

Puisque tu parles de InoDB : Il n'est pas possible en l'état d'utilise SPIP avec InoDB : il force MyISAM... Et #4277 fourni le patch pour résoudre ça (et fonctionne en production sur l'Académie de Rennes)
b_b commented 3 years ago
Owner

Évitons de digresser, on ferme :)
Statut changé à Fermé

Évitons de digresser, on ferme :) **Statut changé à Fermé**
Owner

Salut

Je me permets de réouvrir ce ticket. Si on migre un vieux site sur un nouveau serveur. L'export SQL va indiquer le moteur MyIsam.
Si par la suite on souhaite monter de version on tomber sur l'erreur des index trop courts. Cette erreur est silencieuse et le site se mets à jour sans dire qu'il a un planté.
Sur une migration 1.9.2 vers 3.2, la mise à jour plante sur spip_urls et spip_index qui utilise ce type d'index.

Il serait bon de faire un contrôle amont pour vérifier que spip pourra se mettre à jour.
Statut changé à En cours

Salut Je me permets de réouvrir ce ticket. Si on migre un vieux site sur un nouveau serveur. L'export SQL va indiquer le moteur MyIsam. Si par la suite on souhaite monter de version on tomber sur l'erreur des index trop courts. Cette erreur est silencieuse et le site se mets à jour sans dire qu'il a un planté. Sur une migration 1.9.2 vers 3.2, la mise à jour plante sur spip_urls et spip_index qui utilise ce type d'index. Il serait bon de faire un contrôle amont pour vérifier que spip pourra se mettre à jour. **Statut changé à En cours**
Poster

Une autre piste donnée par b_b sur IRC :

https://florent.poinsaut.fr/2018/08/17/mysql-mariadb-index-column-size-too-large-the-maximum-column-size-is-767-bytes/ :

  • 767 octets est la limite de préfixe déclaré pour les tables InnoDB dans les versions antérieures à la 5.7 de MySQL et dans les versions antérieurs à la 10.2 de MariaDB.
  • A partir de la version 5.7 de MySQL et la 10.2 de MariaDB, cette limite a été augmentée à 3072 octets.

=> bingo on est en 10.1.41-MariaDB-1~stretch

  • L’encodage (latin1, UTF8, UTF8mb4, etc.) peut jouer sur cette taille. Puisqu’en UTF8 un caractère prend 3 octets, il faut diviser la taille disponible par 3 pour trouver la longueur maximale de préfixe d’index. Et par 4 si on utilise UTF8mb4.

=> re bingo on est en utf8

Une piste ici :

global.innodb_large_prefix = 1

https://stackoverflow.com/a/22873006
https://github.com/go-gitea/gitea/issues/2979#issuecomment-412607116
https://answers.launchpad.net/maria/+question/241612

Amha c'est l'option qu'il nous faut tant qu'on est pas en mariadb > 10.1, sinon il faut passer en mariadb 10.3 cf :

https://github.com/go-gitea/gitea/issues/2979#issuecomment-421000381

PS : j'utilise mariadb 10.3 en local et je n'ai pas ce problème.

Une autre piste donnée par b_b sur IRC : https://florent.poinsaut.fr/2018/08/17/mysql-mariadb-index-column-size-too-large-the-maximum-column-size-is-767-bytes/ : - 767 octets est la limite de préfixe déclaré pour les tables InnoDB dans les versions antérieures à la 5.7 de MySQL et dans les versions antérieurs à la 10.2 de MariaDB. - A partir de la version 5.7 de MySQL et la 10.2 de MariaDB, cette limite a été augmentée à 3072 octets. => bingo on est en 10.1.41-MariaDB-1~stretch - L’encodage (latin1, UTF8, UTF8mb4, etc.) peut jouer sur cette taille. Puisqu’en UTF8 un caractère prend 3 octets, il faut diviser la taille disponible par 3 pour trouver la longueur maximale de préfixe d’index. Et par 4 si on utilise UTF8mb4. => re bingo on est en utf8 Une piste ici : global.innodb_large_prefix = 1 https://stackoverflow.com/a/22873006 https://github.com/go-gitea/gitea/issues/2979#issuecomment-412607116 https://answers.launchpad.net/maria/+question/241612 Amha c'est l'option qu'il nous faut tant qu'on est pas en mariadb > 10.1, sinon il faut passer en mariadb 10.3 cf : https://github.com/go-gitea/gitea/issues/2979#issuecomment-421000381 PS : j'utilise mariadb 10.3 en local et je n'ai pas ce problème.
Poster

Pour info, même problème avec la table spip_mailsubscribers et UNIQUE email (email(255))

Pour info, même problème avec la table spip_mailsubscribers et UNIQUE email (email(255))
Owner

Du coup c'est de la documentation, non ?
Version cible mise à 4.0

Du coup c'est de la documentation, non ? **Version cible mise à 4.0**
b_b commented 1 year ago
Owner
There is no content yet.
Poster

Bonjour,

Ayant eu le problème en local avec Laragon, voici ce que j'ai rajouté dans le fichier my.ini de mon installation :

[mysqld]
innodb_file_format=Barracuda
innodb_file_per_table=ON
innodb_large_prefix=ON
innodb_default_row_format=dynamic
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE"

Ceci a permis au Noizetier de s'installer correctement

Bonjour, Ayant eu le problème en local avec Laragon, voici ce que j'ai rajouté dans le fichier my.ini de mon installation : ``` [mysqld] innodb_file_format=Barracuda innodb_file_per_table=ON innodb_large_prefix=ON innodb_default_row_format=dynamic character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE" ``` Ceci a permis au Noizetier de s'installer correctement

J'ai le même problème sur une installation fraiche de SPIP4 sur Mariadb 10.6 .

J'ai forcé l'encodage utf8mb4_unicode_ci en suivant les recommandations de
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html

(l'encodage par défaut de cette version de Mariadb est utf8mb3)

La création de la table spip_meta plante.
Du coup il me faut changer la clé de spip_meta

La requête fautive est

CREATE  TABLE IF NOT EXISTS `spip4mb4`.spip40_meta (
                nom VARCHAR (255)  NOT NULL,
                valeur text  DEFAULT '',
                impt ENUM('non', 'oui') DEFAULT 'oui' NOT NULL,
                maj TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (nom)) ENGINE=MyISAM

Cela passe si je modifie la longueur de la clé, bien entendu.

Il faudrait que je creuse pour que l'installation de SPIP génère une base InnoDB, c'est cela ?

Erreur dans le fichier tmp/log/mysqlmysql.log:
2022-02-22 16:55:52 88.174.137.67 (pid 1099739) :Pri:ERREUR: Erreur 1071 de mysql: Specified key was too long; max key length is 1000 bytes

$> mariadb --version
mariadb  Ver 15.1 Distrib 10.6.7-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

$> /usr/bin/php7.4 -v
PHP 7.4.28 (cli) (built: Feb 17 2022 16:15:33) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.28, Copyright (c), by Zend Technologies

A noter que si j'exécute manuellement la requête sql sans préciser le format de la table, tout passe.

L'export que je retrouve depuis Adminer est alors le suivant :

DROP TABLE IF EXISTS `spip40_meta`;
CREATE TABLE `spip40_meta` (
  `nom` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `valeur` text COLLATE utf8mb4_unicode_ci DEFAULT '',
  `impt` enum('non','oui') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'oui',
  `maj` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

D'où provient ce MyISAM généré par SPIP à l'installation ?

J'ai le même problème sur une installation fraiche de SPIP4 sur Mariadb 10.6 . J'ai forcé l'encodage utf8mb4_unicode_ci en suivant les recommandations de https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html (l'encodage par défaut de cette version de Mariadb est utf8mb3) La création de la table spip_meta plante. Du coup il me faut changer la clé de spip_meta La requête fautive est ``` CREATE TABLE IF NOT EXISTS `spip4mb4`.spip40_meta ( nom VARCHAR (255) NOT NULL, valeur text DEFAULT '', impt ENUM('non', 'oui') DEFAULT 'oui' NOT NULL, maj TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (nom)) ENGINE=MyISAM ``` Cela passe si je modifie la longueur de la clé, bien entendu. Il faudrait que je creuse pour que l'installation de SPIP génère une base InnoDB, c'est cela ? Erreur dans le fichier tmp/log/mysqlmysql.log: `2022-02-22 16:55:52 88.174.137.67 (pid 1099739) :Pri:ERREUR: Erreur 1071 de mysql: Specified key was too long; max key length is 1000 bytes` ``` $> mariadb --version mariadb Ver 15.1 Distrib 10.6.7-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 $> /usr/bin/php7.4 -v PHP 7.4.28 (cli) (built: Feb 17 2022 16:15:33) ( NTS ) Copyright (c) The PHP Group Zend Engine v3.4.0, Copyright (c) Zend Technologies with Zend OPcache v7.4.28, Copyright (c), by Zend Technologies ``` A noter que si j'exécute manuellement la requête sql sans préciser le format de la table, tout passe. L'export que je retrouve depuis Adminer est alors le suivant : ``` DROP TABLE IF EXISTS `spip40_meta`; CREATE TABLE `spip40_meta` ( `nom` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `valeur` text COLLATE utf8mb4_unicode_ci DEFAULT '', `impt` enum('non','oui') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'oui', `maj` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`nom`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ``` D'où provient ce MyISAM généré par SPIP à l'installation ?
Owner
@gilles.vincent oui le problème a aussi été signalé ici https://discuter.spip.net/t/alwaysdata-installation-de-spip-4/158032/6

Ce n'est pas le même problème.

Il faudrait:

  • soit créer la table spip_meta en innodb
  • soit changer la longueur de la clé de spip_meta

Je ne l'ai pas mentionné, mais la table spip_urls n'est pas générée non plus à l'installation (toujours le même problème de taille de clé supérieure à 1000 octets)

La question qui me semble se poser est quand même :
Pourquoi est-ce que ces 2 tables ont besoin d'un index sur un varchar(255) ?

Ce n'est pas le même problème. Il faudrait: - soit créer la table spip_meta en innodb - soit changer la longueur de la clé de spip_meta Je ne l'ai pas mentionné, mais la table spip_urls n'est pas générée non plus à l'installation (toujours le même problème de taille de clé supérieure à 1000 octets) La question qui me semble se poser est quand même : Pourquoi est-ce que ces 2 tables ont besoin d'un index sur un varchar(255) ?
Owner

Ce n'est pas le même problème.

Ha, ben désolé pour le bruit, je pensais que la non création de la table spip_meta sous mariadb 10.6 mentionné dans les deux posts concernait un problème similaire...

> Ce n'est pas le même problème. Ha, ben désolé pour le bruit, je pensais que la non création de la table spip_meta sous mariadb 10.6 mentionné dans les deux posts concernait un problème similaire...

Je viens de voir que j'ai eu exactement le même problème que
https://discuter.spip.net/t/alwaysdata-installation-de-spip-4/158032

Ce problème est documenté comme étant un bug (ou limite) de MyISAM et utf8 : https://bugs.mysql.com/bug.php?id=4541

Peut-être qu'éviter de forcer le type de base (comme le suggère déjà RealEt) et laisser le serveur gérer serait une bonne méthode.
#4277
(est-ce vraiment pertinent de forcer le type de base ?)

Je viens de voir que j'ai eu exactement le même problème que https://discuter.spip.net/t/alwaysdata-installation-de-spip-4/158032 Ce problème est documenté comme étant un bug (ou limite) de MyISAM et utf8 : https://bugs.mysql.com/bug.php?id=4541 Peut-être qu'éviter de forcer le type de base (comme le suggère déjà RealEt) et laisser le serveur gérer serait une bonne méthode. https://git.spip.net/spip/spip/issues/4277 (est-ce vraiment pertinent de forcer le type de base ?)

Pareil

Pareil
Poster

Et je viens de me rendre compte avec SPIP 4.1.2 que 2 tables de SPIP sont aussi concernées :

  • spip_meta
  • spip_urls
2022-05-20 15:03:14 ::1 (pid 43744) :Pri:ERREUR: Erreur 1071 de mysql: Specified key was too long; max key length is 1000 bytes
in C:\laragon\www\spip412zip\ecrire\base\create.php L74 [sql_create(),creer_ou_upgrader_table(),alterer_base(),creer_base(),install_bases(),install_etape_3_dist(),exec_install_dist()]
CREATE  TABLE IF NOT EXISTS `spip412zip`.spip_meta (
		nom VARCHAR (255)  NOT NULL,
		valeur text  DEFAULT '',
		impt ENUM('non', 'oui') DEFAULT 'oui' NOT NULL,
		maj TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		PRIMARY KEY (nom)) ENGINE=MyISAM
2022-05-20 15:06:45 ::1 (pid 43744) :Pri:ERREUR: Erreur 1071 de mysql: Specified key was too long; max key length is 1000 bytes
in C:\laragon\www\spip412zip\ecrire\base\create.php L74 [sql_create(),creer_ou_upgrader_table(),alterer_base(),creer_base(),install_bases(),install_etape_3_dist(),exec_install_dist()]
CREATE  TABLE IF NOT EXISTS `spip412test`.spip_urls (
		id_parent bigint(21) DEFAULT '0' NOT NULL,
		url VARCHAR(255)  NOT NULL,
		type varchar(25)  DEFAULT 'article' NOT NULL,
		id_objet bigint(21) NOT NULL,
		date DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL,
		segments SMALLINT(3) DEFAULT '1' NOT NULL,
		perma TINYINT(1) DEFAULT '0' NOT NULL,
		langue VARCHAR(10)  DEFAULT '' NOT NULL,
		PRIMARY KEY (id_parent, url),
		KEY type (type, id_objet),
		KEY langue (langue),
		KEY url (url)) ENGINE=MyISAM

Par contre, l'instalation a pu se faire sans problème en indiquant innoDB comme moteur de MySQL.
define('_MYSQL_ENGINE', 'InnoDB');

(et https://dba.stackexchange.com/questions/283655/mariadb-error-1071-specified-key-was-too-long-max-key-length-is-1000-bytes confirme que c'est mort avec MyISAM)

Et je viens de me rendre compte avec SPIP 4.1.2 que 2 tables de SPIP sont aussi concernées : * spip_meta * spip_urls ``` 2022-05-20 15:03:14 ::1 (pid 43744) :Pri:ERREUR: Erreur 1071 de mysql: Specified key was too long; max key length is 1000 bytes in C:\laragon\www\spip412zip\ecrire\base\create.php L74 [sql_create(),creer_ou_upgrader_table(),alterer_base(),creer_base(),install_bases(),install_etape_3_dist(),exec_install_dist()] CREATE TABLE IF NOT EXISTS `spip412zip`.spip_meta ( nom VARCHAR (255) NOT NULL, valeur text DEFAULT '', impt ENUM('non', 'oui') DEFAULT 'oui' NOT NULL, maj TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (nom)) ENGINE=MyISAM 2022-05-20 15:06:45 ::1 (pid 43744) :Pri:ERREUR: Erreur 1071 de mysql: Specified key was too long; max key length is 1000 bytes in C:\laragon\www\spip412zip\ecrire\base\create.php L74 [sql_create(),creer_ou_upgrader_table(),alterer_base(),creer_base(),install_bases(),install_etape_3_dist(),exec_install_dist()] CREATE TABLE IF NOT EXISTS `spip412test`.spip_urls ( id_parent bigint(21) DEFAULT '0' NOT NULL, url VARCHAR(255) NOT NULL, type varchar(25) DEFAULT 'article' NOT NULL, id_objet bigint(21) NOT NULL, date DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL, segments SMALLINT(3) DEFAULT '1' NOT NULL, perma TINYINT(1) DEFAULT '0' NOT NULL, langue VARCHAR(10) DEFAULT '' NOT NULL, PRIMARY KEY (id_parent, url), KEY type (type, id_objet), KEY langue (langue), KEY url (url)) ENGINE=MyISAM ``` Par contre, l'instalation a pu se faire sans problème en indiquant innoDB comme moteur de MySQL. `define('_MYSQL_ENGINE', 'InnoDB');` (et https://dba.stackexchange.com/questions/283655/mariadb-error-1071-specified-key-was-too-long-max-key-length-is-1000-bytes confirme que c'est mort avec MyISAM)
b_b commented 2 weeks ago
Owner

Même si je me suis fait bouler gentiment par @gilles.vincent je reviens sur ce ticket :)

@RealET confirme que ça peut être contourné par define('_MYSQL_ENGINE', 'InnoDB'); qu'on a introduit entre temps, on peut donc fermer le ticket, non ?

Même si je me suis fait bouler gentiment par @gilles.vincent je reviens sur ce ticket :) @RealET confirme que ça peut être contourné par `define('_MYSQL_ENGINE', 'InnoDB');` qu'on a introduit entre temps, on peut donc fermer le ticket, non ?
Owner

Bon, je tombe là dessus pour la première fois aussi…
Ça tente de créer des tables en MyIsam et ça ne fonctionne pas :/

Bon, je tombe là dessus pour la première fois aussi… Ça tente de créer des tables en `MyIsam` et ça ne fonctionne pas :/
Sign in to join this conversation.
No Milestone
No project
No Assignees
7 Participants
Notifications
Due Date

No due date set.

Dependencies

This issue currently doesn't have any dependencies.

Loading…
There is no content yet.