ROLLBACK doesn't work after INSERT INTO newly created destination table

Question

I am working on PHP-script which imports CSV file (customers.csv) into MySQL table (customers).

Before inserting contents of CSV-file into the mysql table I am first backing up the original customers table.

I am wrapping whole import process (including backing up) in a mysql transaction (to account for cases when CSV is corrupt somewhere in the middle, and to ensure import is atomic).

The problem is that ROLLBACK doesn't seem to work when I am calling it right after INSERT INTO statement: when checking database via phpMyAdmin I can see the newly created table AND ROWS INSIDE IT still present after roollback.

Here's the log of the operations:

[2015-01-19 14:08:11] DEBUG: "START TRANSACTION" [] []
[2015-01-19 14:08:11] DEBUG: SHOW TABLES LIKE :table_name; [] []
[2015-01-19 14:08:28] DEBUG: CREATE TABLE `customers__20150119_14_08_20` LIKE `customers` [] []
[2015-01-19 14:08:37] DEBUG: INSERT INTO `customers__20150119_14_08_20` SELECT * FROM `customers` [] []
[2015-01-19 14:08:50] DEBUG: "ROLLBACK" [] []

So I wonder why depsite ROLLBACK is called, the transaction is not cancelled. I do understand that CREATE TABLE is not transactional in nature and can't be rolled back. But I was assuming that INSERT INTO because it deals with inserting rows (not defining schema), WILL actually be transactional, and after ROLLBACK I will be left with empty destination table. Why is it not the case?

And here's output SHOW CREATE TABLE customers (so my table is InnoDb):

CREATE TABLE `customers` (
 `Code` varchar(32) NOT NULL,
 `Name` varchar(128) DEFAULT NULL,
 `Price` varchar(128) DEFAULT NULL,
 PRIMARY KEY (`Code`),
 KEY `Price` (`Price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

and here's output for the desination table:

CREATE TABLE `customers__20150119_14_08_20` (
 `Code` varchar(32) NOT NULL,
 `Name` varchar(128) DEFAULT NULL,
 `Price` varchar(128) DEFAULT NULL,
 PRIMARY KEY (`Code`),
 KEY `Price` (`Price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Answer 1

The reason is that that some statements, like CREATE TABLE cause an implicit commit. You can read about them in the documentation: Statements That Cause an Implicit Commit.

So the original sequence of statements:

START TRANSACTION
SHOW TABLES LIKE customers
CREATE TABLE `customers__20150119_14_08_20` LIKE `customers`
INSERT INTO `customers__20150119_14_08_20` SELECT * FROM `customers`
ROLLBACK

will expand into:

START TRANSACTION ;   -- transaction context created
SHOW TABLES LIKE customers ;

COMMIT ;              -- CREATE TABLE forces commit before itself
                      --     (at this point the previous transaction is done.)
START TRANSACTION ;   -- and a new transaction  
CREATE TABLE `customers__20150119_14_08_20` 
    LIKE `customers` ;
COMMIT ;              -- CREATE TABLE forces commit after itself. 
                      -- At this point there's no transaction context

START TRANSACTION ;   --  starts a new transaction
INSERT INTO `customers__20150119_14_08_20` 
    SELECT * FROM `customers` ;
COMMIT ;              -- caused by "autocommit on" setting (guess). 

ROLLBACK ;            -- this rollback HAS NOTHING to undo

The solution would be to start the transaction (or a new one) after the CREATE TABLE statement or use a temporary table.

Answer 2

It looks like the order of the statements is causing the problem.

In my old post row locking within ACID transaction innodb, I named 12 statements that break a transaction intermittently. In your particular case, it was the CREATE TABLE statement.

Once you ran CREATE TABLE inside a START TRANSACTION ... COMMIT/ROLLBACK block, there was no framework to rollback.

Just run the CREATE TABLE before START TRANSACTION and you should be fine.

Give it a Try !!!