Error handling nelle stored procedures in MySQL

Le stored procedures mettono a disposizione alcune modalità per la gestione delle eccezioni che ci permettono di tenere traccia e ricevere notifiche sui malfunzionamenti intercorsi durante l’utilizzo di MySQL e l’invio di istruzioni ed interrogazioni al DBMS.

Nelle pagine seguenti vedremo come sia possibile sfruttare l’error handling di MySQL per ricavare informazioni preziose sulle cause che portano alla generazioni di errori durante l’esecuzione di comandi e istruzioni contenuti in funzioni e procedure.

Utilizzare i log per gestire gli errori

Per questa particolare modalità di error handling prenderemo in esame un particolare tipo di eccezione, cioè un errore durante un tentativo di inserimento:

mysql> CREATE TABLE tbl1 
Id INT, PRIMARY KEY (Id)) 
engine=innodb;//
mysql> CREATE TABLE tbl2 (Id INT, KEY (Id), 
FOREIGN KEY (Id) REFERENCES tbl1 (Id)) 
engine=innodb;// 
mysql> INSERT INTO tbl2 VALUES (9);//

L’esito del nostro comando INSERT sarà un errore. Abbiamo infatti creato una tabella destinata ad ospitare una chiave primaria e un’altra contenente una FOREIGN KEY entrambe sotto engine InnoDB; nel momento in cui provassimo ad inserire un valore per popolare la FOREIGN KEY questa istruzione provocherebbe un errore:

ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails

La notifica contiene il numero di errore, 1216, che ne specifica la tipologia; abbiamo quindi un’informazione utile ad individuare una particolare eccezione e a classificarla. Possiamo quindi basarci sugli output prodotti dal DBMS per creare una tabella/log in cui registrare le differenti tipologie di errore:

mysql> CREATE TABLE errori (messaggio VARCHAR(255))//

A questo punto avremo bisogno di realizzare una stored procedure in grado di popolare la nostra tabella per il log:

CREATE PROCEDURE proc (p1 INT) 
BEGIN 
 DECLARE EXIT HANDLER FOR 1216 
 INSERT INTO errori VALUES 
  (CONCAT('Time: ',current_date, 
  '. Foreign Key Reference Failure For 
  Value = ',p1)); 
 INSERT INTO tbl2 VALUES (p1); 
END;//

Con questo semplice codice abbiamo innanzitutto dato un nome alla nostra procedura (“proc”) a cui viene passato come parametro un dato di tipo intero; in secondo luogo abbiamo inizializzato il blocco BEGIN .. END all’interno del quale sono presenti le istruzioni previste nel caso in cui la stored procedure venga richiamata.

Abbiamo poi introdotto il costrutto DECLARE EXIT HANDLER FOR 1216 che in pratica si occupa della gestione del tipo di errore 1216; nello specifico avremo quindi che quando il parametro viene utilizzato come valore per effettuare un inserimento nella tabella contenente la FOREIGN KEY, l’errore generato verrà archiviato tramite INSERT nella tabella “errori” popolando il log.

La stringa registrata nel campo “messaggio” conterrà sia il valore relativo al parametro utilizzato che la data in cui è stato prodotto l’errore.

La stored procedure dovrà agire in un caso specifico, cioè il tentativo d’inserimento di un valore intero nella FOREIGN KEY della seconda tabella InnoDB; per cui la relativa istruzione rappresenta l’ultimo argomento all’interno del nostro blocco ed è seguita dal delimitatore di chiusura della stored procedure.

Proviamo ora ad effettuare una semplice chiamata:

CALL proc (9) //

Anche in questo caso il tentativo di inserimento a carico della tabella “tbl2” non sarà possibile, ma questa volta l’istruzione non porterà alla notifica di un errore, quest’ultimo sarà infatti precedentemente gestito all’interno della stored procedure.

Il risultato finale sarà quindi che la tabella “tbl2” rimarrà invariata (l’inserimento si è infatti concluso con un fallimento), mentre la tabella “errori” presenterà un nuovo record in cui è stata registrata l’eccezione gestita.

In linea generale possiamo proporre una struttura per la gestione degli errori nelle stored procedures basandoci sul modello sintattico seguente:

DECLARE 
{ EXIT | CONTINUE } 
HANDLER FOR 
{ numero-errore | { SQLSTATE } | condizione } 
Comando SQL

La gestione delle eccezioni prevede quindi due possibili clausole:

• EXIT blocca l’esecuzione delle istruzioni nel momento in cui viene rilevato un errore;
• CONTINUE permette di concludere l’esecuzione delle istruzioni anche se si verifica un errore.

Nel costrutto sintattico appena proposto vediamo che oltre al numero identificativo dell’eccezione (nel caso precedente era 1216) è possibile gestire l’errore anche sulla base dell’SQLSTATE (23000 nell’esempio precedente), non vi sono particolari differenze a fini pratici, è però bene tenere conto che un SQLSTATE è sempre più generico di un SQL code error.

Error handling con CONTINUE

Analizziamo ora un caso di eccezione che presenta non poche differenze rispetto a quello proposto in precedenza:

CREATE TABLE tbl (Id int,primary key(Id));// 
CREATE PROCEDURE proc () 
 BEGIN 
  DECLARE CONTINUE HANDLER 
  FOR SQLSTATE '23000' SET @x2 = 1; 
  SET @x = 1; 
  INSERT INTO tbl VALUES (1); 
  SET @x = 2; 
  INSERT INTO tbl VALUES (1); 
  SET @x = 3; 
END;//

Smontando la nostra nuova stored procedure possiamo dire descrivere il suo funzionamento in questo modo:

1.  non è stato necessario passare parametri alla stored procedure, i valori che determinano gli esiti delle istruzioni sono indicati all’interno del blocco BEGIN .. END;
2. in questo caso l’handling dell’eccezione si basa su un SQLSTATE e non su un codice identificativo di errore del DBMS;
3. la prima istruzione è volta a impostare il parametro @x con un valore pari ad 1, il valore 1 va poi a popolare l’unico campo presente nella tabella;
4. dopo il primo INSERT il valore della variabile è diventato 2 e l’istruzione seguente tenta di inserire nuovamente il valore 1 all’interno della tabella;
5. Id è però una chiave primaria e non accetta un valore identico a quello inserito in precedenza, siamo quindi di fronte alla prima eccezione;
6. al verificarsi dell’eccezione viene eseguita l’istruzione (SET @x2 = 1) prevista per un SQLSTATE 23000 che sopraggiunge sia in presenza di una violazione delle FOREIGN KEY che per una violazione delle PRIMARY KEY;
7. l’handling dell’eccezione non prevede un EXIT per la sua gestione ma un CONTINUE, quindi si passerà all’esecuzione dell’ultima istruzione (SET @x = 3) nonostante il precedente errore;

Ora procediamo con una chiamata alla nostra stored procedure:

mysql> CALL proc()//

ed estraiamo i valori da prodotti in seguito alla sua esecuzione:

mysql> SELECT @x, @x2// 

Il risultato sarà quello atteso, cioè un valore pari a 3 per @x e 1 per @x2. In questo secondo caso quindi l’errore è stato gestito efficacemente impedendo la notifica dell’eccezione, ma il suo verificarsi non ha impedito l’esecuzione delle istruzioni possibili con esito corretto.

Gestione degli errori e dichiarazione delle condizioni

Uno dei vantaggi nell’utilizzare codici di errore e SQLSTATE nella gestione degli errori sta nel fatto che questi possono essere identificati anche tramite denominazioni scelte arbitrariamente dall’utilizzatore.

Vediamo di seguito un semplice esempio che si basa sulla tabella tbl1 creata nel primo paragrafo di questa trattazione:

CREATE PROCEDURE proc () 
BEGIN 
  DECLARE `Chiave Duplicata` 
  CONDITION FOR SQLSTATE '23000'; 
  DECLARE EXIT HANDLER FOR 
  ` Chiave Duplicata` ROLLBACK; 
   START TRANSACTION; 
    INSERT INTO tbl1 VALUES (1); 
    INSERT INTO tbl1 VALUES (1); 
  COMMIT; 
END; //

L’errore che causerebbe l’esecuzione del codice appena proposto risulta evidente: abbiamo due istruzioni INSERT attraverso le quali si cerca di inserire lo stesso valore all’interno di un campo PRIMARY KEY che per sua natura non accetta duplicati.

Il tentativo di introdurre un valore duplicato genera una particolare eccezione detta Constraint Violation il cui esito è quello di produrre un SQLSTATE 23000 che nel caso della nostra stored procedure abbiamo chiamato “Chiave duplicata”.

ROLLBACK e COMMIT sono due comandi utilizzati per far si che una determinata istruzione venga effettuata in un’unica operazione o che non venga effettuata affatto; nel caso in cui una serie di istruzioni debba essere eseguita per intero (o ignorata in caso di eccezione), la stored procedure che la contiene ne indicherà l’inizio tramite il costrutto START TRANSACTION in attesa di un COMMIT che ne lanci l’esecuzione.

Se per un qualunque motivo dovesse verificarsi un errore, il comando ROLLBACK riporterà il tutto alla situazione iniziale ripristinando la situazione precedente.

Per cui, una chiamata alla stored procedure precedentemente definita produrrà un output di zero records, infatti l’impossibilità del secondo INSERT determinerà l’annullamento del primo per quanto sintatticamente corretto e accettabile per il campo di destinazione.

Conclusioni

Nel corso di questo capitolo abbiamo descritto gli aspetti relativi all’utilizzo delle stored procedures di MySQL nella gestione degli errori; questa particolare modalità di Error Handling presenta non pochi vantaggi in quanto:

• permette di creare facilmente tabelle per l’archiviazione di informazioni su eventuali errori di esecuzione;
• consente di gestire istruzioni alternative a quelle in grado di generare errori;
• fornisce uno strumento per vincolare determinati eventi e comportamenti al verificarsi (o meno) di condizioni definite dall’utilizzatore.