MySQL Bugs: #28727: ALTER TABLE does not roll back

Bug #28727	ALTER TABLE does not roll back
Submitted:	28 May 2007 15:39	Modified:	13 May 2010 16:04
Reporter:	Laas Toom	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.0.26-12	OS:	Linux (openSUSE 10.2 linux-2.6.18.8-0.3)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
If ALTER TABLE statement is surrounded by BEGIN..ROLLBACK transaction, the ALTER TABLE statement is not rolled back and instead is committed to the database.

This can cause data loss.

How to repeat:
mysql> SHOW CREATE TABLE test1;
+-------+-------------------------+
| Table | Create Table            |
+-------+-------------------------+
| test1 | CREATE TABLE `test1` (
  `id` int(11) NOT NULL auto_increment,
  `col1` varchar(10) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------+
1 row in set (0.00 sec)

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

mysql> ALTER TABLE test1 DROP col1;
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

mysql> SHOW CREATE TABLE test1;
+-------+-----------------------+
| Table | Create Table          |
+-------+-----------------------+
| test1 | CREATE TABLE `test1` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------+
1 row in set (0.00 sec)

Suggested fix:
After ROLLBACK the table definition should be the same as before BEGIN.

Thank you for the bug report. That is a documented behavior that the
alter table statement causes an implicit commit.

http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html

Though documented, I regard this as a major bug, because DATA LOSS can occur.

From InnoDB documentation:
"InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine that has commit, rollback, and crash recovery capabilities"

This bug violates ACID in following ways:
C - transactions ARE NOT Consistent.
Consider splitting a table into two, with adding FK constraint, where column test2 value is moved to table test2 and referenced by test2_id, but not all test2 values have been moved to table test2 beforehand:

BEGIN;
ALTER TABLE test1 ADD test2_id int(11) not null;
UPDATE test1 SET test2_id=2 WHERE test2='value2'; # non-existant id in test2
ALTER TABLE test1 DROP test2;
ALTER TABLE test1 ADD CONSTRAINT FOREIGN KEY test1 (test2_id) REFERENCES test2 (id);
ROLLBACK;

This throws error that FK constraint fails (which it does) and thus any software rolls back the transaction, but MySQL leaves the table in inconsistent state, where data is lost (the 'value2' is not saved in table test2, so it can not be recovered afterwards).

I - Transactions are not Isolated: all results from statements that cause implicit commit, are instantly visible to other database users, even before COMMIT/ROLLBACK.

Both of these are annoying and can cause dataloss - software that is backend independent (all sorts of DBI's etc) and relays on transactions, expects all commands to be rolled back on ROLLBACK and can not handle situations like these.

Best regards,
Laas Toom

Laas,

if a foreign key constraint fails in an ALTER TABLE, then MySQL does not perform the ALTER. The table is left as it was.

--Heikki

Dear Heikki,

I know that if the FK check fails, the corresponding ALTER TABLE is not committed. But the preceding ALTER TABLE-s were committed and not rolled back, though they are in the same transaction. This is not ACID compliant.

Further more - even the UPDATE statements were committed in that transaction.

If one statement in a transaction fails and can not be executed, entire transaction must be rolled back, not just the one statement.

If you do not plan to implement the ACID compliant behavior, you should explicitly say so on the InnoDB overview page at:
http://dev.mysql.com/doc/refman/5.0/en/innodb-overview.html

Not as it sates now: transaction-safe (ACID compliant).

Best regards,
Laas Toom

Laas,

ALTER TABLE in MySQL is not transactional: it commits the current transaction. Thus, the two ALTER TABLEs are not in the same transaction.

Some databases do have a transactional ALTER TABLE: PostgreSQL is one.

Regards,

Heikki

Heikki,

This is what I am talking about: if ALTER TABLE is not transactional, you should say it loud on your InnoDB overview, where it states that it is fully transaction-safe, and link to the 13.4.3 chapter.
If you be so kind and update your documentation.

But even after considering ALTER TABLE as implicitly pre-committing a transaction, it should not do so, when the ALTER TABLE fails, as it does in the following SQL session. IMHO MySQL should check constraints first and only then pre-commit the transaction and execute ALTER TABLE.

mysql> CREATE TABLE acid_test(id INTEGER AUTO_INCREMENT PRIMARY KEY, col1 VARCHAR(10)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO acid_test(col1) VALUES("before");
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO acid_test(col1) VALUES("after");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM acid_test;
+----+--------+
| id | col1   |
+----+--------+
|  1 | before |
|  2 | after  |
+----+--------+
2 rows in set (0.00 sec)

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

mysql> INSERT INTO acid_test(col1) VALUES("after");
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE acid_test ADD CONSTRAINT UNIQUE KEY (col1);
ERROR 1062 (23000): Duplicate entry 'after' for key 2
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM acid_test;
+----+--------+
| id | col1   |
+----+--------+
|  1 | before |
|  2 | after  |
|  4 | after  |
+----+--------+
3 rows in set (0.00 sec)

Best regards,
Laas Toom

Laas,

many ACID database brands have a non-transactional ALTER TABLE.

I am marking this as a feature request.

--Heikki

Bug: http://bugs.mysql.com/bug.php?id=31345 was marked as duplicate of this
one.

Let me have a suggestion : if ALTER TABLE is not transactional, shoudln't it be denied when in a transaction (which would cause developers to correct the code, rather than getting, one day, in this trap) ?