3

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

4

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

  • This leaves the database in a state where mytable does not exist. Use a RENAME table statement instead, which allows for two atomic renames – Shlomi Noach Nov 5 '14 at 18:12
  • @ShlomiNoach Thank you for the quick peer review :-) – RolandoMySQLDBA Nov 5 '14 at 18:17
3
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.)

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.