Modifier la limite pour “Taille de la ligne Mysql trop grande”

La question a également été posée sur serverfault .

Vous voudrez peut-être jeter un oeil à cet article qui explique beaucoup sur la taille des lignes MySQL. Il est important de noter que même si vous utilisez des champs TEXT ou BLOB, la taille de votre ligne pourrait toujours dépasser 8 Ko (limite pour InnoDB) car elle stocke les 768 premiers octets pour chaque champ en ligne de la page.

Le moyen le plus simple de résoudre ce problème est d’utiliser le format de fichier Barracuda avec InnoDB. Cela élimine complètement le problème en stockant uniquement le pointeur de 20 octets sur les données de texte au lieu de stocker les 768 premiers octets.

La méthode qui fonctionnait pour le PO était la suivante:

1. Ajoutez ce qui suit au fichier my.cnf sous la section [mysqld] .

    innodb_file_per_table=1 innodb_file_format = Barracuda 

2. ALTER la table pour utiliser ROW_FORMAT=COMPRESSED .

    ALTER TABLE nombre_tabla ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; 

Il est possible que ce qui précède ne résout toujours pas vos problèmes. C’est un bogue connu (et vérifié) avec le moteur InnoDB , et une solution temporaire pour le moment consiste à utiliser le moteur MyISAM comme stockage temporaire. Donc, dans votre fichier my.cnf :

 internal_tmp_disk_storage_engine=MyISAM 

J’ai rencontré ce problème récemment et l’ai résolu d’une manière différente. Si vous utilisez la version 5.6.20 de MySQL, il existe un bogue connu dans le système. Voir les documents MySQL

Important En raison du bogue n ° 69477, les écritures de journalisation pour les champs BLOB volumineux stockés en externe peuvent remplacer le sharepoint contrôle le plus récent. Pour corriger ce bogue, un correctif introduit dans MySQL 5.6.20 limite la taille du journal de redo. BLOB écrit à 10% de la taille du fichier journal de redo. En raison de cette limite, innodb_log_file_size doit être défini sur une valeur supérieure à 10 fois la plus grande taille de données BLOB trouvée dans les lignes de vos tables plus la longueur des autres champs de longueur variable (champs de type VARCHAR, VARBINARY et TEXT).

Dans ma situation, la table de blob offensante était d’environ 16 Mo. Ainsi, la façon dont je l’ai résolu était d’append une ligne à my.cnf qui assurait que j’avais au moins 10 fois ce montant, puis quelques-unes:

innodb_log_file_size = 256M

Si vous pouvez changer de moteur et utiliser MyISAM au lieu d’InnoDB, cela devrait vous aider:

ENGINE=MyISAM

Il y a deux mises en garde avec MyISAM (sans doute plus):

1. Vous ne pouvez pas utiliser de transactions.
2. Vous ne pouvez pas utiliser de contraintes de clé étrangère.

J’aimerais partager une réponse géniale, cela pourrait être utile. Crédits Bill Karwin voir ici https://dba.stackexchange.com/questions/6598/innodb-create-table-error-row-size-too-large

Ils varient selon le format de fichier InnoDB. Actuellement, il existe 2 formats appelés Antelope et Barracuda.

Le fichier de tablespace central (ibdata1) est toujours au format Antelope. Si vous utilisez file-per-table, vous pouvez faire en sorte que les fichiers individuels utilisent le format Barracuda en définissant innodb_file_format = Barracuda dans my.cnf.

Points de base:

1. Une page de 16 Ko de données InnoDB doit contenir au moins deux lignes de données. De plus, chaque page a un en-tête et un pied de page contenant les sums de contrôle de page et le numéro de séquence du journal, etc. C’est là que vous obtenez votre limite d’un peu moins de 8 Ko par ligne.

2. Les types de données de taille fixe tels que INTEGER, DATE, FLOAT, CHAR sont stockés sur cette page de données principale et sont comptabilisés dans la limite de taille des lignes.

3. Les types de données de taille variable tels que VARCHAR, TEXT, BLOB sont stockés sur des pages de débordement, de sorte qu’ils ne sont pas totalement comptabilisés dans la limite de taille des lignes. Dans Antelope, jusqu’à 768 octets de ces colonnes sont stockés sur la page de données principale en plus d’être stockés sur la page de débordement. Barracuda prend en charge un format de ligne dynamic, de sorte qu’il ne peut stocker qu’un pointeur de 20 octets sur la page de données principale.

4. Les types de données de taille variable sont également précédés d’un ou plusieurs octets pour coder la longueur. Et le format de ligne InnoDB a également un tableau de décalages de champ. Il y a donc une structure interne plus ou moins documentée dans leur wiki.

Barracuda prend également en charge un ROW_FORMAT = COMPRESSED pour gagner en efficacité de stockage pour les données de débordement.

Je dois également dire que je n’ai jamais vu une table bien conçue dépasser la taille limite des lignes. C’est une forte “odeur de code” que vous ne respectez pas la condition de groupes répétitifs de la première forme normale.

Définissez les suites de votre fichier my.cnf et redémarrez le serveur mysql.

 innodb_ssortingct_mode = 0 

Après avoir passé des heures, j’ai trouvé la solution: lancez le code SQL suivant dans votre admin MySQL pour convertir la table en MyISAM:

 USE db_name; ALTER TABLE table_name ENGINE=MYISAM; 

J’ai eu le même problème, cela l’a résolu pour moi:

 ALTER TABLE `my_table` ROW_FORMAT=DYNAMIC; 

De la documentation MYSQL:

Le format de ligne DYNAMIC conserve l’efficacité du stockage de la ligne entière dans le nœud d’index s’il convient (comme le font les formats COMPACT et REDUNDANT), mais ce nouveau format évite le problème du remplissage de nœuds B-tree avec un grand nombre d’octets de données. longues colonnes. Le format DYNAMIC est basé sur l’idée que si une partie d’une longue valeur de données est stockée hors page, il est généralement plus efficace de stocker toute la valeur hors page. Avec le format DYNAMIC, les colonnes plus courtes risquent de restr dans le nœud B-tree, ce qui minimise le nombre de pages de débordement nécessaires pour une ligne donnée.

J’ai aussi rencontré le même problème. Je résous le problème en exécutant le sql suivant:

 ALTER ${table} ROW_FORMAT=COMPRESSED; 

Mais, je pense que vous devriez savoir à propos du stockage en rangée .
Il existe deux types de colonnes: les colonnes de longueur variable (telles que les types VARCHAR, VARBINARY et BLOB et TEXT) et les colonnes de longueur fixe . Ils sont stockés dans différents types de pages.

Les colonnes de longueur variable constituent une exception à cette règle. Les colonnes telles que BLOB et VARCHAR qui sont trop longues pour tenir sur une page B-tree sont stockées sur des pages de disque affectées séparément, appelées pages de débordement. Nous appelons ces colonnes des colonnes hors page. Les valeurs de ces colonnes sont stockées dans des listes de pages de débordement à lien unique, et chacune de ces colonnes possède sa propre liste d’une ou plusieurs pages de débordement. Dans certains cas, tout ou un préfixe de la valeur de colonne longue est stocké dans l’arborescence, afin d’éviter de gaspiller du stockage et de ne plus avoir à lire une page séparée.

et quand le but de définir ROW_FORMAT est

Lorsqu’une table est créée avec ROW_FORMAT = DYNAMIC ou ROW_FORMAT = COMPRESSED, InnoDB peut stocker des valeurs longues de colonne de longueur variable (pour les types VARCHAR, VARBINARY et BLOB et TEXT) complètement hors page, avec l’enregistrement d’index cluster ne contenant que 20- pointeur d’octet vers la page de débordement.

Vous voulez en savoir plus sur les formats de lignes DYNAMIC et COMPRESSED

Les autres réponses répondent à la question posée. Je vais aborder la cause sous-jacente: une mauvaise conception du schéma.

Ne pas afficher un tableau entre les colonnes. Vous avez ici 3 * 10 colonnes qui doivent être transformées en 10 lignes de 3 colonnes dans une nouvelle table (plus id , etc.)

Votre table Main n’aurait que

 id int(11) No name text No date date No time time No schedule int(11) No category int(11) No status int(11) No admin_id int(11) No 

Votre table supplémentaire ( Top ) aurait

 id int(11) No -- for joining to Main seq TINYINT UNSIGNED -- containing 1..10 img varchar(255) No title varchar(255) No desc text No PRIMARY KEY(id, seq) -- so you can easily find the 10 top_titles 

Il y aurait 10 (ou moins? Ou plus?) Lignes dans Top pour chaque id .

Cela élimine votre problème d’origine et nettoie le schéma. (Ce n’est pas la “normalisation”, comme cela a été débattu dans certains commentaires.)

Ne passez pas à MyISAM; ça s’en va.
Ne vous inquiétez pas pour ROW_FORMAT .

Vous devrez changer votre code pour effectuer la JOIN et gérer plusieurs lignes au lieu de plusieurs colonnes.

J’ai rencontré ce problème lorsque j’essayais de restaurer une firebase database mysql sauvegardée à partir d’un autre serveur. Ce qui a résolu ce problème pour moi a été d’append certains parameters à my.conf (comme dans les questions ci-dessus) et de modifier en plus le fichier de sauvegarde sql:

Étape 1: ajoutez ou modifiez les lignes suivantes dans my.conf:

 innodb_page_size=32K innodb_file_format=Barracuda innodb_file_per_table=1 

Étape 2 ajoutez ROW_FORMAT = DYNAMIC à l’instruction create table dans le fichier de sauvegarde sql de la table à l’origine de cette erreur:

 DROP TABLE IF EXISTS `problematic_table`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `problematic_table` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, ... PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 ROW_FORMAT=DYNAMIC; 

le changement important ci-dessus est ROW_FORMAT = DYNAMIC; (qui n’était pas inclus dans le fichier de sauvegarde SQL d’Orignal)

source qui m’a aidé à résoudre ce problème: MariaDB et InnoDB MySQL Taille de la ligne trop grande