Chapitre 15 Le moteur de tables InnoDB

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 :

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ées 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'auto-validation activé, ce qui valide automatiquement toutes les requêtes que vous soumettez. Pour utiliser des requêtes multi-commandes, vous pouvez désactiver l'auto-validation 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'auto-validation 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 valideé, 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 un 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 utiliser 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'un 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 ».

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 auto-incré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 auto-incré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é d'une unité, et assignée à la colonne auto-incré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 dant 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 auto-increment 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'auto-incré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'auto-incré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 auto-increment 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é é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 ls 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ésente du signe pour les entiers doit être les mêmes. La taille des chaînes doivent être les mêmes. 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 une 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 contienent 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 parente, 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 correspondante 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 toute 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ée 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 auto-référente, d'un autre coté, fonctionne depuis la version 4.0.13. La clause ON DELETE CASCADE auto-référente à toujours fonctionné.

Un exemle :

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 mono-colonnes, 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 reflexives, 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 contraite 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é 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é 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 étranègres qui référence 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 étragnè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 log, 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 ».

Si vous pouvez arrêter le maître ou l'esclave, faîtes 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 :

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 écrite 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é 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 table partagées ibdata. Si vous supprimez cette option, les nouvelles tables seront crées dans l'espace de tables, mais vous pouvez toujours 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 table 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 classique commande 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 :

Dans ce contexte, un fichier .ibd ``propre'' signifie :

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

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

La liste de tâche 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 log 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 a 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 seraient causée par des importations de masse ou ALTER TABLE. Vous pouvez tuer le processu 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 inclus. 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.

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 log. 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 log, et applique les modifications qui ont eu lieu depuis le point de contrôle.

InnoDB écrit dans les fichiers de log 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 log, au cas où InnoDB 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 log 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'auto-validation est activé, chaque commande SQL est une transaction à part entière. MySQL démarre toujours une nouvelle transaction lorsque le mode d'auto-validation est activé.

Si l'auto-validation 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és, et rendus visibles aux autres. Un ROLLBACK, d'un autre coté, annule toutes les modifications.

Si la connexion a activé l'auto-validation, l'utilisateur peut faire une transaction multi-commandes en commen¸ant la transaction avec la commande START TRANSACTION ou BEGIN et en la terminant avec COMMIT ou ROLLBACK.

En terme 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 4 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 connexion 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 :

Une lecture cohérente signifie que InnoDB utilise son système de multi-versionnage 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 ont eu lieu depuis, ou n'ont pas été archivées. L'exception à cette règle est que la requête verra les modification apportées 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 vous 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 implenter l'intégrité référentielle dans votre application.

Ajoutons un autre exemple : nous avons un champs compteur dans la table CHILD_CODES que nous utilisons pour assigner un identifiant unique à chaque enfant que nous ajoutons dans la table CHILD. Evidemment, 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 solutionm, 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 tant 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 comande 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 posent 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 vous 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 verrouille 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é suivant 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 votre. 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é multi-versions.

                  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 voir 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ée durant l'opération. La présence d'une clause WHERE n'a pas d'importance. InnoDB ne se souviens pas de la condition WHERE exacte, mais sait quel 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 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, poru que les requêtes ne fasse pas de scan de table inutiles.

MySQL ouvre les connexions des clients en mode d'auto-validation, par défaut. Lorsque l'auto-validation 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'auto-validation, 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. See Section 15.16, « Gestion des erreurs InnoDB ».

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

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. Deci 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 dangeureux, à 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 transactions 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 :

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 lue 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 écrit 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 log .err dans le dossier datadir. Les données lue 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ête.

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.

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 :

Tous les index de InnoDB sont des index B-tree où les lignes d'index sont stockées dans un noeud terminal de l'arbre. La taille par défaut d'une page d'index est de 16ko. 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 environs 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 coté, 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éatoire, si InnoDB de 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.

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 :

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 écrits 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ée sous Windows et sans couche de contrôle sous Unix, ce qui améliore les performances.

Lorsque vous créer 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 1Mo avec InnoDB vaut 1024 * 1024 octets, alors que 1Mo signifie généralement 1000000 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émararge du serveur, InnoDB remarque le mot newraw et initialise un 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 reinitialiser 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 inialisé 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 table.

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 noeuds non terminaux du B tree, et un autre pour les noeuds terminaux. L'idée ici est d'améliorer la séquence des noeuds terminaux, qui contiennent les données.

Lorsqu'un segment croit dans un espace de table, 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 extension 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 table 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 libre. InnoDB se réserve toujours quelques extensions pour la gestion interne. Ces extensions réservées ne sont pas inclues 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ée 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.

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

La table suivante fournit une liste de certains code d'erreur système Linux. Pour une version complète, voyez Linux source code.

La table suivante fournit une liste de certains code d'erreur système Windows. Pour une version complète, voyez Microsoft website.

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 log d'erreurs. Si le fichier de log dit que la table existe déjà dans le dictionnaire interne d'InnoDB, vous avez un fichier de table orphelin dans l'espace de table 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 st que MySQL affiche MySQL 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 table, 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 temporire, 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.