mysql / datetime / indexes

MySQL has many odd quirks. One that bit us recently was this:
regression: >=mysql-5.4 utf8 collations are marked as not ascii compatible

When using the utf8_unicode_ci collation, datetime column comparisons against strings would ignore any indexes. The lack of working indexes obviously caused huge performance degradation.

Our bug report was ignored in Februari. Apparently a new bug was opened in March:
Datetime field comparisons do not work properly with utf8_unicode_ci collation

And that was deemed important enough to fix.

Looking at the source of version 5.5.33 I see that the UTF-8 flags are now combined into MY_CS_UTF8MB3_UCA_FLAGS, like already done in 5.6.

In the mean time, those looking for a fix on Ubuntu 12.04 Precise can use the OSSO PPA:

$ sudo add-apt-repository ppa:osso/ppa
$ sudo sh -c 'cat >/etc/apt/preferences.d/osso' <<__EOF__
# Decrease prio for all
Package: *
Pin: release o=LP-PPA-osso
Pin-Priority: -10

# Increase prio for mysql*
Package: mysql* libmysql*
Pin: release o=LP-PPA-osso
Pin-Priority: 500
__EOF__

It uses the 5.5.32-0ubuntu0.12.04.1 version as supplied by Ubuntu but patched with 80_utf8_is_ascii_superset.patch (view).

I’d love to comment on the bug report that they have probably fixed it, but Oracle made login and password recovery so tedious that I can’t. For instance, the recovery e-mail that I requested half an hour ago, still isn’t in my inbox.