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#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#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.

From the MySQL User Conference in Santa Clara.

Konstantin Osipov + Peter Gulutzan’s presentation, “New Foreign Keys in 6.1″, is now available as an odp (Open Office presentation slides) file here. Nobody stomped out in a rage. One attendee said it’s a great development, he’s been waiting for years to see multi-storage-engine foreign keys. Another asked whether the feature would be in Drizzle, and Peter didn’t have the answer at the time, but later he asked a Drizzle worker and heard it’s improbable.

Peter heard a sideshow presenter suggest that the MySQL / DB2 storage engine was a surprise. The person didn’t allow time for questions or comments, but here’s a belated note that we sent out the first press release two years ago, here.

Alexander Barkov + Peter Gulutzan’s BoF, about character sets, got only a few non-Sun attendees. The main concerns were not about new features, but about how to upgrade from old versions, or how to convert to UTF8. Peter made a foolish statement about stripping accents, for which apologies are in order, but it’s okay because nobody believed him at the time.

After the conference Peter, as if to remove all doubts re his foolishness, decided to walk around the adjacent communities of Milpitas and San Jose. Subsequent checking on Google Maps reveals that was a 25-mile stroll in the sun, so if you saw him later red-faced and limping, here’s hoping you didn’t get the wrong impression.

The rest of this blog post is the sheet that Konstantin Osipov and Peter Gulutzan used for the MySQL Camp demo, “MySQL 6.1 Test Drive”. Somebody was recording it; if you find the recording on the web, please send a comment. It looks better if you see the results, so come watch next time they demo.

Peter Gulutzan and Konstantin Osipov
Sun Microsystems

"This is a live nothing-behind-the-curtain demonstration
of features that actually work, in pre-alpha or alpha or
beta versions of MySQL including MySQL 6.0, 6.1, and future."

After each demonstration, there is a chance to ask
one or two questions. We will type more statements on request.

======================================================
Feature = Foreign keys / all storage engines (WL#148)
Demonstrator = Konstantin
Version = mysql-6.1-fk
Sample Statement text =
quit
/data1/mysql-6.1-fk.sh
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1
 (s1 INT UNIQUE NOT NULL)
 ENGINE=FALCON;                /* Falcon + foreign key! */
CREATE TABLE t2
 (s1 INT DEFAULT 5 REFERENCES t1(s1)/* Legal Syntax at last! */
 ON UPDATE SET DEFAULT)
 ENGINE=FALCON;
CREATE TRIGGER t2_au AFTER UPDATE ON t2
 FOR EACH ROW SET @a='trigger happened';
INSERT INTO t1 VALUES (1),(5);
INSERT INTO t2 VALUES (2);     /* Won't work! */
INSERT INTO t2 VALUES (1);
UPDATE t1 SET s1 = 2 WHERE s1 = 1; /* Cascades! */
SELECT *,@a FROM t2;           /* Shows cascading worked! */
=======================================================
Feature = Supplementary Unicode Characters (WL#1213)
Demonstrator = Peter
Version = 6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
DROP TABLE IF EXISTS t;
SET NAMES UTF8;
CREATE TABLE t
  (s1 VARCHAR(5) CHARACTER SET UTF32); /* new character set! */
INSERT INTO t VALUES (0x000204d7);     /* rare cjk! */
INSERT INTO t VALUES (0x000100cc);     /* linear b! */
INSERT INTO t VALUES (0x00010400);     /* deseret! */
SELECT * FROM t;                       /* Visible with this font! */
=======================================================
Feature = Retrievable OUT parameters
Demonstrator = Konstantin
Version = 6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
DROP PROCEDURE IF EXISTS p1;
DELIMITER //
CREATE PROCEDURE p1(OUT v1 INT, OUT v2 CHAR(32))
BEGIN
  SELECT 'procedure p1' as result;
  SET v1= 10;
  SET v2= 100;
END//
DELIMITER ;
PREPARE s1 FROM 'CALL p1(?, ?)';
EXECUTE s1 USING @u1, @u2;
SELECT @u1, @u2;
=======================================================
Feature = BACKUP and RESTORE
Demonstrator = Peter
Version = 6.0
Sample Statement text =
quit
rm /data1/mysql-6.0/var/1
/data1/mysql-6.0.sh
DROP DATABASE d;
CREATE DATABASE d;
CREATE TABLE d.t (s1 INT);
INSERT INTO d.t VALUES (5);
BACKUP DATABASE d TO '1';             /* new statement! */
DROP DATABASE d;
SELECT * FROM d.t;                    /* now it's gone! */
RESTORE FROM '1';                     /* new statement! */
SELECT * FROM d.t;                    /* now it's back! */
=======================================================
Feature = EXECUTE IMMEDIATE (WL#2793)
Demonstrator = Konstantin
Version = 6.0-2793
Sample Statement text =
quit
/data1/mysql-6.0-2793.sh
DROP PROCEDURE IF EXISTS p1;
SET @@max_sp_recursion_depth = 5000;
EXECUTE IMMEDIATE 'SELECT 5';         /* literal! */
DELIMITER //
CREATE PROCEDURE p1 (name CHAR(255))
BEGIN                                 /* hara-kiri! */
  EXECUTE IMMEDIATE CONCAT('DROP PROCEDURE ', name);
  CALL p1(name);
END//
DELIMITER ;
CALL p1('p1');
=======================================================
Feature = Performance Schema
Demonstrator = Peter
Version = mysql-6.0-perf
Sample Statement text =
quit
/data1/mysql-6.0-perf.sh
SELECT * FROM performance_schema.EVENTS_WAITS_CURRENT\G
SELECT event_name,count_star,sum_timer_wait,max_timer_wait
 FROM performance_schema.EVENTS_WAITS_SUMMARY_BY_EVENT_NAME
 WHERE COUNT_STAR > 0 ORDER BY sum_timer_wait DESC LIMIT 5;
=======================================================
Feature = SIGNAL + RESIGNAL (WL#2110, WL#2265)
Demonstrator = Konstantin
Version = mysql-6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
DROP PROCEDURE IF EXISTS p;
DROP TABLE IF EXISTS t;
SIGNAL SQLSTATE '77777' SET MESSAGE_TEXT='Oops'; /* customized error! */
DROP TABLE t;                                 /* default error! */
DELIMITER //
CREATE PROCEDURE p ()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  RESIGNAL SQLSTATE '22222';                  /* replaces default! */
  DROP TABLE t;                               /* activates handler! */
END//
DELIMITER ;
CALL p();
=======================================================
Feature = SHA2 (Bug#13174)
Demonstrator = Peter
Version = mysql-6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
SELECT sha2('a',256); /* Contributor = Bill Karwin */
=======================================================
Feature = Parameters view (WL#4301)
Demonstrator = Konstantin
Version = mysql-6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
DROP FUNCTION IF EXISTS f;
CREATE FUNCTION f (parameter1 SMALLINT)
RETURNS SMALLINT
RETURN 5;
SELECT SPECIFIC_NAME AS RNAME, ORDINAL_POSITION AS POS,
PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE,
ROUTINE_TYPE
FROM INFORMATION_SCHEMA.PARAMETERS;    /* 2 rows! */
=======================================================
Feature = Partition by string (WL#3352)
Demonstrator = Peter
Version = mysql-5.1-wl3352
Sample Statement text =
quit
/data1/mysql-5.1-wl3352.sh
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a CHAR, b CHAR, c CHAR)
PARTITION BY RANGE COLUMN_LIST(a,b,c)
(PARTITION p0 VALUES LESS THAN (COLUMN_LIST('a','b','c')));
=======================================================
Feature = Less VARBINARY (WL#2649)
Demonstrator = Konstantin
Version = mysql-6.0-wl2649
Sample Statement text =
quit
/data1/mysql-6.0-wl2649.sh
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 AS SELECT CONCAT(1);
SHOW CREATE TABLE t1;
=======================================================
Feature = WEIGHT_STRING function (WL#3716)
Demonstrator = Peter
Version = 6.0
Sample Statement text =
quit
/data1/mysql-6.0.sh
DROP TABLE IF EXISTS t;
SET NAMES UTF8;
CREATE TABLE t (s1 VARCHAR(5) CHARACTER SET utf8);
INSERT INTO t VALUES ('a'),('Ã');
SELECT s1,WEIGHT_STRING(s1) FROM t;
DROP TABLE IF EXISTS t;
CREATE TABLE t (s1 VARCHAR(5) CHARACTER SET utf8
 COLLATE utf8_unicode_ci);
INSERT INTO t VALUES ('a'),('Ã');
SELECT s1,HEX(WEIGHT_STRING(s1)) FROM t;

The MySQL Performance Schema feature is now publicly available.

The launchpad URL is
https://code.launchpad.net/~marc.alff/mysql-server/mysql-6.0-perfschema

The MySQL User Conference starts April 20 2009. Please come and see:

New Foreign Keys in 6.1, with Konstantin Osipov and Peter Gulutzan, Wednesday April 22, 15:05. Originally the speaker was going to be Dmitri Lenev, the main developer … but alas, he can’t make it. However, the project lead (Konstantin) and the architect (Peter) constitute the largest pool of expertise on this subject in the whole continent.

Character Sets, with Alexander Barkov and Peter Gulutzan, Wednesday April 22, 20:30. This is a Birds-of-a-Feather get-together, so it’s not a show, just a chance to get together with other people who care about (for example) collation, Unicode, SJIS. Mr Barkov has worked with MySQL for several years and is the team lead for globalization.

Test Drive MySQL 6.1, with Peter Gulutzan and Konstantin Osipov, Thursday April 23, 11:55. This will not be a slide show, it will be an actual demonstration of foreign keys, supplementary Unicode characters, Falcon, BACKUP, EXECUTE IMMEDIATE, Performance Schema, SIGNAL + RESIGNAL, SHA2, the Parameters view, partitioning by string, reduced VARBINARY use, and the Weight_String function. Sorry, only a few minutes per topic.

The “Roadmap Shootout” keynote, originally scheduled for Wednesday morning, is canned.

There will be no “Performance Schema” session, alas, but we still intend to upload the source on Monday April 20, and you’ll catch short glimpses of the feature during the sessions Advanced Query Manipulation with MySQL Proxy or Extending MySQL Enterprise Monitor with Custom Advisors, Graphs, and Data Collections.

Although there are very few presentations by server developers this year, many developers who aren’t presenting are coming anyway. So if you don’t find anything on the schedule, just hang around in the corridors and you’re bound to see some interesting buttonholeable engineer wander along.

There are several tasks that depend on WL#2360 Performance Schema. They’re all public now, so you can find the description of the whole set on forge.mysql.com.

WL#2333 SHOW ENGINE … LOCK STATUS
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#4689 Deadlock Monitor
WL#4813 PERFORMANCE_SCHEMA Instrumenting Stages
WL#4816 PERFORMANCE_SCHEMA Summaries
WL#4878 PERFORMANCE_SCHEMA Trace
WL#4895 PERFORMANCE_SCHEMA Instrumenting Table IO
WL#4896 PERFORMANCE_SCHEMA Instrumenting Net IO

Which one has priority for this quarter? We don’t know. We have to decide by the end of this month.

There are also three tasks that WL#2360 depends on:
WL#2373 Use cycle counter for timing
WL#4601 remove fastmutex from the server sources
WL#4876 Parse options before initializing mysys

In at least one person’s opinion those three tasks are already taken care of, or can be resolved without affecting our schedule greatly. There has been a complaint that the timing code from WL#2373 is not “optimal” (true); however, let’s hope that people’s concern at this phase will merely be whether it is “fast” and “safe”.

The main task, WL#2360, is still in code review. It is now clear that code review and Quality Assurance
will not finish in the next week or two. So the plan now is that, when we release the source code for public viewing on April 20 2009, it will be part of a separate launchpad tree.

Yesterday and the day before yesterday I started to say what the status is for “Globalization” tasks.
Today I continue and finish.

Worklog WL#4579 Latin9 (iso-8859-15) character set
Current Status: architecture review done 2009-03-23.
Version = 6.1
It will be possible to create columns and variables with character set = latin9. It’s similar to latin1 and its collations correspond to latin1 collations. It doesn’t add functionality but might save on the conversion time to/from latin1 for a few people.
Example:
”
mysql> CREATE TABLE t
-> (s1 char(1) CHARACTER SET latin9
->             COLLATE latin9_german2_ci);
Query OK, 0 rows affected (0.06 sec)
”

Worklog WL#4583 Case conversion in Asian character sets
Current Status: architecture review done 2009-02-19.
Version = 6.1
For character sets UJIS SJIS GB2312 CP932 EUCJPMS BIG5 EUCKR GBK, for functions UPPER UCASE LOWER LCASE, for fullwidth Latin Latin-with-accents Greek Cyrillic, MySQL will do case folding in a manner that is more correct.
For example:
”
mysql> create table t (s1 varchar(5) character set ujis);
Query OK, 0 rows affected (0.26 sec)

mysql> insert into t values (’àÃ’);
Query OK, 1 row affected (0.08 sec)

mysql> select upper(s1),lower(s1) from t;
+———–+———–+
| upper(s1) | lower(s1) |
+———–+———–+
| Àà       | àã        |
+———–+———–+
1 row in set (0.00 sec)
”
(In MySQL 5.1 the result is àÃ, àÃ.)

Worklog WL#4584 Internationalized number format
Current status: Code done 2009-04-01
Version = 6.1
The FORMAT() function will be “FORMAT(X,D [,locale_name] )”, that is, there is an optional third rgument, which affects the decimal point and the thousands separator. For example, a user specifying German locale will get ‘,’ and ‘.’ in the German way:
”
mysql> SELECT format(1234567.89,2,’de_DE’);
+———————-+
| format(1234567.89,2) |
+———————-+
| 1.234.567,89         |
+———————-+
1 row in set (0.00 sec)
”

WL#4616 Implement UTF-16LE
Status: not passed architecture review
Version = 6.1
The UTF16 character set can be big-endian or little-endian, depending whether you like to put the least significant byte first or last. MySQL supports big-endian. The plan is to support UTF-16LE (”Little Endian”) too.

WL#4617 Translate error messages to Tier1 languages
Status: not passed architecture review
Version = 6.1
Error messages need to be added or updated for Chinese Simplified, Japanese, English, French, German, Italian, Spanish. Volunteers welcome.

WL#4642 Greek locale for DAYNAME, MONTHNAME, DATE_FORMAT
Status: passed architecture review 2009-03-02
Version = 6.1
We had a feature request (Bug#39092) and contribution
from Nikolaos Tsarmpopoulos.
The DAYNAME() and MONTHNAME() and DATE_FORMAT() functions will produce appropriate Greek-language results if one sets @@lc_time_names appropriately.

WL#4649 Translate error messages into Tier 2 languages
Status: not passed architecture review
Version = 6.1
Error messages need to be added or updated for Catalan, Danish, Dutch, Finnish, Malay, Norwegian (Bokmål), Norwegian (Nynorsk), Portuguese/Brazil, Portuguese/Portugal, Swedish, Chinese Traditional (Hong Kong, Taiwan), Russian, Greek, Korean, Czech, Polish. Volunteers welcome.

This was the last of three blog postings re Globalization.
Maybe there will be a Birds of a Feather (BoF) about some language issue at the MySQL User Conference, if so we’ll make sure MySQL experts are there.

Yesterday I started to say what the status is for “Globalization” tasks.
Today I continue.

Worklog WL#1875 Case insensitive Czech collation
Status: not passed architecture review
Version = 6.1
This fills a minor gap, since our Czech collations for the cp1250 and latin2 character sets are case sensitive.
This task adds appropriate _ci (case insensitive) collations.

WL#2555 Standard Japanese collation support
Status: not passed architecture review
Version = 6.x
JIS (Japanese Industrial Standard) JIS X 4061-1996 specifies a standard collation for Japanese characters, with multiple levels and special rules.

WL#2673 Unicode Collation Algorithm new version
Status: passed architecture review 2009-03-23
Version = 6.x
MySQL is using Version 4.0.0 of the UCA (Unicode Collation Algorithm). That’s superseded. We’ll make new collations for the more recent UCA version.

WL#3090 Japanese Character Set Adjustments
Status: not passed architecture review
Version = 6.1
This task has shrunk a bit since we didn’t get feedback from the Japanese community for some parts, but there will be error messages for junk characters and a faster cp932-to-sjis conversion algorithm.

WL#3332 Korean Enhancements
Status: not passed architecture review
Version = 6.1
In euckr and cp949 character sets there are some reported discrepancies with standards, and collation feature requests.

WL#3997 New euckr characters
Status: passed code review
Version = 6.1
In the Korean euckr character set allow euro sign and registered sign, (but not Circled Hangul Ieung U i.e. “Postal code mark”).

WL#4013 Unicode german2 collation
Status: in code review
Version = 6.1
A DIN-2 (phone book) collation similar to latin1_german2_ci.

WL#4024 gb18030 Chinese character set
Status: not passed architecture review
Version = 7.1
MySQL can handle Chinese characters using several character sets (big5, gb2312, gbk, and of course all the Unicode sets).
But a newer character set, gb18030, is becoming popular in mainland China. We’ll support it too.

I’ll continue tomorrow.