11

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

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.

  • @Dimitry, thnx for the edit. – ypercubeᵀᴹ Jan 19 '15 at 15:52
  • 1
    And @RolandoMySQLDBA for your kind words. I am the FGITW today (and only 15 seconds faster than you ;) – ypercubeᵀᴹ Jan 19 '15 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 Jan 19 '15 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 Jan 19 '15 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. – ypercubeᵀᴹ Jan 19 '15 at 17:23
3

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, privacy policy and cookie policy

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