Tester et déboguer une procédure stockée ou un trigger

Tester une procédure stockée, c'est l'appeler avec des cas choisis à l'avance (le cas normal, les cas limites, les cas d'erreur) et vérifier automatiquement le résultat, dans un script que tu peux rejouer après chaque modification. MySQL n'a ni débogueur pas à pas ni outil de test intégré pour le SQL procédural : il faut construire les tiens, avec quelques procédures et un peu de méthode. Ce chapitre montre comment, puis comment versionner et sauvegarder tes routines.

Une procédure d'assertion

Le cœur d'un test automatique est l'assertion : une vérification qui échoue bruyamment si le résultat n'est pas celui attendu. Une petite procédure suffit :

DELIMITER //
CREATE PROCEDURE verifier(IN p_condition BOOLEAN, IN p_message VARCHAR(100))
BEGIN
  IF p_condition IS NULL OR NOT p_condition THEN
    SET @echec = CONCAT('ÉCHEC : ', p_message);
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @echec;
  END IF;
END //
DELIMITER ;

CALL verifier(1 = 2, 'démonstration d''un échec');
-- ERROR 1644 (45000): ÉCHEC : démonstration d'un échec

Une condition NULL est traitée comme un échec : un test qui ne sait pas répondre ne doit pas passer.

Un script de test pour une procédure

Le test de installer_logiciel (chapitre 7) couvre les quatre cas qui comptent : la dernière licence acceptée, la suivante refusée, un logiciel libre sans limite, un doublon refusé. Pour vérifier qu'une erreur est bien levée, on la rattrape dans un sous-bloc et on contrôle son numéro :

DELIMITER //
CREATE PROCEDURE test_installer_logiciel()
BEGIN
  DECLARE v_nb  INT;
  DECLARE v_num INT DEFAULT 0;

  -- Cas 1 : la dernière licence Odoo est acceptée
  CALL installer_logiciel(9, 5);
  SELECT COUNT(*) INTO v_nb FROM installation WHERE id_logiciel = 5;
  CALL verifier(v_nb = 8, 'cas 1, 8 installations d''Odoo attendues');

  -- Cas 2 : la suivante est refusée par SIGNAL (erreur 1644), et rien n'est ajouté
  BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
      GET DIAGNOSTICS CONDITION 1 v_num = MYSQL_ERRNO;
    CALL installer_logiciel(14, 5);
  END;
  CALL verifier(v_num = 1644, 'cas 2, erreur 1644 attendue');
  SELECT COUNT(*) INTO v_nb FROM installation WHERE id_logiciel = 5;
  CALL verifier(v_nb = 8, 'cas 2, toujours 8 installations d''Odoo');

  -- Cas 3 : un logiciel libre n'est pas limité
  CALL installer_logiciel(14, 3);
  CALL verifier(EXISTS (SELECT 1 FROM installation WHERE id_poste = 14 AND id_logiciel = 3),
                'cas 3, Firefox installé sur le poste 14');

  -- Cas 4 : un doublon est refusé (erreur 1062)
  SET v_num = 0;
  BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
      GET DIAGNOSTICS CONDITION 1 v_num = MYSQL_ERRNO;
    CALL installer_logiciel(14, 3);
  END;
  CALL verifier(v_num = 1062, 'cas 4, erreur 1062 attendue');

  SELECT 'installer_logiciel : 4 cas réussis' AS resultat;
END //
DELIMITER ;

CALL test_installer_logiciel();

Trois règles rendent ce test fiable :

• Partir d'un état connu. Le test suppose la base fraîche : 7 installations d'Odoo. Lance-le après avoir rejoué parc.sql et parc-complements.sql, sur une base de test, jamais sur la base de production. Comme installer_logiciel valide sa propre transaction, le test laisse des traces : on remet la base à zéro avant chaque passage.
• Tester les erreurs autant que les réussites. Le cas 2 vérifie à la fois l'erreur et l'absence d'effet : une procédure qui lève la bonne erreur mais a déjà inséré la ligne est fausse.
• Un message par vérification : quand un test échoue, ÉCHEC : cas 2, erreur 1644 attendue dit tout de suite où chercher.

Tester un trigger sans rien laisser

Un trigger ne valide jamais de transaction lui-même : on peut donc le tester dans une transaction annulée à la fin. La base revient exactement à son état de départ, et le test se rejoue à volonté.

START TRANSACTION;

UPDATE ticket SET statut = 'resolu' WHERE id_ticket = 12;
CALL verifier((SELECT date_cloture IS NOT NULL FROM ticket WHERE id_ticket = 12),
              'trg_ticket_cloture : date de clôture renseignée');

UPDATE ticket SET statut = 'en_cours' WHERE id_ticket = 9;
CALL verifier((SELECT date_cloture IS NULL FROM ticket WHERE id_ticket = 9),
              'trg_ticket_cloture : date effacée à la réouverture');

SELECT 'trg_ticket_cloture : 2 cas réussis' AS resultat;
ROLLBACK;

Après le ROLLBACK, le ticket 12 est toujours ouvert et le ticket 9 toujours resolu, avec sa date de clôture du 6 octobre. Si une vérification échoue, le script s'arrête sur l'erreur : pense à exécuter ROLLBACK à la main.

Déboguer : savoir ce qui se passe dedans

Quand une procédure donne un résultat faux sans erreur, il faut voir les valeurs intermédiaires. Trois techniques :

1. Un SELECT de débogage, retiré ensuite. Dans le client mysql, chaque SELECT sans INTO s'affiche : SELECT v_minutes, v_licences AS debug; au milieu de la procédure montre les valeurs au passage. Pratique, mais interdit dans une fonction et dans un trigger, et gênant pour une application qui ne s'attend pas à un jeu de résultats de plus.

2. Une table de traces, qui fonctionne partout, y compris dans les triggers et les événements :

CREATE TABLE trace_debug (
  n       INT AUTO_INCREMENT PRIMARY KEY,
  quand   DATETIME(6) DEFAULT NOW(6),
  message VARCHAR(255)
);

-- dans la procédure à déboguer
INSERT INTO trace_debug (message)
VALUES (CONCAT('classer(', p_id_ticket, ') : v_minutes = ', COALESCE(v_minutes, 'NULL')));

Sur une procédure qui calcule SUM(duree_min) sans COALESCE, la trace v_minutes = NULL pour le ticket 7 explique tout de suite pourquoi le test v_minutes > 60 ne se comporte pas comme prévu. Attention : dans une transaction annulée, les traces disparaissent avec le reste.

3. Les avertissements. Après un CALL, SHOW WARNINGS; affiche les avertissements de la dernière instruction exécutée dans la procédure, par exemple l'avertissement 1329 d'un SELECT … INTO qui n'a rien trouvé, quand c'est la dernière instruction. Une instruction suivante efface la liste : c'est donc surtout utile pour tester un morceau de code isolé.

Quand la trace ne suffit pas, sors la requête de la procédure : remplace les paramètres par des valeurs, exécute-la seule dans le client, et regarde son plan avec EXPLAIN si elle est lente.

Les bogues classiques, à vérifier d'abord

Pense aussi à vérifier ce que le serveur a réellement enregistré : SHOW CREATE PROCEDURE donne le code, mais aussi le sql_mode et le jeu de caractères en vigueur à la création. Une routine s'exécute toujours avec le sql_mode de sa création, même si celui du serveur a changé depuis : une procédure créée par un outil qui désactive le mode strict continuera de tronquer silencieusement les valeurs trop longues.

Versionner et déployer tes routines

Une procédure modifiée directement sur le serveur, dans phpMyAdmin, n'existe dans aucun dépôt : impossible de savoir qui l'a changée, ni de revenir en arrière. Traite tes routines comme du code :

• un fichier par routine (routines/cloturer_ticket.sql), ou un fichier de migration par évolution (migrations/2026-11-installer-logiciel.sql), versionné dans Git avec le reste de l'application (cours Git et GitHub) ;
• chaque fichier est rejouable : DROP PROCEDURE IF EXISTS, puis CREATE PROCEDURE, avec les DELIMITER ;
• le déploiement se fait par script, jamais à la main : mysql --default-character-set=utf8mb4 -u admin_parc -p parc < routines/cloturer_ticket.sql ;
• chaque migration a son script d'annulation (DROP TRIGGER …, DROP PROCEDURE …) : c'est ce que recommande la méthode de l'épreuve E6 quand tu ajoutes un trigger à ta réalisation.

-- routines/cloturer_ticket.sql : rejouable autant de fois qu'on veut
DROP PROCEDURE IF EXISTS cloturer_ticket;
DELIMITER //
CREATE PROCEDURE cloturer_ticket(…)
COMMENT 'Gère sa propre transaction. Erreurs 1644 : ticket inconnu ou déjà résolu.'
BEGIN
  …
END //
DELIMITER ;
GRANT EXECUTE ON PROCEDURE parc.cloturer_ticket TO 'helpdesk'@'192.168.21.20';

N'oublie pas le GRANT à la fin : supprimer une procédure supprime aussi les droits EXECUTE donnés dessus.

Sauvegarder les routines avec mysqldump

mysqldump sauvegarde les triggers par défaut, mais ni les procédures et fonctions, ni les événements :

# Tout : tables, données, triggers, routines et événements
mysqldump --default-character-set=utf8mb4 --routines --events -u sauvegarde -p parc > parc-complet.sql

# Seulement le code : routines, événements et triggers, sans tables ni données
mysqldump --no-data --no-create-info --routines --events -u sauvegarde -p parc > parc-code.sql

Une sauvegarde sans --routines --events se restaure sans erreur, et l'application ne s'en aperçoit qu'au premier CALL : ERROR 1305 (42000): PROCEDURE parc.cloturer_ticket does not exist. Teste tes restaurations (restaurer et tester la restauration), et vérifie que le compte de sauvegarde a bien les privilèges nécessaires (SHOW VIEW, TRIGGER, EVENT et la lecture des routines).

Exercices

DELIMITER //
CREATE PROCEDURE test_affecter_poste()
BEGIN
  DECLARE v_num INT DEFAULT 0;

  START TRANSACTION;

  -- Cas 1 : le PC-0014, en stock, est affecté à Paul Martin
  CALL affecter_poste(14, 14);
  CALL verifier((SELECT id_employe FROM poste WHERE id_poste = 14) = 14, 'cas 1, poste 14 affecté');

  -- Cas 2 : le PC-0004 appartient déjà à Sofiane Benali
  BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION GET DIAGNOSTICS CONDITION 1 v_num = MYSQL_ERRNO;
    CALL affecter_poste(4, 14);
  END;
  CALL verifier(v_num = 1644, 'cas 2, refus attendu');
  CALL verifier((SELECT id_employe FROM poste WHERE id_poste = 4) = 4, 'cas 2, poste 4 inchangé');

  -- Cas 3 : poste inconnu
  SET v_num = 0;
  BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION GET DIAGNOSTICS CONDITION 1 v_num = MYSQL_ERRNO;
    CALL affecter_poste(99, 14);
  END;
  CALL verifier(v_num = 1644, 'cas 3, refus attendu');

  ROLLBACK;
  SELECT 'affecter_poste : 3 cas réussis' AS resultat;
END //
DELIMITER ;

-- migrations/2026-11-trigger-technicien.sql
DROP TRIGGER IF EXISTS trg_intervention_technicien;
DELIMITER //
CREATE DEFINER = 'routines_parc'@'localhost' TRIGGER trg_intervention_technicien
BEFORE INSERT ON intervention
FOR EACH ROW
BEGIN
  IF NOT EXISTS (SELECT 1
                 FROM employe e
                 JOIN service s ON s.id_service = e.id_service
                 WHERE e.id_employe = NEW.id_technicien AND s.code = 'INFO') THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Seul un membre du service informatique peut intervenir';
  END IF;
END //
DELIMITER ;

-- migrations/2026-11-trigger-technicien.annulation.sql
DROP TRIGGER IF EXISTS trg_intervention_technicien;