I am working on PHP-script which imports CSV file (
customers.csv) into MySQL table (
Before inserting contents of CSV-file into the mysql table I am first backing up the original
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
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