Truncating and inserting in a safe transaction on MySQL

Question

I am trying to TRUNCATE and INSERT data into a MySQL InnoDB table.

I wanted to make sure that if any statement in the transaction failed, the data will be untouched.

I can't have an empty or corrupted table if something fails.

This is how I planned to do it.

1. BEGIN TRANSACTION
2. TRUNCATE TABLE table_name
3. INSERT INTO table_name
4. COMMIT

I then learned that TRUNCATE TABLE table_name causes an implicit COMMIT, which makes the whole transcation pointless.

I then replaced the TRUNCATE TABLE table_name with a DELETE FROM table_name approach, which wasn't ideal - but that too causes an implicit COMMIT!

DROP TABLE table_name and CREATE TABLE table_name also causes an implicit COMMIT!

What method / logic is best to perform what I am trying to do?

Using MySQL 5.6.17 64-bit server running on Windows Server 2008 R2

Answer 1

Since TRUNCATE TABLE does an implicit commit, don't use it.

As long as the table does not have foreign keys, you can do it quickly like this:

CREATE TABLE mytable_new LIKE mytable; 
RENAME TABLE mytable TO mytable_old,mytable_new TO mytable;
INSERT INTO mytable ... ;

If the INSERT succeeds, then run this

DROP TABLE mytable_old;

If the INSERT rolls back, just revert back

RENAME TABLE mytable TO mytable_zap,mytable_old TO mytable;
DROP TABLE mytable_zap;

Give it a Try !!!

Answer 2

DELETE FROM t

Does not cause an implicit commit and should work well. I'm actually using this exact solution in production and see no issue transaction-wise (of course deleting all rows from a large table has a significant impact on the size of the transaction, followed possibly by increased IO, increased size of binary logs etc.)