CODEWITHSUNDEEP
mysqlstored-procedurestransactions
Frank
Frank

Reputation: 7595

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.

Upvotes: 1

Views: 4204

Answers (1)

Devart
Devart

Reputation: 122042

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

More information -

Upvotes: 4

Related Questions