14 ms·

In MySQL, don’t use “utf8”, use “utf8mb4” (2016)

244 points by mariuz 8y ago

cyberferret 8y ago
Good reminder. I did read a well hidden article many years ago which highly recommended this. I believe I was hitting an obscure, arcane bug like the OP mentioned in the article, and after much digging managed to find this fix. Since then, I always set every new MySQL database (and even double check the tables) to be 'utf8mb4' even long after the original lessons and pain have been forgotten.
golergka 8y ago
Oh, I love these kind of Unicode bugs. Also, please don't use utf16 in C# - although it advertises as using utf16, all of it's built-in string processing actually works on code units, not Unicode code points and breaks down on surrogate pairs. Anyway, each time you start a new project that is supposed to work correctly with multi-language data, please set up evil strings test suite as early as you can and do not assume that any of the 3rd party software that you're using won't break down.
- machinecoffee 8y ago
  Previous discussion on HN for 'Naughty Strings', was certainly an entertaining and eye opening read... https://news.ycombinator.com/item?id=10035008 https://news.ycombinator.com/item?id=10035008
morgo 8y ago
Hi! Former Product Manager for MySQL Server here. I have a blog post on this here: https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/ https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-ov... The default for MySQL 8.0 (current GA) is utf8mb4. I also recommend reading this introduction post here: http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf8-support-in-mysql-8-0/ http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf...
- im3w1l 8y ago
  Do you have a collation were no two different strings compare equal? Have you considered imposing an arbitrary order where you don't know which one "should" go first?
  - morgo 8y ago
    Yes - there is a binary collation. w.r.t. arbitrary: The ordering must be deterministic so that indexes can maintain sorted order. It's possible to create a custom collation to have your own ordering - but I would say most users don't do that.
    - im3w1l 8y ago
      Sorry if I was unclear I meant some ordering that sorts by natural language but breaks ties in some arbitrary (but deterministic) manner, e.g. by binary.
- JdeBP 8y ago
  > An optimization was chosen to limit utf8 to 3 bytes You chose not to define the UNICODE_32BIT macro that was in the original source from 2002? * https://github.com/mysql/mysql-server/commit/55e0a9cb01af4b01bc4e4395de9e4dd2a1b0cf23#diff-95de3baa9e399c689954d1b8442d0be8R1611 https://github.com/mysql/mysql-server/commit/55e0a9cb01af4b0...
  - morgo 8y ago
    The optimization was made at a different time in history. As I wrote in another comment here: while the decision is regrettable, because of a desire for backwards compatibility and simplified upgrade there wasn't an easy way out of it. The utf8[mb4] support in MySQL 8.0 is much better than prior releases. The team worked really hard on eliminating the use cases for utf8mb3 (performance) so that "just use the default of utf8mb4" can work for everyone.
    - jeltz 8y ago
ofrzeta 8y ago
I've "bookmarked" this: https://mathiasbynens.be/notes/mysql-utf8mb4 https://mathiasbynens.be/notes/mysql-utf8mb4 It digs a little bit deeper on the MySQL side (and it's from 2012 btw)
muglug 8y ago
Counterpoint: emojis are bad and should be discouraged
- ajuc 8y ago
  It's not only for emoji, it's also for less common scripts.
- umanwizard 8y ago
  Why?
- gpvos 8y ago
  The nice thing about emojis (maybe the only nice thing) is that they are so popular that they force companies and developers to implement proper support for all planes of Unicode, which means that all characters and symbols in those planes that are actually useful are then also supported.
- morgo 8y ago
  I feel just the opposite: emojis are a lot more expressive than text and more universal in language than en-us.
  - nolliebs180 8y ago
    What about winking emojis? I feel there's a lot more ambiguity/nuance with them, which can be a good thing, but can also express something you may or may not intend to.
billpg 8y ago
Dear database developers, please don't get hung up on string lengths. I'd be very happy to ask the DB for (say) a UTF-8 string of up-to 80 bytes. That means I could store 80 ASCII characters, 40 two-byte characters, 20 four-byte characters or some mixture as I wish.
- jeltz 8y ago
  At least PostgreSQL does not care about string length, other than as required by the SQL standard (e.g. it enforces varchar(20) by counting the number of code points). The maximum size of a string in PostgreSQL is 1 GB.
- morgo 8y ago
  While I agree with you, as I understand the SQL standard defines the N in varchar(N) as characters (not bytes). There is a pathological case which is not common: max length of all 4-byte characters. Indexes are used to enforce features like unique constraints, it's important that everything can fit in the buffer length.
inyourtenement 8y ago
Currently leading a massive thorny project to convert all of our databases to utf8mb4. Definitely heed this warning.
chx 8y ago
> If you need a database, don’t use MySQL or MariaDB. Use PostgreSQL. This is stupid. There are pros and cons to every database. For example, MySQL allows you to set a trigger on an op, PostgreSQL requires you to write a function first.
- ainar-g 8y ago
  I agree that that point sounds too rough. I would write something like >Every software has issues; so know your software's gotchas. With that said, the perception I get is that PostgreSQL has less such gotchas and works better "out of the box". Though still not optimally, according to our Ops.
  - dijit 8y ago
    PostgreSQL is pretty good- the default config limits the resource consumption to something you very likely wont want; and things like WAL shipping have some awkward gotchas but it's really stable and quite consistent. People usually argue against postgresql because it used to be slow on multi-core systems, the vacuum was kinda crappy in PGSQL <8.2 and it lacks a lot of features in base (because typically pgsql developers don't accept something in base unless it's very stable and orthogonal, which I consider to be a good thing). It's like the OpenBSD of Databases.
- gmac 8y ago
  Thing A having specific advantages over Thing B in no way implies that Thing B is not clearly superior overall. I have used both MySQL and Postgres, although admittedly I haven't touched MySQL for a few years (the last time I tried to use MySQL for something, I found it would happily serve me up an arbitrary result for a clearly nonsensical query). I thought this comment on an earlier thread summed up my feelings about the two systems rather beautifully: https://news.ycombinator.com/item?id=15173100 https://news.ycombinator.com/item?id=15173100
- neverminder 8y ago
  > This is stupid. Talk about stupid, run this query on both Mysql and Postgres: select 0 = 'banana'; Make your own conclusion.
kreetx 8y ago
Given that the article says that everyone should use utf8mb4, then why don't/haven't they just fixed it? Should be easy enough, and probably safe, too.
- ofrzeta 8y ago
  They have. It's the default in MySQL 8.
  - kreetx 8y ago
    Sorry, should have bern more clear: why didn't they fix the bug in utf8 so that it is correct (and would do what utf8mb4 does now)? It's just that the obvious choice of 'utf8' has been plain wrong for a long time.
    - shadofx 8y ago
      from the article: >Once MySQL published this invalid character set, it could never fix it: that would force every user to rebuild every database.
- morgo 8y ago
  The desire for backwards compatibility/easier upgrades is why utf8mb4 could not just replace utf8: The sorting order for the utf8 collations is not identical to utf8mb4 ones, and so an upgrade would require indexed columns to be rebuilt (remember, indexes are sorted). I think with hindsight it would have been better to rip the bandaid off. 8.0 took a middle of the road approach by switching the default to utf8mb4.
- johannes1234321 8y ago
  This can't silently be changed as the setting has impact on disk format for data and indexes and so and will be confusing when using different server versions, i.e. in replication.
ofrzeta 8y ago
Side note: It's not enough to change the encoding of the database, you need to make sure that all involved parties use the same encoding (or convert the charset at the interfaces). For instance in a Laravel app I had to set the charset in the database configuration file "app/config/database.php".
MrBuddyCasino 8y ago
"An optimization was chosen to limit utf8 to 3 bytes, enough to handle almost all modern languages." I can't for the life of me imagine how, when discussing this in a room full of competent developers, everyone just went "great idea, lets do this". Just adds to the long list of questionable MySQL engineering decisions.
- jeltz 8y ago
  I am pretty sure it was not because of any optimization but that the real reason was to make sure a VARCHAR(255) would still fit within the maximum field size of InnoDB (768 bytes) at the time. Since then MySQL has added ROW_FORMAT=DYNAMIC to InnoDB which allows for larger values, but that probably required major changes to InnoDB.
  - morgo 8y ago
    There are various buffers that were* not variable length in query execution (sorting, temp tables). It is not just about the on-disk format in this case. * Use MySQL 8.0 :-)
  - MrBuddyCasino 8y ago
    There is usually a good reason for doing these kind of things. But then don't call it UTF8 encoding! It doesn't do that. It stores the most popular characters out of a larger address space. Call it UTF8-MB3 maybe from the start and let the user decide if having varchar(255) is worth the tradeoff. Thats the trouble with MySQL: doing silently the wrong thing, and thus violating the principle of least surprise. There is software where this might be ok, but a database is not that kind of software.
    - tinus_hn 8y ago
      In the case of MySQL (especially MySQL in 2003) bad design and silly behavior isn’t much of a surprise though :)
- evanelias 8y ago
  Sure, in hindsight, 3 bytes is an abomination to us today. That was almost certainly not apparent to developers at the time. MySQL added its three-byte utf8 support in mysql 4.1, first beta release in April 2003 [1]. Meanwhile the utf8 standard originally permitted up to 6 bytes per char, until officially changed in November 2003 [2]. Unicode support in web software -- and even programming languages -- was still rapidly evolving in 2003. The MySQL team probably just made a bad guess along the lines of "the standard allows 6 bytes but that has terrible perf and memory implications for us, and most people only use 3 bytes, so let's limit to that". While I've lost track of how many hundreds of times I've had to tell coworkers "use utf8mb4 and not utf8 in mysql", I don't begrudge the mysql dev team for not being clairvoyant in 2003! [1] https://lists.mysql.com/announce/161 https://lists.mysql.com/announce/161 [2] https://en.wikipedia.org/wiki/UTF-8#History https://en.wikipedia.org/wiki/UTF-8#History
Quarrelsome 8y ago
Friend of mine just recounted a story of when this bug killed all their MySql instances across an entire enterprise installation because a user sent an email with the hamster face emoji in the title.
- bri3d 8y ago
  I worked at large company where an emoji or other 4-byte UTF character in a commit message would take down multiple systems simultaneously, including JIRA and various internal auditing and pipeline monitoring tools. It happened surprisingly rarely considering the lack of safeguard against the issue.
  - joombaga 8y ago
    This happened to us too. We first noticed it in Bamboo with a failed build job. It's been a long time since I installed Bamboo, but if I recall correctly the installer adds its own schemata.
chrismorgan 8y ago
Possibly my favourite thing about the rise of emoji is that they’re not Basic Multilingual Plane, and so stupid hacks like MySQL’s utf8 character set (I seriously don’t know why anyone ever thought it was a good idea in 2002–2004) are now obviously insufficient for full Unicode support, and enough people care about them that adoption of this basic level of Unicode is driven, and so non-English speakers are inadvertently helped. The next hurdle is to convince people that extended grapheme clusters are the smallest divisible unit, and that just like slicing at the byte level is insufficient if you’re doing UTF-8, slicing at the code point level is insufficient. Basically, strings should just be treated as opaque data in almost all places.
- philbarr 8y ago
  > not Basic Multilingual Plane, and so stupid hacks like the utf8 character set (I seriously don’t know why anyone ever thought it was a good idea in 2004) are now obviously insufficient for full Unicode support From [0]: UTF-8 was designed with a much larger limit of 231 (2,147,483,648) code points (32,768 planes), and can encode 221 (2,097,152) code points (32 planes) even if limited to 4 bytes What is wrong with UTF-8? [0]: https://en.wikipedia.org/wiki/Plane_(Unicode)#Basic_Multilingual_Plane https://en.wikipedia.org/wiki/Plane_(Unicode)#Basic_Multilin...
  - cosarara 8y ago
    Not with utf-8 but the "utf8" mysql encoding.
    - philbarr 8y ago
      ok - but parent referred to "utf8 character set". As if things weren't confusing enough already!
      - chrismorgan 8y ago
        They’re called character sets in SQL; see, for example, https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb3.html https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8... . I’ve clarified the original comment.
yoursql 8y ago
So MySQL is still MySQL. I'm so glad I ditched it.
- ofrzeta 8y ago
  Ridiculous. They fixed it 7 years ago.
  - jeltz 8y ago
    No, the default was fixed in MySQL 8.0, which is about to be released soon. MySQL has improved a lot since Oracle bought it but it takes time to undo all the bad design choices.
  - jillesvangurp 8y ago
    I can confirm I ran into this issue this year with AWS Aurora Mysql which definitely does the wrong thing by default. Basically the only reason we use mysql at this point is that aurora mysql works fine with t2.small.db and posgresql requires bigger instances somehow and is thus way more expensive to run. These days, I definitely prefer postgresql if I can get away with using that. I've also been eyeing cockroach db for a while. Their transactions are serializable by default and work in a clustered multi master & sharded setup is intriguing to me. Aurora updates still involve down time because you always have a single point of failure in the form of an active master.
chrismorgan 8y ago
One troublesome thing about utf8mb4: if you have a VARCHAR(n) column, this means 4n bytes are required; this doesn’t mix particularly well with indexes, as you can only index columns of up to 767 bytes; VARCHAR(192) is thus 768 bytes, and too long to be indexed—so you’re limited to VARCHAR(191) or less. This leads to things like django-celery flat-out not working on utf8mb4 without a (history-rewriting) patch, because it had a VARCHAR(255) primary key: https://github.com/celery/django-celery/issues/259 https://github.com/celery/django-celery/issues/259.
- pilif 8y ago
  then again, indexes on VARCHAR() can only really be used for equality and start matches. Now I'm not saying it's impossible, but I have a feeling you're not likely going to do a lot of `where string_column =… ` or `where string_column like '…%'` where … is 192 characters in length. If you need to do substring search or full text search, a traditional index won't help you anyways.
  - chrismorgan 8y ago
    Yeah, string primary keys are the only place I expect people to actually hit this, as seen in the django-celery case.
- Arie 8y ago
  This is supposedly fixed in databases created with MySQL 5.7 and up. > MySQL 5.7 (2015) added some optimizations such as a variable length sort buffer, and also changed InnoDB’s default row format to DYNAMIC. This allows for indexes on VARCHAR(255) with utf8mb4; something that made migrations more difficult prior. https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/ https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-ov...
spectrox 8y ago
It is worth mentioning that if you're stuck with InnoDB + COMPACT + utf8mb4 then you should be using VARCHAR(191) instead of VARCHAR(255) for unique columns. https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-antelope.html https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-an...
Strilanc 8y ago
Wait, isn't 4 bytes per character also not enough to store UTF8? Can't each character be up to 8 bytes long, because the number of leading 1s in the first byte determines the length of the character?
- ainar-g 8y ago
  According to the Wiki, the original UTF-8 proposal used up to 6 bytes.[1] But the Unicode standards people decided that 21 bit should be enough for everyone: >Since the restriction of the Unicode code-space to 21-bit values in 2003, UTF-8 is defined to encode code points in one to four bytes, depending on the number of significant bits in the numerical value of the code point. So, if they ever decide to use those extra two bytes, it will probably not be called UTF-8. Perhaps something like "UTF-8-MB-6". [1] https://en.wikipedia.org/wiki/UTF-8#History https://en.wikipedia.org/wiki/UTF-8#History
- philbarr 8y ago
  > Can't each character be up to 8 bytes long I don't think so. Not to point you to the wiki again but [0]. That's a pretty good description of how it works, so you don't get to use the all of the bits in the first byte to describe how long the character is in bytes. https://en.wikipedia.org/wiki/UTF-8#Description https://en.wikipedia.org/wiki/UTF-8#Description
- deleted 8y ago
  [deleted]
aiCeivi9 8y ago
> X have subtle bugs and oddities, and you can avoid a lot of bugs by avoiding X. That doesn't sound like a useful advice
Dylan16807 8y ago
Extremely important: If you use mysqldump, make sure it's also using utf8mb4. There is a very high chance it defaults to utf8 and silently corrupts your data.
- crazygringo 8y ago
  Oh my god, this is both terrifying and could one day prove to be the most valuable comment I've seen on HN. I love MySQL, but the fact that they didn't make utf8mb4 the default for mysqldump (instead of utf8), when they created utf8mb4 for database use... is one of the most irresponsible and negligent pieces of engineering I've seen in a long time, considering the amount of data worldwide backed up with mysqldump. The fact that a program designed solely to back up databases would default to silent corruption like this is mind-boggling. At least they've fixed this in the future for MySQL 8.0.
  - WrtCdEvrydy 8y ago
    Imagine how many companies out there are relying on backups that are already corrupted... is your company one of them?
    - ifoundthetao 8y ago
      This is an important reason why testing your backups is a critical part of backing up.
      - Dylan16807 8y ago
        It can be a very subtle issue, though. How would you recommend testing backups for rare corruption? Especially when live data is changing. With this I had used a backup and nobody even noticed the problem for three weeks, thankfully I had some more direct backups too...
      - mmt 8y ago
        This is oft-repeated advice, but, in this context, it ends up being little more than a platitude. Since the complaint here is of corruption that is silent, the level of testing required to catch it would be extraordinary.
danso 8y ago
The article mentions it, but one of the massive frustrations with this issue is the huge amount of misinformation surrounding it (at least back in 2013), with many well-intended users on StackOverflow and the MySQL forums giving the exactly wrong advice on collation. Naming things, and not getting confused by bad names, is damned hard. This issue led me to quit MySQL for good, I can’t think of a more trivial issue that caused me such confusion and frustration as a software engineer.
j_m_b 8y ago
It's a code smell when what is obviously and intuitively stated, is in fact, incorrect.
willvarfar 8y ago
> I’ll make a sweeping statement here: all MySQL and MariaDB users who are currently using “utf8” should actually use “utf8mb4”. Nobody should ever use “utf8”. I completely agree with this sentiment, although I recall being bitten by it in indexes. The article goes on to talk about how big a CHAR(1) is if its utf8 vs utf8mb4. Well, the same applies to varchars and so a varchar(64) is potentially requiring 64, 192 or 384 bytes, depending on the charset. This can bite you because the MySQL query planner limits the longest prefix used for index scans to iirc 767 or so bytes. So I migrated some columns from utf8 to utf8mb4 and suddenly my compound indices stopped being used because the varchar contents could legally exceed that maximum. Something like that.
skywhopper 8y ago
This is a fascinating tale. But the #1 takeaway by the author is dangerous: "Database systems have subtle bugs and oddities, and you can avoid a lot of bugs by avoiding database systems." This is correct as far as it goes, but it's poor advice. You may as well say "Software has subtle bugs and oddities, and you can avoid a lot of bugs by avoiding software." True! But not helpful! If you don't need a relational database, then yes, don't use one. But there's nothing specific about databases as a category that deserves a special callout. You can also avoid a lot of bugs by avoiding TLS, containers, threads, coroutines, blocking calls, asynchronous calls, networks, filesystems, operating systems, and computers.
- drb91 8y ago
  Dangerous? How do you figure? Many people assume a DB is the only way to store data. Now that’s dangerous.
  - sattoshi 8y ago
    Because filesystems don't contain oddities...
    - drb91 8y ago
      All storage systems have trade offs. What else is new?
- kimi 8y ago
  With a slight difference: MySQL or whatnot is battle-tested through millions of systems worldwide, while your in-memory data structure that you save to disk.... :-)
Bromskloss 8y ago
> In MySQL, never use “utf8”. Use “utf8mb4”. I thought you shouldn't use MySQL in the first place.
cdevnull 8y ago
I use utf8 for "normal" field like name, address, etc.. (don't want smiley as first name). And use utf8mb4 for comment for example. But I need filter/validate each inputs (and refuse when there is 4 bytes characters).
- Ryckes 8y ago
  Sorry for not doing my research but, aren't there 4-byte regular characters from other languages? Is it all emojis and symbols?
- khaled 8y ago
  There are more characters outside BMP than just emoji.
tambourine_man 8y ago
Anyone more knowledgeable than me care to explain the difference between utf8mb4_unicode_520_ci and utf8mb4_unicode_ci?
- detaro 8y ago
  Different versions of the Unicode collation algorithm, which determines how strings are sorted. 520 is Version 5.2.0, without the number it's 4.0.0 by default https://dev.mysql.com/doc/refman/5.6/en/charset-collation-names.html https://dev.mysql.com/doc/refman/5.6/en/charset-collation-na...
  - tambourine_man 8y ago
    Thanks, but what are the implications, if any, of using either one?
acabal 8y ago
This bit me hard many years ago in possibly the most frustrating bug I've encountered--frustrating because of the stupidity of MySQL's implementation. I had a utf8 MySQL database that was created back before utf8mb4 existed--this is, back in the days when emojis were just getting popular, but not quite popular enough to be on software keyboards; and when only a few people had smartphones and iPads, but not everyone. People would post long-form content to my site that took a long time for them to compose, and maybe once a month, someone would email me to complain that their long post was mysteriously cut short. Inspecting the database showed that the content was being stored there cut off, but with no rhyme or reason. Nothing in the error logs. 95% of other posts went through fine regardless of length or complexity. I put in tons of debugging code at various levels of the app to see where the data was being mis-parsed or truncated. Nothing! The bug persisted for years without a hint. Finally, emojis became more popular, the bug started occurring more and more often, and MySQL released utf8mb4; and then it dawned on me: Occasionally, people would submit content that contained a Unicode emoji generated by their software keyboard, and MySQL would silently truncate the emoji and all the data after it WITHOUT WARNING when storing it in a utf8 column!! Silently! I was so mad. A bug that plagued users for years, so much time and effort spent trying to debug it, and it was because of a combination of MySQL's dumb engineering decision to not support full Unicode for something called "utf8" and then silently truncating data without any warning. I don't know what the lesson is here. But I do know it made me really, really mad at MySQL when I found out what the problem was.
- mnm1 8y ago
  I think the lesson is "don't silently fail," and in that regard mysql has failed at learning that lesson over and over again to the point where the lesson is now, "don't use mysql" because what else can you do when dealing with such shitty software? This is far, far from the only stupid bug like this. Use mysql only if you don't care about preserving data. Period. There are many valid use cases that don't care about preserving data 100%, apparently most companies, so it's not a huge issue in reality. Hopefully the data that deals with monetary transactions won't get corrupted, but when it does, one cannot complain if they used mysql. It's that simple.
  - 8y ago
codetrotter 8y ago
Instead of MySQL, use PostgreSQL.
JackCh 8y ago
> "Once MySQL published this invalid character set, it could never fix it: that would force every user to rebuild every database. MySQL finally released UTF-8 support in 2010, with a different name: “utf8mb4”." Had MySQL defined UTF-8 as up to 6 bytes initially, would they later force users to rebuilt their databases when UTF-8 was redefined to only 4 bytes?
codedokode 8y ago
In case you want to convert your MySQL database tables from utf8 to utf8mb4 please remember that each character will now take 4, not 3 bytes. This means you can easily run out of limit for a row length (65536 bytes) if you have many string columns and your indexes will grow. Also, if a column is a part of a foreign key reference you might have to remove the foreign key first, upgrade columns on both sides and add the foreign key back. So converting an existing database might be not as easy as it seems before.

244 points by mariuz 8y ago

Good reminder. I did read a well hidden article many years ago which highly recommended this. I believe I was hitting an obscure, arcane bug like the OP mentioned in the article, and after much digging managed to find this fix. Since then, I always set every new MySQL database (and even double check the tables) to be 'utf8mb4' even long after the original lessons and pain have been forgotten.

golergka 8y ago

Oh, I love these kind of Unicode bugs. Also, please don't use utf16 in C# - although it advertises as using utf16, all of it's built-in string processing actually works on code units, not Unicode code points and breaks down on surrogate pairs. Anyway, each time you start a new project that is supposed to work correctly with multi-language data, please set up evil strings test suite as early as you can and do not assume that any of the 3rd party software that you're using won't break down.

machinecoffee 8y ago
Previous discussion on HN for 'Naughty Strings', was certainly an entertaining and eye opening read... https://news.ycombinator.com/item?id=10035008 https://news.ycombinator.com/item?id=10035008

morgo 8y ago

Hi! Former Product Manager for MySQL Server here. I have a blog post on this here: https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/ https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-ov... The default for MySQL 8.0 (current GA) is utf8mb4. I also recommend reading this introduction post here: http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf8-support-in-mysql-8-0/ http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf...

im3w1l 8y ago
Do you have a collation were no two different strings compare equal? Have you considered imposing an arbitrary order where you don't know which one "should" go first?
- morgo 8y ago
  Yes - there is a binary collation. w.r.t. arbitrary: The ordering must be deterministic so that indexes can maintain sorted order. It's possible to create a custom collation to have your own ordering - but I would say most users don't do that.
  - im3w1l 8y ago
    Sorry if I was unclear I meant some ordering that sorts by natural language but breaks ties in some arbitrary (but deterministic) manner, e.g. by binary.
JdeBP 8y ago
> An optimization was chosen to limit utf8 to 3 bytes You chose not to define the UNICODE_32BIT macro that was in the original source from 2002? * https://github.com/mysql/mysql-server/commit/55e0a9cb01af4b01bc4e4395de9e4dd2a1b0cf23#diff-95de3baa9e399c689954d1b8442d0be8R1611 https://github.com/mysql/mysql-server/commit/55e0a9cb01af4b0...
- morgo 8y ago
  The optimization was made at a different time in history. As I wrote in another comment here: while the decision is regrettable, because of a desire for backwards compatibility and simplified upgrade there wasn't an easy way out of it. The utf8[mb4] support in MySQL 8.0 is much better than prior releases. The team worked really hard on eliminating the use cases for utf8mb3 (performance) so that "just use the default of utf8mb4" can work for everyone.
  - jeltz 8y ago

ofrzeta 8y ago

I've "bookmarked" this: https://mathiasbynens.be/notes/mysql-utf8mb4 https://mathiasbynens.be/notes/mysql-utf8mb4 It digs a little bit deeper on the MySQL side (and it's from 2012 btw)

muglug 8y ago

Counterpoint: emojis are bad and should be discouraged

ajuc 8y ago
It's not only for emoji, it's also for less common scripts.
umanwizard 8y ago
Why?
gpvos 8y ago
The nice thing about emojis (maybe the only nice thing) is that they are so popular that they force companies and developers to implement proper support for all planes of Unicode, which means that all characters and symbols in those planes that are actually useful are then also supported.
morgo 8y ago
I feel just the opposite: emojis are a lot more expressive than text and more universal in language than en-us.
- nolliebs180 8y ago
  What about winking emojis? I feel there's a lot more ambiguity/nuance with them, which can be a good thing, but can also express something you may or may not intend to.

billpg 8y ago

Dear database developers, please don't get hung up on string lengths. I'd be very happy to ask the DB for (say) a UTF-8 string of up-to 80 bytes. That means I could store 80 ASCII characters, 40 two-byte characters, 20 four-byte characters or some mixture as I wish.

jeltz 8y ago
At least PostgreSQL does not care about string length, other than as required by the SQL standard (e.g. it enforces varchar(20) by counting the number of code points). The maximum size of a string in PostgreSQL is 1 GB.
morgo 8y ago
While I agree with you, as I understand the SQL standard defines the N in varchar(N) as characters (not bytes). There is a pathological case which is not common: max length of all 4-byte characters. Indexes are used to enforce features like unique constraints, it's important that everything can fit in the buffer length.

inyourtenement 8y ago

Currently leading a massive thorny project to convert all of our databases to utf8mb4. Definitely heed this warning.

chx 8y ago

> If you need a database, don’t use MySQL or MariaDB. Use PostgreSQL. This is stupid. There are pros and cons to every database. For example, MySQL allows you to set a trigger on an op, PostgreSQL requires you to write a function first.

ainar-g 8y ago
I agree that that point sounds too rough. I would write something like >Every software has issues; so know your software's gotchas. With that said, the perception I get is that PostgreSQL has less such gotchas and works better "out of the box". Though still not optimally, according to our Ops.
- dijit 8y ago
  PostgreSQL is pretty good- the default config limits the resource consumption to something you very likely wont want; and things like WAL shipping have some awkward gotchas but it's really stable and quite consistent. People usually argue against postgresql because it used to be slow on multi-core systems, the vacuum was kinda crappy in PGSQL <8.2 and it lacks a lot of features in base (because typically pgsql developers don't accept something in base unless it's very stable and orthogonal, which I consider to be a good thing). It's like the OpenBSD of Databases.
gmac 8y ago
Thing A having specific advantages over Thing B in no way implies that Thing B is not clearly superior overall. I have used both MySQL and Postgres, although admittedly I haven't touched MySQL for a few years (the last time I tried to use MySQL for something, I found it would happily serve me up an arbitrary result for a clearly nonsensical query). I thought this comment on an earlier thread summed up my feelings about the two systems rather beautifully: https://news.ycombinator.com/item?id=15173100 https://news.ycombinator.com/item?id=15173100
neverminder 8y ago
> This is stupid. Talk about stupid, run this query on both Mysql and Postgres: select 0 = 'banana'; Make your own conclusion.

kreetx 8y ago

Given that the article says that everyone should use utf8mb4, then why don't/haven't they just fixed it? Should be easy enough, and probably safe, too.

ofrzeta 8y ago
They have. It's the default in MySQL 8.
- kreetx 8y ago
  Sorry, should have bern more clear: why didn't they fix the bug in utf8 so that it is correct (and would do what utf8mb4 does now)? It's just that the obvious choice of 'utf8' has been plain wrong for a long time.
  - shadofx 8y ago
    from the article: >Once MySQL published this invalid character set, it could never fix it: that would force every user to rebuild every database.
morgo 8y ago
The desire for backwards compatibility/easier upgrades is why utf8mb4 could not just replace utf8: The sorting order for the utf8 collations is not identical to utf8mb4 ones, and so an upgrade would require indexed columns to be rebuilt (remember, indexes are sorted). I think with hindsight it would have been better to rip the bandaid off. 8.0 took a middle of the road approach by switching the default to utf8mb4.
johannes1234321 8y ago
This can't silently be changed as the setting has impact on disk format for data and indexes and so and will be confusing when using different server versions, i.e. in replication.

ofrzeta 8y ago

Side note: It's not enough to change the encoding of the database, you need to make sure that all involved parties use the same encoding (or convert the charset at the interfaces). For instance in a Laravel app I had to set the charset in the database configuration file "app/config/database.php".

MrBuddyCasino 8y ago

"An optimization was chosen to limit utf8 to 3 bytes, enough to handle almost all modern languages." I can't for the life of me imagine how, when discussing this in a room full of competent developers, everyone just went "great idea, lets do this". Just adds to the long list of questionable MySQL engineering decisions.

jeltz 8y ago
I am pretty sure it was not because of any optimization but that the real reason was to make sure a VARCHAR(255) would still fit within the maximum field size of InnoDB (768 bytes) at the time. Since then MySQL has added ROW_FORMAT=DYNAMIC to InnoDB which allows for larger values, but that probably required major changes to InnoDB.
- morgo 8y ago
  There are various buffers that were* not variable length in query execution (sorting, temp tables). It is not just about the on-disk format in this case. * Use MySQL 8.0 :-)
- MrBuddyCasino 8y ago
  There is usually a good reason for doing these kind of things. But then don't call it UTF8 encoding! It doesn't do that. It stores the most popular characters out of a larger address space. Call it UTF8-MB3 maybe from the start and let the user decide if having varchar(255) is worth the tradeoff. Thats the trouble with MySQL: doing silently the wrong thing, and thus violating the principle of least surprise. There is software where this might be ok, but a database is not that kind of software.
  - tinus_hn 8y ago
    In the case of MySQL (especially MySQL in 2003) bad design and silly behavior isn’t much of a surprise though :)
evanelias 8y ago
Sure, in hindsight, 3 bytes is an abomination to us today. That was almost certainly not apparent to developers at the time. MySQL added its three-byte utf8 support in mysql 4.1, first beta release in April 2003 [1]. Meanwhile the utf8 standard originally permitted up to 6 bytes per char, until officially changed in November 2003 [2]. Unicode support in web software -- and even programming languages -- was still rapidly evolving in 2003. The MySQL team probably just made a bad guess along the lines of "the standard allows 6 bytes but that has terrible perf and memory implications for us, and most people only use 3 bytes, so let's limit to that". While I've lost track of how many hundreds of times I've had to tell coworkers "use utf8mb4 and not utf8 in mysql", I don't begrudge the mysql dev team for not being clairvoyant in 2003! [1] https://lists.mysql.com/announce/161 https://lists.mysql.com/announce/161 [2] https://en.wikipedia.org/wiki/UTF-8#History https://en.wikipedia.org/wiki/UTF-8#History

Quarrelsome 8y ago

Friend of mine just recounted a story of when this bug killed all their MySql instances across an entire enterprise installation because a user sent an email with the hamster face emoji in the title.

bri3d 8y ago
I worked at large company where an emoji or other 4-byte UTF character in a commit message would take down multiple systems simultaneously, including JIRA and various internal auditing and pipeline monitoring tools. It happened surprisingly rarely considering the lack of safeguard against the issue.
- joombaga 8y ago
  This happened to us too. We first noticed it in Bamboo with a failed build job. It's been a long time since I installed Bamboo, but if I recall correctly the installer adds its own schemata.

chrismorgan 8y ago

Possibly my favourite thing about the rise of emoji is that they’re not Basic Multilingual Plane, and so stupid hacks like MySQL’s utf8 character set (I seriously don’t know why anyone ever thought it was a good idea in 2002–2004) are now obviously insufficient for full Unicode support, and enough people care about them that adoption of this basic level of Unicode is driven, and so non-English speakers are inadvertently helped. The next hurdle is to convince people that extended grapheme clusters are the smallest divisible unit, and that just like slicing at the byte level is insufficient if you’re doing UTF-8, slicing at the code point level is insufficient. Basically, strings should just be treated as opaque data in almost all places.

philbarr 8y ago
> not Basic Multilingual Plane, and so stupid hacks like the utf8 character set (I seriously don’t know why anyone ever thought it was a good idea in 2004) are now obviously insufficient for full Unicode support From [0]: UTF-8 was designed with a much larger limit of 231 (2,147,483,648) code points (32,768 planes), and can encode 221 (2,097,152) code points (32 planes) even if limited to 4 bytes What is wrong with UTF-8? [0]: https://en.wikipedia.org/wiki/Plane_(Unicode)#Basic_Multilingual_Plane https://en.wikipedia.org/wiki/Plane_(Unicode)#Basic_Multilin...
- cosarara 8y ago
  Not with utf-8 but the "utf8" mysql encoding.
  - philbarr 8y ago
    ok - but parent referred to "utf8 character set". As if things weren't confusing enough already!
    - chrismorgan 8y ago
      They’re called character sets in SQL; see, for example, https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb3.html https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8... . I’ve clarified the original comment.

yoursql 8y ago

So MySQL is still MySQL. I'm so glad I ditched it.

ofrzeta 8y ago
Ridiculous. They fixed it 7 years ago.
- jeltz 8y ago
  No, the default was fixed in MySQL 8.0, which is about to be released soon. MySQL has improved a lot since Oracle bought it but it takes time to undo all the bad design choices.
- jillesvangurp 8y ago
  I can confirm I ran into this issue this year with AWS Aurora Mysql which definitely does the wrong thing by default. Basically the only reason we use mysql at this point is that aurora mysql works fine with t2.small.db and posgresql requires bigger instances somehow and is thus way more expensive to run. These days, I definitely prefer postgresql if I can get away with using that. I've also been eyeing cockroach db for a while. Their transactions are serializable by default and work in a clustered multi master & sharded setup is intriguing to me. Aurora updates still involve down time because you always have a single point of failure in the form of an active master.

chrismorgan 8y ago

One troublesome thing about utf8mb4: if you have a VARCHAR(n) column, this means 4n bytes are required; this doesn’t mix particularly well with indexes, as you can only index columns of up to 767 bytes; VARCHAR(192) is thus 768 bytes, and too long to be indexed—so you’re limited to VARCHAR(191) or less. This leads to things like django-celery flat-out not working on utf8mb4 without a (history-rewriting) patch, because it had a VARCHAR(255) primary key: https://github.com/celery/django-celery/issues/259 https://github.com/celery/django-celery/issues/259.

pilif 8y ago
then again, indexes on VARCHAR() can only really be used for equality and start matches. Now I'm not saying it's impossible, but I have a feeling you're not likely going to do a lot of `where string_column =… ` or `where string_column like '…%'` where … is 192 characters in length. If you need to do substring search or full text search, a traditional index won't help you anyways.
- chrismorgan 8y ago
  Yeah, string primary keys are the only place I expect people to actually hit this, as seen in the django-celery case.
Arie 8y ago
This is supposedly fixed in databases created with MySQL 5.7 and up. > MySQL 5.7 (2015) added some optimizations such as a variable length sort buffer, and also changed InnoDB’s default row format to DYNAMIC. This allows for indexes on VARCHAR(255) with utf8mb4; something that made migrations more difficult prior. https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/ https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-ov...

spectrox 8y ago

It is worth mentioning that if you're stuck with InnoDB + COMPACT + utf8mb4 then you should be using VARCHAR(191) instead of VARCHAR(255) for unique columns. https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-antelope.html https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-an...

Strilanc 8y ago

Wait, isn't 4 bytes per character also not enough to store UTF8? Can't each character be up to 8 bytes long, because the number of leading 1s in the first byte determines the length of the character?

ainar-g 8y ago
According to the Wiki, the original UTF-8 proposal used up to 6 bytes.[1] But the Unicode standards people decided that 21 bit should be enough for everyone: >Since the restriction of the Unicode code-space to 21-bit values in 2003, UTF-8 is defined to encode code points in one to four bytes, depending on the number of significant bits in the numerical value of the code point. So, if they ever decide to use those extra two bytes, it will probably not be called UTF-8. Perhaps something like "UTF-8-MB-6". [1] https://en.wikipedia.org/wiki/UTF-8#History https://en.wikipedia.org/wiki/UTF-8#History
philbarr 8y ago
> Can't each character be up to 8 bytes long I don't think so. Not to point you to the wiki again but [0]. That's a pretty good description of how it works, so you don't get to use the all of the bits in the first byte to describe how long the character is in bytes. https://en.wikipedia.org/wiki/UTF-8#Description https://en.wikipedia.org/wiki/UTF-8#Description
deleted 8y ago
[deleted]

aiCeivi9 8y ago

> X have subtle bugs and oddities, and you can avoid a lot of bugs by avoiding X. That doesn't sound like a useful advice

Dylan16807 8y ago

Extremely important: If you use mysqldump, make sure it's also using utf8mb4. There is a very high chance it defaults to utf8 and silently corrupts your data.

crazygringo 8y ago
Oh my god, this is both terrifying and could one day prove to be the most valuable comment I've seen on HN. I love MySQL, but the fact that they didn't make utf8mb4 the default for mysqldump (instead of utf8), when they created utf8mb4 for database use... is one of the most irresponsible and negligent pieces of engineering I've seen in a long time, considering the amount of data worldwide backed up with mysqldump. The fact that a program designed solely to back up databases would default to silent corruption like this is mind-boggling. At least they've fixed this in the future for MySQL 8.0.
- WrtCdEvrydy 8y ago
  Imagine how many companies out there are relying on backups that are already corrupted... is your company one of them?
  - ifoundthetao 8y ago
    This is an important reason why testing your backups is a critical part of backing up.
    - Dylan16807 8y ago
      It can be a very subtle issue, though. How would you recommend testing backups for rare corruption? Especially when live data is changing. With this I had used a backup and nobody even noticed the problem for three weeks, thankfully I had some more direct backups too...
    - mmt 8y ago
      This is oft-repeated advice, but, in this context, it ends up being little more than a platitude. Since the complaint here is of corruption that is silent, the level of testing required to catch it would be extraordinary.

danso 8y ago

The article mentions it, but one of the massive frustrations with this issue is the huge amount of misinformation surrounding it (at least back in 2013), with many well-intended users on StackOverflow and the MySQL forums giving the exactly wrong advice on collation. Naming things, and not getting confused by bad names, is damned hard. This issue led me to quit MySQL for good, I can’t think of a more trivial issue that caused me such confusion and frustration as a software engineer.

j_m_b 8y ago

It's a code smell when what is obviously and intuitively stated, is in fact, incorrect.

willvarfar 8y ago

> I’ll make a sweeping statement here: all MySQL and MariaDB users who are currently using “utf8” should actually use “utf8mb4”. Nobody should ever use “utf8”. I completely agree with this sentiment, although I recall being bitten by it in indexes. The article goes on to talk about how big a CHAR(1) is if its utf8 vs utf8mb4. Well, the same applies to varchars and so a varchar(64) is potentially requiring 64, 192 or 384 bytes, depending on the charset. This can bite you because the MySQL query planner limits the longest prefix used for index scans to iirc 767 or so bytes. So I migrated some columns from utf8 to utf8mb4 and suddenly my compound indices stopped being used because the varchar contents could legally exceed that maximum. Something like that.

skywhopper 8y ago

This is a fascinating tale. But the #1 takeaway by the author is dangerous: "Database systems have subtle bugs and oddities, and you can avoid a lot of bugs by avoiding database systems." This is correct as far as it goes, but it's poor advice. You may as well say "Software has subtle bugs and oddities, and you can avoid a lot of bugs by avoiding software." True! But not helpful! If you don't need a relational database, then yes, don't use one. But there's nothing specific about databases as a category that deserves a special callout. You can also avoid a lot of bugs by avoiding TLS, containers, threads, coroutines, blocking calls, asynchronous calls, networks, filesystems, operating systems, and computers.

drb91 8y ago
Dangerous? How do you figure? Many people assume a DB is the only way to store data. Now that’s dangerous.
- sattoshi 8y ago
  Because filesystems don't contain oddities...
  - drb91 8y ago
    All storage systems have trade offs. What else is new?
kimi 8y ago
With a slight difference: MySQL or whatnot is battle-tested through millions of systems worldwide, while your in-memory data structure that you save to disk.... :-)

Bromskloss 8y ago

> In MySQL, never use “utf8”. Use “utf8mb4”. I thought you shouldn't use MySQL in the first place.

cdevnull 8y ago

I use utf8 for "normal" field like name, address, etc.. (don't want smiley as first name). And use utf8mb4 for comment for example. But I need filter/validate each inputs (and refuse when there is 4 bytes characters).

Ryckes 8y ago
Sorry for not doing my research but, aren't there 4-byte regular characters from other languages? Is it all emojis and symbols?
khaled 8y ago
There are more characters outside BMP than just emoji.

tambourine_man 8y ago

Anyone more knowledgeable than me care to explain the difference between utf8mb4_unicode_520_ci and utf8mb4_unicode_ci?

detaro 8y ago
Different versions of the Unicode collation algorithm, which determines how strings are sorted. 520 is Version 5.2.0, without the number it's 4.0.0 by default https://dev.mysql.com/doc/refman/5.6/en/charset-collation-names.html https://dev.mysql.com/doc/refman/5.6/en/charset-collation-na...
- tambourine_man 8y ago
  Thanks, but what are the implications, if any, of using either one?

acabal 8y ago

This bit me hard many years ago in possibly the most frustrating bug I've encountered--frustrating because of the stupidity of MySQL's implementation. I had a utf8 MySQL database that was created back before utf8mb4 existed--this is, back in the days when emojis were just getting popular, but not quite popular enough to be on software keyboards; and when only a few people had smartphones and iPads, but not everyone. People would post long-form content to my site that took a long time for them to compose, and maybe once a month, someone would email me to complain that their long post was mysteriously cut short. Inspecting the database showed that the content was being stored there cut off, but with no rhyme or reason. Nothing in the error logs. 95% of other posts went through fine regardless of length or complexity. I put in tons of debugging code at various levels of the app to see where the data was being mis-parsed or truncated. Nothing! The bug persisted for years without a hint. Finally, emojis became more popular, the bug started occurring more and more often, and MySQL released utf8mb4; and then it dawned on me: Occasionally, people would submit content that contained a Unicode emoji generated by their software keyboard, and MySQL would silently truncate the emoji and all the data after it WITHOUT WARNING when storing it in a utf8 column!! Silently! I was so mad. A bug that plagued users for years, so much time and effort spent trying to debug it, and it was because of a combination of MySQL's dumb engineering decision to not support full Unicode for something called "utf8" and then silently truncating data without any warning. I don't know what the lesson is here. But I do know it made me really, really mad at MySQL when I found out what the problem was.

mnm1 8y ago
I think the lesson is "don't silently fail," and in that regard mysql has failed at learning that lesson over and over again to the point where the lesson is now, "don't use mysql" because what else can you do when dealing with such shitty software? This is far, far from the only stupid bug like this. Use mysql only if you don't care about preserving data. Period. There are many valid use cases that don't care about preserving data 100%, apparently most companies, so it's not a huge issue in reality. Hopefully the data that deals with monetary transactions won't get corrupted, but when it does, one cannot complain if they used mysql. It's that simple.
- 8y ago

codetrotter 8y ago

Instead of MySQL, use PostgreSQL.

JackCh 8y ago

> "Once MySQL published this invalid character set, it could never fix it: that would force every user to rebuild every database. MySQL finally released UTF-8 support in 2010, with a different name: “utf8mb4”." Had MySQL defined UTF-8 as up to 6 bytes initially, would they later force users to rebuilt their databases when UTF-8 was redefined to only 4 bytes?

codedokode 8y ago

In case you want to convert your MySQL database tables from utf8 to utf8mb4 please remember that each character will now take 4, not 3 bytes. This means you can easily run out of limit for a row length (65536 bytes) if you have many string columns and your indexes will grow. Also, if a column is a part of a foreign key reference you might have to remove the foreign key first, upgrade columns on both sides and add the foreign key back. So converting an existing database might be not as easy as it seems before.