8 ms·

Unicode support in MySQL is...

157 points by codeka 13y ago

ggreer 13y 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-%F0%9D%92%9Cwesome/ 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_mitsuhiko 13y 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.
  - ggreer 13y 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.
    - ars 13y ago
      A vague problem like this is really not cool. At the minimum your readers deserve that you update the article. Say that there is a case insensitive version. If you want add that you had some problem but that you don't know if it had anything to do with it or not. The problem you had was probably because of the difference between utf8mb4_general_ci and utf8mb4_unicode_ci.
    - the_mitsuhiko 13y ago
      That sounds very much like FUD. There is nothing inherently magical about utf8mb4_unicode_ci.
- ars 13y 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 13y 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 13y 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.
  - joshfraser 13y 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.
    - Larrikin 13y ago
      Good enough when you ignore non English speakers is not good enough
      - coldtea 13y ago
        For a lot of uses cases it very much is. Even ascii can work for some. Not all tables/DBs have to do with spoken language data.
        dmethvin 13y ago
        But a lot of databases have to deal with people's names. To paraphrase an old saying, "I don't care why I'm in the database, as long as they spell my name right."
        joshfraser 13y ago
        utf8_general_ci will still spell your name correctly, it just might not sort it correctly if you use an ORDER BY
        _delirium 13y ago
- Svip 13y 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 13y ago
    At that point they should really consider switching to another DB imo.
    - Svip 13y ago
      Perhaps. But I think they are keeping MySQL support both for legacy reasons and compatibility reasons. You could also argue they should stop implementing it in PHP, if they are willing to sacrifice its ease of install. But MediaWiki does support other DBs, such as Postgresql and Sqlite.
  - jamroom 13y ago
    Might not be any different, but I believe MediaWiki runs on MariaDB: http://blog.wikimedia.org/2013/04/22/wikipedia-adopts-mariadb/ http://blog.wikimedia.org/2013/04/22/wikipedia-adopts-mariad...
- vog 13y 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 13y ago
    very legacy?
    - lsb 13y ago
      much inconsistent. One nifty feature of Postgresql is that if you have a group by a,b,c,d, you need to aggregate everything else in your select that isn't a/b/c/d, or the statement won't prepare, whereas if you don't select an aggregate in MySQL you'll just get one random column, which is probably not what you want.
      - pjungwir 13y ago
        This is mostly true, but in recent Postgres versions (since 9.1 I believe), if you GROUP BY the primary key, you can still include other columns in the SELECT statement. For instance: SELECT a.name, COUNT(b.id) FROM a LEFT OUTER JOIN b ON b.a_id = a.id GROUP BY a.id Pretty nice!
  - contingencies 13y 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.
- a3voices 13y ago
  Is there a phpmyadmin thing for PostgreSQL?
  - AlterEgo20 13y ago
    phppgadmin?
    - a3voices 13y ago
      Cool, thanks for the pointer
kimmel 13y 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.
- willvarfar 13y 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-mysql-i-hate-you 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!
  - fendale 13y 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-top-n-and-window-queries http://betteratoracle.com/posts/18-limiting-query-results-to...
    - sendob 13y ago
      This actually made me think of: Oracle's "UTF8" vs "AL32UTF8" which I consider to be a gotcha, even though it is reasonable in terms of the evolution of the standard, a modern land mine perhaps. The lack of limit sucks, but the thing that annoys me most regularly is that Oracle refuses to make the 0 length string / null behavior configurable to allow for ANSI compliance ( MySQL null handling is worse IMO, makes me insane.... )
      - chris_wot 13y ago
girvo 13y ago
Does anyone know if MariaDB fixes this issue by default?
- the_mitsuhiko 13y ago
  It does not.
- ars 13y 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 :)
nknighthb 13y 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 13y 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.
- Dylan16807 13y 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 13y 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?
  - nknighthb 13y 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.
schappim 13y 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 http://www.joelonsoftware.com/articles/Unicode.html
- deleted 13y ago
  [deleted]
- oofabz 13y 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 13y 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.
    - rb12345 13y ago
      I imagine it's linked to Win32 and .Net using UCS-2 internally, since then there's no need to convert strings before and after API calls.
  - tszming 13y 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-maximum-number-of-bytes-for-a-utf-8-encoded-character http://stackoverflow.com/questions/9533258/what-is-the-maxim...
codexon 13y 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 13y ago
  I'm pretty sure that's intented
  - deleted 13y ago
    [deleted]
- cstuder 13y 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.
- ggreer 13y 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 https://code.google.com/p/chromium/issues/detail?id=62435
  - ezequiel-garzon 13y 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 http://www.unicode.org/L2/L2011/11149-webdings.pdf
- bskap 13y 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
peteretep 13y 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 13y 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/ 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 13y 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 13y 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/ http://vagosec.org/2013/09/wordpress-php-object-injection/
- userbinator 13y 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 13y ago
Concerning this issue please also consider Mathias Bynens's article from 2012 http://mathiasbynens.be/notes/mysql-utf8mb4 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 13y 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 13y 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 13y 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...
- coldtea 13y ago
  You mean masochism like going for the "better" solution when you need it for your use case?
j4mie 13y 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 13y 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-utf8mb4.html 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 13y 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.. 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.. 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]
lmm 13y 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 13y 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 https://gist.github.com/eduard-io/9268175
micheljansen 13y ago
Interestingly, Hacker News does not show the "thumbs down" emoji in the title. No UTF-8 support either?
- codeka 13y ago
  I think it was removed by the moderators, it was definitely there before.
blablabla123 13y ago
Unicode support in <insert name of popular library or program that touches characters> is...
olssy 13y ago
I think it's actually CESU-8 encoding: http://www.unicode.org/reports/tr26/ 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 13y 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 13y ago
  RDS is just hosted, managed MySQL (or Postgres, or Oracle). Don't know about Cloud SQL.
mariuz 13y 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-correctly-by-firebird.html http://flamerobin.blogspot.ro/2014/02/utf-8-is-handled-corre...
DrJ 13y ago
every time I see a MySQL is XYZ article I think about switching to MariaDB or PostgreSQL.

157 points by codeka 13y 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-%F0%9D%92%9Cwesome/ 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_mitsuhiko 13y 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.
- ggreer 13y 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.
  - ars 13y ago
    A vague problem like this is really not cool. At the minimum your readers deserve that you update the article. Say that there is a case insensitive version. If you want add that you had some problem but that you don't know if it had anything to do with it or not. The problem you had was probably because of the difference between utf8mb4_general_ci and utf8mb4_unicode_ci.
  - the_mitsuhiko 13y ago
    That sounds very much like FUD. There is nothing inherently magical about utf8mb4_unicode_ci.
ars 13y 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 13y 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 13y 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.
- joshfraser 13y 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.
  - Larrikin 13y ago
    Good enough when you ignore non English speakers is not good enough
    - coldtea 13y ago
      For a lot of uses cases it very much is. Even ascii can work for some. Not all tables/DBs have to do with spoken language data.
      - dmethvin 13y ago
        But a lot of databases have to deal with people's names. To paraphrase an old saying, "I don't care why I'm in the database, as long as they spell my name right."
        joshfraser 13y ago
        utf8_general_ci will still spell your name correctly, it just might not sort it correctly if you use an ORDER BY
        _delirium 13y ago
Svip 13y 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 13y ago
  At that point they should really consider switching to another DB imo.
  - Svip 13y ago
    Perhaps. But I think they are keeping MySQL support both for legacy reasons and compatibility reasons. You could also argue they should stop implementing it in PHP, if they are willing to sacrifice its ease of install. But MediaWiki does support other DBs, such as Postgresql and Sqlite.
- jamroom 13y ago
  Might not be any different, but I believe MediaWiki runs on MariaDB: http://blog.wikimedia.org/2013/04/22/wikipedia-adopts-mariadb/ http://blog.wikimedia.org/2013/04/22/wikipedia-adopts-mariad...
vog 13y 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 13y ago
  very legacy?
  - lsb 13y ago
    much inconsistent. One nifty feature of Postgresql is that if you have a group by a,b,c,d, you need to aggregate everything else in your select that isn't a/b/c/d, or the statement won't prepare, whereas if you don't select an aggregate in MySQL you'll just get one random column, which is probably not what you want.
    - pjungwir 13y ago
      This is mostly true, but in recent Postgres versions (since 9.1 I believe), if you GROUP BY the primary key, you can still include other columns in the SELECT statement. For instance: SELECT a.name, COUNT(b.id) FROM a LEFT OUTER JOIN b ON b.a_id = a.id GROUP BY a.id Pretty nice!
- contingencies 13y 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.
a3voices 13y ago
Is there a phpmyadmin thing for PostgreSQL?
- AlterEgo20 13y ago
  phppgadmin?
  - a3voices 13y ago
    Cool, thanks for the pointer

kimmel 13y 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.

willvarfar 13y 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-mysql-i-hate-you 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!
- fendale 13y 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-top-n-and-window-queries http://betteratoracle.com/posts/18-limiting-query-results-to...
  - sendob 13y ago
    This actually made me think of: Oracle's "UTF8" vs "AL32UTF8" which I consider to be a gotcha, even though it is reasonable in terms of the evolution of the standard, a modern land mine perhaps. The lack of limit sucks, but the thing that annoys me most regularly is that Oracle refuses to make the 0 length string / null behavior configurable to allow for ANSI compliance ( MySQL null handling is worse IMO, makes me insane.... )
    - chris_wot 13y ago

girvo 13y ago

Does anyone know if MariaDB fixes this issue by default?

the_mitsuhiko 13y ago
It does not.
ars 13y 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 :)

nknighthb 13y 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 13y 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.
Dylan16807 13y 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 13y 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?
- nknighthb 13y 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.

schappim 13y 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 http://www.joelonsoftware.com/articles/Unicode.html

deleted 13y ago
[deleted]
oofabz 13y 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 13y 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.
  - rb12345 13y ago
    I imagine it's linked to Win32 and .Net using UCS-2 internally, since then there's no need to convert strings before and after API calls.
- tszming 13y 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-maximum-number-of-bytes-for-a-utf-8-encoded-character http://stackoverflow.com/questions/9533258/what-is-the-maxim...

codexon 13y 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 13y ago
I'm pretty sure that's intented
- deleted 13y ago
  [deleted]
cstuder 13y 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.
ggreer 13y 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 https://code.google.com/p/chromium/issues/detail?id=62435
- ezequiel-garzon 13y 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 http://www.unicode.org/L2/L2011/11149-webdings.pdf
bskap 13y 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

peteretep 13y 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 13y 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/ 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 13y 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 13y 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/ http://vagosec.org/2013/09/wordpress-php-object-injection/
userbinator 13y 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 13y ago

Concerning this issue please also consider Mathias Bynens's article from 2012 http://mathiasbynens.be/notes/mysql-utf8mb4 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 13y 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 13y 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 13y 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...
coldtea 13y ago
You mean masochism like going for the "better" solution when you need it for your use case?

j4mie 13y 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 13y 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-utf8mb4.html 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 13y 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.. 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.. 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]

lmm 13y 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 13y 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 https://gist.github.com/eduard-io/9268175

micheljansen 13y ago

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

codeka 13y ago
I think it was removed by the moderators, it was definitely there before.

blablabla123 13y ago

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

olssy 13y ago

I think it's actually CESU-8 encoding: http://www.unicode.org/reports/tr26/ 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 13y 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 13y ago
RDS is just hosted, managed MySQL (or Postgres, or Oracle). Don't know about Cloud SQL.

mariuz 13y 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-correctly-by-firebird.html http://flamerobin.blogspot.ro/2014/02/utf-8-is-handled-corre...

DrJ 13y ago

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