MySQL temporary tables inside Transaction <br> and the magic of implicit commit

I've run into interesting and very strange problem. I was writing transactional Spring test that opens transaction at the beginning of it, and rollbacks at the end. First part of my test performed bunch of INSERT and UPDATE SQL commands and after that I was checking persisted changes by loading data back from the database. Suddenly my tests started to fail. And I was searching for the reason ...

The reason was that the data created by the first part of test was not cleared by rollback at the end of the test. I examined the log and verified, that Spring really opened and rollbacked the transaction during the test. So I started to suspect myself, that I setup Spring wrong test and that they are using different DataSource than application logic. But after several minutes I realized that suspicion probably wasn't right (if you look at the Spring transaction logic, you will understand why I couldn't say it for sure).

I started to be quite annoyed, so I continued with removing piece after piece of the test code and realized, that when I remove verification part (that loaded data back from the database), the transaction was successfuly rollbacked and stored data vanished after test. So where was the problem?

Loading logic took advantage of another part of library I was programming and this logic involved creating and dropping temporary table while fetching data. And this caused rollback to be ignored by MySQL server, moreover instead of rollback data was commited. I found one bug in MySQL issue tracker, that describes this problem more in detail. Following statement was the key:

"According to http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html: CREATE TABLE and DROP TABLE do not commit a transaction if the TEMPORARY keyword is used. (This does not apply to other operations on temporary tables such as CREATE INDEX, which do cause a commit.) However, although no implicit commit occurs, neither can the statement be rolled back. Therefore, use of such statements will violate transaction atomicity: For example, if you use CREATE TEMPORARY TABLE and then roll back the transaction, the table remains in existence."

Though the statement says, that creating temporary table doesn't caus an implicit commit - test behaved like that. Data stayed at the database after rollback. One way or another, it was something I didn't want to happen and what I hadn't idea of. Another day, another surprise. I had to rewrite loading logic to get along without temporary table and tests started to work again.

I wish you'll find it more quickly than I did.