New Features In MySQL 6.x

MySQL’s TRUNCATE statement will evolve over the next few years, as we fix bugs or strange behaviours, and as we try to make storage engines more consistent with each other. Equally interesting are the aspects that will remain the same, as we confirm that our behaviour conforms to standard requirements.

Define TRUNCATE

TRUNCATE T has two definitions:
(1) it means “delete all rows in T”, as the MySQL Reference Manual says in section “TRUNCATE Syntax”. (2) it means “drop T, then create it again”, as the Reference Manual says later on. The better definition is (2). I have proofs.

Proof #1: The required privilege for TRUNCATE is DROP.

Proof #2: TRUNCATE does not cause activation of DELETE triggers. For example:
CREATE TABLE t (s1 INT);
CREATE TRIGGER t_ad AFTER DELETE ON t
FOR EACH ROW SET @a = @a + 1;
SET @a = 0;
INSERT INTO t VALUES (1),(2),(3);
TRUNCATE TABLE t;
Result: @a = 0, which shows that trigger t_ad was never activated.

Proof #3: TRUNCATE ignores locks, DELETE doesn’t. For example:
On Connection#1, say:
set @@autocommit=0;
create table t1 (s1 int, s2 blob) engine=innodb;
insert into t1 values (1,’a'),(2,’b'),(3,’c');
commit;
truncate table t1;
On Connection #2, say:
set @@autocommit=0;
update t1 set s2 = ”;
Result: the truncation succeeds, it is not blocked by the INSERT statement. If Connection#1 had said DELETE instead of TRUNCATE, there would be a lock. This is actually a danger point with TRUNCATE, by the way: you cannot expect locking to happen the same way that it would happen for DELETE statements.

Proof #4: TRUNCATE is much faster than DELETE if there are many rows in the table. For example:
CREATE TABLE t (s1 INT);
/* insert many rows */
DELETE FROM t;
/* insert many rows */
TRUNCATE TABLE t;
I timed the DELETE, it took several seconds. I timed the TRUNCATE, it took less than one second. This speed difference is what I would expect of a statement that affects only metadata, not rows.

There is no interesting side effect that’s due to TRUNCATE’s “drop and recreate” method, namely freeing of space in an underlying file. For example:
USE test
CREATE TABLE t (s1 INT);
/* insert many rows */
/* Look at size of underlying file in datadir/test */
DELETE FROM t;
DROP TABLE t;
CREATE TABLE t (1 INT);
/* insert many rows */
/* Look at size of underlying file in datadir/test */
TRUNCATE TABLE t;
Result: DELETE and TRUNCATE have the same effect on the underlying file. If the table was made with MyISAM, the file size becomes 0, either way. If the table was made with InnoDB and Per-Table Tablespaces, the file size is unchanged, either way. But in theory this could change, giving another “efficiency” reason to use TRUNCATE.

Against the advantages, one has to lay the things that are a little peculiar. They’re documented, but frankly the MySQL Reference Manual is getting a bit clogged with all the documentations of unintuitive non-obviosities.

The SQL:2008 Standard

In December 2007 the SQL standard committee published a draft document for the next standard, which I’m tentatively calling SQL:2008 in case it comes out in 2008. The draft document has new sections which describe non-core (that is, optional) features F200 and F202, which amount to support of the TRUNCATE statement.

TRUNCATE TABLE base_table_name
[ CONTINUE IDENTITY | RESTART IDENTITY ];

Differences between SQL:2008 and current MySQL implementation:

1. SQL:2008 allows
[ CONTINUE IDENTITY | RESTART IDENTITY ]
The default is CONTINUE IDENTITY.
MySQL has no such clause, and it nowadays (after Bugs 1514, 5033, 11946, 14945, 15754) handles auto_increment by sayng that TRUNCATE TABLE causes “restart”. It’s still not working correctly with partitions, see Bug#35111 ‘Truncate a MyISAM partitioned table does not reset the auto_increment value”.

2. SQL:2008 says TRUNCATE TABLE’s function is to “delete all rows” and TRUNCATE TABLE is a data-change statement, not a schema-change statement. MySQL says TRUNCATE TABLE is “DDL” because there’s an implied DROP TABLE, which means we treat it as a schema change.

There’s actually no problem about the main issue: The standard says “… all rows are deleted from T” but it doesn’t refer to sections that would cause execution of triggers, which means the effect is not what you’d expect with DELETE, but what you’d expect from drop + create. However, the fact that MySQL thinks TRUNCATE is DDL (vaguely like the standard’s “schema change” classification), and the tendency to autocommit, are certainly differences.

3. SQL:2008 says TRUNCATE TABLE is illegal on a referenced table of a foreign key. MySQL allows it for InnoDB, and WL#148 (the worklog task for foreign keys in MySQL version 6.1) disallows it for all tables where foreign keys are involved.

So right now we’re allowing something that we shouldn’t, and in future we won’t allow something that we should.

4. SQL:2008 says TRUNCATE TABLE should return “no data” if there were no rows in the table. MYSQL returns “okay” just as it does for DELETE. This is WL#4194 “Standard handling of ‘no data’”.

This relates as well to MySQL’s display of the number of affected rows.

5. SQL:2008 says TRUNCATE TABLE table_name. MySQL says TRUNCATE [TABLE] table_name.

6. SQL:2008 says TRUNCATE is a reserved word. MySQL says it’s not.

By the way, you’ll generally find that words that begin MySQL-server statements are reserved words, but besides TRUNCATE there are other exceptions: BACKUP BEGIN CACHE CHECKSUM COMMIT DEALLOCATE DO EXECUTE FLUSH HANDLER HELP INSTALL PREPARE REPAIR RESET RESTORE SAVEPOINT SLAVE START STOP UNINSTALL XA. I’ve seen a suggestion that TRUNCATE should be special, see Bug#12074 “TRUNCATE keyword not hilighted like SELECT, INSERT, etc.”.

7. SQL:2008 says the required privilege is “be the schema owner”. MySQL says the required privilege is DROP.

8. SQL:2008 says there is no trigger action. MySQL usually has no trigger action, but there’s a bug, Bug##27935 “TRUNCATE fires DELETE trigger if InnoDB table is referenced by a foreign key”.

All your standards are belong to us

I’ve just noticed that my future boss, Sun Microsystems CEO Jonathan Schwartz, has started off one of his blog posts by saying
“De facto standards are the only ones that matter.”
This is my opportunity to say “Brilliantly put, boss.” I’ll only add that the de facto standard (what all the SQL vendors do) and de jure standard (what SQL:2008 says) tend to converge.

I can see that because, in this wonderful age when every company puts its documentation online, I can act like a grocery-store price checker and wander among the competitor’s shelves. I can find TRUNCATE’s description in the Oracle 11g manual, the SQL Server 2008 manual, the Informix manual, the PostgreSQL 8.3 manual, and the DB2 Version 9.1 for z/OS manual. Of course, I find that there are details where the other vendors don’t follow the standard, just as MySQL doesn’t, see above. But in general, and in many of the details, I find that de facto = de jure.

I’ve seen this so often, and probably will devote a whole column to the subject someady … Convergence happens. Sometimes convergence happens because individual vendors commit themselves to adhering to an established standard feature. Sometimes the standard committee tries to reflect what goes on already, which is what’s happening here. In any case, the reason is rarely “academic”. TRUNCATE is hardly the sort of feature that one would add because of orthogonality or relational theory.

Even when the new standard becomes official, I doubt that the material on TRUNCATE will muchly affect MySQL unless users make requests. If any of the above are really burning issues for your installation, you can fill in a feature request form on bugs.mysql.com. Some of the TRUNCATE-related feature requests that I’ve seen already are
Bug#5507 TRUNCATE should work with views
Bug#17092 allowing TRUNCATE TABLE while LOCKing done
Bug#19405 No way to truncate a partition.
We have in-development plans for other tasks that mention TRUNCATE adjustments:
WL#3241 Truncate table in Maria
WL#2418 DDL Triggers (including perhaps a TRUNCATE trigger)
WL#3821 TRUNCATE for VIEWs (including perhaps a TRUNCATE privilege)
WL#3129 Consistent clauses in CREATE and DROP (including perhaps TRUNCATE IF EXISTS table1, table2 …)

This entry was posted on Monday, June 9th, 2008 at 1:47 am and is filed under Personal Opinion. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.