Discussion :

[Invité]

Bonjour à tous.

Dans ma quête pour comprendre le SQL et l'utiliser correctement je viens de tomber sur un os.

Mettons que je doive écrire un long script qui fait des modifications dans mes tables (pas un simple SELECT donc). Le problème c'est qu'on est jamais sûr que notre script est exempt d'erreurs (erreur de frappe, mauvaise orthographe d'un champ, etc.). Or si une exception est levée en plein milieu du script celui-ci aura déjà été partiellement exécuté.
On va alors se retrouver avec une base complètement inconsistante et il faudra déterminer quelle requête est fautive pour réécrire un nouveau script ad hoc pour exécuter les requêtes qui ne l'ont pas été. Lequel nouveau script contiendra sans doute des erreurs de frappe, ce qui oblige à recommencer cette opération fastidieuse à chaque problème.

Existe-t-il donc un moyen de retrouver l'état initial de la base au cas où une exception survient au cours du script afin de pouvoir corriger simplement le script et l'exécuter à nouveau, sans avoir à en écrire un autre ?

[SQLpro]

Existe-t-il donc un moyen de retrouver l'état initial de la base au cas où une exception survient au cours du script afin de pouvoir corriger simplement le script et l'exécuter à nouveau, sans avoir à en écrire un autre ?

Oui, cela s'appelle une transaction. Lisez ce sue j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/sqlaz/techniques/#L1

A +

[Invité]

Merci pour la réponse.

J'ai orienté mes recherches sur les transactions, mais ça n'a pas l'air de marcher...

Note : J'utilise MySQL version 5.0 avec le moteur InnoDB (qui supporte donc les transactions).

Quand j'exécute un script contenant une erreur (en utilisant la console), j'observe que seule la requête fautive est annulée et l'exécution du script continue jusqu'à la fin.
L'occurrence d'une erreur ne provoque donc pas la fin de la transaction et le rollback que j'attendais.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
START TRANSACTION;
 
REQUETE 1;
 
REQUETE 2;
 
...
 
COMMIT;

J'ai aussi vu à plusieurs endroit des personnes suggérer d'utiliser une variable de MySQL : autocommit. Mais même en faisant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SET autocommit = 0;

en début de code ça ne change rien.
D'ailleurs je ne comprend pas bien l'intérêt de faire ça vu qu'une transaction est justement censée empêcher le commit à la fin de chaque requête et ne commiter qu'à la fin... Me trompe-je ?

Si la solution nécessite d'utiliser des techniques propres à MySQL et plus les fonctionnalités SQL pur je veux bien déplacer mon post, mais ça me semble bizarre que la récupération des erreurs ne soit pas prévue dans la spécification de base du SQL.

[MarcS]

Bonjour,
Cela peut paraitre idiot comme question, mais vos tables concernées sont bien de type Innodb et non myisam ?
Cordialement
MS

[Invité]

Oui ce sont bien des InnoDB (je l'avais précisé au milieu de mon deuxième post )

[MarcS]

Bonjour,
Cela semble étonnant.
Pourriez vous donner vos dessins de table et les requêtes utilisées.
Passez de bonnes fêtes.
Cordialement
MS

[Invité]

Juste pour être sûr.
Le comportement normal d'une transaction c'est de s'arrêter et de faire un rollback quand une des requêtes lève une exception ? J'ai regardé rapidement la doc MySQL et ils ne disent pas quel comportement est censé suivre une transaction dans ce cas.

Sinon je veux bien donner la structure de ma base, mais il y a une vingtaine de tables. De même le script est très long.

En plus c'était surtout une question théorique à la base. Je voulais juste savoir comment annuler une suite d'opération quand une exception SQL est levée.

[MarcS]

Le comportement est de ne pas valider la transaction tant qu'un commit ou un end transaction n'est pas effectué.
C'est bien ce qui semble bizarre, si l'on ne fait pas au minimum un commit les transactions ne sont pas validées sauf pour les tables myisam
Etes vous sûr que dans les requêtes aucuns commit n'est émis ?
Cordialement
MS

[Invité]

Justement si. Il y a un commit à la fin de mon script (au cas où tout se passe bien ). Le problème viendrait donc du fait que lorsque MySQL arrive sur la requête contenant une faute il se contente de l'annuler, mais continue à exécuter les requêtes suivantes (dont le COMMIT final).

[MarcS]

Pour s'assurer qu'il passe bien par le commit, supprimez le et regardez si le rollback a eu lieu.
Pour régler le problème, une solution serait de positionner une variable à faux lors de la première requête et à vrai lors de la fin de la dernière, ensuite de ne faire le commit que si on est bien passé par la dernière requête, sinon un rollback
Cordialement
MS

[Invité]

J'ai essayé d'exécuter un script sans erreurs, mais sans COMMIT :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
START TRANSACTION;
 
INSERT INTO contact (ContactId, ContactNom) VALUES (1, "Contact1");
 
INSERT INTO contact (ContactId, ContactNom) VALUES (2, "Contact2");
 
INSERT INTO contact (ContactId, ContactNom) VALUES (3, "Contact3");
 
-- COMMIT;

Ce script génère un comportement très bizarre. Apparemment la connexion avec la base de données n'est pas coupée et la transaction continue à l'infini.

Par contre dès que je lance un requête (toujours à partir de la console) les lignes sont insérées. A mon avis le fait de lancer une nouvelle requête crée une nouvelle transaction, ce qui a pour effet de terminer la précédente et de commiter les changements.

[Invité]

Non j'ai tout faux.

Vu que j'ai pas validé la transaction avec le commit je reste dans la même transaction quand je lance une autre requête dans la même console.

Jusqu'à ce que je fasse un commit ou un rollback manuellement.

Par contre si je fais un START TRANSACTION juste après, cela provoque bien un commit de la transaction (et la ferme aussi probablement pour ouvrir la nouvelle).


Finalement je pense que ma première intuition était la bonne. Quand j'exécute ce script qui contient une erreur dans le deuxième INSERT :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
START TRANSACTION;
 
INSERT INTO contact (ContactId, ContactNom) VALUES (1, "Contact1");
 
INSERT INTO cotact (ContactId, ContactNom) VALUES (2, "Contact2");
 
INSERT INTO contact (ContactId, ContactNom) VALUES (3, "Contact3");
 
COMMIT;

MySQL se contente d'annuler la requête fautive, mais continue le script jusqu'au commit. Dans notre cas on se retrouve donc avec Contact1 et Contact3 insérés correctement, mais pas Contact2.

Ma question de départ reste donc entière : Comment faire pour obliger MySQL à faire un rollback de la transaction lorsqu'il lève une exception sur le deuxième INSERT.

[MarcS]

Est ce que plusieurs requêtes peuvent être appelées simultanément ?
Cad est ce que plusieurs utilisateurs peuvent travailler en même temps sur la table ?
Si ce n'est pas le cas le script suivant devrait permettre de s'en sortir

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
set @combien=3;
START TRANSACTION;
select count(*) from contact into @compte;
 
INSERT INTO contact (ContactId, ContactNom) VALUES (1, "Contact1");
 
 
INSERT INTO contact (CntactId, ContactNom) VALUES (2, "Contact2");
 
INSERT INTO contact (ContactId, ContactNom) VALUES (3, "Contact3");
 
select count(*) from contact into @compteFinal;
select if(@compte+@combien<=@compteFinal,'commit','rollback') into @query;
prepare ex from @query;
execute ex;
DEALLOCATE PREPARE Ex;

Dans le cas contraire, cela peut quand même fonctionner en verrouillant la table le temps de la transaction

[MarcS]

JE viens de trouver cette info :http://dev.mysql.com/doc/refman/5.0/...e-handler.html
Je n'ai pas creusé mais cela répond peut être à la question

[Invité]

J'avais effectivement vu les handlers et j'avais essayé de déclarer un handler de ce style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
DECLARE EXIT HANDLER FOR SQLEXCEPTION
COMMIT;

Mais j'ai l'impression que les handlers doivent être déclarés pour des procédures stockées (même si je ne sais pas ce qu'est une procédure stockée, j'ai juste vu passer le terme par-ci par-là) et pas pour une transaction. En tous cas le code ci-dessus, placé avant ma transaction ou au début de celle-ci m'avait renvoyé une erreur.

[Antoun]

Le comportement des transactions est le suivant :

• Si aucune transaction n'est en cours dans la session, une requête DML en ouvre une nouvelle
• Les requêtes DML suivantes de la même session restent dans la même transaction, jusqu'à ce que celle-ci soit fermée
• Une erreur sur une requête ne provoque pas de ROLLBACK automatique
• Tant que la transaction n'est pas fermée, les modifications apportée par les requêtes sont visibles pour la session en cours, mais pas pour les autres (sauf si la variable @@tx_isolation est READ-UNCOMMITTED)
• [EDIT] un ordre START TRANSACTION

Une transaction est fermée par l'un des événements suivants :

• un ordre COMMIT ou ROLLBACK
• la fin de la session (équivaut à un ROLLBACK)
• un autre DDL comme CREATE, ALTER, DROP ou TRUNCATE, qui équivaut à un COMMIT implicite
• le @@autocommit est à 1 ou change de valeur (COMMIT implicite à nouveau)

[Antoun]

J'avais effectivement vu les handlers et j'avais essayé de déclarer un handler de ce style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
DECLARE EXIT HANDLER FOR SQLEXCEPTION
COMMIT;

Mais j'ai l'impression que les handlers doivent être déclarés pour des procédures stockées (même si je ne sais pas ce qu'est une procédure stockée, j'ai juste vu passer le terme par-ci par-là) et pas pour une transaction. En tous cas le code ci-dessus, placé avant ma transaction ou au début de celle-ci m'avait renvoyé une erreur.

Dans ton cas, il me semble effectivement judicieux de mettre l'ensemble de tes requêtes dans une procédure stockée, ce qui te permettra de mettre des handlers gérant les COMMIT/ROLLBACK.

En très gros, ça ressemble à ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE PROCEDURE Toto
AS
BEGIN
DECLARE HANDLER...
INSERT...
INSERT...
END

Et ça se déclenche ensuite comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CALL Toto ;

[Invité]

Merci beaucoup pour ces réponses. Du coup le comportement des transactions est plus clair pour moi. Et j'ai compris comment récupérer les exceptions SQL.

Hop ! Résolu. Merci encore à SQLPro, MarcS et Antoun.