Transactions at the MySQL console

Question

MySQL comes with a nice SQL prompt.

One of it's commands is source which reads in commands from a text file and executes them as if they were typed at the prompt.

Imagine such a text file with the following lines:

BEGIN TRANSACTION;
UPDATE ....;
UPDATE ....;
DELETE FROM ...;
COMMIT;

If there is a syntax or other error on any of those SQL statements in the middle, they still continue to execute the rest, including the COMMIT! Ouch!

How can you make it so that if any of the commands fail, the transaction and the script are aborted?

Answer 1

You'll need to declare an exception handler:

DECLARE EXIT HANDLER
    FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
        ROLLBACK;