New Features In MySQL 6.x

I’ve been editing a task description in our worklog:
WL#946 TIME/TIMESTAMP/DATETIME with fractional seconds.

This has been around for a long time, and will be around for a long time. But I did think it’s at least time that we should let people know that we think about it. The specific impetus today was a fairly thorough survey of the feature request’s history in a MySQL DBA’s blog posting Once upon a timestamp(milliseconds)….

In my 2008-05-04 blog posting about the roadmap, I included WL#946 in a list with the heading “Let’s not forget that these will fit in somewhere in the 6.x / 7.x period”. There’s a lot to do — I know that’s not clear from reading a typical bugs.mysql.com feature request, but I hope it’s clear from clicking and reading “High Level Architecture” in WL#946.

I’ve been editing a task description in our worklog:
WL#706 Add EXPLAIN support for other statements (UPDATE/DELETE).

Currently MySQL supports EXPLAIN SELECT but there have been requests for EXPLAIN UPDATE, EXPLAIN DELETE, and so on. In fact it’s my impression that the proposition in WL#706 (transform the statement to a SELECT and then say EXPLAIN SELECT) is not what people want. But that’s okay, while a worklog is an early stage we allow simple ’solutions’ that might disappear later.

I thought I was clear in earlier blog postings about Performance Schema, but I’ll have to write more firmly.

1. Performance Schema source code is available:
https://code.launchpad.net/~marc.alff/mysql-server/mysql-6.0-perfschema

2. Performance Schema has been in development for less than one year.

3. All Performance Schema worklog task descriptions are public on forge.mysql.com.

Developments seem to be accelerating, here’s anecdotal evidence.

Foreign Keys
The Foreign Keys (all engines) worklog task passed another milestone last week: now MySQL is doing referential constraint checks at end of statement when the standard so prescribes, rather than row-by-row. This means that the all-engines functionality is now slightly greater than what’s in MySQL’s current InnoDB-only foreign-key handling.

The closing of Bug#989
“The wheels of the gods grind slowly, but they grind exceedingly fine.” Our Bordeaux-area Maria expert Guilhem Bichot reported Bug#989 “If DROP TABLE while there’s an active transaction, wrong binlog order” in August 2003 and some unkind soul even mentioned it on the wikipedia article about MySQL. The mention is gone now. And so is the bug. I’m being a glass-is-half-full kind of guy these days, so instead of whining “it took a while” I’m exulting “these are the days when things are getting done”. Send thanks to the fixer and the reviewer, their names are on the report.

Performance Schema
The feature I blogged about so extensively last month, the WL#2360: Performance Schema, now has several people working on it: 1 implementor, 1 architect, 2 code reviewers, 1 Quality Assurance reviewer, 1 person from the Enterprise Tools team that puts together the MySQL Enterprise Monitor. The April 20 2009 deadline, for making the code public, is looking very doable.

Character Sets
Some worklog tasks have passed the architecture-review stage and coding is in progress: one which fixes a complaint about certain functions that return BINARY/VARBINARY when CHAR/VARCHAR would do, one for a change to FORMAT() so that for example the number 1.23 might appear as 1,23 with comma instead of decimal point in some locales, one for Latin9 (iso-8859-15) character set, one for Error message construction that will eventually replace the unwieldy errmsg system we have now. The worker on this is Alexander Barkov, and if you want to ask him how’s it going, meet him at the MySQL User Conference in three weeks.

MySQL 6.x Roadmap in Development
By Peter Gulutzan

MySQL’s marketing folks know what goes on, but they emphasize the marketable. I’ll re-spin what they say about MySQL Version 6 and 7, emphasizing what we’re developing now.

The slide show from the April 2008 User Conference lists the coming features thus:
”
[ MySQL 6.0 ]
Falcon Engine (Transactional engine)
New Backup (version 1.0) (Cross engine, non blocking)
Online add column (Cluster only)
Replication conflict detection (Cluster only)
Optimizer enhancements (Faster subqueries)
Better performance info (Diagnostics and more)
Alpha available now (with Falcon beta)
GA scheduled for Q4/2008
[ MySQL 6.x ]
Foreign keys (all storage engines)
Better prepared statements (prepare any SQL statement)
Better server-side cursors (Faster/less memory)
Replication improvements (Checksums)
Optimizer enhancements (Faster subqueries)
Online backup enhancements (Backup catalogs, more)
Stored procedure changes (Signal/Resignal, more)
Performance interface (Better perf diagnostics)
Alphas, betas begin late 2008
GAs expected first part of 2009
[ MySQL 7.x also known as Citadel ]
Security groups/roles (Easier privilege management)
Transparent data encryption (Table/column level)
Data auditing (for compliance)
External authentication (O/S level authorizations)
Online alter table (Online DDL changes)
Optimizer enhancements (More subquery changes)
”
The Future Of MySQL What you Need to Know About What’s Coming

Now here’s how we look at it in the development department. I’ll refer frequently to URLs for “worklog tasks”. We’re open, so almost all tasks are visible on MySQL Forge.
Occasionally we hide items where we have to guard customer privacy, and occasionally I don’t refer to a worklog task because I don’t know which one the marketers mean.

[ MySQL 6.0 ]

“Falcon Engine (Transactional engine)”
Too many worklog tasks to mention. In this case the tip is
to follow the musings of some members of the team
Hakan Kuecuekyilmaz
Christopher Powers
Jim Starkey (QuickTime video)
Full comments are in a coming-soon issue of this blog.

“New Backup (version 1.0) (Cross engine, non blocking)”
Several colleagues and I have written a long article about the online backup feature set. I expect to publish it in two weeks.

“Online add column (Cluster only)”
There’s no consistent definition of the word Online, but in this case it means allow
ALTER TABLE … ADD COLUMN ….;
without blocking other transactions while MySQL rewrites every row. There’s been talk about this for Falcon too, and there the idea has been:
if the column doesn’t contain the row, then it must have been inserted before the ALTER, and therefore it has the column’s default value — and that’s always the same, so there’s no need to update every row immediately, just look it up in the new table definition. The going gets rough when you contemplate the details.

“Replication conflict detection (Cluster only)”

“Optimizer enhancements (Faster subqueries)”
These four words fail to do justice to the optimizer team’s efforts, but the larger subquery optimizations come later.
Subquery optimization: FROM (SELECT)

“Better performance info (Diagnostics and more)”
Well, in fact it’s hard to guess what this is about. In the server, which is where performance information ultimately must come from, the only initiative that pops to mind is DTrace. That’s a Solaris-only and still crude.

“Alpha available now (with Falcon beta)”
The server is alpha but one of the storage engines is beta. Weird, eh? Essentially: the Falcon code is getting stable, but other new features are crashy, so when you use Falcon with those other new features, you crash. So don’t. The Falcon part is worthy of the beta designation, so we’re eager to see people try it out. Thus the weirdness goes away.

“GA scheduled for Q4/2008″
People have commented on this before, how 6.0 comes so soon after 5.1.
But that’s an issue for MySQL’s poor support workers. Not our department.

“”
Oh wow, the slide show left out one of the Big Three New MySQL 6.0 Features:
Supplementary Characters, described in the inaugural entry of this blog! I apologize to everyone in Japan, and assure them that this necessary first step towards JIS 2004 is, indeed, an irrevocable feature of MySQL 6.0.

Plus here’s a selection of small feature that are already in 6.0:
INFORMATION_SCHEMA: PARAMETERS view as required by our Connector colleagues
WEIGHT_STRING() function
Information schema optimization
Add SHA2 support
When I say something’s already in 6.0, that means you can download it
from our public sources: http://dev.mysql.com/downloads/mysql/6.0.html

“”
So MySQL 6.0 looks good in most respects, so far. I worry about
some upgrade difficulties, which are trivial but troublesome:
* Indexing change due to Bug#25420 Tilde = ‘Y’ in Chinese.
It’s an obscure character in an Asian character set, in the wrong place. Easy for Alexander Barkov to fix — but then index keys will be out of order, necessitating an index rebuild.
* Identifier case sensitivity change due to Bug#22166 Falcon: case insensitive table names. It’s a simple issue with a simple solution, but we didn’t agree what that is. So far, we’re having trouble getting Falcon to follow the usual MySQL rules.

There are other coming-later publicly-known features in 6.0 that we wish to make no fanfare about, yet. Watch this space.

[ MySQL 6.1 ]

“Foreign keys (all storage engines)”
Development is going on now.
The main team members, Konstantin Osipov and Dmitri Lenev, are committed to putting out a new demo every week or two.
See http://forge.mysql.com/worklog/task.php?id=148.

“Better prepared statements (prepare any SQL statement)”
Prepared Statements: overview of tasks
Prepare any SQL
Whoops, that “Prepare any SQL” task says 6.2 not 6.1. Well, whatever.

“Better server-side cursors (Faster/less memory)”
This does not mean “Holdable cursors”.
The advantage of a client-side cursor is that the client can navigate through the list without blocking anything on the server. But the server-side cursor is better for operations like “UPDATE … WHERE CURRENT OF cursor_name”.

“Replication improvements (Checksums)”
Replication event checksums

“Optimizer enhancements (Faster subqueries)”
One possibility: a task that says
“Avoid recalculating subquery if external fields have not changed”.

“Online backup enhancements (Backup catalogs, more)”
There are many tasks in this department. MySQL 6.0 has good backup capabilities, but almost anything that we could call a nice-to-have extra feature is deferred to after 6.0.

“Stored procedure changes (Signal/Resignal, more)”
Stored procedures: Implement SIGNAL
Stored Procedures: Implement RESIGNAL
SIGNAL is what the standard requires, and therefore is what we’re implementing, to generate an error. it’s particularly useful in stored procedures. The task was quiescent for a long time. I was taken by surprise last week when the main developer (Marc Alff) asked me to try SIGNAL out. It works. this is an unretouched copy of a run on a mysql client:

mysql> DELIMITER //
mysql> CREATE PROCEDURE p ()
-> BEGIN
-> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
-> SIGNAL SQLSTATE ‘44444′
-> SET SQLCODE=44444, MESSAGE_TEXT=’44444′;
-> DROP TABLE no_such_table;
-> END//
Query OK, 0 rows affected (0.00 sec)

mysql> CALL p()//
ERROR 44444 (44444): 44444

Staggeringly good stuff. Unfortunately it arrives just too late for inclusion in MySQL 6.0.

“Performance interface (Better perf diagnostics)”
For example, suppose MySQL grabs a mutex — a low-level internal tasklet that can happen millions of times without users noticing, but can bottleneck when many connections are trying to grab the same thing. DBAs would love to know: how many times did mutex X get grabbed? How many cycles or microseconds did it take, maximally or averagely? Summarize all the mutexes grouped by frequency of calling and by operation being performed. And so on for everything that can cause waiting and can be summarized. The worklog task for this has been around for 3.4 years and has been stalled repeatedly. That’s tragic, since well-meaning community coders are trying to fill the gap with patches that don’t mesh with the grand plan. I’m pushing to deprecate or reject those patches, in the name of progress.

“”
Plus about a dozen tasks related to plugin enhancements.

“Alphas, betas begin late 2008″
I think this will turn out to be true.

“GAs expected first part of 2009″
No comment.

[ MySQL 7.x also known as Citadel ]

Apparently the word Citadel is supposed to conjure up ideas about security, which is the version’s theme. I, who live in a city where streets are named 140, 141, 142, 143, 144, 145, 146, 147, 148, etc., appreciate the simplicity of a number system. I call it MySQL 7.x, and to with the Citadel.

“Security groups/roles (Easier privilege management)”
Roles

“Transparent data encryption (Table/column level)”
Not public because the description mentions a client.

“Data auditing (for compliance)”
Simple data auditing

“External authentication (O/S level authorizations)”
Pluggable authentication support
I hear that our CTO Monty Widenius said during the User Conference that some authentication work could be part of some “summer of code” project
http://www.paragon-cs.com/wordpress/?p=150
And indeed that would be excellent.

“Online alter table (Online DDL changes)”
There are many speed-up-ALTER-TABLE sub-projects. I’ll just list parts of their titles to give you the flavour.
Rebuild unique indexes by sorting in ALTER TABLE
Modify MyISAM handler to support fast CREATE/DROP INDEX
Use multiple threads to rebuild index
Create hidden short key, InnoDB
Add versioning information to a table on row basis, MyISAM
Faster index creation for InnoDB tables
Add versioning information to a table on row basis, InnoDB

“Optimizer enhancements (More subquery changes)”

“”
Let’s not forget that these will fit in somewhere in the 6.x / 7.x period:
MySQL IPv6 support
Primary, Secondary and Tertiary Sorts
TIME/TIMESTAMP/DATETIME with fractional seconds
Unicode Collation Algorithm new version
Japanese character set adjustments
Data Dictionary: relational schema
… and since 7.x is still quite a ways away, it’s easy to note many, many more.

Obviously we’ll have to say a lot more about these things. And a lot of what we’ve already said is subject to change. So this blog has a future.