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`)

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`)
  • Is the behaviour the same if you reorder to first create table, then start transaction, insert, rollback? Commented Jan 19, 2015 at 14:40
  • I was just about to say that to !!! Commented Jan 19, 2015 at 14:41
  • Do you disable autocommit on the connection in your program?
    – mustaccio
    Commented Jan 19, 2015 at 14:50

2 Answers 2


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:

CREATE TABLE `customers__20150119_14_08_20` LIKE `customers`
INSERT INTO `customers__20150119_14_08_20` SELECT * FROM `customers`

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.

  • @Dimitry, thnx for the edit. Commented Jan 19, 2015 at 15:52
  • 1
    And @RolandoMySQLDBA for your kind words. I am the FGITW today (and only 15 seconds faster than you ;) Commented Jan 19, 2015 at 16:03
  • @ypercube welcome! It took me a while to figure where exactly this CREAT TABLE will cause an implicit commit... So had to do this outline on paper anyways :) @RolandoMySQLDBA thank for rapid input as well. I have read few dozens of your replies in the last year and they helped me a lot!!
    – Dimitry K
    Commented Jan 19, 2015 at 16:23
  • So you're saying that the implicit commit before the INSERT, caused by the DDL statement, also somehow causes a commit after the insert?
    – mustaccio
    Commented Jan 19, 2015 at 17:13
  • 1
    Yes, there are two parts on the reasoning but the main part in my opinion, that the OP could not figure out was the implicit commit by the create table. Commented Jan 19, 2015 at 17:23

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 !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.