New Features In MySQL 6.x

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.