Search code examples
mysqlstored-procedurestransactions

Execute script with transaction

I have a stored procedure to take a input script and upgrada tables. It also write into a table with update history. I wrapped them up with transaction.

START TRANSACTION;
PREPARE action from @upgradeScript;
EXECUTE action;
INSERT INTO database_history (version, changes)
VALUES (Version, comments);
COMMIT;

I set @upgradeScript to:

"ALTER TABLE t ADD column test1 varchar(45) NOT NULL;"

However, when the inset fails, it doesn't rollback changes in @upgradeScript.

I am wondering why and can anybody help.

Solution

  • That is because 'ALTER TABLE' is a DDL statement which cannot be rolled back and implicitly ends a transaction.

    More information -