CODEWITHSUNDEEP
mysqlinnodb
Simon A. Eugster
Simon A. Eugster

Reputation: 4264

Rollback in the MySQL shell not working after ALTER TABLE

(MySQL 5.5, InnoDB tables) Why can't I rollback, either to a savepoint or to the previous begin statement?

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> savepoint id;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table sg_Section add column (published tinyint(1) default 0);
Query OK, 2 rows affected (0.30 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> rollback to savepoint id;
ERROR 1305 (42000): SAVEPOINT id does not exist

When not running any command after the savepoint, it seems to «work»:

mysql> savepoint id;
Query OK, 0 rows affected (0.00 sec)

mysql> rollback to savepoint id;
Query OK, 0 rows affected (0.00 sec)

Upvotes: 5

Views: 1479

Answers (1)

user330315
user330315

Reputation:

DDL is not transactional in MySQL.

Any DDL statement implicitly commits the open transaction.

More details in the manual: http://dev.mysql.com/doc/refman/5.5/en/implicit-commit.html

Upvotes: 10

Related Questions