New Features In MySQL 6.x

Readers following the performance schema saga will be happy to know that the man who designed and wrote the code, Marc Alff, has a blog. His first posting about it is at http://marcalff.blogspot.com/2010/01/performance-schema-overview.html.

The performance schema changes are now in the ‘mysql-next-mr’ tree. This means the feature is irrevocably part of the next milestone.

Documentation:

http://dev.mysql.com/doc/performance-schema/en/

Blog postings in order of appearance:

http://blogs.mysql.com/peterg/2009/02/05/mysql-performance-schema/

http://blogs.mysql.com/peterg/2009/02/06/mysql-performance-schema-2/>

http://blogs.mysql.com/peterg/2009/02/09/mysql-performance-schema-3/

http://blogs.mysql.com/peterg/2009/02/10/mysql-performance-schema-4/

http://blogs.mysql.com/peterg/2009/02/11/mysql-performance-schema-5/

http://blogs.mysql.com/peterg/2009/02/12/mysql-performance-schema-6/

http://blogs.mysql.com/peterg/2009/02/13/todo-mysql-performance-schema-7/

Source code of server including Performance Schema:

https://code.launchpad.net/~mysql/mysql-server/mysql-next-mr

Specifications:

WL#2360: Performance Schema

WL#2515: Performance statements

WL#3249: SHOW PROCESSLIST should show memory

WL#4674: PERFORMANCE_SCHEMA Setup For Actors

WL#4678: PERFORMANCE_SCHEMA Instrumenting File IO

WL#4813 PERFORMANCE_SCHEMA Instrumenting Stages

WL#4816: PERFORMANCE_SCHEMA Summaries

WL#4895: PERFORMANCE_SCHEMA Instrumenting Table IO

WL#4878: PERFORMANCE_SCHEMA Trace

WL#4896: PERFORMANCE_SCHEMA Instrumenting Net IO

WL#2333: SHOW ENGINE … LOCK STATUS

WL#2360 (the big one) and WL#2373 and WL#4678 are done.

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.