New Features In MySQL 6.x

Sometimes we have to add new reserved words along with new features. This can cause irritation. For example, if you have a table named NONE and we make NONE a reserved word, you have to start using backticks or change the name. Beware particularly of these words that might someday become reserved according to one worklog task or another.

ARRAY ASYMMETRIC AUDIT BOOLEAN CIDR CONNECT CUBE CURRENT CURRENT_CATALOG CURRENT_PATH CURRENT_ROLE CYCLE DEFAULT DENSE_RANK EXCEPT FOR GET GLOBAL INET INTERSECT LOCAL MACADDR MERGE NEW NONE OLD OVER PARTITION RANK RESIGNAL ROWNUM ROW_NUMBER ROWS SESSION_USER SIGNAL START SOME SYMMETRIC SYSTEM_USER TRUNC UNKNOWN WINDOW

For an old list that includes reserved words in other DBMSs, check the end of an article I wrote several years ago for DBAzine, SQL Naming Conventions.

Pete Freitag’s SQL Reserved Words Checker could be interesting too.

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#411 Computed virtual columns as MS SQL server has.

The quirky thing about the description, nowadays, is that the columns aren’t necessarily computed, aren’t necessarily virtual, and — if anybody follows the general policy that we should be like the standard — won’t much resemble SQL Server. So it’s not a very good name, eh?

The topical thing about the description is that it differs from a bugs.mysql.com feature request
Bug#46491 Patch: Virtual columns (WL#411). This is another name that could be questioned, since in reality the patch doesn’t look like WL#411. (And before I edited the specification, the resemblance was no better.) Yet it’s perfectly acceptable, as evidenced by the fact that a non-MySQL DBMS accepted it.

What we have, and probably always will have in MySQL, is a question about whether a patch should come in because it’s done (as illustrated by the frequent plaint “why is my patch taking years to get in?”), or whether the specification should come first and then the architecture review, with implementation last. I’ll avoid predicting how this one will come out.

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’ve been editing a task description in our worklog: WL#827. It has to do with CREATE SEQUENCE, ALTER SEQUENCE, and using “sequence generators” to get the next number in a sequence. After the editing, I was able to set the private flag to ‘NO’, so now you can see the specification here: http://forge.mysql.com/worklog/task.php?id=827.

As always, one can “vote” on a worklog task that’s in forge.mysql.com, or comment on the corresponding feature request in bugs.mysql.com. I wouldn’t hold out much hope for this one, though. It’s been around since 2003, and we’ve always deemed that it’s higher priority to make minor changes to our auto_increment facility.

The MySQL 5.4 “Azalea” version was scheduled for a feature freeze last week. Thus it’s getting clearer what will come with the next milestone in a few months. The following is not official information but it is what I can actually see.

What’s not in

Some features, which I expected and predicted would be in, are not in.
* WL#2360 Performance Schema. We’re now in the fifth month of the first code review.
* WL#2649 Number-to-string conversions. We’re still using VARBINARY a lot.
* WL#1326, WL#2377 GIS
* WL#4803 Pluggable Query Cache module
* WL#1213 Supplementary Characters. This feature works great but could disrupt upgrades.

I apologize if I got anyone’s hopes up. The tasks are not cancelled and I’m hopeful about the next milestone releases.

What’s in

BACKUP and RESTORE are in, provided you start the server with mysqld –new. Some people say the feature is disabled, but it’s not disabled in the latest download.

There will be changes related to the Innodb plugin made by our Oracle partners.

Other changes that I’ve blogged about previously are in, including SIGNAL/RESIGNAL. OUT parameters in prepared statements, Sinhala collation, and changes to INFORMATION_SCHEMA.PARAMETERS. Of course we’re billing Azalea as a bouquet of performance enhancements, but these visible features alone are a big deal, people have wanted them for a long time.

One missing feature in our stored procedures and functions is that, if there’s an exception (error), you don’t know the error number or sqlstate or error message. The plan is there, of course, WL#2111 Stored Procedures: Implement GET DIAGNOSTICS but I haven’t ticked the architecture review box, and the best prospect for a volunteer is busy with performance schema work.

However, there is a way to get this in the next MySQL 5.4 (which is called mysql-next on launchpad and which will almost certainly become the September milestone). Use RESIGNAL.

For space reasons I’m only going to illustrate how to do this to find out the SQLSTATE. I think bright souls will realize it’s simple, though tedious, to extend this so you can get the error number too.

I found out what the possible SQLSTATE values are by looking in the manual (of all places, eh?) in section Appendix B.3. Server Error Codes and Messages. No doubt they’ll change, but not often and wildly.

I added 38 DECLARE EXIT HANDLER statements at the start of my procedure, just after the variable declarations. These lines are always the same for any procedure. They’re not executed unless an error happens so I don’t worry about speed. (Update: Roland Bouman’s blog says you should worry, I suppose it depends whether a large fixed parse cost is significant for your workload. Thanks for correcting, Roland.)


USE test
DROP PROCEDURE IF EXISTS p//
CREATE PROCEDURE p ()
BEGIN
DECLARE v INT;
DECLARE EXIT HANDLER FOR SQLSTATE '01000' BEGIN SET @e='01000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '08004' BEGIN SET @e='08004'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '08S01' BEGIN SET @e='08S01'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '0A000' BEGIN SET @e='0A000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '20000' BEGIN SET @e='20000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '21000' BEGIN SET @e='21000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '21S01' BEGIN SET @e='21S01'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '22001' BEGIN SET @e='22001'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '22003' BEGIN SET @e='22003'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '22004' BEGIN SET @e='22004'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '22007' BEGIN SET @e='22007'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '22008' BEGIN SET @e='22008'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '22012' BEGIN SET @e='22012'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '23000' BEGIN SET @e='23000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '25000' BEGIN SET @e='25000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '28000' BEGIN SET @e='28000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '2F003' BEGIN SET @e='2F003'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '2F005' BEGIN SET @e='2F005'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '3D000' BEGIN SET @e='3D000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '40001' BEGIN SET @e='40001'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '42000' BEGIN SET @e='42000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '42S01' BEGIN SET @e='42S01'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '42S02' BEGIN SET @e='42S02'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '42S12' BEGIN SET @e='42S12'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '42S21' BEGIN SET @e='42S21'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '42S22' BEGIN SET @e='42S22'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE '70100' BEGIN SET @e='70100'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'HY000' BEGIN SET @e='HY000'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'HY001' BEGIN SET @e='HY001'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XA100' BEGIN SET @e='XA100'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XA102' BEGIN SET @e='XA102'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XA106' BEGIN SET @e='XA106'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XAE03' BEGIN SET @e='XAE03'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XAE04' BEGIN SET @e='XAE04'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XAE05' BEGIN SET @e='XAE05'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XAE07' BEGIN SET @e='XAE07'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XAE08' BEGIN SET @e='XAE08'; RESIGNAL; END;
DECLARE EXIT HANDLER FOR SQLSTATE 'XAE09' BEGIN SET @e='XAE09'; RESIGNAL; END;
DROP TABLE no_such_table;
END//


The final line, DROP TABLE, is an error I threw in there for illustration. Here’s what happens when I invoke the procedure:


mysql> call p()//
ERROR 1051 (42S02): Unknown table 'no_such_table'


Fine, but where’s the SQLSTATE? Well, if you paid attention, you know it’s in @e.


mysql> select @e//
+-------+
| @e |
+-------+
| 42S02 |
+-------+
1 row in set (0.00 sec)


Remember, you need the next release of 5.4 (”Azalea”) for this. My compliments as always to the architect (Peter Gulutzan) (me), the implementor (Marc Alff), the first code reviewer (Davi Arnaut), the second code reviewer (Sergei Golubchik), and the documenter (Paul DuBois).

In an earlier blog posting “SQL Standards, ANSI committees, and Sun”, I (Peter Gulutzan) talked about our prospects for joining the American committee charged with database standards, which we typically call “ANSI” although that’s not the formal name (and by the way the formal name is about to change, but I’ll chat about organization some other time).

Well, I’m now Sun’s official voting delegate to the committee. There are also three “alternate” delegates from other parts of Sun; I’ll loosely categorize them as advocates from our PostgreSQL-ophile and Java / Java DB interest groupings. Mostly my concern is the MySQL side of things.

The committee holds frequent meetings by telephone conference, and infrequent ones in personal get-togethers. I’ve just finished attending one of the lengthier meetings. I won’t discuss details or say how votes are going — our meetings are not public. But it’s always been harmless to inform the public about some things that might or might not be in a future version of the standard (”SQL:201x”). So, if we MySQLers act on some relevant feature in the next few years, you’ll know what we’ll take into account from the standard.

Enforced constraints.
A table foreign-key or check constraint may contain [NOT] ENFORCED. For example:
CREATE TABLE t2 (s1 INT, FOREIGN KEY f REFERENCES t1 (s1) NOT ENFORCED);
ALTER TABLE t2 ALTER CONSTRAINT f ENFORCED;
The DBMS ignores a not-enforced constraint. The default is ENFORCED. Currently MySQL has no equivalent, although it does have
SET @@foreign_key_checks = 0 | 1;
ALTER TABLE t2 DISABLE | ENABLE KEYS;
The words DISABLE and ENABLE are what Oracle uses. The words [NOT] ENFORCED are what DB2 uses.

Versioned tables.
Think of a bank-application table which has current balances and an associated history of deposits/withdrawals. We’d like to ask “what was the balance as of January 1″, which involves reversing transactions that took place since January 1. In other words, the DBMS should maintain and be aware of a history of changes. An example of syntax is:
CREATE TABLE t
(bank_balance DECIMAL(5,2),
sv_start TIMESTAMP GENERATED ALWAYS AS SYSTEM VERSION START,
sv_end TIMESTAMP GENERATED ALWAYS AS SYSTEM VERSION END
) WITH SYSTEM VERSIONING KEEP VERSIONS FOR 2 YEARS;
This might be good for audit trails. I don’t regard this as terribly close to Flashback Technology or version-enabled tables in Oracle; this is new to me.

General intervals.
The currrent standard INTERVAL data type is for datetime differences, for example “2 hours 15 minutes”. A more general interval would be applicable for spatial differences as well a datetime differences, and could have an anchor, for example “2 hours 15 minutes starting at noon on January 1″. Sometimes this sort of thing has been called a PERIOD. The effect on MySQL is small, we don’t even support INTERVAL yet (WL#831).

Named parameters
You can give a parameter a name, for example
CREATE PROCEDURE p (p1 INT, p2 INT) …
CALL p(p2 => 5, p1 => 10) …
I would have preferred to use AS for naming (as we do for SQL select lists), but Oracle and (since very recently) DB2 have decided to go with the new ‘=>’ token.

Defaults for parameters
You can use a parameter’s default value, for example
CREATE PROCEDURE p (p1 INT DEFAULT 1, p2 INT DEFAULT 2) …
CALL p (5, DEFAULT);
CALL p (5);
Presumably, if we keep the MySQL non-standard extension that allows CALL without parentheses, this will mean that
CALL p;
is the same as “CALL p(1,2);”.

Spatial changes
For the “Spatial” features, some changes will appear that we can mostly regard as bug fixes.

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.

I suppose it looks odd that many of the “6.x” features I’ve blogged about are now “5.4″ features. Sorry about the surprise. In mitigation I observe that this means some things are now scheduled a bit quicker, and some are a lot better than expected. Sometimes it’s hard to know till tests are in.

The current server 5.4 download is here:
http://dev.mysql.com/downloads/mysql/5.4.html.
It does not have all the features yet that we have announced as 5.4.

The features in the current 5.4 download are:

* Scalability, that is, it works faster if you add cores or processors.
A lot of the work for this happened outside the MySQL group per se, it’s lovely to have bunches of performance experts from elsewhere in Sun Microsystems, also known as Sun Classic, doing the tweaks for us. Tests show that 5.4 is faster than any variant, whether genuine MySQL or not.

There’s a slideshow, a blog from a Sun Classic employee, a blog from an old-time MySQL employee, and even a reference manual with sections “Changes in MySQL 5.4.0″ and “What Is New in MySQL 5.4″.

Large customers are trying out the beta with (as I understand) happiness. Tomorrow (May 21 2009) there will be a web seminar (”webinar”) on the subject, see http://www.mysql.com/news-and-events/web-seminars/display-343.html.

* DTrace. [sic -- with a capital T!]
The worklog task description “Improved DTrace support in server” (WL#4541) explains that this is about adding instrumentation in the SQL code for routines that aren’t system calls (system calls are handled without us having to add instrumentation). The WL#4541 description is out of date, and so is the manual at the time I’m writing this, so expect the list of instrumentation points to be better than what’s documented.

The features that are already pushed into 6.0, and therefore almost certain to be in 5.4, are:

* Things that make it go faster.
Well, this has been called a “performance release”, so the scalability improvements get coupled with reduced locking / mutex contention (WL#3262 WL#3561 WL#3726 WL#4284) , subquery semijoins and materializations (WL#1110 WL#2980 WL#3751 WL#3985), batch/range joining (WL#2475 WL#2771 WL#4424).

* Things which make programmers’ lives easier.
In this category I put SIGNAL and RESIGNAL (WL#2110 WL#2265), INFORMATION_SCHEMA addition (WL#2822 for thefirst time in public!, and the PARAMETERS view), replication additions, character-set additions (WL#4164 WL#3759) and XML additions (WL#1397).

The features that are not already pushed into 6.0, but which I personally expect have a good chance
of going to 5.4 (we’ll know by the end of June), are:
Performance Schema (WL#2360 WL#4678), Globalization (WL#1349 WL#3764 WL#3997 WL#4642), error messages (WL#751), number-to-string conversions (WL#2649), GIS (WL#1326 WL#2377 ), and partitioning (WL#4444 WL#4571).

There’s also a bit of refactoring work going on in mysys.
And there are bug fixes, including Bug#989.

I decided not to reference worklog tasks that are private, so the above list has a few gaps.

The Date

The goal is that 5.4 will be GA in December 2009. The date is earlier than we thought of for 6.0. I think it’ll probably make some people happy.