New Features In MySQL 6.x

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.

It’s certain now that Peter Gulutzan will be at the MySQL User Conference, live on stage.

Details within a few days.

“Leap seconds” are starting to cause questions on our forums and in some bug reports because ‘2008-12-31 23:59:60′ will be the first leap second in the last 3 years. We have a short answer and a long answer.

The short answer is: it will only cause a problem if you
(
(
(a) arrange so your operating system returns the leap-second value for MySQL to see,
or
(b) load the appropriate ‘leap second’ tables as described in our manual,
)
and
(c) do something which uses the NOW() or CURRENT_TIMESTAMP at precisely midnight UTC on December 31 (remember your local time is not UTC time!).
)
or
use CONVERT_TZ() or FROM_UNIXTIME().

Even if those unlikely things occur, you can kludge the results next day. So don’t worry. My colleague Dmitri Lenev points out: there are leap -second geeks who are configuring their systems to have non-posix unix time/zoneinfo with leap seconds included and only they are affected.

The long answer, for the people who insist on worrying anyway or who are just curious about leap seconds in SQL and/or MySQL, is the rest of this article.

GENERAL INFORMATION

If you haven’t heard about leap seconds before, try these articles first:

http://www.ucolick.org/~sla/leapsecs/HTMLutcdoomed.html
(My favourite line from this is: the rotating earth is decelerating at a rate of 1.7 milliseconds per day per century, but not in a regular way.

http://www.airspacemag.com/flight-today/One-More-Second.html
(My favourite line from this is: leap seconds are affected by “thinning of glaciers caused by global warming”, among other things.)

http://kbase.redhat.com/faq/docs/DOC-15153
(My favourite line from this is: Red Hat handles leap seconds by making ‘23:59:59′ twice as long, which is inspiring. But I don’t have information about every Linux installation. Ask your OS vendor.)

My colleague Magne Mæhre points out: ” mostly it’s due to a sloppy estimation of the natural frequency of the caesium atom. When the SI second was introduced, it was defined to be the duration of 9 192 631 770 cycles of the radiation (…) of the Cs-133 atom. The problem is that this has nothing to do with the rotation of the earth If the second had been defined as 9 192 631 997 periods instead, we would only have had three leap seconds (2 positive and 1 negative) since 1972, instead of the 22 (all positive) we’ve had.”

LOOK AT YOUR MYSQL INSTALLATION

For some platforms, MySQL supplies “Time zone description tables” http://dev.mysql.com/downloads/timezones.html

If you didn’t upload a time-zone and leap-second table, then you’re looking at a case for which there’s no reported problem.

To find out what’s in your leap-second table, say:
select transition_time,from_unixtime(transition_time),correction
from mysql.time_zone_leap_second;
If the result looks like this:

+-----------------+--------------------------------+------------+
| transition_time | from_unixtime(transition_time) | correction |
+-----------------+--------------------------------+------------+
|        78796800 | 1972-06-30 18:00:00            |          1 |
|        94694401 | 1972-12-31 17:00:01            |          2 |
...
|       867715220 | 1997-06-30 18:00:20            |         21 |
|       915148821 | 1998-12-31 17:00:21            |         22 |
|      1136073622 | 2005-12-31 17:00:22            |         23 |
+-----------------+--------------------------------+------------+
23 rows in set (0.00 sec)

then your table has not been updated for the leap second.

SUPPORT OF LEAP SECONDS BY OTHER DBMS VENDORS

Oracle won’t accept a timestamp literal with a leap second. I tried
insert into t values (timestamp ‘2005-12-31 23:59:60′);
The result was “ORA-1852: seconds must be between 0 and 59″. That’s not an abnormal limitation. The SQL Server 2008 manual says:
“Using hour 24 to represent midnight and leap second over 59 as defined by ISO 8601 (5.3.2 and 5.3) are not supported to be backward compatible and consistent with the existing date and time types. They are not defined by SQL standard 2003.”
http://msdn.microsoft.com/en-us/library/bb677243.aspx
I only found one vendor document which clearly claims support for leap seconds: for Informix:
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.docnotes.doc/xc8w4/ids_sqls_docnotes_10.0.html

STANDARD SQL

Microsoft isn’t telling the whole story when it says “not defined by SQL standard 2003″. The SQL standard doesn’t define, but it allows:
“… sometimes, but very rarely, a particular minute will contain exactly 59, 61, or 62 seconds. Whether an SQL-implementation supports leap seconds, and the consequences of such support for date and interval arithmetic, is implementation-defined.”
In another place, it says that valid values for the SECOND field in a datetime are between 00 and 61.9. It’s unfortunately true that when talking about casting to INTERVAL targets the standard says
“according to the natural rules for intervals as defined in the Gregorian calendar (that is, there are 60 seconds in a minute”.
Well, Pope Gregory XIII would be surprised to hear that he said that, but that’s only about normalizing interval literals, we can ignore it.

TIME ZONE AGNOSTICISM

According to Wikipedia. Leap seconds occur “simultaneously worldwide; for example, the leap second on December 31, 2005 occurred at 23:59:60 UTC. This was 6:59:60 p.m. U.S. Eastern Standard Time and 0:59:60 a.m. on January 1, 2006 Central European Time.” Therefore MySQL might not always say that ‘22:59:60′ is invalid.
But these things are still invalid:
* (datetime/timestamp < ‘2009-05-31′
and (month,day) not between ‘12-31 11:00:00′ and 01-01 13:00′)
and (month,day) not between ‘05-31 11:00:00′ and 06-01 13:00′))
or datetime/timestamp < ‘1971-12-31′
* datetime/timestamp < ‘2009-05-31′
and second > 60 /* because there has never been a 2-second jump */
* MINUTES not in (44,59,14,29)
and second > 59 /* because zone offsets like ‘12 minutes’ don’t occur */
* datetime/timestamp in UTC doesn’t end in 23:59:60′
and second > 59 /* if user actually specifies a time zone */
None of the above checks depend on actual access to time zone tables. Therefore, getting invalid data in is hard, except via NOW() or CURRENT_TIMESTAMP at the aforementioned moment.

INTERVAL ARITHMETIC

Currently I get this:

mysql> select timestampdiff(second,'2009-01-01 00:00:00','2008-12-31 23:59:59');
+-------------------------------------------------------------------+
| timestampdiff(second,'2009-01-01 00:00:00','2008-12-31 23:59:59') |
+-------------------------------------------------------------------+
|                                                                -1 |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

Some might say that it should be -2, or at least Bug#40610 “Date and time functions like DATE_SUB(), DATE_ADD() are not leap second enabled.” said o, because -1 is “not what people expect”.

I disagree for these reasons:
* I’m not so sure “what people expect”.
* The effect will be: if you have an up-to-date leap seconds table,
you’ll get -2, but otherwise you’ll get -1. This could hurt replication
more than it helps.

Therefore I claimed that we should dismiss bug#40610 as an unreasonable feature request, ignore leap seconds in all interval calculations, and say that ‘2008-12-31 23:59:60′ = ‘2008-12-31 23:59:59′. It turned out there’s an even easier solution, though.

MAKETIME

Currently I get this:

mysql> select maketime(23,59,60);
+--------------------+
| maketime(23,59,60) |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

In other words, MAKETIME treats ‘23:59:60′ as invalid.

STR_TO_DATE

Currently I get this:

mysql> SELECT STR_TO_DATE('2008-12-31 23:59:60', '%Y-%m-%d %H:%i:%S');
+---------------------------------------------------------+
| STR_TO_DATE('2008-12-31 23:59:60', '%Y-%m-%d %H:%i:%S') |
+---------------------------------------------------------+
| NULL                                                    |
+---------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

In other words, STR_TO_DATE treats ‘23:59:60′ as invalid.

UNIX_TIMESTAMP

Currently I get this:

mysql> select unix_timestamp('2008-12-31 23:59:60');
+---------------------------------------+
| unix_timestamp('2008-12-31 23:59:60') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

I think we could have accepted that unix_timestamp(’2008-12-31 23:59:60′) would return different values on different systems. If there is a leap second table available, then the response could be the ‘:60′ value. If there is no leap second table available, then the response should not be 0, it should be the same as for 2008-12-31 23:59:59. in my opinion.
But it’s not. Oh, well.

UPDATING MYSQL.TIME_ZONE_LEAP_SECOND TABLE

For some platforms, MySQL supplies “Time zone description tables”
http://dev.mysql.com/downloads/timezones.html
Probably, at time of reading, your copy is not up to date! 2008-12-31 is not there! Also I expect that operating-system vendors’ tables are not up to date, or else that users haven’t updated lately. A similar thing happened with the time-zone tables last year, when some countries decided to change the dates that daylight saving time begins and ends.
I’d say it’s up to each user to check for update bulletins every six months from http://hpiers.obspm.fr/iers/bul/bulc/.

CONNECTORS

I am reasonably confident that there would be no problems moving ‘2008-12-31 23:59:60′ back and forth from C and Java programs, although I suppose somebody should ask if there is any JDBC problem retrieving completely invalid values like ‘2008-12-01 23:45:61′. And I have noticed that some common C structures e.g. in time.h
http://www.uwm.edu/cgi-bin/IMT/wwwman?topic=ctime(3)&msection= say that the maximum number of seconds is 60, not 61. MySQL should be rejecting such things, but I can’t be sure of every possible case.

STORAGE ENGINES

We know of no problems with our own storage engines. In theory there could be a storage engine which stores dates and datetimes in a compressed form that doesn’t allow for 62 seconds per minute.

EVENTS

An event created with
create event e on schedule every 1 second starts ‘2008-12-31 23:59:59′ do set @a=5;
should happen at 23:59:59 but not at 23:59:60.

A GENERAL OBSERVATION

My colleague Georgi Kodinov points out: in MySQL date arithmetic in general (when using DATE/TIME/DATETIME columns) is done according to our internal understanding of the UTC time zone (see Bug#40329 “TIMESTAMPADD does not take in to account Daylight Savings Time”). If you need time-zone dependent calculations you’ll need to convert to local time, do the math and then convert back it seems.

THE FIX

As a fix, we’re supplying a patched version of the MySQL server. Its NOW() function will return 23:59:59 twice on December 31. That’s not exactly the right answer, but it sure saves a lot of trouble.

You can get this fix as a source code patch now, just look at the comments for Bug#39920 “MySQL cannot deal with Leap Second expression in string literal.” I’m hoping that MySQL will put this in binaries soon, but I don’t guarantee such things.

The alternative is to do an “after the fact” patch when you head into the office on January 1 2009. You can find and get rid of the value in affected columns thus:
UPDATE affected_table
SET affected_column = CONCAT(LEFT(affected_column,LENGTH(affected_column)-2),’59′)
WHERE RIGHT(affected_column,2) = ‘60′;

REFERENCES

Bug#6387 Queried timestamp values do not match the inserted value (closed in 2004)
Bug#18883 Error fetching TIME columns > 23:59:59
Bug#39920 MySQL cannot deal with Leap Second expression in string literal.
Bug#40610 Date and time functions like DATE_SUB(), DATE_ADD() are not leap second enabled.
“RE: leap second” http://lists.mysql.com/internals/36100

Last month I went to the Sun Microsystems / MySQL Developer Conference in Riga Latvia. Along with about 250 other people. The majority were MySQL developers, but as usual we had several attendees from MySQL support and administration, and a few non-MySQL people came to talk about Google, Drizzle, and PBXT. This was one of our annual “internal” conferences, so lots of stuff that we talked about in Riga will only be publicly announced around, say, the time of the next MySQL User Conference . I’ll just talk about the stuff that’s already leaked, and about my own involvements.

First let me give my impression of ‘morale’. It’s up, way up, since two years ago. I attribute this to two causes: removal of a problem, and a better team.

Huh? Haven’t I read non-MySQLers’ blogs about people leaving the team? Sure, and I mumble to myself ‘get a grip, get a sense of proportion’ when I read them. All organizations have comings and goings. Some of the departures are exaggerated; for example Jim Starkey hasn’t totally left us. Others won’t affect development because the departers haven’t been contributing to the code base significantly for a long time, they’ve concentrated on evangelism or visionary stuff. More people have joined than have left, and I wish it was obvious how high-quality the new ones are.
* We have several new managers from “Classic Sun” with experience and training as managers. I laud the pure-old-MySQL Team Leads who were running some things before, but they were seeing supervisory or project-administration tasks as burdens, and the ones I’ve talked to seemed glad that their trophies at last they lay down.
* We have other Sun teams helping us out with performance analysis (especially on SPARC or Solaris), and with putting together builds on exotic platforms (MySQL has several million machines available for community testing but having direct access to more machines is a plus too).
* We have new people joining MySQL development teams from other Sun departments. I’ve hesitated to say that the difference so far has been significant, but orientation time for a developer is always several months, and now that we old-guarders have met these ‘newbies’ we’re happy. I lost count of the corridor conversations I overheard that went something like “hey, so-and-so seems to be what we needed for [online backup] [optimizer] [fill-in-the-blank]“.

Other things that contributed to my personal morale were the great hotel facilities, the fact that Georg Richter (Development Manager - Connectors and Client Connectivity Engineering) made sure all the Russians got visas this time (I really missed seeing the whole gang on a previous occasion), and the sound of people actually discussing the work that I think is important. Our idea of having developers working out of home offices worldwide is great, but at least once a year we have to interact with people instead of with IRC chat lines and Skype noises. I’m less isolated than most since I’m not the only MySQLer in Edmonton Canada; however, man is a social animal.

The big theme for all-hands talk was MySQL version 5.1; don’t get the wrong impression from me; I was cheering along with the crowd when our fearless leaders exhorted us to give 5.1 all our waking thoughts. And now I’ll drop that topic since this is a MySQL-6.x blog.

The high point of the conference was a slide show by MySQL 6.x release architect Peter Gulutzan (that’s me) about what’s in the 6.x roadmap. Community Relations Manager Lenz Grimmer took a few pictures, here and here. I’d like to say it was well received, and perhaps most objective observers would say that it was. But our technical discussions do sometimes lead to what diplomats call ‘a frank exchange’. Updating from what’s been said earlier for 6.x: (1) maybe we’re using data types BINARY and VARBINARY excessively and maybe the fixing should happen before I thought it would; (2) specific references about optimizer-related tasks should now be regarded as uncertain; (3) online backup might occur with a different schedule, although this mostly means that things will happen sooner (my next blog post will discuss one of the new online-backup features that we settled in Riga); (4) Sun has ‘globalization requirements’ that we have to meet with 6.1 so that means more character-set and collation tasks for Alexander Barkov (Technical Lead of Localization); (5) there are new committees charged with commenting about new features or overriding decisions made by other committees. I should re-explain here that we often use the term ‘6.x’ for ‘MySQL 6.1, 6.2, or [if necessary] 6.3′ not 6.0.

A historic occasion: I and Senior Software Developer Marc Alff gave the first-time-ever live demo of MySQL’s SIGNAL and RESIGNAL statements, and of a bombshell project that’s so neat we will defer discussion till we know how to describe it better. A picture is here. That’s me in the background demoing to Senior QA Architect Omer BarNir. Marc is the French-looking guy with the rapt audience. One question that I had to face often was “since it works today why will you wait many months to release it?” but I fended that off with the usual that’s-not-my-department shrugging.

Another historic occasion: the “WL#148 (Implement Foreign Keys (all storage engines)” project reached Milestone 7. This project is approximately on schedule. That’s a good thing because soon we’re going to have to say what’s in ‘6.1′ as opposed to the vague ‘6.x’ designation, and I see no reason why WL#148 will be anything but a flagship feature in that release.

The two big developments in the SQL world this summer are the pending arrival of the SQL:2008 ANSI standard, and my (Peter Gulutzan’s) attendance on Sun’s behalf at an ANSI meeting. Okay, I concede that the events have unequal importance, but sometimes I slip in a bit about personal involvements.

What’s a standard?

Review: ANSI (which stands for “INCITS Technical Committee H2 on Database”), and its counterparts in many other countries, produce official standards for many things, including SQL. The international co-ordinating body is ISO (International Organization for Standards), so there’s an ANSI standard and an ISO standard, and they’re practically the same. There’s a partially-restricted site containing ISO papers here.

The current edition is ANSI/ISO SQL:2003. But SQL:2008 is only a few days or weeks away. I’m taking a risk here by jumping the gun and saying something about it this early, but I must take the risk, there’s a time constraint that forces me to state this week what we Sun folk know about SQL:2008 and what we’re doing about it.

Differences between SQL:2003 and SQL:2008

SQL is stable. We can expect new editions to have only small changes. As with any product, the changes are of two types: fixes and new features.

FIXES. Quite a few bugs are now corrected or at least some clarification has happened. They’re of no great importance now that they’re dead, but I’d like to (ahem) just observe that a small number of them were found by MySQL AB personnel. I’m proud that we played a part, however trivial, and glad that the standard editor took an “open source” attitude when deciding whether to incorporate the things I sent him.

NEW FEATURES. There’s a section at the end of Part II called Appendix F SQL feature taxonomy. By comparing the SQL:2003 and SQL:2008 files I was able to see that around 60 small non-core optional features are new. Everybody will have a different idea which ones matter. My own choice is the ones that look relevant to MySQL.

F123 all diagnostics. Allows “GET DIAGNOSTICS target = ALL”. Support for GET DIAGNOSTICS isn’t in our plans for real soon now, but it’s inevitable. The mysqlforge task description is WL#2111 Stored Procedures: Implement GET DIAGNOSTICS.
F200 TRUNCATE TABLE statement. See the earlier article “TRUNCATE now and unto ages of ages”.
F382 alter column data type. Allows “ALTER … SET DATA TYPE data type”. MySQL can already alter data type, but with non-standard syntax “ALTER … MODIFY …”.
F763 CURRENT_SCHEMA. Allows “SELECT CURRENT_SCHEMA …”. MySQL can already select this, but with non-standard syntax “SELECT DATABASE() …”. See the feature request in MySQL’s bugs database, “Please implement a CURRENT_DATABASE()/CURRENT_SCHEMA() function”.
F852 Top-level order by clause in views. Allows “CREATE VIEW … ORDER BY …”. MySQL can already do this.
F857 Top-level fetch first clause in query expression; F865 offset row count in result offset clause. Allows picking how many rows to select, and where to start, with “SELECT … OFFSET … FIRST | NEXT”. MySQL can already pick, but with non-standard syntax “SELECT … LIMIT …”.
T021 BINARY and VARBINARY data types. See the earlier article: “Tiny Tweak: BINARY and VARBINARY”.
T023 Compound binary literals. Allows concatenation of hexits in binary string literals. This isn’t in MySQL’s plans, though we’ve talked about it.
T213 INSTEAD OF triggers. If you design an INSTEAD OF trigger on a view, then delete from the view, the rows aren’t deleted but the trigger is activated — and the trigger might delete the rows that you want. We’ve had a customer request for this.
T285 Enhanced derived column names. This clears up some rules for automatic assignment of column names in a select list, when there’s no AS clause.

I’ll add as well that there’s a reference to the “current” Unicode standard. That’s not a change in the wording, but the meaning of “current” is now Unicode 5.1. Well, we’ve just added support for Unicode 5.0 in MySQL 6.0. Keeping up with other standards can muss our scheduling, but we’ve added a worklog task for Unicode 5.1 and we’ll see what happens to it. See the earlier article “MySQL 6.0 Feature #1: Supplementary Characters”.

Joining the committee

The work on the standard will continue, so that there will be fixes and feature changes for a new standard in a few years. The interesting question now facing us is: Will Sun Microsystems join ANSI?

A few weeks ago some committee members were kind enough to invite me to sit in as an observer for one of the regular ANSI meetings. I learned a lot about the mechanics and interrelationships (though I have no scoops about new happenings that aren’t public). It was gently noted a few times that here was a gathering of all the behemoths of the database industry, except one: Sun.

Well, it was natural for MySQL to stay out. (Time to confess now: we weren’t a behemoth, more a micro-behemoth.) Before the acquisition, we were a relatively small outfit and relatively cloutless. Sun is a different sort of organization, though. And Sun does join other committees, for example it plays a big role with Unicode and (duh!) Java standards. Equally importantly, Sun is serious about standards to an extent that some MySQL old-timers weren’t. Probably this has a lot to do with the influence of big enterprise customers in the new field that we’re playing in.

This attitude will I hope be useful in other points of MySQL 6.x development, and I’ll keep you posted about “MySQL and progress toward the SQL standard” in this blog. But I’m going to emphasize that the attitude has an effect on a lot more than MySQL. Sun does JavaDB. Sun even does PostgreSQL … it would be interesting to join the committee and act as a conduit for another open-source DBMS’s suggestions under Sun’s aegis. But any of Sun’s many PostgreSQL experts could do that better than a MySQL expert could.

I’m mildly warm to the concept of Sun joining. But now it’s time for a boilerplate disclaimer: “Any opinions in this blog do not represent the opinions of anyone official at Sun Microsystems.” The only thing I’m saying is that we’re thinking about it, and gathering opinions.

Instructions for adding a new Unicode collation

By Alexander Barkov and Peter Gulutzan
Copyright (c) 2007 by MySQL AB
All rights reserved

[ Previously published as a note on a bug report.]

MySQL offers 42 built-in collations (sets of rules
for comparison and sorting) for the Unicode character
sets utf8 and ucs2. But you can add your own.

We will describe here how to add Vietnamese Unicode
collations. This is intended as an example, so
that you can try out all the steps and then modify
based on your own rules for some other language.
We chose Vietnamse because many people asked for it
via our "Feature Request" option in bugs.mysql.com,
http://bugs.mysql.com/bug.php?id=4745.

There are two ways to add a new collation:
* Change the source code. This is the trickier way,
but it's the only way for pre-August-2007 versions.
* Change the LDML "markup" file. This hasn't been
tested much, but it's simpler, it requires no
recompilation. (LDML stands for Locale Data Markup
Language, which provides a formal way to specify
ordering.)

We will describe both ways.

ADDING A NEW COLLATION BY CHANGING THE SOURCE CODE

In this section we will describe how to change
MySQL source code to add Vietnamese Unicode collations.

To understand this, you will have to know how to
download source code and how to build the server.
(The MySQL Reference Manual explains all that).
These instructions should work for versions 4.1,
5.0, and 5.1.

"(Note for MySQL 4.1 users only: the first file to edit will be
configure.in rather than config/ac-macros/character_sets.m4,
and in subsequent files the last defined language will probably
be Persian rather than Hungarian, and when editing ctype-uca.c
you shouldn't add the lines that have 'caseinfo' or
'caseup_multiply' or 'casedn_multiply' or pad char' in them.)"

1. Edit config/ac-macros/character_sets.m4

Look for lines approximately like this:
"
      define(UCSC1, ucs2_general_ci ucs2_bin)
      define(UCSC2, ucs2_czech_ci ucs2_danish_ci)
      define(UCSC3, ucs2_esperanto_ci ucs2_estonian_ci ucs2_icelandic_ci)
      define(UCSC4, ucs2_latvian_ci ucs2_lithuanian_ci)
      define(UCSC5, ucs2_persian_ci ucs2_polish_ci ucs2_romanian_ci)
      define(UCSC6, ucs2_slovak_ci ucs2_slovenian_ci)
      define(UCSC7, ucs2_spanish2_ci ucs2_spanish_ci)
      define(UCSC8, ucs2_swedish_ci ucs2_turkish_ci)
      define(UCSC9, ucs2_unicode_ci)
"
Change the last line to
"
      define(UCSC9, ucs2_unicode_ci ucs2_vietnamese_ci)
"
(Collations are in alphabetic order so "vietnamese" comes after
"unicode".)

Look for lines approximately like this:
"
        define(UTFC1, utf8_general_ci utf8_bin)
        define(UTFC2, utf8_czech_ci utf8_danish_ci)
        define(UTFC3, utf8_esperanto_ci utf8_estonian_ci utf8_icelandic_ci)
        define(UTFC4, utf8_latvian_ci utf8_lithuanian_ci)
        define(UTFC5, utf8_persian_ci utf8_polish_ci utf8_romanian_ci)
        define(UTFC6, utf8_slovak_ci utf8_slovenian_ci)
        define(UTFC7, utf8_spanish2_ci utf8_spanish_ci)
        define(UTFC8, utf8_swedish_ci utf8_turkish_ci)
        define(UTFC9, utf8_unicode_ci)
"
Change the last line to
"
        define(UTFC9, utf8_unicode_ci utf8_vietnamese_ci)
"

2. Edit mysys/charset-def.c

Look for lines approximately like this:
"
#ifdef HAVE_CHARSET_ucs2
extern CHARSET_INFO my_charset_ucs2_general_uca;
extern CHARSET_INFO my_charset_ucs2_icelandic_uca_ci;
extern CHARSET_INFO my_charset_ucs2_latvian_uca_ci;
... (etc.)
extern CHARSET_INFO my_charset_ucs2_hungarian_uca_ci;
#endif
"
Add this line before the #endif:
"
extern CHARSET_INFO my_charset_ucs2_vietnamese_uca_ci;
"

Look for lines approximately like this:
"
#ifdef HAVE_CHARSET_utf8
extern CHARSET_INFO my_charset_utf8_general_uca_ci;
extern CHARSET_INFO my_charset_utf8_icelandic_uca_ci;
extern CHARSET_INFO my_charset_utf8_latvian_uca_ci;
... (etc.)
extern CHARSET_INFO my_charset_utf8_hungarian_uca_ci;
#endif
"
Add this line before the #endif:
"
extern CHARSET_INFO my_charset_utf8_vietnamese_uca_ci;
"

Look for lines approximately like this:
"
#ifdef HAVE_UCA_COLLATIONS
  add_compiled_collation(&my_charset_ucs2_general_uca);
  add_compiled_collation(&my_charset_ucs2_icelandic_uca_ci);
  add_compiled_collation(&my_charset_ucs2_latvian_uca_ci);
... (etc.)
  add_compiled_collation(&my_charset_ucs2_hungarian_uca_ci);
#endif
"
Add this line before the #endif:
"
  add_compiled_collation(&my_charset_ucs2_vietnamese_uca_ci);
"

Look for lines approximately like this:
"
#ifdef HAVE_UCA_COLLATIONS
  add_compiled_collation(&my_charset_utf8_general_uca_ci);
  add_compiled_collation(&my_charset_utf8_icelandic_uca_ci);
  add_compiled_collation(&my_charset_utf8_latvian_uca_ci);
... (etc.)
  add_compiled_collation(&my_charset_utf8_hungarian_uca_ci);
#endif
"
Add this line before the #endif:
"
  add_compiled_collation(&my_charset_utf8_vietnamese_uca_ci);
"

3. Edit strings/ctype-uca.c

This is where you have to use your knowledge of Vietnamese.
We know nothing. We just believe what Vietnamese people said
in http://vietunicode.sourceforge.net/charset/vietalphabet.html.

Look for lines approximately like this:
"
/*
  A simplified version of Hungarian, without consonant contractions.
*/
static const char hungarian[]=
    "&O < \\u00F6 <<< \\u00D6 << \\u0151 <<< \\u0150"
    "&U < \\u00FC <<< \\u00DC << \\u0171 <<< \\u0170";
"
This is an example of "tailoring" following a subset of
"ICU Collation Customization (tailorings)". It has rules
for expressing the difference from the usual UCA collation.
Add these lines:
(NB: each quoted string is on a single line, turn word wrapping off.)
"
/*
  Vietnamese experiment. Not for use in production systems.
*/
static const char vietnamese[]=
    "& A < \\u0103 <<< \\u0102 << \\u1EB1 <<< \\u1EB0 << \\u1EB3 <<< \\u1EB2 << \\u1EB5 <<< \\u1EB4 << \\u1EAF <<< \\u1EAE << \\u1EB7 <<< \\u1EB6"
       " < \\u00E2 <<< \\u00C2 << \\u1EA7 <<< \\u1EA6 << \\u1EA9 <<< \\u1EA8 << \\u1EAB <<< \\u1EAA << \\u1EA5 <<< \\u1EA4 << \\u1EAD <<< \\u1EAC"
    "& D < \\u0111 <<< \\u0110"
    "& E < \\u00EA <<< \\u00CA << \\u1EC1 <<< \\u1EC0 << \\u1EC3 <<< \\u1EC2 << \\u1EC5 <<< \\u1EC4 << \\u1EBF <<< \\u1EBE << \\u1EC7 <<< \\u1EC6"
    "& O < \\u00F4 <<< \\u00D4 << \\u1ED3 <<< \\u1ED2 << \\u1ED5 <<< \\u1ED4 << \\u1ED7 <<< \\u1ED6 << \\u1ED1 <<< \\u1ED0 << \\u1ED9 <<< \\u1ED8"
       " < \\u01A1 <<< \\u01A0 << \\u1EDD <<< \\u1EDC << \\u1EDF <<< \\u1EDE << \\u1EE1 <<< \\u1EE0 << \\u1EDB <<< \\u1EDA << \\u1EE3 <<< \\u1EE2"
    "& U < \\u01B0 <<< \\u01AF << \\u1EEB <<< \\u1EEA << \\u1EED <<< \\u1EEC << \\u1EEF <<< \\u1EEE << \\u1EE9 <<< \\u1EE8 << \\u1EF1 <<< \\u1EF0";
"

Look for lines approximately like this:
"
CHARSET_INFO my_charset_ucs2_hungarian_uca_ci=
{
    146,0,0,            /* number       */
    MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE,
    "ucs2",             /* cs name    */
    "ucs2_hungarian_ci",/* name         */
    "",                 /* comment      */
    hungarian,          /* tailoring    */
    NULL,               /* ctype        */
    NULL,               /* to_lower     */
    NULL,               /* to_upper     */
    NULL,               /* sort_order   */
    NULL,               /* contractions */
    NULL,               /* sort_order_big*/
    NULL,               /* tab_to_uni   */
    NULL,               /* tab_from_uni */
    my_unicase_default, /* caseinfo     */
    NULL,               /* state_map    */
    NULL,               /* ident_map    */
    8,                  /* strxfrm_multiply */
    1,                  /* caseup_multiply  */
    1,                  /* casedn_multiply  */
    2,                  /* mbminlen     */
    2,                  /* mbmaxlen     */
    9,                  /* min_sort_char */
    0xFFFF,             /* max_sort_char */
    ' ',                /* pad char      */
    0,                  /* escape_with_backslash_is_dangerous */
    &my_charset_ucs2_handler,
    &my_collation_ucs2_uca_handler
};
"
Add these lines:
"
CHARSET_INFO my_charset_ucs2_vietnamese_uca_ci=
{
    147,0,0,            /* number       */
    MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE,
    "ucs2",             /* cs name    */
    "ucs2_vietnamese_ci",/* name         */
    "",                 /* comment      */
    vietnamese,         /* tailoring    */
    NULL,               /* ctype        */
    NULL,               /* to_lower     */
    NULL,               /* to_upper     */
    NULL,               /* sort_order   */
    NULL,               /* contractions */
    NULL,               /* sort_order_big*/
    NULL,               /* tab_to_uni   */
    NULL,               /* tab_from_uni */
    my_unicase_default, /* caseinfo     */
    NULL,               /* state_map    */
    NULL,               /* ident_map    */
    8,                  /* strxfrm_multiply */
    1,                  /* caseup_multiply  */
    1,                  /* casedn_multiply  */
    2,                  /* mbminlen     */
    2,                  /* mbmaxlen     */
    9,                  /* min_sort_char */
    0xFFFF,             /* max_sort_char */
    ' ',                /* pad char      */
    0,                  /* escape_with_backslash_is_dangerous */
    &my_charset_ucs2_handler,
    &my_collation_ucs2_uca_handler
};
"
The "number" must be greater than the last
"number" for ucs2. At the time of writing, the
last ucs2 collation is "Hungarian" and its number is
146 so we're adding 147 here. But this is transient!
You'll see this number with SHOW COLLATION, illustrated later.

Look for lines approximately like this:
"
CHARSET_INFO my_charset_utf8_hungarian_uca_ci=
{
    210,0,0,            /* number       */
    MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE,
    "utf8",             /* cs name    */
    "utf8_hungarian_ci",/* name         */
    "",                 /* comment      */
    hungarian,          /* tailoring    */
    ctype_utf8,         /* ctype        */
    NULL,               /* to_lower     */
    NULL,               /* to_upper     */
    NULL,               /* sort_order   */
    NULL,               /* contractions */
    NULL,               /* sort_order_big*/
    NULL,               /* tab_to_uni   */
    NULL,               /* tab_from_uni */
    my_unicase_default, /* caseinfo     */
    NULL,               /* state_map    */
    NULL,               /* ident_map    */
    8,                  /* strxfrm_multiply */
    1,                  /* caseup_multiply  */
    1,                  /* casedn_multiply  */
    1,                  /* mbminlen     */
    3,                  /* mbmaxlen     */
    9,                  /* min_sort_char */
    0xFFFF,             /* max_sort_char */
    ' ',                /* pad char      */
    0,                  /* escape_with_backslash_is_dangerous */
    &my_charset_utf8_handler,
    &my_collation_any_uca_handler
};
"
Add these lines:
"
CHARSET_INFO my_charset_utf8_vietnamese_uca_ci=
{
    211,0,0,            /* number       */
    MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE,
    "utf8",             /* cs name    */
    "utf8_vietnamese_ci",/* name         */
    "",                 /* comment      */
    vietnamese,         /* tailoring    */
    ctype_utf8,         /* ctype        */
    NULL,               /* to_lower     */
    NULL,               /* to_upper     */
    NULL,               /* sort_order   */
    NULL,               /* contractions */
    NULL,               /* sort_order_big*/
    NULL,               /* tab_to_uni   */
    NULL,               /* tab_from_uni */
    my_unicase_default, /* caseinfo     */
    NULL,               /* state_map    */
    NULL,               /* ident_map    */
    8,                  /* strxfrm_multiply */
    1,                  /* caseup_multiply  */
    1,                  /* casedn_multiply  */
    1,                  /* mbminlen     */
    3,                  /* mbmaxlen     */
    9,                  /* min_sort_char */
    0xFFFF,             /* max_sort_char */
    ' ',                /* pad char      */
    0,                  /* escape_with_backslash_is_dangerous */
    &my_charset_utf8_handler,
    &my_collation_any_uca_handler
};
"
The "number" must be greater than the last
"number" for utf8. At the time of writing, the
last utf8 collation is "Hungarian" and its number is
210 so we're adding 211 here. But this is transient!
You'll see this number with SHOW COLLATION, illustrated later.

4. Build!

If all went well, after building you'll have two new
collations. They will be in information_schema.collations.

If all went well, after building you'll have two new
collations. They will be in the metadata. For example:

mysql> show collation like '%vietnamese%';
+--------------------+---------+-----+---------+----------+---------+
| Collation          | Charset | Id  | Default | Compiled | Sortlen |
+--------------------+---------+-----+---------+----------+---------+
| utf8_vietnamese_ci | utf8    | 209 |         | Yes      |       8 |
| ucs2_vietnamese_ci | ucs2    | 145 |         | Yes      |       8 |
+--------------------+---------+-----+---------+----------+---------+
2 rows in set (0.00 sec)

CHARACTER SET utf8 COLLATE utf8_vietnamese_ci will work.
CHARACTER SET ucs2 COLLATEE ucs2_vietnamese_ci will work.

ADDING A NEW COLLATION BY CHANGING THE MARKUP FILE

In this section we will describe how to change
an LDML file to add Vietnamese Unicode collations.

To understand this, you will have to know how what
an LDML file is. (The Unicode Consortium explains
that in their Locale Data Markup Language page,
http://unicode.org/reports/tr35/.)

These instructions apply to MySQL versions 5.0.44
or higher and 5.1.20 or higher. They might work
with earlier versions, but we don't recommend that
you try. They should work for most operating
systems including Windows.

1. Open MySQL character set definition file
(usually /usr/share/mysql/charsets/Index.xml,
but its location may vary depending on build flags).

2. Find this code:

<charset name="ucs2">
   <family>Unicode</family>
   <description>UCS-2 Unicode</description>
   <collation name="ucs2_general_ci" id="35">
     <flag>primary</flag>
     <flag>compiled</flag>
   </collation>
   <collation name="ucs2_bin" id="90">
     <flag>binary</flag>
     <flag>compiled</flag>
   </collation>
</charset>

3. Change it to:

<charset name="ucs2">
   <family>Unicode</family>
   <description>UCS-2 Unicode</description>
   <collation name="ucs2_general_ci" id="35">
     <flag>primary</flag>
     <flag>compiled</flag>
   </collation>
   <collation name="ucs2_bin" id="90">
     <flag>binary</flag>
     <flag>compiled</flag>
   </collation>
   <collation name="ucs2_vietnamese_ci" id="147">
     <!-- Vietnamese experimental collation -->
     <rules>
       <reset>A</reset>
       <p>\u0103</p><t>\u0102</t>
       <s>\u1EB1</s><t>\u1EB0</t>
       <s>\u1EB3</s><t>\u1EB2</t>
       <s>\u1EB5</s><t>\u1EB4</t>
       <s>\u1EAF</s><t>\u1EAE</t>
       <s>\u1EB7</s><t>\u1EB6</t>
       <p>\u00E2</p><t>\u00C2</t>
       <s>\u1EA7</s><t>\u1EA6</t>
       <s>\u1EA9</s><t>\u1EA8</t>
       <s>\u1EAB</s><t>\u1EAA</t>
       <s>\u1EA5</s><t>\u1EA4</t>
       <s>\u1EAD</s><t>\u1EAC</t>
       <reset>D</reset>
       <p>\u0111</p><t>\u0110</t>
       <reset>E</reset>
       <p>\u00EA</p><t>\u00CA</t>
       <s>\u1EC1</s><t>\u1EC0</t>
       <s>\u1EC3</s><t>\u1EC2</t>
       <s>\u1EC5</s><t>\u1EC4</t>
       <s>\u1EBF</s><t>\u1EBE</t>
       <s>\u1EC7</s><t>\u1EC6</t>
       <reset>O</reset>
       <p>\u00F4</p><t>\u00D4</t>
       <s>\u1ED3</s><t>\u1ED2</t>
       <s>\u1ED5</s><t>\u1ED4</t>
       <s>\u1ED7</s><t>\u1ED6</t>
       <s>\u1ED1</s><t>\u1ED0</t>
       <s>\u1ED9</s><t>\u1ED8</t>
       <p>\u01A1</p><t>\u01A0</t>
       <s>\u1EDD</s><t>\u1EDC</t>
       <s>\u1EDF</s><t>\u1EDE</t>
       <s>\u1EE1</s><t>\u1EE0</t>
       <s>\u1EDB</s><t>\u1EDA</t>
       <s>\u1EE3</s><t>\u1EE2</t>
       <reset>U</reset>
       <p>\u01B0</p><t>\u01AF</t>
       <s>\u1EEB</s><t>\u1EEA</t>
       <s>\u1EED</s><t>\u1EEC</t>
       <s>\u1EEF</s><t>\u1EEE</t>
       <s>\u1EE9</s><t>\u1EE8</t>
       <s>\u1EF1</s><t>\u1EF0</t>
     </rules>
   </collation>
</charset>

The "id" must be greater than the last ID for ucs2.
At the time of writing, the last ucs2 collation is "Hungarian"
and its number is 146:

mysql> select collation_name, id from information_schema.collations
      > where character_set_name='ucs2' order by id;
+--------------------+-----+
| collation_name     | id  |
+--------------------+-----+
| ucs2_general_ci    |  35 |
  .....
| ucs2_hungarian_ci  | 146 |
+--------------------+-----+
21 rows in set (0.00 sec)

So we're adding 147 here.

4. Find this code:

<charset name="utf8">
   <family>Unicode</family>
   <description>UTF-8 Unicode</description>
   <alias>utf-8</alias>
   <collation name="utf8_general_ci"     id="33">
    <flag>primary</flag>
    <flag>compiled</flag>
   </collation>
   <collation name="utf8_bin"            id="83">
     <flag>binary</flag>
     <flag>compiled</flag>
   </collation>
</charset>

5. Change it to:

<charset name="utf8">
   <family>Unicode</family>
   <description>UTF-8 Unicode</description>
   <alias>utf-8</alias>
   <collation name="utf8_general_ci"     id="33">
    <flag>primary</flag>
    <flag>compiled</flag>
   </collation>
   <collation name="utf8_bin"            id="83">
     <flag>binary</flag>
     <flag>compiled</flag>
   </collation>
   <collation name="utf8_vietnamese_ci" id="211">
     <!-- Vietnamese experimental collation -->
     <rules>
       <reset>A</reset>
       <p>\u0103</p><t>\u0102</t>
       <s>\u1EB1</s><t>\u1EB0</t>
       <s>\u1EB3</s><t>\u1EB2</t>
       <s>\u1EB5</s><t>\u1EB4</t>
       <s>\u1EAF</s><t>\u1EAE</t>
       <s>\u1EB7</s><t>\u1EB6</t>
       <p>\u00E2</p><t>\u00C2</t>
       <s>\u1EA7</s><t>\u1EA6</t>
       <s>\u1EA9</s><t>\u1EA8</t>
       <s>\u1EAB</s><t>\u1EAA</t>
       <s>\u1EA5</s><t>\u1EA4</t>
       <s>\u1EAD</s><t>\u1EAC</t>
       <reset>D</reset>
       <p>\u0111</p><t>\u0110</t>
       <reset>E</reset>
       <p>\u00EA</p><t>\u00CA</t>
       <s>\u1EC1</s><t>\u1EC0</t>
       <s>\u1EC3</s><t>\u1EC2</t>
       <s>\u1EC5</s><t>\u1EC4</t>
       <s>\u1EBF</s><t>\u1EBE</t>
       <s>\u1EC7</s><t>\u1EC6</t>
       <reset>O</reset>
       <p>\u00F4</p><t>\u00D4</t>
       <s>\u1ED3</s><t>\u1ED2</t>
       <s>\u1ED5</s><t>\u1ED4</t>
       <s>\u1ED7</s><t>\u1ED6</t>
       <s>\u1ED1</s><t>\u1ED0</t>
       <s>\u1ED9</s><t>\u1ED8</t>
       <p>\u01A1</p><t>\u01A0</t>
       <s>\u1EDD</s><t>\u1EDC</t>
       <s>\u1EDF</s><t>\u1EDE</t>
       <s>\u1EE1</s><t>\u1EE0</t>
       <s>\u1EDB</s><t>\u1EDA</t>
       <s>\u1EE3</s><t>\u1EE2</t>
       <reset>U</reset>
       <p>\u01B0</p><t>\u01AF</t>
       <s>\u1EEB</s><t>\u1EEA</t>
       <s>\u1EED</s><t>\u1EEC</t>
       <s>\u1EEF</s><t>\u1EEE</t>
       <s>\u1EE9</s><t>\u1EE8</t>
       <s>\u1EF1</s><t>\u1EF0</t>
     </rules>
   </collation>
</charset>

The "id" must be greater than the last ID for ucs2.
At the time of writing, the last utf8 collation is
"Hungarian" and its number is 210:

mysql> select collation_name, id from information_schema.collations
     -> where character_set_name='utf8' order by id;
+--------------------+-----+
| collation_name     | id  |
+--------------------+-----+
| utf8_general_ci    |  33 |
  ...
| utf8_hungarian_ci  | 210 |
+--------------------+-----+
21 rows in set (0.00 sec)

So we're adding 211 here.

4. Restart mysqld

If all went well, after restart you'll have two new
collations. They will be in information_schema.collations.
CHARACTER SET utf8 COLLATE utf8_vietnamese_ci will work.
CHARACTER SET ucs2 COLLATE ucs2_vietnamese_ci will work.

WHAT TO DO IF YOU HAVE A PROBLEM, OR HAVE NO PROBLEM

There is no warranty or liability for MySQL if you
change the product. MySQL will only accept bug reports
for unchanged versions. However, our instructions work
according to our tests at this time. If the instructions
have to change due other changes in the source code in
future MySQL versions, we intend to add notes in the
MySQL Internals Manual,
http://forge.mysql.com/wiki/MySQL_Internals.

Later, someday, after everyone is sure that these
changes are what Vietnamese people really want, somebody
might consider submitting a "patch" to MySQL so
that this might appear in a future version of
MySQL/community server.

ABOUT THE AUTHORS

Alexander Barkov and Peter Gulutzan work for MySQL AB.

In the earlier article about upcoming features, “Roadmap”, there was no mention of materialized views. The explanation is simple: they’re not on the roadmap. Yet.

However, this is one of the things that might change. There is a strong desire for materialized views, and one of MySQL’s architects has a strong interest (he wrote his PhD thesis on the topic). So if their absence from the “Roadmap” article troubled you, despair not. Keep watching this space and maybe there will be further news someday.

MySQL Version 6.x is alpha. This blog is about its new features.

Expect a new article every week or two.

The first article,
MySQL 6.0 Feature #1: Supplementary Characters
is out now.
http://blogs.mysql.com/peterg/2008/04/