MySQL et ses messages d’erreur

13 septembre 2011 par stephane

Je suis en généralement plutôt content de MySQL : c’est simple et stable, ça fonctionne bien. Mais il reste encore du travail pour que les messages d’erreur soient explicites. Petit résumé d’une frayeur causée par un message d’erreur approximatif.

Une de mes tables a une tendance marquée à la fragmentation, ce qui a pour conséquence de faire gonfler artificiellement sa taille et de faire baisser les performances de certaines requêtes. Je dois donc de temps à autre la défragmenter. Pas de problème : je commence par regarder la taille sur le disque du fichier .ibd (il s’agit d’une table InnoDB sur un serveur pour lequel innodb_file_per_table est activé). Quand la table est défragmentée, elle fait environ 8 Go :

# cd /data/mysql/main_revshare
# du -sh bris_statistic_video.ibd
11G bris_statistic_video.ibd

Pas de doute, une séance de défragmentation s’impose. Je me connecte donc au serveur MySQL pour exécuter un OPTIMIZE TABLE :

mysql> OPTIMIZE TABLE bris_statistic_video\G
******** 1. row ********
Table: dailymotion.bris_statistic_video
Op: optimize
Msg_type: Error
Msg_text: Table 'dailymotion.bris_statistic_video' doesn't exist
******** 2. row ********
Table: dailymotion.bris_statistic_video
Op: optimize
Msg_type: error
Msg_text: Corrupt

Et là, c’est le drame ! Quoi, ma table est corrompue ? Impossible, l’application fonctionne toujours et la table est utilisée partout !

Comment sortir de ce problème : redémarrer le serveur en espérant qu’InnoDB répare la table, récupérer un backup ? En fait non, il faut simplement rester calme et bien regarder le message : si à la 2ème ligne, MySQL nous annonce que la table est corrompue, à la 1ère MySQL nous annonce que la table n’existe pas … alors que nous avons vu que le fichier .ibd existe bien. Louche cette histoire !

Un tour dans le journal d’erreurs confirme bien qu’il n’existe aucune erreur : il doit donc y avoir une erreur avec la commande OPTIMIZE. Réfléchissons, réfléchissons … ça y est : j’ai exécuté la commande OPTIMIZE dans la base main alors que ma table se trouve dans la base main_revshare. Il suffit donc de se positionner dans main_revshare et de relancer l’OPTIMIZE, avec succès cette fois.

Moralité : Mieux vaut se méfier des messages d’erreurs qui ne vous sont pas familiers et bien réfléchir à la cause de l’erreur avant de se lancer dans la correction d’un problème qui n’existe peut-être pas !

Mots-clefs : , ,

2 commentaires sur “MySQL et ses messages d’erreur”

  1. Dudu dit :

    Merci pour l’article et tous les autres au passage.

    J’ai 2 petites question :
    Comment peut on voir si un table à besoin d’être optimiser ?
    Et sur une architecture avec maître / slave, faut il utiliser le OPTIMIZE TABLE seulement sur le maître, ou sur les esclaves aussi ?

    Merci Bien

  2. stephane dit :

    Avec InnoDB, OPTIMIZE TABLE recalcule les statistiques sur les index et défragmente la table en la reconstruisant. Il est donc surtout intéressant d’utiliser cette commande quand l’optimiseur sort des plans d’exécution complètement délirants ou quand la table est fragmentée.
    Dans le 1er cas, les mauvais plans d’exécution se manifestent d’abord par des requêtes devenues extrêmement lentes du jour au lendemain, alors que rien n’a changé dans l’application.
    Dans le second cas, la colonne data_free du résultat de la commande show table status donne l’espace disque alloué par l’OS mais non utilisé par InnoDB, en gros ça représente la fragmentation. Si cette valeur est élevée, il peut être intéressant de faire un OPTIMIZE TABLE.

    Un OPTIMIZE TABLE verrouille la table de telle sorte qu’il n’est plus possible d’écrire dessus (et peut durer très longtemps selon la taille de la table). Donc il vaut mieux faire cette opération sur des machines qui sont hors production. Pour les slaves, c’est en général assez facile de faire l’OPTIMIZE slave par slave. Pour le master en revanche, il faut souvent le faire devenir slave pour pouvoir le mettre hors production (et le remplacer par un des slaves).

Laisser une réponse