New Features In MySQL 6.x

Bazaar Branches of MySQL 6.0

Now that MySQL has switched to using bazaar for source code repositories, an in place to go for 6.0 public code is Canonical’s “launchpad” site, specifically “Bazaar Branches related to Sun/MySQL Engineering” at https://code.launchpad.net/~mysql. The Bazaar Branches page has a list. Each row in the list is for a source-code package with all the MySQL files necessary to build the server, as described in the MySQL Reference Manual chapter MySQL Installation Using a Source Distribution. For 6.0 lovers, the interesting entries in the Bazaar Branches list are mysql-6.0, mysql-6.0-backup, mysql-6.0-falcon, mysql-6.0-rpl, mysql-6.0-wl3220, mysql-maria.

how to read the list
The Bazaar Branches page’s instructions look good enough to me. But comments in the “Last commit” column might have MySQL-specific abbreviations that I’d like to warn about. Specifically:
* A comment like “Fix to Bug#37679 …” will not refer to the bugs in Canonical’s launchpad.net site, but to MySQL’s own database of bugs: http://bugs.mysql.com.
* A comment containing the initials “WL”, for example “WL#4398 fix compile error …” or “WL3220: Loose index scan …”, is referring to a WorkLog task number. The public worklog tasks are on http://forge.mysql.com. Not all worklog tasks are public.

mysql-6.0
We call mysql-6.0 the “main” tree. When 6.0 becomes generally available, every feature will be in mysql-6.0. Probably this is the only branch you’ll need, unless you are a fanatical follower of a particular team’s progress on a particular day. The code here might be more recent, and more experimental, than what’s in the tar file on the “MySQL 6.0 Downloads” page.

mysql-6.0-backup
I had a long article on BACKUP and RESTORE a few weeks ago. A few changes since then: (a) some people from “Sun Classic” have joined the team (yay, Sun integration!); (b) the progress data is now in true “logs” so there are some options for viewing them as tables or files, turning them on and off, etc., the same way that it’s done with query log or general log.

mysql-6.0-rpl
The letters “rpl” stand for “replication”. Don’t ask me why, maybe this is the way that Swedes do abbreviations. (MySQL AB was originally a Swedish company, and two of the prominent replication people, Mats Kindahl and Lars Thalmann, are from places near Stockholm and Uppsala.)

mysql-6.0-falcon
Elsewhere I’ve seen statements like “Falcon is currently only supported within a special fork of the MySQL release, termed MySQL-6.0-falcon.” That’s false. All mysql-6.0 packages include Falcon. The distinguishing feature of mysql-6.0-falcon is that it has recent changes done by Falcon team members which will be merged with the other trees after a delay of perhaps a few weeks.

mysql-6.0-wl3220
The suffix ‘wl3220″ means “this branch contains the experimental code for worklog task 3220″. I mentioned before that some worklog tasks are public. You can find the description for this one at this location:
http://forge.mysql.com/worklog/task.php?id=3220.
The task is to speed up COUNT(DISTINCT column_name) and similar functions. At the moment that I’m writing this, it isn’t working yet.

mysql-maria
I included this in a list of 6.0 “sites worth seeing”, even though, if you say
SELECT VERSION();
you’ll see “5.1.25-maria-alpha”. I’m not sure that the maria storage engine is to be released along with version 5.1, it now seems more likely that it will be in 6.0 or 6.1. But it’s a little early to state what the Maria storage engine will be part of.

Other branches
Other branches exist internally, for example
mysql-6.0-wl2110 SIGNAL/RESIGNAL (all parts working now)
mysql-6.1-fk Foreign keys (parser and metadata storage working now)
Eventually these branches will either appear on the Bazaar Branches page, or get merged into one of the other branches before becoming public. Keep watching the page. You’ll probably see frequent changes.

You’ve probably seen the MySQL Reference Manual page “What’s New in MySQL 6.0″ and so you’ve seen this description of a new feature: “Support for extended comments for tables, columns, and indexes.” Here’s what “extended” means.

You can do a little more with COMMENTs in ALTER or CREATE statements. The easy way to see the difference is by running this statement in both MySQL 5.1 and MySQL 6.0:

SELECT table_schema, table_name, column_name, character_maximum_length

FROM information_schema.columns

WHERE column_name LIKE '%comment';

From that, you’ll see that the differences are:

TABLE_NAME     COLUMN_NAME      CHARACTER_MAXIMUM_LENGTH CHARACTER_MAXIMUM_LENGTH

                                in MySQL 5.1             in MySQL 6.0

COLUMNS        COLUMN_COMMENT   255                      1024

PARTITIONS     PARTITION_COMMENT 80                        80

TABLES         TABLE_COMMENT     80                      2048

STATISTICS     INDEX_COMMENT    n/a                      1024

In other words, the maximum length of a column or table comment is muchly increased, and it’s now possible to have index comments too. Partition comment length is changed too, but you can’t see it yet (Bug#37728). This change was for a special request from one of MySQL’s partners.

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