I had a quite painful experience yesterday, I would say…

We were about to run live database migrations, when I decided to try it first in a staging database server, to have a more precise idea of how long they would take. So I connected to the MySQL test system:

mysql> use myopera
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

mysql> ALTER TABLE blah ADD COLUMN xxx ...
...

The ALTER operation completed in a minute or so. Then I wanted to revert it, so I typed:

mysql> ROLLBACK;

So the transaction was rolled back. Just to double check, I verified that the table was indeed left in its original state, and to my surprise, it wasn't. Yes, the table is a InnoDB table, and yes, I had put the ALTER statement in a transaction.

Even after a ROLLBACK the table is left altered… Isn't that surprising? For me, it was. Of course, it is a documented MySQL behavior, and it's filed as bug/feature request.