New Features In MySQL 6.x

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.

For today and tomorrow and the day after tomorrow, I (Peter Gulutzan) will say what the status is for “Globalization” tasks. These tasks meet Sun requirements for software; you could say that they’re moving forward due to Sun’s acquisition. I’ll give examples where the code is already working; for other cases I’ll just say what’s in the description in the worklog task.

WL#751 Error message construction
Current status: architecture review done
Version = 6.1
We’ll produce error messages taking into account the current character set and the user’s choice of language. Expect changes in errmsg.sys, errmsg.txt, internals documentation, string formatting, and @@character_set_results.

WL#897 Accept SQL statements written with UCS2, UTF16, UTF32
Status: not passed architecture review
Version = 6.x
Currently clients pass messages in UTF8, which can be converted to UCS2 or UTF16 or UTF32 by the server (it’s easy because Unicode character sets have the same repertoire). But let’s let clients pass messages in UCS2 or UTF16 or UTF32 from the start, possibly saving a conversion step.

WL#1287 REAL DIN-1 German collation
Status: not passed architecture review
Version = 6.x
Change handling of Sharp S when following DIN-1 (German dictionary) rules with certain character sets.

WL#1349 Use operating system localization to send it as a default client character set
Status: In code review
Version = 6.1
Thus utilities like ‘mysql’ will, instead of the compiled-in character set, try to figure it out via nl_langinfo, GetLocaleInfo, etc. I opposed this, but our support department thinks it will save them time.

Worklog WL#1820 Variant SJIS and UJIS Japanese Character Sets
Current status: not passed architecture review
Version = 6.1
Don’t be fooled into thinking that SJIS and UJIS always mean the same thing to everyone, there are several different encodings for yen sign, reverse solidus, overline and tilde. The weird but only practical solution is to add even more Japanese character sets, with differences only for those characters.

I’ll continue tomorrow.

Developments seem to be accelerating, here’s anecdotal evidence.

Foreign Keys
The Foreign Keys (all engines) worklog task passed another milestone last week: now MySQL is doing referential constraint checks at end of statement when the standard so prescribes, rather than row-by-row. This means that the all-engines functionality is now slightly greater than what’s in MySQL’s current InnoDB-only foreign-key handling.

The closing of Bug#989
“The wheels of the gods grind slowly, but they grind exceedingly fine.” Our Bordeaux-area Maria expert Guilhem Bichot reported Bug#989 “If DROP TABLE while there’s an active transaction, wrong binlog order” in August 2003 and some unkind soul even mentioned it on the wikipedia article about MySQL. The mention is gone now. And so is the bug. I’m being a glass-is-half-full kind of guy these days, so instead of whining “it took a while” I’m exulting “these are the days when things are getting done”. Send thanks to the fixer and the reviewer, their names are on the report.

Performance Schema
The feature I blogged about so extensively last month, the WL#2360: Performance Schema, now has several people working on it: 1 implementor, 1 architect, 2 code reviewers, 1 Quality Assurance reviewer, 1 person from the Enterprise Tools team that puts together the MySQL Enterprise Monitor. The April 20 2009 deadline, for making the code public, is looking very doable.

Character Sets
Some worklog tasks have passed the architecture-review stage and coding is in progress: one which fixes a complaint about certain functions that return BINARY/VARBINARY when CHAR/VARCHAR would do, one for a change to FORMAT() so that for example the number 1.23 might appear as 1,23 with comma instead of decimal point in some locales, one for Latin9 (iso-8859-15) character set, one for Error message construction that will eventually replace the unwieldy errmsg system we have now. The worker on this is Alexander Barkov, and if you want to ask him how’s it going, meet him at the MySQL User Conference in three weeks.