Gestion des erreurs et journalisation dans MySQL

Introduction

Dans ce laboratoire, vous allez explorer les techniques de gestion des erreurs et de journalisation de MySQL. Le laboratoire couvre l'activation de la journalisation générale des requêtes pour enregistrer toutes les instructions SQL exécutées sur le serveur, ce qui est utile pour le débogage et l'audit. Vous apprendrez à activer la journalisation générale des requêtes en définissant la variable système general_log sur ON et à vérifier ou modifier l'emplacement du fichier journal à l'aide de la variable general_log_file.

La première étape consiste à vous connecter au serveur MySQL en tant qu'utilisateur root, puis à activer la journalisation générale des requêtes au niveau global. Vous apprendrez également à afficher l'emplacement actuel du fichier journal et à le changer pour un autre chemin, en vous assurant que le processus du serveur MySQL a les autorisations d'écriture pour l'emplacement spécifié.

Skills Graph

Activer la journalisation générale des requêtes

Dans cette étape, nous allons activer la journalisation générale des requêtes dans MySQL. La journalisation générale des requêtes enregistre toutes les instructions SQL exécutées sur le serveur. Cela peut être utile pour le débogage, l'audit et l'analyse des performances. Cependant, il est important de noter que l'activation de la journalisation générale des requêtes peut générer une grande quantité de données et peut avoir un impact sur les performances du serveur, en particulier dans les environnements à fort trafic. Par conséquent, elle doit être utilisée avec prudence et activée uniquement lorsque cela est nécessaire.

Tout d'abord, connectons-nous au serveur MySQL en tant qu'utilisateur root. Ouvrez un terminal et exécutez la commande suivante :

mysql -u root -p

Vous serez invité à saisir le mot de passe root. Entrez le mot de passe et appuyez sur Entrée. Si vous n'avez pas défini de mot de passe root, appuyez simplement sur Entrée.

Maintenant que vous êtes connecté au serveur MySQL, vous pouvez activer la journalisation générale des requêtes. Pour ce faire, vous devez définir la variable système general_log sur ON. Exécutez l'instruction SQL suivante :

SET GLOBAL general_log = 'ON';

Cette commande active la journalisation générale des requêtes globalement pour toutes les connexions.

Ensuite, vous devez spécifier le fichier journal dans lequel les requêtes seront écrites. Le fichier journal par défaut est généralement situé dans le répertoire de données MySQL et porte le nom hostname.log. Vous pouvez vérifier l'emplacement actuel du fichier journal en exécutant l'instruction SQL suivante :

SHOW VARIABLES LIKE 'general_log_file';

La sortie affichera la valeur actuelle de la variable general_log_file, qui est le chemin du fichier journal.

Si vous souhaitez changer l'emplacement du fichier journal, vous pouvez définir la variable système general_log_file sur un autre chemin. Par exemple, pour définir le fichier journal sur /tmp/mysql_general.log, exécutez l'instruction SQL suivante :

SET GLOBAL general_log_file = '/tmp/mysql_general.log';

Important : Assurez-vous que le processus du serveur MySQL a les autorisations d'écriture pour l'emplacement du fichier journal spécifié.

Maintenant que la journalisation générale des requêtes est activée, toutes les instructions SQL exécutées sur le serveur seront écrites dans le fichier journal. Pour vérifier que la journalisation fonctionne, exécutez quelques instructions SQL simples, telles que :

SELECT NOW();
SHOW DATABASES;

Ensuite, quittez le client MySQL :

exit

Enfin, vérifiez le contenu du fichier journal pour voir si les instructions SQL ont été enregistrées. Vous pouvez utiliser la commande cat pour afficher le fichier journal. Si vous avez utilisé l'emplacement du fichier journal par défaut, la commande serait similaire à :

sudo cat /var/log/mysql/mysql.log

Si vous avez changé l'emplacement du fichier journal pour /tmp/mysql_general.log, la commande serait :

sudo cat /tmp/mysql_general.log

Vous devriez voir les instructions SQL que vous avez exécutées dans le fichier journal, ainsi que des horodatages et d'autres informations.

N'oubliez pas de désactiver la journalisation générale des requêtes une fois que vous avez fini de l'utiliser, car elle peut consommer beaucoup d'espace disque et avoir un impact sur les performances du serveur. Pour désactiver la journalisation générale des requêtes, exécutez l'instruction SQL suivante :

mysql -u root -p -e "SET GLOBAL general_log = 'OFF';"

Cette commande désactive la journalisation générale des requêtes globalement.

Ajouter la gestion des erreurs à une procédure stockée

Dans cette étape, nous allons ajouter la gestion des erreurs à une procédure stockée dans MySQL. La gestion des erreurs est essentielle pour garantir la robustesse et la fiabilité de vos procédures stockées. Elle vous permet de gérer gracieusement les situations inattendues, telles que des entrées invalides, des erreurs de connexion à la base de données ou des violations d'intégrité des données.

Tout d'abord, créons une simple procédure stockée sans gestion des erreurs. Cette procédure tentera d'insérer un nouvel enregistrement dans une table. Si l'insertion échoue (par exemple, en raison d'une clé en double), la procédure se terminera simplement sans fournir d'informations spécifiques sur l'erreur.

Connectez-vous au serveur MySQL en tant qu'utilisateur root :

mysql -u root -p

Maintenant, créons une base de données et une table pour notre exemple :

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

Ensuite, créez une procédure stockée nommée insert_product qui insère un nouveau produit dans la table products :

DELIMITER //
CREATE PROCEDURE insert_product(IN p_id INT, IN p_name VARCHAR(255))
BEGIN
    INSERT INTO products (id, name) VALUES (p_id, p_name);
END //
DELIMITER ;

Cette procédure stockée prend deux paramètres d'entrée : p_id (l'identifiant du produit) et p_name (le nom du produit). Elle tente ensuite d'insérer un nouvel enregistrement dans la table products avec les valeurs données.

Maintenant, ajoutons la gestion des erreurs à la procédure stockée. Nous allons utiliser la syntaxe DECLARE ... HANDLER pour définir des gestionnaires d'erreurs qui seront exécutés lorsque des erreurs spécifiques se produisent.

Modifiez la procédure stockée insert_product comme suit :

DELIMITER //
CREATE PROCEDURE insert_product(IN p_id INT, IN p_name VARCHAR(255))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'An error occurred during the insertion.' AS message;
    END;

    INSERT INTO products (id, name) VALUES (p_id, p_name);
END //
DELIMITER ;

Dans cette version modifiée, nous avons ajouté un gestionnaire d'erreurs en utilisant DECLARE EXIT HANDLER FOR SQLEXCEPTION. Ce gestionnaire sera exécuté si une exception SQL se produit lors de l'exécution de la procédure stockée. À l'intérieur du gestionnaire, nous sélectionnons simplement un message indiquant qu'une erreur s'est produite.

Testons la procédure stockée avec la gestion des erreurs. Tout d'abord, insérez un produit :

CALL insert_product(1, 'Product A');

Cela devrait insérer un nouvel enregistrement dans la table products sans erreur.

Maintenant, essayez d'insérer le même produit à nouveau :

CALL insert_product(1, 'Product A');

Cette fois, l'insertion échouera car la colonne id est une clé primaire et ne peut pas contenir de valeurs en double. Cependant, au lieu de simplement se terminer, le gestionnaire d'erreurs sera exécuté et vous verrez le message "An error occurred during the insertion."

Vous pouvez également ajouter des gestionnaires d'erreurs plus spécifiques pour différents types d'erreurs. Par exemple, vous pouvez ajouter un gestionnaire pour les erreurs de clé en double (SQLSTATE '23000') :

DELIMITER //
CREATE PROCEDURE insert_product(IN p_id INT, IN p_name VARCHAR(255))
BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    BEGIN
        SELECT 'Duplicate key error.' AS message;
    END;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'An error occurred during the insertion.' AS message;
    END;

    INSERT INTO products (id, name) VALUES (p_id, p_name);
END //
DELIMITER ;

Maintenant, si vous essayez d'insérer un produit en double, vous verrez le message "Duplicate key error." au lieu du message d'erreur générique.

Cet exemple montre comment ajouter une gestion des erreurs de base à une procédure stockée dans MySQL. En utilisant des gestionnaires d'erreurs, vous pouvez rendre vos procédures stockées plus robustes et fournir des messages d'erreur plus informatifs aux utilisateurs.

Générer une erreur personnalisée avec SIGNAL

Dans cette étape, nous allons apprendre à générer une erreur personnalisée dans MySQL en utilisant l'instruction SIGNAL. L'instruction SIGNAL vous permet de générer des conditions d'erreur définies par l'utilisateur dans des procédures stockées, des fonctions ou des déclencheurs (triggers). Cela est utile pour appliquer des règles métier, valider les données d'entrée et fournir des messages d'erreur plus informatifs aux utilisateurs.

Tout d'abord, connectez-vous au serveur MySQL en tant qu'utilisateur root :

mysql -u root -p

Nous allons continuer à utiliser la base de données testdb et la table products de l'étape précédente. Si vous ne les avez pas encore créées, exécutez les instructions SQL suivantes :

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    quantity INT
);

Maintenant, créons une procédure stockée qui met à jour la quantité d'un produit. Nous allons ajouter une vérification pour nous assurer que la quantité mise à jour n'est pas négative. Si c'est le cas, nous allons générer une erreur personnalisée en utilisant l'instruction SIGNAL.

DELIMITER //
CREATE PROCEDURE update_quantity(IN p_id INT, IN p_quantity INT)
BEGIN
    IF p_quantity < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Quantity cannot be negative.';
    END IF;

    UPDATE products SET quantity = p_quantity WHERE id = p_id;
END //
DELIMITER ;

Dans cette procédure stockée, nous vérifions d'abord si la quantité d'entrée p_quantity est inférieure à 0. Si c'est le cas, nous exécutons l'instruction SIGNAL.

L'instruction SIGNAL prend une valeur SQLSTATE comme argument. SQLSTATE est une chaîne de cinq caractères qui représente une condition d'erreur spécifique. Dans ce cas, nous utilisons la valeur SQLSTATE '45000', qui est une erreur générique définie par l'utilisateur. Vous pouvez utiliser n'importe quelle valeur SQLSTATE dans la plage '45000' à '45999' pour vos erreurs personnalisées.

La clause SET MESSAGE_TEXT vous permet de spécifier un message d'erreur personnalisé qui sera renvoyé à l'utilisateur. Dans ce cas, nous définissons le message d'erreur sur 'Quantity cannot be negative.'.

Maintenant, testons la procédure stockée. Tout d'abord, insérez un produit dans la table products :

INSERT INTO products (id, name, quantity) VALUES (1, 'Product A', 10);

Maintenant, essayez de mettre à jour la quantité à une valeur négative :

CALL update_quantity(1, -5);

Cela générera une erreur personnalisée avec le message "Quantity cannot be negative.". Vous devriez voir un message d'erreur similaire au suivant :

ERROR 1644 (45000): Quantity cannot be negative.

Si vous essayez de mettre à jour la quantité à une valeur positive, la mise à jour réussira :

CALL update_quantity(1, 15);
SELECT * FROM products WHERE id = 1;

Cela mettra à jour la quantité du produit 1 à 15.

Vous pouvez également définir des valeurs SQLSTATE personnalisées et des messages d'erreur pour différentes conditions d'erreur. Cela vous permet de fournir des messages d'erreur plus spécifiques et informatifs aux utilisateurs.

Cet exemple montre comment générer une erreur personnalisée en utilisant l'instruction SIGNAL dans MySQL. En utilisant l'instruction SIGNAL, vous pouvez appliquer des règles métier, valider les données d'entrée et fournir des messages d'erreur plus informatifs aux utilisateurs.

Vérifier les entrées du journal d'erreurs

Dans cette étape, nous allons vérifier le journal d'erreurs de MySQL pour identifier et comprendre les messages d'erreur générés lors des opérations sur la base de données. Le journal d'erreurs est une ressource essentielle pour résoudre les problèmes, diagnostiquer les défauts et surveiller l'état de votre serveur MySQL.

L'emplacement du fichier du journal d'erreurs de MySQL dépend de la configuration de votre système. Un emplacement courant est /var/log/mysql/error.log. Vous pouvez déterminer l'emplacement exact en interrogeant la variable système log_error.

Tout d'abord, connectez-vous au serveur MySQL en tant qu'utilisateur root :

mysql -u root -p

Ensuite, exécutez l'instruction SQL suivante pour trouver l'emplacement du fichier du journal d'erreurs :

SHOW VARIABLES LIKE 'log_error';

La sortie affichera la valeur actuelle de la variable log_error, qui est le chemin vers le fichier du journal d'erreurs.

Maintenant que vous connaissez l'emplacement du fichier du journal d'erreurs, vous pouvez utiliser un éditeur de texte ou un outil en ligne de commande pour afficher son contenu. Dans l'environnement de la machine virtuelle (VM) LabEx, nous recommandons d'utiliser l'éditeur nano ou la commande cat.

Par exemple, si le fichier du journal d'erreurs est situé à /var/log/mysql/error.log, vous pouvez l'afficher en utilisant la commande suivante :

sudo cat /var/log/mysql/error.log

Ou, vous pouvez utiliser nano pour ouvrir le fichier dans un éditeur de texte :

sudo nano /var/log/mysql/error.log

Le fichier du journal d'erreurs contient un enregistrement chronologique des événements, y compris les erreurs, les avertissements et les messages d'information. Chaque entrée comprend généralement une horodatage, le niveau de gravité du message et une description de l'événement.

Générons quelques entrées dans le journal d'erreurs en provoquant intentionnellement des erreurs dans nos opérations sur la base de données. Nous allons utiliser la base de données testdb et la table products des étapes précédentes.

Tout d'abord, essayons d'insérer un produit en double dans la table products :

INSERT INTO products (id, name, quantity) VALUES (1, 'Product A', 10);

Cela générera une erreur de clé en double car la colonne id est une clé primaire.

Ensuite, essayons de mettre à jour la quantité d'un produit à une valeur négative en utilisant la procédure stockée update_quantity :

CALL update_quantity(1, -5);

Cela générera une erreur personnalisée que nous avons définie dans l'étape précédente.

Maintenant, vérifions à nouveau le fichier du journal d'erreurs pour voir les messages d'erreur générés par ces opérations. Vous devriez voir des entrées similaires aux suivantes :

[timestamp] [ERROR] [MY-013187] [InnoDB] Duplicate entry '1' for key 'products.PRIMARY'
[timestamp] [ERROR] [MY-013187] [Server] Quantity cannot be negative.

La première entrée indique une erreur de clé en double lors de la tentative d'insertion d'un produit avec un identifiant existant. La deuxième entrée montre le message d'erreur personnalisé généré par la procédure stockée update_quantity.

En analysant les entrées du journal d'erreurs, vous pouvez obtenir des informations précieuses sur les causes des erreurs et prendre les mesures appropriées pour les résoudre. Par exemple, vous devrez peut-être corriger les erreurs de saisie de données, modifier les procédures stockées ou ajuster la configuration de la base de données.

Il est important de vérifier régulièrement le fichier du journal d'erreurs pour identifier et résoudre les problèmes potentiels avant qu'ils ne deviennent plus graves. Vous pouvez également configurer MySQL pour faire tourner automatiquement le fichier du journal d'erreurs afin d'éviter qu'il ne devienne trop volumineux.

Enfin, n'oubliez pas de désactiver la journalisation générale des requêtes si vous l'avez activée dans la première étape, car cela peut consommer beaucoup d'espace disque et affecter les performances du serveur :

mysql -u root -p -e "SET GLOBAL general_log = 'OFF';"

Résumé

Dans ce laboratoire (lab), nous avons commencé par activer la journalisation générale des requêtes dans MySQL, qui enregistre toutes les instructions SQL exécutées pour le débogage, l'audit et l'analyse des performances. Nous nous sommes connectés au serveur MySQL en tant qu'utilisateur root et avons activé globalement la variable système general_log.

Nous avons ensuite examiné l'emplacement par défaut du fichier de journalisation générale des requêtes en utilisant SHOW VARIABLES LIKE 'general_log_file' et avons appris à modifier l'emplacement du fichier de journal en définissant la variable système general_log_file, en soulignant l'importance de s'assurer que le processus du serveur MySQL a les autorisations d'écriture pour l'emplacement du fichier de journal spécifié.