Gérer les erreurs : DECLARE HANDLER, SIGNAL et GET DIAGNOSTICS

Gérer les erreurs dans une procédure stockée MySQL, c'est décider ce qui se passe quand une instruction échoue : un gestionnaire déclaré avec DECLARE … HANDLER intercepte l'erreur, SIGNAL lève une erreur métier avec ton propre message, RESIGNAL la renvoie à l'appelant, et GET DIAGNOSTICS en lit le détail. Ce chapitre montre chaque mécanisme sur la base parc, et comment journaliser les erreurs dans une table.

Ce qui se passe sans gestionnaire

Cette procédure clôture un ticket en deux temps, sans aucune gestion d'erreur :

DELIMITER //
CREATE PROCEDURE cloturer_naif(IN p_id_ticket INT, IN p_id_technicien INT, IN p_duree_min SMALLINT)
BEGIN
  UPDATE ticket SET statut = 'resolu', date_cloture = NOW() WHERE id_ticket = p_id_ticket;

  INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min)
  VALUES (p_id_ticket, p_id_technicien, NOW(), p_duree_min);
END //
DELIMITER ;

CALL cloturer_naif(14, 11, 0);
-- ERROR 3819 (HY000): Check constraint 'ck_intervention_duree' is violated.

SELECT statut FROM ticket WHERE id_ticket = 14;   -- resolu

L'INSERT échoue (une durée de 0 minute viole la contrainte CHECK), la procédure s'arrête et l'erreur remonte à l'appelant. Mais l'UPDATE, lui, a réussi et reste en place : le ticket 14 est résolu sans aucune intervention. Une erreur arrête la procédure, elle n'annule pas ce qui précède. C'est le rôle des transactions (chapitre 7) ; ce chapitre donne d'abord les outils pour réagir à l'erreur.

Pour remettre le ticket dans son état : UPDATE ticket SET statut = 'ouvert', date_cloture = NULL WHERE id_ticket = 14;.

Codes d'erreur et SQLSTATE

Chaque erreur MySQL porte deux codes : un numéro MySQL (1062) et un SQLSTATE à cinq caractères, normalisé (23000). Plusieurs numéros partagent souvent le même SQLSTATE.

Les deux premiers caractères du SQLSTATE donnent la classe : 00 succès, 01 avertissement, 02 « pas de donnée », tout le reste est une erreur. MySQL regroupe ces classes sous trois noms que tu utiliseras dans les gestionnaires : SQLWARNING (classe 01), NOT FOUND (classe 02) et SQLEXCEPTION (toutes les autres).

DECLARE … HANDLER

Un gestionnaire dit : « si cette condition survient dans ce bloc, exécute ceci ».

DECLARE { CONTINUE | EXIT } HANDLER FOR condition [, condition …]
  instruction;   -- ou un bloc BEGIN … END

La condition est un numéro d'erreur MySQL (1062), un SQLSTATE (SQLSTATE '23000'), l'une des trois classes (SQLWARNING, NOT FOUND, SQLEXCEPTION) ou une condition nommée (plus bas). Après l'exécution du gestionnaire :

• CONTINUE : la procédure reprend à l'instruction qui suit celle qui a échoué ;
• EXIT : le bloc BEGIN … END qui déclare le gestionnaire se termine. Si c'est le bloc principal, la procédure se termine, sans erreur pour l'appelant (sauf si le gestionnaire fait un RESIGNAL).

Une procédure d'installation qui transforme les erreurs prévisibles en message clair, au lieu de les laisser remonter :

DELIMITER //
CREATE PROCEDURE ajouter_installation(
  IN  p_id_poste    INT,
  IN  p_id_logiciel INT,
  OUT p_message     VARCHAR(100)
)
BEGIN
  DECLARE CONTINUE HANDLER FOR 1062 SET p_message = 'Ce logiciel est déjà installé sur ce poste';
  DECLARE CONTINUE HANDLER FOR 1452 SET p_message = 'Poste ou logiciel inconnu';

  SET p_message = 'Installation enregistrée';
  INSERT INTO installation (id_poste, id_logiciel, date_installation)
  VALUES (p_id_poste, p_id_logiciel, CURDATE());
END //
DELIMITER ;

CALL ajouter_installation(14, 3, @m);   SELECT @m;   -- Installation enregistrée
CALL ajouter_installation(1, 3, @m);    SELECT @m;   -- Ce logiciel est déjà installé sur ce poste
CALL ajouter_installation(99, 3, @m);   SELECT @m;   -- Poste ou logiciel inconnu

Le message par défaut est écrit avant l'INSERT ; si l'INSERT échoue, le gestionnaire le remplace, puis la procédure continue jusqu'à son END. Pour annuler l'installation de test : DELETE FROM installation WHERE id_poste = 14 AND id_logiciel = 3;.

Quel gestionnaire s'exécute ?

Quand plusieurs gestionnaires correspondent à une erreur, MySQL choisit le plus précis : un numéro d'erreur MySQL l'emporte sur un SQLSTATE, qui l'emporte sur une classe (SQLEXCEPTION). Dans cette procédure, chaque INSERT échoue et déclenche un gestionnaire différent :

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION       SELECT 'SQLEXCEPTION'   AS gestionnaire;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'   SELECT 'SQLSTATE 23000' AS gestionnaire;
DECLARE CONTINUE HANDLER FOR 1062               SELECT '1062'           AS gestionnaire;

INSERT INTO installation VALUES (1, 3, CURDATE());          -- doublon : 1062
INSERT INTO installation VALUES (99, 3, CURDATE());         -- poste inconnu : 1452, SQLSTATE 23000
INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min)
VALUES (1, 11, NOW(), 0);                                   -- contrainte CHECK : 3819, SQLSTATE HY000

Le premier affiche 1062, le deuxième SQLSTATE 23000, le troisième SQLEXCEPTION. Sur MariaDB, le troisième affiche SQLSTATE 23000, puisque la violation d'une contrainte CHECK y porte ce SQLSTATE (encadré ci-dessus).

Deux règles de portée complètent le tableau :

• un gestionnaire ne s'applique qu'aux erreurs de son bloc (et des blocs imbriqués). Un gestionnaire déclaré dans un sous-bloc BEGIN … END ne rattrape pas une erreur survenue après ce sous-bloc ;
• une erreur non traitée dans une procédure appelée remonte à la procédure appelante, dont les gestionnaires peuvent la rattraper. La procédure appelée s'arrête à l'erreur ; l'appelante, elle, continue si son gestionnaire est CONTINUE.

Nommer une condition

DECLARE … CONDITION donne un nom à un code, ce qui rend les gestionnaires lisibles :

DECLARE doublon       CONDITION FOR 1062;
DECLARE poste_inconnu CONDITION FOR SQLSTATE '45000';

DECLARE CONTINUE HANDLER FOR doublon SET p_message = 'Déjà installé';

Une condition se déclare avec les variables, avant les curseurs et les gestionnaires.

SIGNAL : lever ta propre erreur

SIGNAL lève une erreur, exactement comme si une instruction avait échoué. C'est ainsi qu'une procédure ou un trigger refuse une opération qui viole une règle de gestion :

SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT = 'Ticket inconnu';

• SQLSTATE '45000' est le code réservé aux erreurs définies par l'utilisateur (« exception non gérée définie par l'utilisateur ») ; c'est celui qu'on utilise presque toujours ;
• MESSAGE_TEXT est le message que reçoit l'appelant. Sans lui, le message est Unhandled user-defined exception condition, ce qui n'aide personne ;
• côté client, l'erreur arrive avec le numéro 1644 : ERROR 1644 (45000): Ticket inconnu. C'est ce numéro que l'application teste pour reconnaître une erreur métier (chapitre 12) ;
• MYSQL_ERRNO = 5001 remplace ce numéro par le tien, pour distinguer plusieurs erreurs métier côté application : ERROR 5001 (45000): ….

Le message accepte une variable, ce qui permet un message précis :

DECLARE v_message VARCHAR(128);
…
SET v_message = CONCAT('Plus de licence ', v_nom, ' : ', v_installees, ' installations pour ',
                       v_licences, ' licences');
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_message;

Un SIGNAL avec un SQLSTATE de classe 01 lève un simple avertissement : la procédure continue, et l'avertissement apparaît dans SHOW WARNINGS. SIGNAL fonctionne aussi hors d'une routine, directement dans le client, ce qui est pratique pour tester le message que verra l'application.

RESIGNAL : relayer une erreur

Dans un gestionnaire, RESIGNAL relance l'erreur qui l'a déclenché, telle quelle ou avec un message modifié. Le gestionnaire peut ainsi faire quelque chose (annuler, journaliser) et prévenir l'appelant :

DELIMITER //
CREATE PROCEDURE supprimer_logiciel(IN p_id_logiciel INT)
BEGIN
  DECLARE EXIT HANDLER FOR 1451
    RESIGNAL SET MESSAGE_TEXT = 'Logiciel encore installé : désinstalle-le d''abord';

  DELETE FROM logiciel WHERE id_logiciel = p_id_logiciel;
END //
DELIMITER ;

CALL supprimer_logiciel(5);
-- ERROR 1451 (23000): Logiciel encore installé : désinstalle-le d'abord

Le numéro et le SQLSTATE d'origine sont conservés, seul le message change : le message technique de la clé étrangère, qui révèle les noms des tables et des contraintes, ne sort pas de la base. RESIGNAL n'est permis que dans un gestionnaire.

GET DIAGNOSTICS : lire le détail d'une erreur

Un gestionnaire SQLEXCEPTION rattrape toutes les erreurs : pour savoir laquelle, on interroge la zone de diagnostic avec GET DIAGNOSTICS.

GET DIAGNOSTICS CONDITION 1
  v_etat = RETURNED_SQLSTATE,   -- '23000'
  v_num  = MYSQL_ERRNO,         -- 1062
  v_msg  = MESSAGE_TEXT;        -- 'Duplicate entry …'

GET DIAGNOSTICS v_nb = NUMBER, v_lignes = ROW_COUNT; lit les informations sur l'instruction elle-même : le nombre de conditions et le nombre de lignes modifiées par la dernière instruction.

La zone de diagnostic est remplacée par chaque nouvelle instruction. Dans un gestionnaire, lis-la en premier, avant toute autre instruction ; ou utilise GET STACKED DIAGNOSTICS, qui lit la zone telle qu'elle était au déclenchement du gestionnaire, même après d'autres instructions. GET DIAGNOSTICS seul équivaut à GET CURRENT DIAGNOSTICS.

Journaliser les erreurs

Le modèle complet : un gestionnaire EXIT qui lit le diagnostic, l'écrit dans la table journal_erreur, puis relance l'erreur pour que l'appelant sache que l'opération a échoué.

DELIMITER //
CREATE PROCEDURE changer_priorite(IN p_id_ticket INT, IN p_priorite VARCHAR(10))
BEGIN
  DECLARE v_etat CHAR(5);
  DECLARE v_num  INT;
  DECLARE v_msg  VARCHAR(512);

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    GET DIAGNOSTICS CONDITION 1
      v_etat = RETURNED_SQLSTATE, v_num = MYSQL_ERRNO, v_msg = MESSAGE_TEXT;
    INSERT INTO journal_erreur (routine, etat_sql, num_erreur, message)
    VALUES ('changer_priorite', v_etat, v_num, v_msg);
    RESIGNAL;
  END;

  IF NOT EXISTS (SELECT 1 FROM ticket WHERE id_ticket = p_id_ticket) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Ticket inconnu';
  END IF;

  UPDATE ticket SET priorite = p_priorite WHERE id_ticket = p_id_ticket;
END //
DELIMITER ;

CALL changer_priorite(12, 'urgente');
-- ERROR 3819 (HY000): Check constraint 'ck_ticket_priorite' is violated.
CALL changer_priorite(99, 'haute');
-- ERROR 1644 (45000): Ticket inconnu

SELECT id_erreur, routine, etat_sql, num_erreur, message FROM journal_erreur;

Trois remarques :

• le SIGNAL de la procédure est rattrapé par son propre gestionnaire SQLEXCEPTION (la classe 45 est une erreur) : il est journalisé comme les autres ;
• RESIGNAL relance l'erreur d'origine, même après l'INSERT du gestionnaire : l'appelant reçoit bien l'erreur 3819 ou 1644 ;
• la ligne du journal reste, bien que l'opération ait échoué, parce qu'en mode autocommit chaque instruction est validée à part. Dans une procédure transactionnelle, l'ordre compte : ROLLBACK d'abord, journal ensuite, sinon le ROLLBACK effacerait la trace (chapitre 7).

Exercices

DELIMITER //
CREATE PROCEDURE test_gestionnaire()
BEGIN
  DECLARE v_etape VARCHAR(30) DEFAULT 'début';
  DECLARE CONTINUE HANDLER FOR 1062 SET v_etape = CONCAT(v_etape, ', doublon');

  SET v_etape = CONCAT(v_etape, ', avant');
  INSERT INTO installation VALUES (1, 3, CURDATE());   -- Firefox est déjà sur le poste 1
  SET v_etape = CONCAT(v_etape, ', après');
  SELECT v_etape;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE affecter_poste(IN p_id_poste INT, IN p_id_employe INT)
BEGIN
  DECLARE v_existe     INT;
  DECLARE v_actuel     INT;
  DECLARE v_message    VARCHAR(128);

  SELECT COUNT(*), MAX(id_employe) INTO v_existe, v_actuel
  FROM poste WHERE id_poste = p_id_poste;

  IF v_existe = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Poste inconnu';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM employe WHERE id_employe = p_id_employe) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Employé inconnu';
  END IF;
  IF v_actuel IS NOT NULL AND v_actuel <> p_id_employe THEN
    SET v_message = CONCAT('Poste déjà affecté à l''employé ', v_actuel);
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_message;
  END IF;

  UPDATE poste SET id_employe = p_id_employe WHERE id_poste = p_id_poste;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE ajouter_installation(IN p_id_poste INT, IN p_id_logiciel INT)
BEGIN
  DECLARE EXIT HANDLER FOR 1062
    RESIGNAL SET MESSAGE_TEXT = 'Ce logiciel est déjà installé sur ce poste';
  DECLARE EXIT HANDLER FOR 1452
    RESIGNAL SET MESSAGE_TEXT = 'Poste ou logiciel inconnu';

  INSERT INTO installation (id_poste, id_logiciel, date_installation)
  VALUES (p_id_poste, p_id_logiciel, CURDATE());
END //
DELIMITER ;