April 13, 2006

MySQL Gotchas | TRUNCATE: not transaction safe

Beginning with version 5.0.3, MySQL now includes a "real" TRUNCATE command for InnoDB tables, similar to that provided by Oracle and PostgreSQL.

George-Cristian Bîrzan (gcbirzan {at} constanta dot rdsnet dot ro) wrote to point out that it is affected by the following interesting gotcha:

mysql> WARNINGS; Show warnings enabled. mysql> CREATE TABLE trunc_test (id INT) ENGINE=InnoDB; Query OK, 0 rows affected (0.44 sec) mysql> INSERT INTO trunc_test VALUES(1); Query OK, 1 row affected (0.05 sec) mysql> BEGIN; Query OK, 0 rows affected (0.01 sec) mysql> TRUNCATE trunc_test; Query OK, 1 row affected (0.09 sec) mysql> SELECT * FROM trunc_test; Empty set (0.00 sec) mysql> ROLLBACK; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM trunc_test; Empty set (0.00 sec)

(tested in MySQL 5.0.19)

Evidently TRUNCATE is not transaction safe. It appears to behave in the same way as other DDL operations (see here for another example) and no warnings are provided to indicated that the table data has just disappeared permenantly and irrevocably.

The relevant documentation is itself not very clear whether TRUNCATE is transaction safe, at least for InnoDB table types. For non-InnoDB table types it says:

Truncate operations are not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock.

(I don't know who writes the MySQL documentation; it could do with a critical review from someone who actually uses MySQL. Hint: will work for money )

For comparision, this is what PostgreSQL does with the same sequence of operations:

test=> CREATE TABLE trunc_test (id INT); CREATE TABLE test=> INSERT INTO trunc_test VALUES(1); INSERT 62192155 1 test=> BEGIN; BEGIN test=> TRUNCATE trunc_test; TRUNCATE TABLE test=> SELECT * FROM trunc_test; id ---- (0 rows) test=> ROLLBACK; ROLLBACK test=> SELECT * FROM trunc_test; id ---- 1 (1 row)
Posted at 2:43 PM

Despite the fact I share with you the same disregard for this piece of crap called MySQL, I have to admit that you're plainly wrong here:
TRUNCATE IS transaction safe, only that you're failing to notice that any DDL, including truncate are committing by default the changes, without expecting any explicit COMMIT command from user.
In the above test cases, the transaction is ended by applying the changes following a successful TRUNCATE. It was the intended behaviour and it is EXACTLY the same behaviour you will find on Oracle or any other decent database. In the other words, rtfm.
Posted by: gigiduru | 2007-10-19 21:57
Umm, I wouldn't go as far as describing MySQL as you do, although I have continuing issues with it.

As far as RTFM goes, I have - and MySQL classes TRUNCATE TABLE as DML, not DDL. However, when TRUNCATE is issued *within a transaction*, all data is removed from the table and the transaction is invalidated *without any warning*. It is actually behaving like a DDL instruction, and the manual is very unclear about what is going on.
Posted by: Ian | 2007-10-20 16:51
With all due respect, I think you're misinformed. Excerpts from an Oracle manual, concerning TRUNCATE:
-TRUNCATE is a DDL statement;
-DDL is a subset of SQL that is employed to define database objects;
- a DDL statement cannot be rolled back, only DML can;
- DDL statements include CREATE, ALTER, DROP and TRUNCATE;
- One key difference between DML and DDL is that DDL statements will implicitly perform a commit, affecting not only the change in object definition, but also committing any pending DML.

Now, let's see MySQL AB's approach (excerpts from MySQL Manual):
- "Truncate operations drop and re-create the table, which is much faster than deleting rows one by one" - so this ought to tell me it must be a DDL statement, even in MySQL's implementation.

Show me that piece of MySQL manual that says that truncate is a DML statement and I'll eat it.
Oh, btw, I still think that MySQL should file for bankruptcy. Oh, wait, the company is Swedish...
Posted by: gigiduru | 2008-01-10 04:11
The hole point of TRUNCATE is meant to be a DDL statement. Otherwise is equivalent to DELETE TABLE command and it would be redundant.

ok. now some quotes from www.mysql.com:
>>For InnoDB before version 5.0.3, TRUNCATE TABLE is mapped to DELETE.
From MySQL 5.0.13:
Truncate operations drop and re-create the table
Truncate operations are not transaction-safe;
The number of deleted rows is not returned<<

there is a delete mapping isue between 5.0.3 and 5.0.13, but after that the truncate is a DDL.

let's see how things are on 5.1:
it appeared that in 5.1 they go back one step and the situation is:
>>For InnoDB tables, TRUNCATE TABLE is mapped to DELETE if there are foreign key constraints that reference the table,otherwise fast truncation (dropping and re-creating the table) is used.<<

and seems that they kept the same behavior in 6.0.

I don't get it though, why a TRUNCATE should reference to the delete constraints. THIS IS NOT A DELETE COMMAND.

Any way ... this are the mystery of mysql database (an exaggeration of terms in my opinion).

It should look at it's big brothers and learn and improve what they see there. not make a poor hybrid.
Posted by: dboyr | 2008-01-11 09:49