New Features In MySQL 6.x

A bunch of people held a meeting in California just before the last MySQL User Conference. They decided that tablespaces are not inside databases, and databases are not part of tablespaces. They are separate objects which overlap, for example table A can belong to tablespace X and database Y, while table B can belong to tablespace Y and database X. The result will be some restrictions regarding what you can do with tablespace maintenance statements, particularly for Falcon tablespaces.

The meeting participants also decided that there must be a new privilege, CREATE TABLESPACE, which will be required for CREATE TABLESPACE, ALTER TABLESPACE, and DROP TABLESPACE.

And there was talk about a new table for metadata: INFORMATION_SCHEMA.TABLESPACES.

Suppose you use Linux and suppose you have two tables, and their names are alike except for an uppercase / lowercase difference: ‘City’ and ‘CITY’.

Consider the statement
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ‘City’;

In MySQL 5.0 you’ll see both tables.

In recent versions of MySQL 5.1 you’ll see only one table: ‘City’.

In later versions there’s a proposal — definitely not an accepted proposal, but this blog can be speculative — that the collation for INFORMATION_SCHEMA.TABLES.TABLE_NAME should be changed to reflect this fact. Currently TABLE_NAME is UTF8_GENERAL_CI. If the proposal goes through, then the collation becomes UTF8_BIN for TABLE_NAME and for a few other columns that end with _NAME (but not all of them), except for Falcon tables, but only on some platforms where case is significant. On other platforms, such as Windows, the collation would be a new case-insensitive accent-sensitive collation.

The things that might change in future versions for the BINARY and VARBINARY data types are:

* We’ll drop the last vestiges of the idea that they’re not really data types, that they’re just variants of char/varchar data types. The product has some quirks that reflect that old idea, but it will no longer be tenable after the next version of the SQL standard says firmly: they’re data types. This will affect, for example, some connectors.

* The result of some functions will cease to be VARBINARY. For example if you say
CREATE TABLE xm AS SELECT CONCAT(’a',0);
SHOW CREATE TABLE tm;
you’ll see that the result of concatenating a char with an integer is a varbinary. But we now feel that a more useful result data type would be char or varchar with the connection character set.

* We’ll allow BINARY VARYING as an equivalent for VARBINARY in definitions.

* We’ll allow X’AB’ ‘CD’ as an equivalent for X’ABCD’ in literals.

Suppose you create a table like this:
CREATE TABLE t (current_role INT);
It works in MySQL 5.1. But it might fail in MySQL 7.0.

The problem is that CURRENT_ROLE might be a reserved word if we implement “Roles” as described in the worklog task description
Roles and the warning is right there in the description: “CURRENT_ROLE is a new reserved word”. The task description might change before we implement it, and your application might change too, after all MySQL 7.0 alpha is far from imminent. But if you have an inkling of our plans for reserved words later, maybe you’ll want to avoid using them now.

These words are marked as “possibly reserved” in some future version:
ARRAY AUDIT BOOLEAN CIDR CONNECT CUBE CURRENT CURRENT_CATALOG CURRENT_PATH CURRENT_ROLE CYCLE DEFAULT DENSE_RANK EXCEPT FOR GET INET INTERSECT MACADDR MERGE NEW NONE OLD OVER PARTITION RANK RESIGNAL ROWNUM ROWNUM ROW_NUMBER ROWS SESSION_USER SIGNAL START SOME SYSTEM_USER TRUNC UNKNOWN WINDOW

A longer list, including words that are in standard SQL or other vendors’ products, can be found in the article SQL Naming Conventions.

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.