Before filing a bug report, I was hoping someone could point out if I am misunderstanding something here or if this is a legitimate problem. The following is documentation on MySQL's implicit-commit (I am running 5.0.45-community on RHEL 5).
http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html
I was originally under the impression that with InnoDB, any number of queries which changed data and were included in a single transaction would all be rolled back properly, but as it turns out, this is not the case, as discussed in this bug:
http://bugs.mysql.com/bug.php?id=28727
I would tend to agree with Laas that this is a pretty big issue ACID-wise, but my issue is with temporary tables.
According to this documentation, "CREATE TABLE and DROP TABLE do not commit a transaction if the TEMPORARY keyword is used." I don't believe this is the case, and here is the test that confuses me:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> create temporary table t1(test varchar(2));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1(test) values('te');
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select * from t1;
Empty set (0.00 sec)
mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------+
| t1 | CREATE TEMPORARY TABLE `t1` (
`test` varchar(2) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
The insert is rolled back, but the table creation is not. If this were a normal table, I would understand (though be annoyed), but MySQL's documentation seems to indicate that this should not occur with temporary tables. So right now I am thinking that this is either a bug or a mistake in the documentation.
Please let me know if any of you have any ideas. Thanks.