Manuel de référence MySQL 5.0

Si InnoDB renvoie une erreur de système d'exploitation lors d'une opération sur fichier, habituellement le problème est l'un des suivants.

• Vous n'avez pas créé les dossiers de données ou de logs InnoDB.
• mysqld n'a pas le droit de créer des fichiers dans ces dossiers.
• mysqld ne lit pas le bon fichier my.cnf ou my.ini, et donc ne voit pas les options que vous spécifiez.
• Le disque ou l'espace disque alloué est plein.
• Vous avez créé un sous-dossier dont le nom est le même que celui d'un fichier de données que vous avez spécifié.
• Il y a une erreur de syntaxe dans innodb_data_home_dir ou innodb_data_file_path.

Si quelque chose se passe mal lors de la création d'une base de données InnoDB, vous devez effacer tous les fichiers créés par InnoDB. Cela inclut tous les fichiers de données, tous les journaux, les archives. Dans le cas où vous avez déjà créé des tables InnoDB, effacez aussi les fichiers .frm (et tous les fichiers .ibd si vous utilisez les espaces de tables multiples) concernés dans le dossier de données de MySQL. Vous pourrez alors essayer une nouvelle création de base de données InnoDB.

Par défaut, chaque client qui se connecte à MySQL commence avec le mode d'autovalidation activé, ce qui valide automatiquement toutes les requêtes que vous soumettez. Pour utiliser des requêtes multicommandes, vous pouvez désactiver l'autovalidation avec la commande SET AUTOCOMMIT = 0 et utiliser les commandes COMMIT et ROLLBACK pour valider ou annuler vos transactions. Si vous voulez laisser le mode d'autovalidation tranquille, vous pouvez placer vos commandes entre START TRANSACTION et COMMIT ou ROLLBACK. Avant MySQL 4.0.11, vous deviez utiliser la commande BEGIN au lieu de START TRANSACTION. L'exemple suivant montre deux transactions. La première est validée, et la seconde est annulée.

shell> mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.50-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
    -> TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A    | B      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>

Avec des interfaces comme PHP, Perl DBI/DBD, JDBC, ODBC ou l'interface C, vous pouvez envoyer des commandes de contrôle de transaction comme COMMIT au serveur sous la forme de chaîne, tout comme une autre commande SELECT et INSERT. Certaines interfaces proposent des fonctions spécifiques pour les validations et annulations de transactions.

Important : vous ne devez pas convertir les tables de la base mysql, telles que user ou host en type InnoDB. Ces tables doivent conserver le moteur MyISAM.

Si vous voulez que toutes les tables non-système soient créées directement en MySQL, depuis MySQL 3.23.43, ajoutez la ligne default-table-type=innodb dans la section [mysqld] de votre fichier my.cnf ou my.ini.

InnoDB n'a pas d'optimisation particulière pour séparer la création d'index. Par conséquent, cela ne sert à rien d'exporter et de réimporter les données de la table pour créer les index après. Le moyen le plus rapide pour mettre la table au format InnoDB est d'insérer directement les lignes dans une table InnoDB, c'est-à-dire, utiliser ALTER TABLE ... TYPE=INNODB, ou créer une table InnoDB vide, avec la même définition et de faire l'insertion de toutes les lignes avec INSERT INTO ... SELECT * FROM ....

Si vous avez une contrainte UNIQUE sur des clés secondaires, depuis MySQL 3.23.52, vous pouvez accélérer l'importation dans les tables en désactivant la vérification de cette contrainte durant l'insertion : SET UNIQUE_CHECKS=0; Pour les grosses tables, cela économise beaucoup d'accès disques, car InnoDB peut alors utiliser un buffer d'insertion pour écrire les lignes par bloc.

Pour avoir un meilleur contrôle sur le processus d'insertion, il est mieux de faire les insertions des grosses tables par blocs :

INSERT INTO newtable SELECT * FROM oldtable
   WHERE yourkey > something AND yourkey <= somethingelse;

Une fois que toutes les données ont été insérées dans la table, vous pouvez la renommer.

Durant la conversion des grosses tables, vous pouvez donner à InnoDB un gros buffer pour réduire les accès disques. Ne le portez pas au-delà de 80 % de votre mémoire physique. Donnez aussi de gros fichiers de logs, et un buffer de log important.

Assurez-vous que vous n'allez pas manquer d'espace : les tables InnoDB prennent beaucoup plus d'espace que les tables MyISAM. Si la commande ALTER TABLE manque d'espace, elle va lancer une annulation, et cela peut prendre des heures sur un disque plein. Durant les insertions, InnoDB utilise un buffer d'insertion pour rassembler les lignes d'index secondaires avec les index, en groupe. Cela économise beaucoup d'accès disques. Durant l'annulation, ce mécanisme n'est pas utilisé, et elle peut prendre jusqu'à 30 fois plus de temps.

Dans le cas d'une annulation immensément longue, si vous n'avez pas de données critiques dans votre base, il est mieux de tuer le processus, d'effacer tous les fichiers, et de recommencer, plutôt que d'attendre la fin de l'annulation. Pour la procédure complète, voyez Section 15.9.1, « Forcer la restauration »15.9.1. Forcer la restauration.

Si vous spécifiez une colonne AUTO_INCREMENT dans une table, la table InnoDB va ajouter dans le dictionnaire de données un compteur spécial appelé le compteur autoincrément, qui est utilisé pour assigner les nouvelles valeurs de la colonne. Le compteur est stocké uniquement en mémoire, et non pas sur le disque.

InnoDB utilise l'algorithme suivant pour initialiser le compteur autoincrément pour la table T qui contient la colonne de type AUTO_INCREMENT appelée ai_col : après le démarrage du serveur, lorsqu'un utilisateur fait une insertion dans la table T, InnoDB exécute la commande suivante :

SELECT MAX(ai_col) FROM T FOR UPDATE;

La valeur lue par la commande est incrémentée d'une unité, et assignée à la colonne autoincrément et au compteur de table. Si la table est vide, la valeur de 1 est assignée. Si le compteur n'est pas initialisé et que l'utilisateur appelle la commande SHOW TABLE STATUS qui affiche les informations de la table T, le compteur est initialisé mais pas incrémenté, et stocké pour être utilisé ultérieurement. Notez que dans cette initialisation, nous posons un verrou exclusif en lecture sur la table, et le verrou durera jusqu'à la fin de la transaction.

InnoDB suit la même procédure pour initialiser un compteur autoincrément avec une table fraîchement créée.

Notez que si l'utilisateur spécifie la valeur NULL ou 0 pour la colonne AUTO_INCREMENT dans la commande INSERT, InnoDB traitera la ligne comme si aucune valeur n'avait été spécifiée, et générera une nouvelle valeur.

Après l'initialisation du compteur d'autoincrémentation, si un utilisateur insère une ligne qui définit explicitement la valeur de la colonne, et que cette valeur est plus grande que la valeur courante du compteur, le compteur prend alors cette valeur. Si l'utilisateur ne spécifie pas de valeur, InnoDB incrémente le compteur d'une unité, et assigne une nouvelle valeur à cette colonne.

Lorsque vous accédez au compteur d'autoincrémentation, InnoDB utilise un verrou de table spécial, AUTO-INC, qui reste actif jusqu'à la fin de la commande SQL, et non pas la fin de la transaction. Le verrou spécial a été créé pour éviter les accès concurrents dans la table qui contient la colonne AUTO_INCREMENT. Deux transactions ne peuvent pas avoir le même verrou AUTO-INC simultanément.

Notez que vous pourriez voir des trous dans la séquence de valeur générée par AUTO_INCREMENT si vous annulez des transactions après avoir obtenu une valeur automatiquement.

Le comportement du mécanisme autoincrément n'est pas défini si vous assignez une valeur négative à la colonne, ou si cette dernière dépasse la capacité de la colonne.

Depuis la version 3.23.43b, InnoDB respecte les contraintes de clés étrangères.

La syntaxe des définitions de contraintes de clés étrangères de InnoDB est la suivante :

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

Les deux tables doivent être de type InnoDB, dans la table, il doit y avoir un INDEX où les clés étrangères sont listées comme première colonne, dans le même ordre, et dans la table référencée, il doit y avoir un INDEX où les colonnes référencées sont listées comme premières colonnes, dans le même ordre. Les préfixes d'index ne sont pas supportés pour les clés de contrainte.

InnoDB ne crée pas automatiquement les index nécessaires pour les clés étrangères : vous devez les créer vous-même. Les index sont nécessaires pour accélérer les vérifications de contrainte, et éviter un scan de table.

Les colonnes correspondantes de la contrainte dans la table et la table de référence doivent avoir le même type, pour éviter les conversions lors des comparaisons. La taille et la présence du signe pour les entiers doivent être les mêmes. La taille des chaînes doit être la même. Si vous spécifiez une action SET NULL, assurez-vous que vous n'avez pas déclaré les colonnes de la table fille NOT NULL.

Si MySQL vous retourne un numéro d'erreur 1005 lors de la commande CREATE TABLE, et un message d'erreur de numéro 150, alors la création de la table a échoué à cause de la contrainte de clé étrangère, qui n'a pas été correctement formulée. Similairement, si une commande ALTER TABLE échoue et indique une erreur 150, c'est que la définition de la clé étrangère est incorrectement formulée dans la table modifiée. Depuis la version 4.0.13, vous pouvez utiliser la commande SHOW INNODB STATUS pour avoir une explication détaillée de la dernière erreur de clé étrangère InnoDB sur le serveur.

Depuis la version 3.23.50, InnoDB ne vérifie pas la clé étrangère pour les clés étrangères ou les clés référencées qui contiennent des valeurs NULL.

Une entorse aux standards : si dans la table parente, il y a plusieurs lignes qui ont la même valeur de clé référencée, alors InnoDB effectue les vérifications de clé étrangères comme si les autres parents avec la même valeur de clé n'existaient pas. Par exemple, si vous avez défini une contrainte de type RESTRICT et qu'il y a une ligne fille avec plusieurs lignes parentes, InnoDB n'acceptera pas l'effacement d'aucun des parents.

Depuis la version 3.23.50, vous pouvez aussi associer la clause ON DELETE CASCADE ou ON DELETE SET NULL avec la contrainte de clé étrangère. Les options correspondantes ON UPDATE sont disponibles depuis la version 4.0.8. Si ON DELETE CASCADE est spécifiée, et qu'une ligne de la table parente est effacée, alors InnoDB va automatiquement effacer toutes les lignes qui sont dans la table fille et dont les valeurs de clé étrangère sont celles référencées dans la ligne parente. Si ON DELETE SET NULL est spécifiée, les lignes filles sont automatiquement modifiées pour que la colonne de la clé étrangère prenne la valeur de NULL.

Une entorse aux standards : si ON UPDATE CASCADE ou ON UPDATE SET NULL cascade récursivement jusqu'à la même table, elle agira comme pour un RESTRICT. Cela est fait pour éviter les boucles infinies des modifications en cascade. Une clause ON DELETE SET NULL autoréférente, d'un autre côté, fonctionne depuis la version 4.0.13. La clause ON DELETE CASCADE autoréférente a toujours fonctionné.

Un exemple :

CREATE TABLE parent(id INT NOT NULL,
                    PRIMARY KEY (id)
) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT,
                   INDEX par_ind (parent_id),
                   FOREIGN KEY (parent_id) REFERENCES parent(id)
                     ON DELETE CASCADE
) TYPE=INNODB;

Voici un exemple plus complexe où la table product_order a des clés étrangères sur deux tables. La première clé est un index à deux colonnes, dans la table product. Les autres clés sont monocolonnes, dans la table customer :

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                      PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                      product_category INT NOT NULL,
                      product_id INT NOT NULL,
                      customer_id INT NOT NULL,
                      PRIMARY KEY(no),
                      INDEX (product_category, product_id),
                      FOREIGN KEY (product_category, product_id)
                        REFERENCES product(category, id)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
                      INDEX (customer_id),
                      FOREIGN KEY (customer_id)
                        REFERENCES customer(id)) TYPE=INNODB;

Depuis la version 3.23.50, InnoDB vous permet d'ajouter une nouvelle clé à une table, grâce à la syntaxe

ALTER TABLE yourtablename
    ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

N'oubliez pas de commencer par créer les index nécessaires en premier !. Vous pouvez aussi ajouter des clés étrangères réflexives, en utilisant la commande ALTER TABLE.

Depuis la version 4.0.13, InnoDB supporte la syntaxe ALTER TABLE pour supprimer une clé étrangère :

ALTER TABLE yourtablename
    DROP FOREIGN KEY fk_symbol

Si la clause FOREIGN KEY inclut un nom de contrainte CONSTRAINT lors de la création, vous pouvez utiliser ce nom pour effacer la clé. Les contraintes peuvent porter un nom depuis MySQL 4.0.18. Sinon, la valeur fk_symbol est générée en interne par InnoDB lorsque la clé étrangère est créée. Pour savoir quel symbole utiliser pour effacer une clé étrangère, utilisez la commande SHOW CREATE TABLE. Par exemple :

mysql> SHOW CREATE TABLE ibtest11c\G
*************************** 1. row ***************************
       Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
  `A` int(11) NOT NULL auto_increment,
  `D` int(11) NOT NULL default '0',
  `B` varchar(200) NOT NULL default '',
  `C` varchar(175) default NULL,
  PRIMARY KEY  (`A`,`D`,`B`),
  KEY `B` (`B`,`C`),
  KEY `C` (`C`),
  CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB CHARSET=latin1
1 row in set (0.01 sec)

mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;

Depuis MySQL version 3.23.50, l'analyseur InnoDB autorise l'utilisation des guillemets obliques autour des noms de tables et colonnes dans une clause FOREIGN KEY ... REFERENCES .... Depuis MySQL 4.0.5, l'analyseur InnoDB prend aussi en compte la variable système lower_case_table_names.

Dans InnoDB en versions < 3.23.50, ALTER TABLE et CREATE INDEX ne doivent pas être utilisés avec des tables qui ont des contraintes de clés étrangères, ou qui sont référencées dans des clés étrangères : une commande ALTER TABLE supprime toutes les clés étrangères qui sont définies pour cette table. Vous ne devriez pas utiliser ALTER TABLE sur la table référencée, mais utiliser DROP TABLE puis CREATE TABLE pour modifier le schéma. Lorsque MySQL exécute la commande ALTER TABLE, il risque d'utiliser en interne la commande RENAME TABLE, et cela va poser des problèmes pour les clés étrangères qui reposent sur cette table. Une commande CREATE INDEX est traitée par MySQL comme une commande ALTER TABLE, et ces restrictions s'appliquent aussi.

Lorsqu'il vérifie les clés étrangères, InnoDB pose des verrous de lignes partagées sur les lignes des tables qu'il utilise. InnoDB vérifie immédiatement les contraintes de clés étrangères : la vérification n'attend pas la validation de la transaction.

Si vous voulez ignorer les contraintes de clés étrangères durant, par exemple, une opération de LOAD DATA, vous pouvez utiliser la commande SET FOREIGN_KEY_CHECKS=0.

InnoDB vous permet d'effacer n'importe quelle table, même si cela va casser les contraintes de clés étrangères qui référencent cette table. Lorsque vous supprimez une table, la contrainte de clé étrangère qui y était attachée est aussi supprimée.

Si vous recréez une table qui a été supprimée, sa définition doit se conformer aux contraintes des clés étrangères qui la référencent. Elle doit avoir les bons types et noms de colonnes, et doit avoir les bonnes clés, comme indiqué ci-dessus. Si ces contraintes ne sont pas vérifiées, MySQL vous gratifiera d'une erreur 1005, et vous enverra lire le message numéro 150.

Depuis la version 3.23.50 InnoDB retourne la définition de clé étrangère lorsque vous utilisez la commande

SHOW CREATE TABLE tbl_name;

De plus, mysqldump produit aussi les définitions correctes de tables, sans oublier les clés étrangères.

Vous pouvez aussi lister les clés étrangères d'une table T avec

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name'

Les contraintes de clés étrangères sont listées dans les commentaires de la table.

Lors des vérifications des contraintes, InnoDB pose des verrous de lignes sur les lignes parents ou enfants qu'il utilise. InnoDB vérifie immédiatement les contraintes de clés : la vérification n'est pas reportée jusqu'à la validation de la transaction.

Pour simplifier l'importation de données dans des tables qui ont des contraintes, mysqldump ajoute automatiquement la commande qui met la variable FOREIGN_KEY_CHECKS à 0, depuis MySQL version 4.1.1. Cela évite des problèmes spécifiques avec les tables qui doivent être chargées dans un ordre particulier. Pour les versions antérieures, vous pouvez désactiver manuellement la variable depuis mysql lors du chargement du fichier comme ceci :

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;

Cela vous permet de faire l'importation des données des tables dans n'importe quel ordre. Cela accélère aussi l'opération d'importation. FOREIGN_KEY_CHECKS est disponible depuis MySQL 3.23.52 et 4.0.3.

Mettre FOREIGN_KEY_CHECKS à 0 peut aussi être utile pour les opérations de LOAD DATA.

InnoDB permet l'effacement de n'importe quelle table, même si cela casse les contraintes de clés étrangères. Lorsque vous effacez une table, les contraintes définies sur cette table sont aussi effacées.

Si vous recréez une table qui a été effacée, elle doit avoir une définition qui est compatible avec les clés étrangères qui l'utilise. Elle doit avoir les bonnes colonnes et les index. Si cela n'est pas vrai, MySQL retourne une erreur 1005, et fait référence à un message d'erreur numéro 150.

La réplication MySQL fonctionne pour les tables InnoDB comme pour les tables MyISAM. Il est aussi possible d'utiliser la réplication pour que les tables de l'esclave ne soient pas les mêmes que les tables du maître. Par exemple, vous pouvez répliquer les modifications d'une table InnoDB sur le maître dans une table MyISAM sur l'esclave.

Pour configurer un nouvel esclave sur le maître, vous devez faire une copie de l'espace de table InnoDB, des fichiers de logs, ainsi que les fichiers .frm des tables InnoDB, et les placer sur l'esclave. Pour une procédure à suivre pour réaliser cela, voyez Section 15.10, « Transférer une base de données InnoDB vers une autre machine »15.10. Transférer une base de données InnoDB vers une autre machine.

Si vous pouvez arrêter le maître ou l'esclave, faites une sauvegarde à l'arrêt de l'espace de table InnoDB et des fichiers de logs, puis utilisez-les pour redémarrer l'esclave. Pour faire un nouvel esclave sans arrêter le serveur, utilisez le logiciel commercial InnoDB Hot Backup tool.

Il y a des limitations mineures à la réplication InnoDB :

• LOAD TABLE FROM MASTER ne fonctionne pas pour les tables InnoDB. Il y a des palliatifs : 1) exportez la table du maître, et envoyez-la sur l'esclave, ou, 2) utilisez ALTER TABLE tbl_name TYPE=MyISAM sur le maître avant de configurer la réplication avec LOAD TABLE tbl_name FROM MASTER, et ensuite, ALTER TABLE pour remettre les tables en mode InnoDB après cela.
• Avant MySQL 4.0.6, SLAVE STOP ne respectait pas la limite de transaction. Une transaction incomplète était annulée, et la prochaine commande SLAVE START n'exécutait que le reste de la transaction, ce qui conduisait à un échec.
• Avant MySQL 4.0.6, un crash de l'esclave au milieu d'une transaction multicommande causait le même problème que SLAVE STOP.
• Avant MySQL 4.0.11, la réplication de la commande SET FOREIGN_KEY_CHECKS=0 ne fonctionnait pas correctement.

La plupart de ces limitations peuvent être levées en utilisant un serveur récent, pour lequel les limitations n'existent pas.

Les transactions qui échouent sur le serveur n'affectent pas la réplication. La réplication MySQL est basée sur le log binaire où MySQL écrit les requêtes SQL qui modifient des données. Un esclave lit le log binaire du maître, et exécute les mêmes commandes SQLO. Cependant, les commandes d'une transaction ne sont pas écrites avant la fin de la transaction, où toutes les commandes sont écrites d'un coup. Si une transaction échoue, par exemple, à cause d'une clé étrangère, ou si la transaction est annulée, aucune requête ne sera écrite dans le log binaire, et la transaction ne sera pas du tout exécutée sur le serveur.

Depuis MySQL 4.1.1, vous pouvez stocker chaque table InnoDB et ses index dans son propre fichier. Cette fonctionnalité est appelée « espaces de tables multiples », car chaque table dispose de son propre espace de table.

Note importante : si vous passez en version InnoDB 4.1.1 ou plus récent, il devient très difficile de retourner en versions 4.0 ou 4.1.0 ! Ceci est dû au fait que les versions antérieures de InnoDB ne sont pas compatibles avec les espaces de tables multiples.

Si vous devez revenir à une vieille version 4.0, vous devez faire des exports des tables, et recréer tout votre espace de tables InnoDB. Si vous n'avez pas créé de nouvelles tables sous InnoDB >= 4.1.1, et que vous devez revenir rapidement en arrière, vous pouvez passer directement en versions 4.0.18, ou plus récent. Avant de faire un retour en arrière direct en versions 4.0, vous devez terminer toutes les connexions, et laisser mysqld vider les buffers d'insertion, jusqu'à ce que SHOW INNODB STATUS indique que le thread principal soit dans un état de waiting for server activity. Alors, vous pouvez éteindre le serveur mysqld et démarrer votre version 4.0.18 ou plus récent. Un retour en arrière direct n'est pas recommandé, car il n'a pas été totalement testé.

Depuis MySQL version 4.1.1, vous pouvez stocker chaque table InnoDB et ses index dans son propre fichier. Cette fonctionnalité est appelée espaces de tables multiples, car chaque table a son propre espace de table.

Vous pouvez activer cette fonctionnalité en ajoutant une ligne dans le groupe [mysqld] du fichier my.cnf :

[mysqld]
innodb_file_per_table

Après redémarrage du serveur, InnoDB va stocker chaque table dans son propre fichier tablename.ibd du dossier de données, où les tables sont stockées. C'est la même méthode que MyISAM, mais si MySQL divise les tables en un fichier de données et un fichier d'index, tablename.MYD et tablename.MYI, InnoDB place les données et les index dans le même fichier .ibd. Le fichier tbl_name.frm est toujours créé, comme d'habitude.

Si vous supprimez la ligne innodb_file_per_table du fichier my.cnf, alors InnoDB créera les tables dans le fichier de données.

innodb_file_per_table affecte seulement la création de tables. Si vous démarrez le serveur avec cette option, les nouvelles tables sont créées avec le fichier .ibd, mais vous pouvez toujours accéder aux tables qui existent dans l'espace de tables partagées ibdata. Si vous supprimez cette option, les nouvelles tables seront créées dans l'espace de tables, mais vous pouvez toujours accéder aux tables qui ont été créées indépendamment.

InnoDB a toujours besoin du système d'espace de tables, les fichiers .ibd ne sont pas suffisants. Le système d'espaces de tables est constitué des fichiers classiques ibdata. InnoDB y place son dictionnaire de données interne, et ses historiques d'annulation.

Vous ne pouvez pas déplacer les fichiers .ibd librement, comme vous pouvez le faire avec les tables MyISAM. Ceci est dû au fait que les définitions de tables sont stockées dans le système d'espace de tables InnoDB, et aussi, parce que InnoDB doit préserver la cohérence des identifiants de transactions et les numéros de séquence des logs.

Vous pouvez déplacer le fichier .ibd et la table associée d'une base à l'autre (dans la même installation MySQL/InnoDB) avec la commande classique RENAME :

RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name;

Si vous avez une sauvegarde « propre » du fichier .ibd, vous pouvez restaurer l'installation MySQL comme ceci :

1. Utilisez cette commande ALTER TABLE :

    

   Sélectionnez

   ALTER TABLE tbl_name DISCARD TABLESPACE;

   Attention : cette commande efface le fichier .ibd.

2. Placez le fichier de sauvegarde .ibd dans le bon dossier de base de données.

3. Utilisez cette commande ALTER TABLE :

ALTER TABLE tbl_name IMPORT TABLESPACE;

Dans ce contexte, un fichier .ibd « propre » signifie :

• Il n'y a pas de modifications non validées par des transactions dans le fichier .ibd file.
• Il n'y a pas de buffer d'insertion non intégrés dans le fichier .ibd.
• La purge a supprimé toutes les lignes marquées pour l'effacement dans le fichier .ibd.
• mysqld a envoyé toutes les pages modifiées au fichier .ibd depuis le buffer de fichier.

Vous pouvez faire une sauvegarde propre du fichier .ibd en suivant la méthode :

1. Cessez toute activité sur le serveur mysqld et validez toutes les transactions.
2. Attendez que SHOW INNODB STATUS\G indique qu'il n'y a plus de transaction active dans la base, et que le thread principal de InnoDB est dans l'état Waiting for server activity. Vous pouvez alors faire une copie du fichier .ibd.

Une autre méthode (non libre) de faire une sauvegarde propre du fichier .ibd est de :

1. Utilisez InnoDB Hot Backup pour faire une sauvegarde de votre installation InnoDB.
2. Lancez un second serveur mysqld sur la sauvegarde, et laissez-le nettoyer les fichiers .ibd de la sauvegarde.

La liste de tâches inclut la possibilité de déplacer les fichiers .ibd vers une autre installation MySQL/InnoDB. Cela impose la remise à zéro des numéros de transactions et des séquences de fichiers de logs du fichier .ibd.

S'il survient une corruption de base de données, vous souhaiterez exporter vos tables de la base avec SELECT INTO OUTFILE, et généralement, la plupart des données seront intactes et correctes. Mais la correction peut faire que SELECT * FROM table ou une opération en tâche de fond d'InnoDB crashe, ou même, que le processus de récupération de InnoDB crashe. Depuis InnoDB version 3.23.44, il y a une option du fichier d'options qui vous permet de forcer InnoDB à démarrer, et vous permet d'éviter le lancement des opérations en tâche de fond, pour que vous puissiez exporter vos tables. Par exemple, vous pouvez configurer :

[mysqld]
innodb_force_recovery = 4

Avant MySQL 4.0, utilisez cette syntaxe :

[mysqld]
set-variable = innodb_force_recovery=4

Les autres possibilités pour innodb_force_recovery sont listées ci-dessous. La base ne doit pas être utilisée lorsque vous utilisez ces options. Comme mesure de sécurité, InnoDB empêchera un utilisateur de faire des commandes INSERT, UPDATE et DELETE lorsque cette option est supérieure à 0.

Depuis la version 3.23.53 et 4.0.4, vous êtes autorisés à utiliser les commandes DROP et CREATE sur une table, même si la restauration forcée est active. Si vous savez qu'une table particulière vous pose des problèmes, vous pouvez l'effacer. Vous pouvez aussi utiliser cette commande pour stopper une annulation sauvage, qui serait causée par des importations de masse ou ALTER TABLE. Vous pouvez tuer le processus mysqld et utiliser l'option innodb_force_recovery=3 pour relancer votre base sans l'annulation. Alors, la suppression de la table avec DROP vous aidera.

Un nombre plus grand signifie que toutes les précautions des nombres inférieurs sont incluses. Si vous êtes capables d'exporter vos tables avec une option au maximum de 4, alors vous êtes sûr que très peu de données sont perdues. L'option 6 est plus dramatique, car les pages de la base de données sont laissées dans un état obsolète, et cela va introduire encore plus de corruption dans les arbres B-tree et les structures de la base.

• 1 (SRV_FORCE_IGNORE_CORRUPT) laisse le serveur fonctionner même s'il détecte une page corrompue. Essaie d'éviter les index et pages avec SELECT * FROM table, ce qui aide à l'export.
• 2 (SRV_FORCE_NO_BACKGROUND) empêche le thread principal de fonctionner. Si un crash survenait durant la purge, cela l'évitera.
• 3 (SRV_FORCE_NO_TRX_UNDO) ne lance pas les annulations de transactions après la restauration.
• 4 (SRV_FORCE_NO_IBUF_MERGE) empêche les opérations de vidange du buffer d'insertion. Si ce sont eux qui causent le crash, il vaut mieux les ignorer. Ne recalcule pas les statistiques de tables.
• 5 (SRV_FORCE_NO_UNDO_LOG_SCAN) ne regarde pas les logs d'annulations lors du lancement de la base. InnoDB va traiter les transactions, même incomplètes, comme validées.
• 6 (SRV_FORCE_NO_LOG_REDO) ne lance pas le rattrapage des opérations avec le log, après la restauration.

InnoDB utilise un mécanisme de contrôle appelé points de contrôle flou. InnoDB va écrire des blocs de données modifiées depuis un buffer vers le disque par petits paquets : il n'y a pas besoin de tout écrire en une seule fois, ce qui en général, conduit à l'arrêt du traitement des autres instructions pour quelques instants.

Durant une restauration de base, InnoDB recherche un point de contrôle écrit dans les fichiers de logs. Il sait que toutes les modifications de la base placées avant ce point de contrôle sont aussi présentes sur le disque de la base. Puis, InnoDB analyse le fichier de logs, et applique les modifications qui ont eu lieu depuis le point de contrôle.

InnoDB écrit dans les fichiers de logs en mode circulaire. Toutes les modifications validées qui font que le buffer de MySQL est différent de la version sur le disque doivent être disponibles dans les fichiers de logs, au cas où InnoDB en aurait besoin pour une restauration. Cela signifie que lorsque InnoDB commence à réutiliser le fichier d'historique, il doit commencer par s'assurer que le disque a reçu les modifications qui sont dans le buffer. En d'autres termes, InnoDB doit placer un point de contrôle, et souvent, cela se traduit par l'écriture de données du buffer sur le disque.

Ceci explique pourquoi utiliser de grands fichiers de logs peut éviter des accès disques pour les points de contrôle. Il est recommandé d'utiliser une taille de fichier d'historique aussi grande que le buffer, voire même plus grande. L'inconvénient des grands fichiers est que la restauration dure alors plus longtemps, puisqu'il y a plus de modifications à appliquer.

Avec InnoDB, toutes les opérations sont placées dans une transaction. Si le mode d'autovalidation est activé, chaque commande SQL est une transaction à part entière. MySQL démarre toujours une nouvelle transaction lorsque le mode d'autovalidation est activé.

Si l'autovalidation est désactivée avec SET AUTOCOMMIT = 0, alors nous pouvons considérer qu'une transaction est toujours commencée. Une commande SQL COMMIT ou ROLLBACK termine la transaction courante et en commence une autre. Ces deux commandes vont libérer tous les verrous InnoDB qui étaient posés durant la transaction. Un COMMIT signifie que les modifications durant la transaction seront enregistrées, et rendues visibles aux autres. Un ROLLBACK, d'un autre côté, annule toutes les modifications.

Si la connexion a activé l'autovalidation, l'utilisateur peut faire une transaction multicommande en commençant la transaction avec la commande START TRANSACTION ou BEGIN et en la terminant avec COMMIT ou ROLLBACK.

En termes de niveau d'isolation des transactions SQL-92, le comportement par défaut de InnoDB est REPEATABLE READ. Depuis la version 4.0.5, InnoDB offre quatre niveaux différents d'isolation de transactions, tels que décrit dans la norme SQL-92. Vous pouvez configurer le niveau d'isolation par défaut dans le groupe [mysqld] du fichier my.cnf:

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
                         | REPEATABLE-READ | SERIALIZABLE}

Un utilisateur peut changer le niveau d'isolation d'une session ou des nouvelles connexions avec la commande SET TRANSACTION. La syntaxe est la suivante :

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
                       {READ UNCOMMITTED | READ COMMITTED
                        | REPEATABLE READ | SERIALIZABLE}

Notez qu'il n'y a pas de tiret dans les noms de niveaux de la syntaxe SQL.

Le comportement par défaut est de configurer le niveau d'isolation de la prochaine transaction non démarrée. Si vous utilisez le mot-clé GLOBAL, la commande configure le niveau d'isolation globalement, pour toutes les nouvelles connexions, mais pas pour les connexions existantes. Vous devez avoir les droits de SUPER pour faire cela. En utilisant le mot-clé SESSION, vous allez configurer le niveau d'isolation des prochaines transactions de la session courante. Tous les clients sont autorisés à changer le niveau d'isolation de la session, même au milieu d'une transaction, ainsi que le niveau d'isolation de la prochaine transaction. Dans les versions 3.23.50 et plus anciennes, SET TRANSACTION n'avait pas d'effet sur les tables InnoDB. Dans les versions < 4.0.5, seules REPEATABLE READ et SERIALIZABLE étaient disponibles.

Vous pouvez connaître les niveaux d'isolation de transaction de session et global avec ces commandes :

SELECT @@global.tx_isolation;
SELECT @@tx_isolation;

Pour le verrouillage de ligne, InnoDB utilise le système dit de verrouillage de la prochaine clé. Cela signifie qu'en plus des lignes d'index, InnoDB peut aussi verrouiller le « trou » après une ligne d'index pour éviter les insertions des autres utilisateurs. Un verrou de prochaine clé représente un verrou qui bloque la ligne d'index et le trou qui le précède. Un verrou de trou représente un verrou qui ne bloque que le trou avant la ligne.

Voici une description détaillée de chaque niveau d'isolation de InnoDB :

• READ UNCOMMITTED aussi appelé « dirty read » : les commandes SELECT non verrouillantes sont effectuées sans rechercher de versions plus récentes de la ligne. Elles sont donc non cohérentes, sous ce niveau d'isolation. Sinon, ce niveau fonctionne comme le niveau READ COMMITTED.
• READ COMMITTED Proche du niveau d'isolation d'Oracle. Toutes les commandes SELECT ... FOR UPDATE et SELECT ... LOCK IN SHARE MODE ne verrouillent que les lignes d'index, et non pas les trous entre elles, ce qui permet l'insertion de nouvelles lignes, adjacentes aux lignes verrouillées. UPDATE et DELETE qui utilisent un seul index avec une condition de recherche unique ne verrouillent que la ligne d'index trouvée, par le trou qui la précède. Mais, pour un intervalle de lignes traitées par UPDATE et DELETE, InnoDB doit mêler des verrous de prochaine clé ou des verrous de trous et bloquer les insertions des autres utilisateurs dans les espaces couverts par l'intervalle. Ceci est nécessaire, car les « lignes fantômes » doivent être bloquées pour la réplication MySQL et la restauration. La lecture cohérente se comporte comme avec Oracle : chaque lecture cohérente, même dans une transaction, lit et modifie son propre contexte. Voir Section 15.11.3, « Lecture cohérente non bloquante »15.11.3. Lecture cohérente non bloquante.
• REPEATABLE READ
  C'est le niveau d'isolation par défaut d'InnoDB. Les commandes SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE et DELETE qui utilisent un index unique dans une condition de recherche, ne verrouillent que la ligne d'index trouvée, et non pas le trou précédent. Sinon, ces opérations utilisent le verrouillage de prochaine clé, en verrouillant l'intervalle utilisé, et bloque les insertions des utilisateurs. Avec les lectures cohérentes, il y a une importante différence avec le niveau d'isolation précédent : dans ce mode, toutes les lectures cohérentes d'une même transaction liront les mêmes données, établies par la première lecture. Cette convention signifie que si vous émettez plusieurs commandes SELECT dans la même transaction, ces SELECT seront cohérents les uns avec les autres. Voir Section 15.11.3, « Lecture cohérente non bloquante »15.11.3. Lecture cohérente non bloquante.
• SERIALIZABLE Ce niveau est identique au précédent, mais toutes les lectures SELECT sont implicitement converties en SELECT ... LOCK IN SHARE MODE.

Une lecture cohérente signifie que InnoDB utilise son système de multiversionnage pour présenter à une requête, une photo de la base à un moment donné. La requête va alors voir les différentes modifications apportées par les transactions qui ont eu lieu avant cette date, et masquera les transactions qui ont eu lieu depuis, ou qui n'ont pas été archivées. L'exception à cette règle est que la requête verra les modifications apportées par la requête qui a émis cette commande.

Si vous utilisez le niveau d'isolation REPEATABLE READ, alors les lectures cohérentes dans une même transaction liront le même bilan. Vous pouvez obtenir un bilan plus récent pour vos requêtes en archivant la requête courante, et en démarrant une autre.

Les lectures cohérentes sont le mode par défaut de traitement des commandes SELECT par InnoDB avec les niveaux d'isolation READ COMMITTED et REPEATABLE READ. Une lecture cohérente ne pose aucun verrou sur les tables auxquelles elle accède, et par conséquent, les autres utilisateurs peuvent librement modifier ces tables en même temps qu'une lecture cohérente est exécutée.

Une lecture cohérente n'est pas toujours pratique, dans certaines circonstances. Supposons que vous voulez ajouter une ligne dans votre table CHILD, et vous assurer que l'enfant a déjà un parent dans la table PARENT.

Supposez que vous utilisiez une lecture cohérente, pour lire la table PARENT, et que vous découvrez le parent de l'enfant dans cette table. Pouvez-vous ajouter tranquillement la ligne fille dans la table CHILD ? Non, car il peut arriver que durant ce temps, un autre utilisateur a effacé la ligne parente dans la table PARENT, et vous n'en êtes pas conscient.

La solution est d'exécuter la commande SELECT en mode verrouillage, avec LOCK IN SHARE MODE.

SELECT * FROM PARENT WHERE NAME = 'Jones' LOCK IN SHARE MODE;

Effectuer une lecture en mode partagé signifie que nous allons lire les dernières données disponibles, et que nous allons poser un verrou sur les lignes que nous lisons. Si les dernières données appartiennent à une transaction non validée d'un autre utilisateur, nous allons attendre que ce soit fait. Un verrou partagé évite que les autres utilisateurs ne modifient ou n'effacent la ligne que nous lisons. Après que nous ayons obtenu le nom du parent, nous pouvons tranquillement ajouter le nom du fils dans la table CHILD, et valider notre transaction. Cet exemple montre comment implanter l'intégrité référentielle dans votre application.

Ajoutons un autre exemple : nous avons un champ compteur dans la table CHILD_CODES que nous utilisons pour assigner un identifiant unique à chaque enfant que nous ajoutons dans la table CHILD. Évidemment, en utilisant une lecture cohérente ou une lecture partagée pour lire la valeur courante du compteur n'est pas une bonne idée, car deux utilisateurs de la base peuvent simultanément lire la même valeur de compteur, et nous allons obtenir une erreur de clé doublon lorsque nous ajouterons le second des deux fils.

Ici, LOCK IN SHARE MODE n'est pas une bonne solution, car si deux utilisateurs lisent le compteur en même temps, au moins l'un des deux sera bloqué lorsqu'il tentera de modifier le compteur.

Dans ce cas, il y a deux bonnes méthodes pour implémenter la lecture et l'incrémentation du compteur : (1) modifiez le compteur d'une unité, et lisez-le après cela ou (2) lisez le compteur d'abord, avec un verrou en mode FOR UPDATE, puis incrémentez-le :

SELECT COUNTER_FIELD FROM CHILD_CODES FOR UPDATE;
UPDATE CHILD_CODES SET COUNTER_FIELD = COUNTER_FIELD + 1;

Une commande SELECT ... FOR UPDATE va lire les dernières données disponibles pour chaque ligne, et pose un verrou dessus en même temps qu'il lit. De cette façon, il pose le même verrou que la commande UPDATE.

Notez que la commande ci-dessus est simplement un exemple de fonctionnement de SELECT ... FOR UPDATE. En MySQL, la tâche spécifique de création d'un identifiant unique peut être réalisée avec un seul accès à la table :

UPDATE child_codes
       SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

La commande SELECT ne fait que lire l'identifiant spécifique à la connexion. Il ne fait aucun accès à la table.

Avec le verrouillage de ligne, InnoDB utilise un algorithme appelé le verrouillage de la clé suivante. InnoDB fait un verrouillage de telle sorte que lorsqu'il fait une recherche ou un scan d'index, il pose des verrous partagés ou exclusifs sur les lignes d'index qu'il rencontre. Par conséquent, les verrous de lignes sont plus exactement appelés des verrous d'index.

Les verrous que InnoDB pose affectent aussi l'espace qui le sépare de la ligne suivante. Si un utilisateur a un verrou partagé ou exclusif sur une ligne L dans un index, alors un autre utilisateur ne peut faire d'insertion immédiatement avant la ligne L, dans l'ordre de l'index. Ce verrouillage est fait pour éviter le problème de la ligne fantôme. Supposons que je veuille lire et verrouiller tous les enfants ayant un identifiant supérieur à 100 dans la table CHILD, puis modifier certains champs des lignes ainsi identifiées :

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

Supposons qu'il y ait un index sur la table CHILD, sur la colonne ID. Notre requête va scanner l'index à partir de la première ligne où ID est plus grand que 100. Maintenant, si le verrou posé sur les lignes d'index n'empêche pas l'utilisation des intervalles entre les lignes d'index, un nouvel enfant peut être inséré dans la table durant la lecture. Et maintenant, si ma transaction exécute la commande :

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

je vais trouver un nouvel enfant dans le résultat de ma requête. Ceci va à l'encontre du principe d'isolation des transactions : une transaction doit être capable de s'exécuter sans que les lectures soient affectées durant ce temps. Si nous considérons un intervalle de données, alors la nouvelle ligne 'fantôme' va casser le principe d'isolation.

Lorsque InnoDB scanne un index, il va aussi verrouiller l'espace après la dernière ligne de l'index. C'est ce qui est arrivé dans l'exemple ci-dessus : le verrou posé par InnoDB va éviter qu'une insertion n'intervienne dans la table où ID serait plus grand que 100.

Vous pouvez utiliser le verrouillage de la clé suivante pour implémenter des vérifications d'unicité dans votre application : si vous lisez des données en mode partagé, et que vous ne voyez pas de duplicata de la ligne que vous allez insérer, alors vous pouvez l'insérer sans problème, en sachant que le verrou de clé suivante va vous garantir que durant ce temps, personne ne pourra insérer de ligne, qui déboucherait sur un duplicata de la vôtre. Le verrou de clé suivante permet de verrouiller aussi la non-existence de ligne dans votre table.

Supposons que vous exécutez MySQL avec le niveau d'isolation des transactions de REPEATABLE READ. Lorsque vous demandez une lecture cohérente avec une commande SELECT ordinaire, InnoDB va donner à votre transaction une date jalon, en fonction de laquelle votre requête va voir la base. Ainsi, si une transaction B efface une ligne après l'assignation de ce jalon, vous ne verrez pas cette ligne. De même pour une insertion ou une modification.

Vous pouvez faire avancer votre date jalon en validant votre transaction et en exécutant un autre SELECT.

Cela s'appelle le contrôle simultané multiversion.

                  User A                 User B

              SET AUTOCOMMIT=0;      SET AUTOCOMMIT=0;
time
|             SELECT * FROM t;
|             empty set
|                                    INSERT INTO t VALUES (1, 2);
|
v             SELECT * FROM t;
              empty set
                                     COMMIT;

              SELECT * FROM t;
              empty set;

              COMMIT;

              SELECT * FROM t;
              ---------------------
              |    1    |    2    |
              ---------------------

De cette façon, A voit la ligne insérée par B uniquement lorsque B a validé son insertion, et que A a validé sa propre transaction, de façon à ce que la date jalon soit plus récente que la validation de B.

Si vous voulez avoir une vision aussi « fraîche » que possible de votre base, vous devez utiliser le verrou en lecture :

SELECT * FROM t LOCK IN SHARE MODE;

Un verrou de lecture, une commande UPDATE ou DELETE pose généralement des verrous sur toutes les lignes qui sont analysées durant l'opération. La présence d'une clause WHERE n'a pas d'importance. InnoDB ne se souvient pas de la condition WHERE exacte, mais sait quels intervalles d'index ont été scannés. Les verrous sont du type 'prochaine clé', et cela empêche aussi les insertions dans l'« espace » immédiatement après la ligne.

Si le verrou est exclusif, alors InnoDB essaie de lire les groupes d'index et de poser un verrou dessus.

Si vous n'avez pas d'index valable pour votre requête, et que MySQL scanne toute la table, chaque ligne sera verrouillée, et bloquera ainsi toutes les insertions. Il est important de bien configurer ses index, pour que les requêtes ne fassent pas de scan de table inutile.

• SELECT ... FROM ... : ceci est une lecture cohérente, qui lit un bilan de la base, et ne pose aucun verrou.
• SELECT ... FROM ... LOCK IN SHARE MODE : pose un verrou partagé sur la prochaine clé sur tous les index que la lecture rencontre.
• SELECT ... FROM ... FOR UPDATE : pose un verrou exclusif sur la prochaine clé sur tous les index que la lecture rencontre.
• INSERT INTO ... VALUES (...) : pose un verrou exclusif sur la ligne insérée. Notez que ce verrou n'est pas un verrou de clé, et il n'empêche pas les autres utilisateurs d'insérer des lignes. Si une erreur de clé double apparaît, un verrou partagé sera posé sur la ligne doublon.
• Durant l'initialisation d'une colonne AUTO_INCREMENT dans une table, InnoDB pose un verrou exclusif à la fin de l'index associé à la colonne AUTO_INCREMENT. Lors de l'accession au compteur d'incrémentation, InnoDB utilise un verrou spécifique, en mode AUTO-INC où le verrou ne dure que jusqu'à la fin de la requête SQL courante, au lieu de la fin de la transaction. Voir Section 15.11.1, « InnoDB et AUTOCOMMIT »15.11.1. InnoDB et AUTOCOMMIT.
  Avant MySQL 3.23.50, SHOW TABLE STATUS posait aussi un verrou exclusif sur les tables ayant une colonne AUTO_INCREMENT. Cela signifie que la commande SHOW TABLE STATUS pouvait aussi causer un blocage de verrou, ce qui surprenait beaucoup les utilisateurs. Depuis MySQL 3.23.50, InnoDB lit la valeur d'une table dont la colonne AUTO_INCREMENT a été initialisée, sans poser de verrou.
• INSERT INTO T SELECT ... FROM S WHERE ... : pose un verrou exclusif sur chaque ligne insérée dans T. Effectue la recherche sur S sous la forme d'une lecture cohérente, mais pose un verrou partagé sur l'index de prochaine clé de S si MySQL a activé le log. InnoDB doit poser un verrou dans cette dernière situation, car en cas d'exécution des instructions dans une phase de restauration, toutes les requêtes doivent être exécutées dans le même ordre.
• CREATE TABLE ... SELECT ... effectue une commande SELECT sous la forme d'une lecture cohérente, ou avec des verrous partagés, comme précédemment.
• REPLACE est similaire à une insertion, s'il n'y a pas de collision sur la clé unique. Sinon, un verrou exclusif sur l'index de prochaine clé est posé sur la ligne qui sera modifiée.
• UPDATE ... SET ... WHERE ... : pose un verrou exclusif sur l'index de prochaine clé, à chaque ligne que la recherche trouve.
• DELETE FROM ... WHERE ... : pose un verrou exclusif sur l'index de prochaine clé à chaque ligne que la recherche trouve.
• Si la contrainte de FOREIGN KEY est définie sur une table, toute insertion, modification ou effacement qui requiert la vérification de la contrainte va poser un verrou de ligne sur la ligne dont il doit vérifier la contrainte. De plus, dans certains cas où la contrainte échoue, InnoDB pose ces verrous.
• LOCK TABLES ... : pose un verrou de table. L'implémentation de la couche MySQL pose ce verrou. La détection automatique des blocages de InnoDB ne peut détecter les blocages lorsque de tels verrous sont posés. Voyez la section suivante. Voir Section 15.11.9, « Détection des blocages et annulation »15.11.9. Détection des blocages et annulation.
  De plus, comme MySQL ne connaît pas le verrouillage de lignes, il est possible que vous posiez un verrou sur une table où un autre utilisateur a déjà posé un verrou. Mais cela ne pose pas de problème quant à l'intégrité de la requête. Voir Section 15.17, « Restrictions sur les tables InnoDB »15.17. Restrictions sur les tables InnoDB.

MySQL ouvre les connexions des clients en mode d'autovalidation, par défaut. Lorsque l'autovalidation est activée, MySQL fait une validation après chaque commande SQL, si la commande n'a pas retourné d'erreur.

Si vous n'avez pas de mode d'autovalidation, et que vous fermez une connexion sans valider explicitement vos transactions, alors MySQL annule votre transaction.

Si une erreur est retournée par une commande SQL, le comportement de la transaction dépend de l'erreur. Voir Section 15.16, « Gestion des erreurs InnoDB »15.16. Gestion des erreurs InnoDB.

Les commandes SQL suivantes causent une validation implicite de la transaction courante :

• ALTER TABLE, BEGIN, CREATE INDEX, DROP DATABASE, DROP INDEX, DROP TABLE, LOAD MASTER DATA, LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, TRUNCATE, UNLOCK TABLES.
• CREATE TABLE (elle valide uniquement avant MySQL 4.0.13 et si le log binaire MySQL est utilisé).
• La commande CREATE TABLE de InnoDB est traitée comme une seule transaction. Cela signifie qu'une commande ROLLBACK ne va pas annuler la commande CREATE TABLE qui a été faite dans la transaction.

InnoDB détecte automatiquement les blocages de transactions et annule une ou plusieurs transactions pour l'éviter. Depuis la version 4.0.5, InnoDB va essayer d'annuler les petites transactions. La taille de la transaction est déterminée par le nombre de lignes qu'elle a inséré, modifié ou effacé. Avant la version 4.0.5, InnoDB annulait toujours la transaction qui avait posé le dernier verrou avant le blocage, c'est-à-dire, un cycle dans le graphe des transactions.

InnoDB ne peut pas détecter les blocages causés par la commande MySQL LOCK TABLES, ou si un verrou est posé par un autre gestionnaire de table que InnoDB. Vous devez résoudre ces situations avec l'option innodb_lock_wait_timeout du fichier de configuration.

Lorsque InnoDB effectue une annulation de transaction, tous les verrous de cette transaction sont libérés. Cependant, si une commande SQL est annulée pour cause d'erreur, certains verrous de la transaction peuvent être conservés. Ceci est dû au fait que InnoDB enregistre les verrous dans un format qui ne permet pas de savoir qui l'a posé.

Les blocages de verrous sont un problème classique des bases de données transactionnelles, mais ils ne sont pas dangereux, à moins qu'ils ne se répètent si souvent que vous ne puissiez pas exécuter tranquillement certaines transactions. Normalement, vous devriez écrire vos applications de manière à ce qu'elles soient prêtes à tenter à nouveau une transaction si la transaction est annulée pour cause de blocage.

InnoDB utilise un verrouillage de lignes automatique. Vous pouvez obtenir des blocages sur une ligne, même si votre transaction ne fait que modifier ou insérer une seule ligne. Cela est dû au fait que les opérations ne sont pas réellement 'atomiques' : elles posent automatiquement des verrous (éventuellement plusieurs) sur les lignes d'index de l'enregistrement concerné.

Vous pouvez gérer ces blocages et réduire leur nombre avec les trucs suivants.

• Utilisez la commande SHOW INNODB STATUS avec MySQL version supérieure à 3.23.52 et 4.0.3, pour déterminer la cause du dernier blocage. Cela peut vous aider à optimiser votre application pour les éviter.
• Soyez toujours prêts à tenter une nouvelle fois une transaction si elle échoue à cause d'un blocage. Les verrous ne sont pas dangereux. Essayez juste une autre fois.
• Validez souvent vos transactions. Les petites transactions sont moins sujettes aux blocages.
• Si vous utilisez des verrous en lectures avec SELECT ... FOR UPDATE ou ... LOCK IN SHARE MODE, essayez d'utiliser un niveau d'isolation plus bas comme READ COMMITTED.
• Accédez à vos tables et lignes dans un ordre fixé. Les transactions vont alors former des queues, et non pas des blocages.
• Ajoutez de bons index à vos tables. Vos requêtes devront scanner moins souvent les tables, et poseront donc moins de verrous. Utilisez EXPLAIN SELECT pour déterminer si MySQL choisit les bons index pour vos requêtes.
• Limitez votre utilisation des verrous. Si vous pouvez vous permettre de faire retourner à une commande SELECT des données un peu anciennes, n'ajoutez pas la clause FOR UPDATE ou LOCK IN SHARE MODE. Utiliser le niveau d'isolation READ COMMITTED est bon ici, car chaque lecture cohérente dans la même transaction lira avec des données aussi fraîches que possible à chaque fois.

• En dernier recours, vous pouvez forcer les verrous avec la commande :

   

  Sélectionnez

  LOCK TABLES t1 WRITE, t2 READ, ...;
  [faire quelque chose avec les tables t1 et t2]; 
  UNLOCK TABLES;

  Les verrous de niveau de table forcent les transactions à se mettre en ligne, et les blocages sont évités. Notez que LOCK TABLES démarre implicitement une transaction, tout comme BEGIN, et UNLOCK TABLES termine une transaction avec un COMMIT.

• Une dernière solution est de créer un sémaphore auxiliaire sous la forme d'une table avec une seule ligne. Chaque transaction modifie cette table avant d'accéder aux autres tables. Dans ce cas, toutes les transactions se font en ordre séquentiel. Notez que dans cette configuration, même l'algorithme InnoDB de détection des blocages fonctionne, car le sémaphore est un verrou de ligne. Avec les verrous de niveau de table de MySQL, nous devons nous résoudre à une méthode de délai d'expiration pour résoudre un verrou.

Depuis la version 3.23.42, InnoDB inclut le moniteur InnoDB (InnoDB Monitor) qui affiche des informations sur l'état interne des tables InnoDB. Depuis les versions 3.23.52 et 4.0.3 vous pouvez aussi utiliser la commande SQL SHOW INNODB STATUS pour lire les informations de InnoDB Monitor depuis le client SQL. Les données lues sont importantes pour les réglages de performances. Si vous utilisez le client mysql interactif, le résultat est plus lisible si vous remplacez le point-virgule classique par \G :

SHOW INNODB STATUS\G

Une autre méthode pour utiliser le InnoDB Monitors est de le laisser écrire continuellement des données dans la sortie de mysqld : (note : le client MySQL n'affichera rien). Lorsqu'il est activé, InnoDB peut afficher des données toutes les 15 secondes. Si vous utilisez mysqld comme démon, alors ces données sont dirigées vers le fichier de logs .err dans le dossier datadir. Les données lues sont importantes pour les réglages de performances. Sous Windows, vous devez lancer mysqld-max depuis une fenêtre MS-DOS, avec l'option --console, si vous voulez voir le résultat affiché dans la fenêtre.

Il existe aussi innodb_lock_monitor qui affiche les mêmes informations que innodb_monitor, mais qui indique aussi les verrous posés par les transactions.

• Les verrous de tables et de ligne de chaque transaction,
• les attentes de verrous par les transactions,
• les attentes de sémaphores pour les threads,
• les requêtes en attente d'accès aux fichiers,
• les statistiques sur les buffers, et
• les statistiques sur les purges et buffers d'insertion sur le thread principal InnoDB.

Vous pouvez démarrer le moniteur InnoDB avec la commande SQL suivante :

CREATE TABLE innodb_monitor(a int) type = innodb;

et le stopper avec :

DROP TABLE innodb_monitor;

La syntaxe CREATE TABLE est simplement un moyen de passer une commande à une table InnoDB, via l'analyseur MySQL : la table créée n'est pas importante pour le moniteur InnoDB. Si vous interrompez le serveur lorsque le moniteur fonctionne, et que vous voulez redémarrer le moniteur, vous devez alors effacer la table avant de pouvoir exécuter la même commande CREATE TABLE pour démarrer le moniteur. Cette syntaxe est susceptible de changer dans le futur.

Un exemple de résultat du moniteur InnoDB :

mysql> SHOW INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
030709 13:00:59 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 413452, signal count 378357
--Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds the semaphore:
X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135
a writer (thread id 32782) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file btr0sea.c line 731
Last time write locked in file btr0sea.c line 1347
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 108462, OS waits 37964; RW-excl spins 681824, OS waits 375485
------------------------
LATEST FOREIGN KEY ERROR
------------------------
030709 13:00:59 Transaction:
TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 inser
ting
15 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 25, query id 4668733 localhost heikki update
insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
Foreign key constraint fails for table test/ibtest11a:
,
  CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`, `D`)
 ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index PRIMARY tuple:
 0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2: len 4;
 hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4: len 7; hex
 00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;;
But in parent table test/ibtest11b, in index PRIMARY,
the closest match we can find is record:
RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex 80000005; a
sc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex 0000111ef3eb; asc ......
;; 4: len 7; hex 800001001e0084; asc .......;; 5: len 3; hex 6b6864; asc khd;;
------------------------
LATEST DETECTED DEADLOCK
------------------------
030709 12:59:58
*** (1) TRANSACTION:
TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185, OS thread id 30733 inser
ting
LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146
MySQL thread id 21, query id 4553379 localhost heikki update
INSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t','e187358f'
,'g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),7
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
trx id 0 290252780 lock mode S waiting
Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a
a35818;; 1:
*** (2) TRANSACTION:
TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190, OS thread id 32782 inser
ting
130 lock struct(s), heap size 11584, undo log entries 437
MySQL thread id 23, query id 4554396 localhost heikki update
REPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','', NULL,'
h396', NULL, NULL, 7.31,7.31,7.31,200)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
trx id 0 290251546 lock_mode X locks rec but not gap
Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a
a35818;; 1:
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
trx id 0 290251546 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230; asc aa
35720;; 1:
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 290328385
Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
Total number of lock structs in row lock hash table 70
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 3491, OS thread id 42002
MySQL thread id 32, query id 4668737 localhost heikki
show innodb status
---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 3205, OS thread id 38929 in
serting
1 lock struct(s), heap size 320
MySQL thread id 29, query id 4668736 localhost heikki update
insert into speedc values (1519229,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlhh
gghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh
---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 3180, OS thread id 28684 co
mmitting
1 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 19, query id 4668734 localhost heikki update
insert into speedcm values (1603393,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlh
hgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf
---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 3200, OS thread id 36880 st
arting index read
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 27, query id 4668644 localhost heikki Searching rows for update
update ibtest11a set B = 'kHdkkkk' where A = 89572
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a index PRIM
ARY trx id 0 290328327 lock_mode X waiting
Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc
 supremum.;;
------------------
---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 ro
llback of SQL statement
ROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 25, query id 4668733 localhost heikki update
insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 3190, OS thread id 32782
58 lock struct(s), heap size 5504, undo log entries 159
MySQL thread id 23, query id 4668732 localhost heikki update
REPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t','e200498f
','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),
---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 3185, OS thread id 30733 in
serting
4 lock struct(s), heap size 1024, undo log entries 165
MySQL thread id 21, query id 4668735 localhost heikki update
INSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','', NULL,
'h321', NULL, NULL, 7.31,7.31,7.31,200)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs
25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 19, seg size 21,
85004 inserts, 85004 merged recs, 26669 merges
Hash table size 207619, used cells 14461, node heap has 16 buffer(s)
1877.67 hash searches/s, 5121.10 non-hash searches/s
---
LOG
---
Log sequence number 18 1212842764
Log flushed up to   18 1212665295
Last checkpoint at  18 1135877290
0 pending log writes, 0 pending chkp writes
4341 log i/o's done, 1.22 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 84966343; in additional pool allocated 1402624
Buffer pool size   3200
Free buffers       110
Database pages     3074
Modified db pages  2674
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 171380, created 51968, written 194688
28.72 reads/s, 20.72 creates/s, 47.55 writes/s
Buffer pool hit rate 999 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 3004, id 7176, state: purging
Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779
1586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.05 sec)

Quelques notes sur le résultat :

• Si la section TRANSACTIONS rapporte des attentes de verrous, alors votre application a des problèmes de rétention de verrous. Le rapport peut vous aider à trouver les blocages de verrous, et les transactions bloquantes.
• La section SEMAPHORES rapporte les threads en attente d'un sémaphore, et les statistiques d'attentes pour un mutex ou un verrou en lecture/écriture. Les longues attentes peuvent être dues à des requêtes parallèles, ou des problèmes avec la programmation des threads. Donner à la variable innodb_thread_concurrency une valeur plus petite que le 8 par défaut peut réduire ce type de situations.
• La section BUFFER POOL AND MEMORY vous donne des statistiques sur les pages qui ont été lues et écrites. Vous pouvez calculer depuis ces nombres combien d'accès disques vos requêtes réalisent actuellement.
• La section ROW OPERATIONS montre ce que le thread principal fait.

Tous les index de InnoDB sont des index B-tree où les lignes d'index sont stockées dans un nœud terminal de l'arbre. La taille par défaut d'une page d'index est de 16 ko. Lorsque de nouvelles lignes sont insérées, InnoDB essaie de laisser 1/16 de la page de libre pour les prochaines insertions et modifications dans les lignes d'index.

Si les lignes d'index sont insérées dans un ordre séquentiel (croissant ou décroissant), les pages d'index résultantes seront environ pleines à 15/16. Si les lignes sont insérées dans un ordre aléatoire, les pages seront pleines de 1/2 à 15/16. Si le taux de remplissage d'une page d'index tombe à 1/2, InnoDB va essayer de contracter l'arbre d'index pour libérer la page.

Une situation courante dans les applications de base de données apparaît lorsque la clé primaire est un identifiant unique, et que les nouvelles lignes sont insérées dans un ordre ascendant. Par conséquent, les insertions dans l'index en grappe ne nécessitent pas de lectures aléatoires dans le disque.

D'un autre côté, les index secondaires sont généralement non uniques, et les insertions surviennent dans un ordre aléatoire. Cela causerait de nombreux accès disques aléatoires, si InnoDB ne disposait pas d'un mécanisme spécial.

Si une ligne doit être insérée dans un index secondaire non unique, InnoDB vérifie si la page d'index fait partie du buffer. Dans ce cas, InnoDB va faire directement l'insertion dans une structure de buffer destinée à l'insertion. Le buffer d'insertion est conservé petit, pour qu'il reste dans le buffer général, et les insertions sont faites très vite.

Le buffer d'insertion est périodiquement fusionné avec l'arbre d'index secondaires dans la base. Souvent, nous fusionnons plusieurs insertions dans la même page de l'arbre d'index, et donc, nous économisons des accès disques. Il a été mesuré que les insertions sont jusqu'à 15 fois plus rapides de cette façon.

Si une base de données est suffisamment petite pour tenir en mémoire, alors le plus rapide pour faire des requêtes est d'utiliser les index hash. InnoDB a un mécanisme automatique pour surveiller les recherches utilisant les index d'une table, et si InnoDB remarque que la requête pourrait profiter d'un index hash, un tel index est automatiquement constitué.

Mais notez que les index hash sont toujours bâtis à partir d'un index B-tree existant. InnoDB peut bâtir un index hash sur un préfixe de taille arbitraire de clé B-tree, suivant le modèle de recherche que InnoDB remarque dans l'index B-tree. Un index hash peut être partiel : il n'est pas obligatoire que tout l'index B-tree soit mis en cache dans le pool. InnoDB va bâtir des index hash à la demande pour les tables dont les index sont souvent sollicités.

En un sens, grâce au mécanisme d'index hash adaptatif, InnoDB s'adapte tout seul à la mémoire interne, et se rapproche des architectures de bases en mémoire vive.

• Chaque ligne d'index de InnoDB contient un entête de 6 octets. L'entête est utilisé pour lier des lignes consécutives ensemble, et aussi pour le verrouillage de ligne.
• Les lignes dans un index en grappe contiennent des champs pour toutes les colonnes définies par l'utilisateur. De plus, il y a un champ de 6 octets pour l'identification de transaction, et un champ de 7 octets pour le pointeur d'annulation.
• Si l'utilisateur n'a pas défini de clé primaire pour la table, chaque ligne de l'index en grappe contient aussi une colonne supplémentaire de 6 octets, qui sert d'identification.
• Chaque ligne d'index secondaire contient aussi les champs définis pour la clé de l'index en grappe.
• Une ligne contient aussi un pointeur pour chaque champ de la ligne. Si la taille totale des champs représente moins de 128 octets, alors le pointeur fait 1 octet, sinon 2.
• En interne, InnoDB stocke les colonnes de taille fixe comme CHAR(10) dans un format à taille fixe. InnoDB supprime les espaces terminaux des colonnes VARCHAR. Notez que MySQL peut convertir en interne les colonnes CHAR en VARCHAR. Voir Section 13.2.5.1, « Modification automatique du type de colonnes »13.2.5.1. Modification automatique du type de colonnes.
• Une valeur SQL NULL réserve 0 octet si elle est stockée dans une colonne à taille variable. Dans une colonne à taille fixe, elle utilise toute la largeur de la colonne. La raison de la réservation de toute la colonne pour les valeurs NULL est que lors de la mise à jour de la colonne depuis la valeur NULL vers une valeur non nulle, il n'y aura pas de fragmentation sur le disque.

InnoDB utilise des accès disques asynchrones : InnoDB crée quelques threads pour s'occuper des opérations de lecture, notamment les lectures anticipées.

Voici deux heuristiques de lectures anticipées de InnoDB :

• Dans une lecture anticipée séquentielle, si InnoDB remarque que l'accès à une partie de l'espace de tables est séquentiel, il fait plusieurs lectures à l'avance.
• Dans une lecture aléatoire, si InnoDB remarque qu'une partie de l'espace de tables semble être totalement lu dans le buffer, il va aussi faire une lecture à l'avance.

Depuis la version 3.23.40b, InnoDB utilise une nouvelle technique de flush de fichier, appelée doublewrite. Elle apporte de la sécurité lors de la restauration après crash du système d'exploitation, ou un problème électrique, et améliore les performances sous Unix, pour plusieurs distributions, en réduisant le besoin de synchronisation.

Doublewrite (Double écriture, en français) signifie que InnoDB, avant d'écrire les pages dans le fichier de données, les écrit dans une zone continue d'espace, appelée un buffer de double écriture. Une fois que cette écriture est faite, que le buffer de double écriture a été vidé, InnoDB écrit les pages à leur destination finale. Si le système d'exploitation crashe entre temps, InnoDB va trouver une bonne copie des données dans le buffer, lors de la restauration.

Depuis MySQL 3.23.41, vous pouvez utiliser des partitions raw disk pour stocker les fichiers d'espace de tables. En utilisant un raw disk, vous pouvez faire des E/S non bufferisées sous Windows et sans couche de contrôle sous Unix, ce qui améliore les performances.

Lorsque vous créez un nouveau fichier de données, vous devez mettre le mot newraw immédiatement après la taille du fichier de données dans innodb_data_file_path. La partition doit être aussi grande que la taille que vous spécifiez. Notez que 1 Mo avec InnoDB vaut 1024 x 1024 octets, alors que 1 Mo signifie généralement 1 000 000 d'octets pour un disque.

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

Lors de votre prochain redémarrage du serveur, InnoDB remarque le mot newraw et initialise une nouvelle partition. Cependant, ne créez ou ne modifiez pas les tables InnoDB pour le moment. Sinon, lors de votre prochain redémarrage serveur, InnoDB va réinitialiser la partition et vous aurez tout perdu. Depuis la version 3.23.44, par mesure de sécurité, InnoDB empêche les utilisateurs de modifier des données dans une partition newraw.

Une fois que InnoDB a initialisé la nouvelle partition, stoppez le serveur, remplacez newraw dans le fichier d'options par raw :

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:5Graw;/dev/hdd2:2Graw

Puis relancez le serveur et InnoDB va permettre les modifications dans cet espace de tables.

Sous Windows, depuis la version 4.1.1, vous pouvez allouer une partition de disque comme ceci :

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=//./D::10Gnewraw

Le //./ correspond à la syntaxe Windows de \\.\, pour accéder aux disques physiques.

Lorsque vous utilisez une partition raw disque, assurez-vous que vous avez les permissions qui permettent de lire et écrire avec le compte qui fait tourner le serveur.

Les fichiers de données que vous définissez dans le fichier de configuration forment l'espace de données InnoDB. Les fichiers sont simplement concaténés pour former un espace de données, et il n'y a pas de parallélisme utilisé. Actuellement, vous ne pouvez pas spécifier où l'espace est alloué pour vos tables, hormis en utilisant la méthode suivante : pour chaque nouvel espace de données créé, InnoDB va allouer de l'espace en partant depuis la fin.

L'espace de tables est constitué de pages de taille 16 ko. Les pages sont groupées en ensembles de 64 pages consécutives. Les 'fichiers' dans un espace de tables sont appelés des segments en InnoDB. Le nom du segment d'annulation est un peu trompeur, car il contient en fait de nombreux segments dans l'espace de table.

Pour chaque index de InnoDB, nous allons créer deux segments : un pour les nœuds non terminaux du B tree, et un autre pour les nœuds terminaux. L'idée ici est d'améliorer la séquence des nœuds terminaux, qui contiennent les données.

Lorsqu'un segment croît dans un espace de tables, InnoDB alloue les 32 premières pages spécifiquement pour un segment. Après cela, InnoDB commence à allouer des extensions au segment. InnoDB peut ajouter aux grands segments jusqu'à 4 extensions en même temps, pour améliorer la séquence de données.

Certaines pages dans l'espace de table contiennent des cartes des autres pages, et donc, quelques extensions dans un espace de tables InnoDB ne pourront pas être allouées en tant que segment, mais comme pages individuelles.

Lorsque vous exécutez une commande SHOW TABLE STATUS FROM ... LIKE ... pour demander quel est l'espace libre dans la table, InnoDB va rapporter les extensions qui sont encore totalement libres. InnoDB se réserve toujours quelques extensions pour la gestion interne. Ces extensions réservées ne sont pas incluses dans l'espace libre.

Lorsque vous effacez des donnes d'une table, InnoDB va contracter les index B-tree correspondants. Suivant les effacements qui vont libérer des pages individuelles ou des extensions, de l'espace sera rendu aux autres tables. Effacer une table ou en effacer toutes les lignes va rendre obligatoirement de l'espace aux autres tables, mais souvenez-vous que les lignes effacées ne pourront être physiquement effacées qu'après une opération de purge, si aucune transaction n'en a plus besoin.

S'il y a plusieurs insertions et suppressions dans les index d'une table, les index peuvent devenir fragmentés. Par fragmentation, nous voulons dire que l'ordre physique de la page d'index n'est pas proche de l'ordre alphabétique des enregistrements dans les pages, ou qu'il y a plusieurs pages non utilisées dans le bloc de 64 pages qui ont été allouées à l'index.

Une manière d'accélérer les index est d'extraire périodiquement les données de la table dans un fichier avec mysqldump, d'effacer la table puis de la recréer.

Une autre manière de défragmenter consiste à exécuter un ALTER sur le type de la table pour le changer en MyISAM puis de le repasser en InnoDB.

ALTER TABLE tbl_name TYPE=InnoDB

Cela fait que MySQL va reconstruire la table. Un autre moyen de réaliser la défragmentation est d'utiliser l'utilitaire mysqldump pour exporter la table vers un fichier texte, effacer la table, la recréer et importer le fichier de données.

Si les insertions dans un index sont toujours ascendantes et que les lignes supprimées le sont à la fin du fichier, alors l'algorithme de gestion de l'espace fichiers de InnoDB garantit qu'aucune fragmentation n'aura lieu dans l'index.

Voici une liste non exhaustive des erreurs courantes et spécifiques à InnoDB que vous pouvez rencontrer, avec des détails pour les corriger.

• 1005 (ER_CANT_CREATE_TABLE)
  Impossible de créer la table. Si le message d'erreur fait référence à une erreur de code errno 150, la création de la table a échoué à cause d'une contrainte de clé étrangère, qui n'est pas correctement formée.
• 1016 (ER_CANT_OPEN_FILE)
  Impossible de trouver le fichier de tables InnoDB dans les fichiers de données InnoDB alors que le fichier .frms. Voir Section 15.18.1, « Solutions pour le dictionnaire de données InnoDB »15.18.1. Solutions pour le dictionnaire de données InnoDB.
• 1114 (ER_RECORD_FILE_FULL)
  InnoDB n'a plus d'espace libre dans l'espace de tables. Essayez de reconfigurer l'espace de tables, et d'ajouter un nouveau fichier de données.
• 1205 (ER_LOCK_WAIT_TIMEOUT)
  Le délai d'expiration du verrou a été dépassé. La transaction a été annulée.
• 1213 (ER_LOCK_DEADLOCK)
  Blocage de transactions. Vous devriez relancer la transaction.
• 1216 (ER_NO_REFERENCED_ROW)
  Vous essayez d'ajouter une ligne, mais il n'y a pas de ligne parente, et une contrainte de clé étrangère échoue. Vous devez ajouter le parent en premier.
• 1217 (ER_ROW_IS_REFERENCED)
  Vous essayez d'effacer une ligne parente qui a des enfants, et une contrainte de clé étrangère échoue. Vous devez effacer la ligne fille en premier.

Sous Unix, pour connaître la signification d'un message d'erreur système, utilisez le programme perror qui est livré avec la distribution MySQL.

La table suivante fournit une liste de certains codes d'erreur système Linux. Pour une version complète, voyez Linux source code (http://www.iglu.org.il/lxr/source/include/asm-i386/errno.h lien mort).

• 1 (EPERM)
  Cette operation ne peut pas être effectuée.
• 2 (ENOENT)
  Le répertoire ou le fichier n'a pas été trouvé.
• 3 (ESRCH)
  Processus invalide.
• 4 (EINTR)
  L'accès au système a été interrompu.
• 5 (EIO)
  Erreur d'I/O.
• 6 (ENXIO)
  Le périphérique ou l'adresse est invalide.
• 7 (E2BIG)
  La liste des arguments est trop longue.
• 8 (ENOEXEC)
  Erreur de format de l'exécutable.
• 9 (EBADF)
  Le numéro du fichier est invalide.
• 10 (ECHILD)
  Il n'y a pas de processus enfant.
• 11 (EAGAIN)
  Relancer un essai.
• 12 (ENOMEM)
  La mémoire est saturée.
• 13 (EACCES)
  Accès refusé.
• 14 (EFAULT)
  L'adresse est invalide.
• 15 (ENOTBLK)
  Un périphérique de bloc est nécessaire.
• 16 (EBUSY)
  Le périphérique n'est pas disponible.
• 17 (EEXIST)
  Le fichier est déjà présent.
• 18 (EXDEV)
  Erreur de lien sur les périphériques croisés.
• 19 (ENODEV)
  Le périphérique n'existe pas.
• 20 (ENOTDIR)
  Le répertoire n'existe pas.
• 21 (EISDIR)
  Un répertoire du même nom existe déjà.
• 22 (EINVAL)
  Argument invalide.
• 23 (ENFILE)
  Le fichier tableau n'a plus d'espace libre.
• 24 (EMFILE)
  Trop de fichiers ouverts simultanément.
• 25 (ENOTTY)
  Cet ioctl est inactif pour ce périphérique.
• 26 (ETXTBSY)
  Le fichier texte est déjà ouvert.
• 27 (EFBIG)
  Le fichier est trop volumineux
• 28 (ENOSPC)
  Plus d'espace libre sur le périphérique.
• 29 (ESPIPE)
  Recherche interdite.
• 30 (EROFS)
  Fichier système en mode lecture seule.
• 31 (EMLINK)
  Trop de liens dans ce fichier.

La table suivante fournit une liste de certains codes d'erreur système Windows. Pour une version complète, voyez Microsoft website (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/debug/base/system_error_codes.asp lien mort).

• 1 (ERROR_INVALID_FUNCTION)
  La fonction est incorrecte.
• 2 (ERROR_FILE_NOT_FOUND)
  Le système ne trouve pas le fichier demandé.
• 3 (ERROR_PATH_NOT_FOUND)
  Le système ne trouve pas le répertoire demandé.
• 4 (ERROR_TOO_MANY_OPEN_FILES)
  Le système ne peut pas ouvrir le fichier.
• 5 (ERROR_ACCESS_DENIED)
  Accès refusé.
• 6 (ERROR_INVALID_HANDLE)
  Action demandée est invalide.
• 7 (ERROR_ARENA_TRASHED)
  Les blocs de contrôle de stockage ont été détruits.
• 8 (ERROR_NOT_ENOUGH_MEMORY)
  L'espace de stockage n'est pas suffisant pour lancer cette commande.
• 9 (ERROR_INVALID_BLOCK)
  L'adresse du bloc de contrôle de stockage est invalide.
• 10 (ERROR_BAD_ENVIRONMENT)
  L'environnement est mal défini.
• 11 (ERROR_BAD_FORMAT)
  Tentative de chargement du programme sous un format invalide.
• 12 (ERROR_INVALID_ACCESS)
  Le code d'accès est invalide.
• 13 (ERROR_INVALID_DATA)
  Les données sont invalides.
• 14 (ERROR_OUTOFMEMORY)
  L'espace de stockage disponible n'est pas suffisant pour réaliser cette opération.
• 15 (ERROR_INVALID_DRIVE)
  Le système ne trouve pas le disque demandé.
• 16 (ERROR_CURRENT_DIRECTORY)
  Le répertoire ne peut pas être supprimé.
• 17 (ERROR_NOT_SAME_DEVICE)
  Le système ne peut pas déplacer le fichier vers un autre disque.
• 18 (ERROR_NO_MORE_FILES)
  Pas de fichier disponible.
• 19 (ERROR_WRITE_PROTECT)
  Écriture impossible sur ce média protégé.
• 20 (ERROR_BAD_UNIT)
  Le système ne trouve pas le périphérique.
• 21 (ERROR_NOT_READY)
  Le périphérique n'est pas prêt.
• 22 (ERROR_BAD_COMMAND)
  Commande inconnue de ce périphérique.
• 23 (ERROR_CRC)
  Erreur de données (problème de redondance cyclique).
• 24 (ERROR_BAD_LENGTH)
  Commande trop longue pour ce programme.
• 25 (ERROR_SEEK)
  La commande ne trouve pas la zone ou piste sur le disque.
• 26 (ERROR_NOT_DOS_DISK)
  Disque ou disquette inaccessible.
• 27 (ERROR_SECTOR_NOT_FOUND)
  Secteur disque introuvable.
• 28 (ERROR_OUT_OF_PAPER)
  L'imprimante n'a plus de papier.
• 29 (ERROR_WRITE_FAULT)
  Le système ne peut pas écrire dans ce périphérique.
• 30 (ERROR_READ_FAULT)
  Le système ne peut pas accéder au périphérique.
• 31 (ERROR_GEN_FAILURE)
  Un périphérique relié au système ne fonctionne pas.
• 32 (ERROR_SHARING_VIOLATION)
  Le processus ne peut accéder au fichier : celui-ci est actuellement en cours d'utilisation par un autre processus.
• 33 (ERROR_LOCK_VIOLATION)
  Le processus ne peut accéder au fichier : le fichier est actuellement verrouillé par un autre processus.
• 34 (ERROR_WRONG_DISK)
  Erreur de disque demandé. Insérer le disque %2 (Numéro de série : %3) dans le lecteur %1.
• 36 (ERROR_SHARING_BUFFER_EXCEEDED)
  Trop de fichiers ouverts en partage.
• 38 (ERROR_HANDLE_EOF)
  La fin du fichier a été atteinte.
• 39 (ERROR_HANDLE_DISK_FULL)
  Espace disque rempli.
• 112 (ERROR_DISK_FULL)
  Espace disque rempli.
• 123 (ERROR_INVALID_NAME)
  Le nom de fichier, nom de répertoire ou la syntaxe descriptive du volume est incorrecte.
• 1450 (ERROR_NO_SYSTEM_RESOURCES)
  Ressources systèmes insuffisantes pour compléter la requête.

Un problème spécifique avec les tables est que le serveur MySQL garde les données dans un dictionnaire de données .frm, qu'il stocke dans le dossier de base de données, alors que InnoDB stocke aussi les informations dans son propre dossier de données dans l'espace de tables. Si vous déplacez le fichier .frm, ou si vous utilisez DROP DATABASE en MySQL versions 3.23.44 et plus anciennes, ou si le serveur crashe au milieu d'une opération dans le dictionnaire de données, le fichier .frm peut être déconnecté du dictionnaire de données interne InnoDB.

Un symptôme de ce déphasage est que les commandes CREATE TABLE échouent. Si cela arrive, vous devez lire le fichier de logs d'erreurs. Si le fichier de logs dit que la table existe déjà dans le dictionnaire interne d'InnoDB, vous avez un fichier de tables orphelin dans l'espace de tables InnoDB, qui n'a plus de fichier .frm correspondant. Le message d'erreur ressemble à ceci :

InnoDB: Error: table test/parent already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

Vous pouvez effacer le fichier orphelin en suivant les instructions du message d'erreur.

Un autre symptôme de déphasage est que MySQL affiche une erreur qui dit qu'il ne peut pas ouvrir le fichier .InnoDB :

ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)

Dans les logs d'erreurs, vous trouverez le message suivant :

InnoDB: Cannot find table test/child2 from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?

Cela signifie qu'il y a un fichier .frm orphelin sans table correspondante dans les données InnoDB. Vous pouvez effacer simplement le fichier orphelin .frm.

Si MySQL crashe au milieu d'une opération ALTER TABLE, vous pouvez vous retrouver avec un fichier temporaire de table, orphelin, dans le code InnoDB. Avec innodb_table_monitor vous pouvez voir une table dont le nom est #sql..., mais comme MySQL ne vous permet pas d'accéder à une table avec un tel nom vous ne pouvez ni l'effacer, ni la détruire. La solution est d'utiliser un mécanisme spécial, disponible depuis MySQL 3.23.48.

Lorsque vous avez une table orpheline #sql_id dans un espace de tables, vous pouvez faire que InnoDB le renomme en rsql_id_recover_innodb_tmp_table avec la commande :

CREATE TABLE `rsql_id_recover_innodb_tmp_table`(...) TYPE=InnoDB;

Les guillemets obliques autour du nom de table sont nécessaires, car une table temporaire contient un tiret '-'.

La définition de la table doit être similaire à celle de la table temporaire. Si vous ne connaissez pas la définition de la table temporaire, vous pouvez utiliser une définition arbitraire dans la commande CREATE TABLE précédente, et ensuite, remplacer le fichier rsql_id.frm par le fichier #sql_id.frm de la table temporaire. Notez que pour copier ou pour renommer un fichier dans le shell, vous devez mettre le nom du fichier entre guillemets, car le nom du fichier contient '#'. Alors, vous pouvez exporter et effacer la table renommée.