New Features In MySQL 6.x

As of July 1 all Canadian MySQLers are employees of Sun Microsystems. I was expecting that we would be the rear guard, I was going to greet the great day singing “a broken man on an Edmonton pier / the last of Monty’s engineers” (with thanks to the writer of “Barrett’s privateers”), or perhaps “And I, the last, go forth companionless … Among new men, strange faces, other minds.” (with thanks to Mr Tennyson). But as things turned out, the Ukrainians and the French were delayed. So Trudy Pelzer and I were the last Canadian engineers to come on board, but not quite last of all.

And it’s been wonderful. A free lunch, a tour of Sun’s Edmonton office, a SafeWord security doohickey, a carrying case, and an alternative address = peter.gulutzan@sun.com. More seriously, I’ve gained several thousand new co-workers while keeping the old ones (almost none of the MySQL engineers didn’t join Sun, and I know of none that intend to leave now that they’ve joined Sun). This is one of those friendly acquisitions where everything has just worked, and my hat is off to the integration teams who’ve kept us all in a high-morale gung-ho cloud. The new company is an even better friend of open source than the old, the number of intelligent people I’ve encountered is even greater, and the European level of politeness — always one of MySQL AB’s best points — hasn’t been polluted at all, touch wood. I highly recommend a Sun job for connoisseurs of tasteful engineering positions.

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 …)

I can find most of MySQL’s “timeout” variables with a SHOW statement:

mysql> show variables like '%timeout%';+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| connect_timeout            | 10    |

| delayed_insert_timeout     | 300   |

| falcon_lock_wait_timeout   | 50    |

| innodb_lock_wait_timeout   | 50    |

| ...................................|

| interactive_timeout        | 28800 |

| net_read_timeout           | 30    |

| net_write_timeout          | 60    |

| slave_net_timeout          | 3600  |

| table_lock_wait_timeout    | 50    |

| wait_timeout               | 28800 |

+----------------------------+-------+

11 rows in set (0.01 sec)

The straightforward list conceals several anomalies and buried controversies.

* Should we allow non-integer values like 0.55 seconds, or perhaps make all values milliseconds rather than seconds, in order to reflect the importance of blocking for as little time as possible?

* Should we allow the value to be zero? If so, does zero mean “there is no timeout” (i.e. zero = infinity), or does 0 mean “timeout should go on for zero seconds”?

* Should the default be 50 for more cases, or tailored to something that looks less arbitrary?

* Should it be possible to set timeouts per session, or should it always be done with GLOBAL?

It’s a set of questions that we had to revisit in January when falcon_lock_wait_timeout appeared, and we’ll see it again for an upcoming timeout variable in BACKUP. And my opinion is: I wish we had a magic formula for every question that ever arose at MySQL.

Occasionally I hear the worry that MySQL might plan a feature that’s “half baked”. The term’s users include some of the world’s top MySQL experts so I’ll avoid a technical argument.

“In future we should not release a version with half-baked features and call it enterprise-ready.”
– Konstantin Osipov
http://www.xaprb.com/blog/2007/08/12/what-would-make-me-buy-mysql-enterprise/

“will Falcon be pushed hard as Innodb replacement even if it is half baked?”
– Peter Zaitsev
http://www.mysqlperformanceblog.com/page/2/?s=falcon&search_posts=true

As for me, though, I believe in features that others call “half baked”. I just don’t use the term much myself because it seems to convey a wistful regret. While “we’re halfway there” would generally be taken as a report of good progress.

The analogy seems to be with a cake: if you take a cake out of the oven and let it cool, and then find it’s not done, and then put it back in the oven to bake again, it won’t be good. But I prefer to analogize with a journey: if you take one step then you have completed a useful fraction of it. Of course, that assumes that the step is in the right direction, which means you must decide in advance what your destination is, and it is that — the failure to decide the destination — that causes embarrassing steps in seemingly random directions. I call those “drunkard’s walk” features, rather than “half baked” features. A bad sign is if someone at MySQL says “let’s do this because it’s low hanging fruit”, but I’m happy to say that I’m hearing those dread words less frequently nowadays.

So when I hear about a “half baked MySQL feature”, I’d want proof, not that this is only part of a job, but that it’s not getting us toward the job we really want.