Unicode support in MySQL is...

http://www.codeka.com.au/blog/2014/02/unicode-support-in-mysql-is--

by codeka • 12 years ago

157 107 12 years ago

codeka.com.au

ggreer3• 12 years ago

Now presumably there is some drawback from doing this, otherwise "utf8mb4" would be the default (right?) but I'll be damned if I can figure out what the drawback is.

There are a couple, and they are insidious. From http://geoff.greer.fm/2012/08/12/character-encoding-bugs-are...:

This is when I discovered that InnoDB limits index columns to 767 bytes. Why is this suddenly an issue? Because changing the charset also changes the number of bytes needed to store a given string. With MySQL’s utf8 charset, each character could use up to 3 bytes. With utf8mb4, that goes up to 4 bytes. If you have an index on a 255 character column, that would be 765 bytes with utf8. Just under the limit. Switching to utf8mb4 increases that index column to 1020 bytes (4 * 255).

And later:

You might say, “OK, now we’re finished, right?” Ehh… it’s not so simple. MySQL’s utf8 charset is case-insensitive. The utf8mb4 charset is case-sensitive. The implications are vast. This change in constraints forces you to sanitize the data currently in your database, then make sure you don’t insert anything with the wrong casing. At work, there was much weeping and gnashing of teeth.

Good luck. The real solution is to switch to something that isn't MySQL. The project in that post ended up switching to Cassandra. For new SQL stuff, I now use PostgreSQL.

Edit:

To those of you who point out that utf8mb4_unicode_ci is case-insensitive, we did try that. This whole fiasco happened two years ago, and I don't remember what the show-stopper was, but we decided not to use that collation. Whatever issues we had with it were bad enough to warrant switching to utf8mb4_bin and sanitizing everything. Really, the solution is to switch away from MySQL and forget about collations and encodings.

the_mitsuhiko4• 12 years ago

There is a case insensitive collation for utf8mb4 so half of the problems there are just a misconfiguration. The index size restrictions are a problem though.

ggreer3• 12 years ago

There is a utf8mb4_unicode_ci collation, but we had big problems with it. I'm sorry, but this happened two years ago and I can't remember what the exact issues were. Whatever they were, they were bad enough that we ended up using utf8mb4_bin and sanitizing everything.

Edit: Apologies, I wrote utf8mb4_general_ci when I meant to say utf8mb4_bin. I think the issue we had was conflicting rows on unique constraints due to utf8mb4_unicode_ci having different comparison rules than utf8_unicode_ci.

Don't want to be confused by utf8 vs utf8mb4 charsets or deal with the subtle differences between utf8_general_ci, utf8_unicode_ci, utf8mb4_general_ci, utf8mb4_unicode_ci, and utf8mb4_bin? Switch to PostgreSQL. You will not regret it.

ars3• 12 years ago

The case sensitive part is incorrect, the author made a mistake and corrects it in the comments, but not in the article (don't know why).

Case insensitive works fine with mb4.

Erm, what? You are the author! So why would you copy/paste a section you know is incorrect? And an even bigger question is why would you leave it on your website to confuse lots and lots of people!

Fix it both here and in the article!

oxymoron• 12 years ago

Someone pointed out in the comments on that link that utf8mb4 isn't necessarily case sensitive. The author believed that due to the fact that he was using utf8mb4_bin_ci as collation, which is a binary collation. It works as expected when using a "true" collation like utf8mb4_general_ci.

The index problem is mildly annoying, but limiting index length isn't a big problem. For most applications, you can uniquely identify a row with much less than 191 characters, and it might be a good optimization to limit index length regardless to prevent the index size from growing out of control. If it's truly a problem, hacks like adding a sha1 or md5 hash column with a value derived from the original column and indexing that instead solves the problem.

Edit: Ops. Didn't realize parent was the article author. :)

metafunctor• 12 years ago

For anyone thinking of using utf8_general_ci, it's also seriously broken if you care about sameness of characters at all.

For example, utf8_general_ci treats u and Ü as the same character. They are very much not the same. Your sort orders for international texts will be all over the place if you use utf8_general_ci.

Use utf8_unicode_ci instead.

joshfraser3• 12 years ago

utf8_general_ci is a lot faster than utf8_unicode_ci and it's good enough for whenever you don't need to sort international strings.

Dylan168072• 12 years ago

The effect of various marks on whether it's the same letter or not depends on locale. You need to change the sorting based on context.

Don't hardcode utf8_unicode_ci either.

Svip2• 12 years ago

This is also the reason why MediaWiki have been using binary blobs for text data in their MySQL database. And have been for years.

jmnicolas• 12 years ago

At that point they should really consider switching to another DB imo.

jamroom• 12 years ago

Might not be any different, but I believe MediaWiki runs on MariaDB:

http://blog.wikimedia.org/2013/04/22/wikipedia-adopts-mariad...

vog• 12 years ago

For new SQL stuff, I now use PostgreSQL.

That's so true! Years ago I switched almost all projects to PostgreSQL. I never looked back. I use MySQL only for very legacy applications.

MySQL has so many bugs an inconsistencies, unicode support is only one of many issues. (Unsafe GROUP BY, silently cutting concatenated text fields when the result grows too big, silently converting invalid dates to '0000-00-00', and so on ...)

In the past, MySQL had some performance advantages over PostgreSQL, but only for MyISAM, i.e. without real transaction safety. Nowadays, if you want to trade ACID for speed, you wouldn't use MySQL but some "NoSQL" database instead. So even from a performance point of view, I see no point in using MySQL for anything but legacy stuff.

mephi5t0• 12 years ago

very legacy?

contingencies• 12 years ago

I switched to sqlite. I've done my time on SQL servers. In my view, they simply have too much complexity for many scenarios. Basically, one big database server equals one big downtime when issues occur. Sharded data architecture on the other hand can design for failure and maintain graceful degraded performance, not to mention optimized resource allocation and the option of adopting vastly different security and backup policies across different parts of the datastore (eg. encryption, snapshots, etc.). Yes, you lose database-internal consistency guarantees. No, that doesn't mean you have to lose consistency.

a3voices2• 12 years ago

Is there a phpmyadmin thing for PostgreSQL?

AlterEgo20• 12 years ago

phppgadmin?

kimmel• 12 years ago

I recently moved an application off mysql to postgresql after hitting this error. After over a decade of mysql doing things their way and it always being a pain in the ass for everyone else when is enough, enough?

I no longer develop software to run on mysql because I like my sanity.

willvarfar2• 12 years ago

Well, funnily enough MySQL are now owned by Oracle and I've wasted a couple of days trying to get some sane semblance of `LIMIT` working for me on Oracle... so don't hold your breath, Oracle have an even longer history of damning their customers...

I've been keeping a list of MySQL gotchas, but steering clear of the MySQL take on the SQL standard itself: http://williamedwardscoder.tumblr.com/post/25080396258/oh-my...

I still contemplate MySQL for new projects, because better the enemy you know and all that... OMG, I'm mad! Postgres rah rah rah!

fendale2• 12 years ago

Oracle does seems to make limit queries much harder than they need to be. I wrote an article about limit and paging queries in oracle ages ago that may interest you, but you probably have it all figured out now.

http://betteratoracle.com/posts/18-limiting-query-results-to...

AlisdairO• 12 years ago

You should be able to do LIMIT in Oracle using http://www.oracle.com/technetwork/issue-archive/2006/06-sep/... . If you want a standards-compliant way to do it, you can use the ROW_NUMBER() window function. This might not be as fast, but will work on virtually every DB except MySQL...

rimantas2• 12 years ago

Interesting, pretty much every system has insane things in it. Is postgresql an exception?

sitharus• 12 years ago

Certainly not! However, its WTFs per minute count is far lower than MySQL.

I started off my career as a PHP/MySQL developer. PostgreSQL is definitely years ahead of MySQL. It used to be that MySQL had far superior read performance for simple queries, but that hasn't been true since around 2006. Also Postgres' SQL optimiser is really good.

sgt• 12 years ago

In my experience, it has fewer "insane things in it" than MySQL.

einhverfr2• 12 years ago

PostgreSQL is no exception, but hey I do insane things with the db so I suppose it is fitting that I occasionally (but rarely) run into insane aspects of the system.

The difference is, you don't have to do crazy things to run into truly insane behavior on MySQL ;-)

s_kilk• 12 years ago

Postgres may not be perfect, but it's certainly not as consistently dopey as MySQL.

codeka3• 12 years ago

I basically chose MySQL because of familiarity for the most part, but I think I'll definitely consider an alternative for my next project...

kalleboo• 12 years ago

For me it was because AWS RDS didn't support Postgres until recently, although that has changed now.

grey-area2• 12 years ago

Yep, I switched to Postgresql after seeing a client lose data to corrupted databases - they had rebooted their server, and mysql refused to restart as a result and had to be manually recovered. Every time I hear another mysql horror story like this (truncation without warning was another favourite), it makes me thankful I no longer use it.

sendob5• 12 years ago

if it matters, must have backups! ( +replication +geographical distribution etc as needs must )

Even a perfect database, should such a thing ever exist, is not sufficient to protect us from a determined disaster

girvo• 12 years ago

Does anyone know if MariaDB fixes this issue by default?

the_mitsuhiko4• 12 years ago

It does not.

ars3• 12 years ago

It can't. Because of index sizes it would mean incompatibility.

But it's not really all that much of an issue - just use the mb4 character encoding.

The hard part is knowing you need to :)

nknighthb3• 12 years ago

Why they call this encoding "UTF-8" is beyond me, it most definitely is not UTF-8.

This is a confusion of concepts. That an encoding can encode a particular value does not mean all applications using that encoding will support that value. You can store any size number you like into a JSON number, but if you send me a 256-bit value in a field for temperature in celsius, I'm going to give you back an error.

Encodings ease data exchange, they do not alter the requirements or limitations of the applications using them. MySQL's original unicode implementation supported only the Basic Multilingual Plane, so unsurprisingly, characters outside that plane are rejected. That would be the case regardless of the particular encoding used.

What you want is support for all Unicode characters. This is a reasonable request, but it is decoupled from the encoding used to communicate those characters to MySQL.

qnaal• 12 years ago

When the purpose of your software is simply to record data, saying you support a particular encoding when you only support _some_ of it is pretty irresponsible.

Dylan168072• 12 years ago

It seems to me that you're the one confusing concepts. MySQL originally didn't support characters outside the BMP, fine. But since the encoding is completely different from that, a real UTF-8 would have automatically supported astral characters as soon as MySQL supported them.

The fact that MySQL itself gained support for all of unicode, but did not use this support in the 'utf8' encoding, shows that it is not actually UTF-8.

perlgeek• 12 years ago

That an encoding can encode a particular value does not mean all applications using that encoding will support that value.

And yet you have to change the encoding of a column to support storage of non-BMP characters, even though the UTF-8 encoding supports them just fine. Where's the sense in that?

nknighthb3• 12 years ago

No, you have to change the character set of a column. It's unfortunate they used a name normally used for an encoding to name a character set, but that doesn't make the character set an encoding.

riffraff• 12 years ago

but I expect an app that says "you can store utf8 data here" to actually store that, not "I will silently drop everything after the first utf8 character that doesn't fit in 3 bytes".

I don't see how I should imagine that putting utf8 as a column encoding should be interpreted as "I will read and discard utf8".

This is different from declaring utf8 as the connection encoding.

nknighthb3• 12 years ago

Your expectation is common among westerners. It typically goes away within a few days of beginning to work with large amounts of CJK content.

When someone says they accept UTF-8, UCS-2, or any other encoding, my first question is always "Are characters outside the Basic Multilingual Plane supported?". There is a long history quite apart from MySQL of the answer to that question being "no", particularly if the project in question originated in the 90s (or even early 2000s).

chris_wot3• 12 years ago

Say what? UTF8 allows for any character that the Unicode standard defines. If they wanted to restrict a characterset to the BMP, then they should call it UTF-8-BMP.

schappim• 12 years ago

Sounds like the OP should read "The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)" over at: http://www.joelonsoftware.com/articles/Unicode.html

oofabz• 12 years ago

In this article, Joel says he "decided to do everything internally in UCS-2 (two byte) Unicode". He fell into the same trap that MySQL did and the software he describes would also fail on Emoji.

It is still a very informative piece and Joel was way ahead of the curve by supporting and evangelizing Unicode at all in 2003. But it is not the best article to point the OP at, as it does not mention the BMP or discuss proper handling of characters beyond the BMP.

jwarkentin• 12 years ago

I never got that. He wrote a great article about the issue and then ended with using UCS-2. I always wondered if there was something I missed that made him choose UCS-2 over UTF-8, since UTF-8 can represent every Unicode code point.

tszming• 12 years ago

Actually, Joel's article is also outdated, e.g. Only code points 128 and above are stored using 2, 3, in fact, up to 6 bytes.

See:

What is the maximum number of bytes for a UTF-8 encoded character?

http://stackoverflow.com/questions/9533258/what-is-the-maxim...

chris_wot3• 12 years ago

Why? UTF8 covers more than just the BMP, it's just an encoding scheme. This has nothing to do with the fact that UTF8 is restricting characters to 0xFFFF!

codexon• 12 years ago

Does anyone else just see squares instead of whatever unicode symbol it is supposed to be? I'm using Chrome 33.0.1750.117 m

Mchl• 12 years ago

I'm pretty sure that's intented

cstuder• 12 years ago

On Windows and with Chrome, yes.

On iOS 7.0.1 it's a colorful thumbs down.

Internet Explorer 11 shows a black/white thingie which might be a thumbs down with a lot of imagination. Firefox shows the same bitmap.

ggreer3• 12 years ago

It's an emoji thumbs-down. You should be able to see it using Safari or Firefox on OS X. There is an outstanding Chrome issue related to this: https://code.google.com/p/chromium/issues/detail?id=62435

ezequiel-garzon• 12 years ago

Available here [1] on page 18, or just look for "1F44E". I found the twist really clever, and first thought the author had simply used a generic block.

I didn't spend much time reading the cited document, but it looks like some existing glyphs may be merged with newer ones under certain conditions. What a mess... I feel Unicode has tried to compass way too many things (even italics!). Go figure...

[1] http://www.unicode.org/L2/L2011/11149-webdings.pdf

bskap• 12 years ago

It's a Unicode thumbs down sign. You must not have any fonts with that glyph. It works fine for me in both Firefox 27 and IE 11 on Windows 8.1

codeka3• 12 years ago

You need a font which supports emoji. Android, iOS and Mac OS X should support it out of the box, on Linux I had to install a font. I'm not sure what the situation is on Windows.

peteretep• 12 years ago

This bit me pretty hard. I was trying to debug other Unicode issues an application had, and in my infinite and enduring wisdom decided to use one of those "turn text upside down" sites to generate sample data. Turns out they use a bunch of very high codepoints (Mormon text or something) and MySQL choked on them. Took me ages to figure out that was the issue.

pilif• 12 years ago

Anther nice "feature" of mysql: If you have a table configured to UTF-8, and you use utf-8 client encoding but then accidentally try to send something that's not UTF-8 (shit happens), then mysql will happily accept your data and truncate it on the first byte it doesn't understand.

It issues a warning, but most frameworks don't care to inform the caller if a warning has happened (some don't even provide a way to access them).

Yes. You should not be sending invalid data to your database, but holy sh*t, your database shouldn't (mostly) silently alter the data you entrust it with.

If the input data is wrong and you can't deal with it, blow up in the face of the user. Don't try to "fix" it by corrupting it.

Disclaimer: This happened to me in 2008 (http://pilif.github.io/2008/02/failing-silently-is-bad/), so it might have been fixed since, but stuff like this made me lose my trust in MySQL long ago.

mst• 12 years ago

DBIx::Class (in perl) has a connection info option that calls a method that does its best to turn all of that horrible crap off:

    sub connect_call_set_strict_mode {
      my $self = shift;

      # the @@sql_mode puts back what was previously set on the session handle
      $self->_do_query(q|SET SQL_MODE = CONCAT('ANSI,TRADITIONAL,ONLY_FULL_GROUP_BY,', @@sql_mode)|);
      $self->_do_query(q|SET SQL_AUTO_IS_NULL = 0|);
    }

Please feel free to steal it if it helps :)

tomvangoethem• 12 years ago

Except for causing annoyances, the truncation of data may also have security implications. Last year, I found that WordPress (which has the utf8 character set by default), was vulnerable to PHP object injection because their filter could be bypassed by (ab)using the truncation of strings in MySQL. For more details: http://vagosec.org/2013/09/wordpress-php-object-injection/

userbinator• 12 years ago

but most frameworks don't care to inform the caller if a warning has happened (some don't even provide a way to access them).

Then I would consider the framework broken. Warnings about truncated data are the norm in other RDBMSs.

jhh• 12 years ago

Concerning this issue please also consider Mathias Bynens's article from 2012 http://mathiasbynens.be/notes/mysql-utf8mb4

It has a good treatment of how to migrate encoding-wise which includes additional precautions not mentioned in the originally linked article.

josteink• 12 years ago

That people still use MySQL for anything new is something I find completely amazing.

But then again, masochism is a reasonably common, if not normal, thing.

bhaak• 12 years ago

IMO this is a combination of "doesn't know any better", "works for me", and "it's easy enough, don't bother me with complicate solutions".

Which is unfortunate as the "complicate" solution is often just the right solution but if the wrong solution doesn't blow up into their faces in 99% to 90% of the use cases, many people are satisfied enough with it.

I think these reasons also play a role in PHP's ongoing popularity.

taf2• 12 years ago

Last I checked the alternatives were either very expensive Oracle or did not have a good HA solution Postgres... MySQL/MariaDB has two good HA solutions - either behind haproxy with master-master or a VIP+keepalived with Galera cluster... The issue of encodings for most applications is minor...

coldtea2• 12 years ago

You mean masochism like going for the "better" solution when you need it for your use case?

j4mie• 12 years ago

It'd be really interesting to hear from an engineer at one of the big MySQL users (I'm thinking Twitter, Facebook, Github) how they solve this problem.

marlin• 12 years ago

I also hit this issue when saving wikipedia pages to database, since some languages use these "4-byte UTF-8":s.

AFAICR, there is performance issues about this. Western text does not "need" this representation and thus the MySQL utf8 handling could be faster.

I was also shocked to learn this while using MySQL 5.3 (where utf8mb4 is missing), which led me to upgrade to the 5.5 alpha.

From https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8...

"The character set named utf8 uses a maximum of three bytes per character and contains only BMP characters.

As of MySQL 5.5.3, the utf8mb4 character set uses a maximum of four bytes per character supports supplemental characters"

PS. I have migrated to MariaDB. Take this chance to move away from Oracle MySQL!

NKCSS• 12 years ago

I can recommend reading the comment from Alex on there as well; it has some good info on UTF-8:

[quote] 😸 works just fine in PostgreSQL by default (Python too).

The UTF-8 issue is pretty complex, so it's not really a surprise that implementations (like MySQL's) are incomplete. The basic encoding from the code points into UTF-8 bytes could handle up to 30 bits of data (first byte of 0xFC indicating 5 following bytes with 6 bits of the code point each (6 bytes for 36 bit code points are only impossible because UTF-8 bans 0xFF and 0xFE as byte values other than for the Byte Order Mark). Yet various standards attempt to restrict the range, currently with RFC 3629 putting the ceiling at 0x10FFFF (4 bytes per code point). That RFC doesn't bother to justify the constraint, other than as a backwards compatibility band-aid with the more limited UTF-16. The point isn't to rag on UTF-16, which made sense once, but to express sympathy for the various attempts made to avoid coping with the full, 30-bit range the underlying encoding can actually handle.

Not only is there a wash of hodgepodgery on the range, but UTF-8 (and other similar encodings) can put small values in large byte encodings. There's nothing stopping someone from just using a 6-byte long, 30-bit codepoint block of RAM for each character, even just ASCII (obviously this violates a bunch of RFCs, but the coding system does provide for it). The result would confuse many UTF-8 parsers (and blocked by the more complete ones, a Pyrrhic victory), since ASCII characters are expected to be exactly one byte long in UTF-8, not seven. Even in RFC-compliant UTF-8, an ASCII character codepoint value can be encoded in four different ways. Example: an "a" (ASCII 97, hex value 0x61) can be encoded as any of (pardon any bit errors, I'm doing this off-the-cuff, late at night...):

00111101 - 0x61 - conventional ASCII 11000000 10111101 - 0xCO 0xBD - two-byte UTF-8 11100000 10000000 10111101 - 0xE0 0x80 0xBD - three byte UTF-8 11110000 10000000 10000000 10111101 - 0xF0 0x80 0x80 0xBD - four byte UTF-8

The latter three overlong encodings aren't considered canonical, and software devs have to fix them to make string matching efficient and so forth, to save space, and most worrisomely to prevent attackers from sliding special characters into strings to crack systems - say by using larger, noncanonical encodings to evade filters that would catch and block the canonical, shorter encodings.

Some developers would try to limit UTF-8 characters to four bytes because that's a power of two, and fits comfortably into a 32-bit long int.

UTF-16 also complicates UTF-8 with the banning of codepoints between 0xd000 and 0xdfff in UTF-8, used as "surrogate pairs" in UTF-16. See http://www.unicode.org/version... for an update that mentions this explicitly)

Anyway, the general idea is that I can sympathize with MySQL having incomplete UTF-8 support, most implementations are incomplete in some way, and one could argue the RFC's variant is pointlessly incomplete itself (no 24 and 30 bit code points, though apparently UNICODE's Annex D does allow the use of those two larger sizes for characters outside of the UNICODE range, perhaps widely interpreted as a constraint of UTF-8 itself instead of being about the UNICODE subrange of UTF-8). Implementations vary enormously, and with good reason (see http://www.unicode.org/L2/L200... for some of them).

Fortunately, MySQL's limitation doesn't trouble me, since I do almost all my work in PostgreSQL or some non-SQL database or another anyway. ;-P [/quote]

threedaymonk• 12 years ago

I don't know; to me it reads like someone trying to show off, but I don't think it's helpful.

The latter three overlong encodings aren't considered canonical, and software devs have to fix them to make string matching efficient and so forth, to save space, and most worrisomely to prevent attackers from sliding special characters into strings to crack systems - say by using larger, noncanonical encodings to evade filters that would catch and block the canonical, shorter encodings.

This is misleading. "Software devs" don't have to "fix them". Overlong encodings in UTF-8 are invalid since Unicode 3.1, precisely because of the security considerations, and conformant implementations are required "not to interpret any ill-formed code unit subsequences" (Unicode Standard section 3.9). RFC 3629 also states that "[i]mplementations of the decoding algorithm above MUST protect against decoding invalid sequences."

Fortunately, it's pretty straightforward to achieve this, and the Unicode standard contains a nine-row table listing all valid combinations of ranges of one to four bytes.

lmm• 12 years ago

Also, if you set a column charset to "Latin1", guess what encoding MySQL actually gives you. If you guessed "like windows cp1252, but with 8 random characters swapped around", have a cookie.

(Though I have to admit, based on the title I was hoping for a deeper problem. MySQL has plenty of stupid gotchas like this, but it's (marginally) easier to deal with them than to cluster postgres)

edu• 12 years ago

I'd this same issue a couple of days ago. If you use Rails you can use my migration: https://gist.github.com/eduard-io/9268175

micheljansen• 12 years ago

Interestingly, Hacker News does not show the "thumbs down" emoji in the title. No UTF-8 support either?

codeka3• 12 years ago

I think it was removed by the moderators, it was definitely there before.

blablabla123• 12 years ago

Unicode support in <insert name of popular library or program that touches characters> is...

olssy• 12 years ago

I think it's actually CESU-8 encoding: http://www.unicode.org/reports/tr26/

I'm guessing it's implemented like this for performance reasons and calling it utf-8 was just a marketing ploy as everyone knows we should always use utf-8 for everything...

dudus• 12 years ago

Is this also an issue with Google's Cloud SQL or Amazon RDS?

From what I understand these have the same interface as MySQL but internally are different.

Xorlev• 12 years ago

RDS is just hosted, managed MySQL (or Postgres, or Oracle). Don't know about Cloud SQL.

mariuz• 12 years ago

UTF-8 is handled correctly by Firebird and Flamerobin You can create a new db with utf-8 charset as default and then you can insert/select

Unicode codepoint U+1F638 a perfectly valid Emoji character

http://flamerobin.blogspot.ro/2014/02/utf-8-is-handled-corre...

DrJ• 12 years ago

every time I see a MySQL is XYZ article I think about switching to MariaDB or PostgreSQL.

Unicode support in MySQL is...

Related Stories