Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

medium.com

Post reply on HN
Print view View original

121–130 of 170 posts

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

#121

Post by acabal »

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.

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

#122

Post by teh_klev »

simonh wrote: post #96
twoodfin wrote: post #72

Earlier quoted context omitted.

I think folks who weren't there might not realize how big a deal the original Unicode standard was and how quickly technology vendors raced to adopt it. Windows NT, for example, must have been developed with Unicode in mind even before the standard was official. In those circumstances the broad adoption of UCS-2 was effectively inevitable. Even if UTF-8 had been widely known, I don't think the eventual necessity for…

One factor is that back in the early 90s China was still an economically backward, closed Communist enclave barely more integrated into the global economy than North Korea is now. One of the deciding factors in having to decisively move beyond the BMP was China's adoption of GB 18030 as a mandatory standard in 2006[1]. [1] https://en.wikipedia.org/wiki/GB_18030

> One factor is that back in the early 90s China was still an economically backward, closed Communist enclave barely more integrated into the global economy

I'd disagree, China was well on the way to becoming an economic power house in the early 90's:

https://en.wikipedia.org/wiki/Five-year_plans_of_China#Eight...

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

#123

Post by detaro »

tambourine_man wrote: post #120

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

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-na...

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

#124

Post by gpvos »

Piskvorrr wrote: post #108
bradleyjg wrote: post #90

Earlier quoted context omitted.

> How exactly is Latin-1 privileged in this regard? Latin-1 is sufficient for languages which countries representing more than 50% of global GDP speak. It might not be fair but I’m not sure this is a question of fairness to begin with.

Oh. In that case, you do mean ASCII, methinks (which also works), with Latin-1 you're shooting yourself in both feet. (Looks for link to "falsehoods programmers believe about encodings")

Not really if you can make a good living writing software for only the US/UK, France, Germany, Italy and a few more Western European countries, which is perfectly feasible. Latin1 works perfectly fine there, and if you wanted, and you didn't have to interact too much with other systems, you could just completely ignore all other encodings until UTF-8 started to get traction in the later 1990s. It even got into many RFCs as the default encoding until the RFC editors started to enforce everyone to implement UTF-8 and default to it.

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

#125

Post by steveklabnik »

chrismorgan wrote: post #62
philbarr wrote: post #53

Earlier quoted context omitted.

Can't reply to the sibling comment by Chris, but there isn't a "utf8 character set". There's just different encodings of a character set. Even after hunting around after going to the 404 you pointed me to, I still get this: utf8mb4: A UTF-8 encoding of the Unicode character set using one to four bytes per character. utf8mb3: A UTF-8 encoding of the Unicode character set using one to three bytes per character. So I do…

Not sure why the reply link is sometimes missing, but in my experience if you open just that comment (via the “N minutes ago” link) the textbox for replying is there and works. Perhaps the link is omitted to discourage rapid conversation? I fixed the link almost immediately—pesky inclusion of the dot in “.)” --- The SQL specification (I cite SQL-92) speaks of character sets (and uses the keyword CHARACTER SET). That…

> Perhaps the link is omitted to discourage rapid conversation?

That's correct, in my understanding. And yes, clicking on "N minutes ago" is the way around it.

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

#126

Post by makmanalp »

crazygringo wrote: post #91
Dylan16807 wrote: post #50

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.

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. Th…

Uh oh. https://stackoverflow.com/questions/20216849/mysqldump-with-...

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

#127

Post by tambourine_man »

Dylan16807 wrote: post #50

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.

Thanks, that would be:

mysqldump --default-character-set=utf8mb4 -u user database > dump.sql

right?

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

#128

Post by tambourine_man »

detaro wrote: post #123
tambourine_man wrote: post #120

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

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-na...

Thanks, but what are the implications, if any, of using either one?

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

#129

Post by kilburn »

bradleyjg wrote: post #90
Piskvorrr wrote: post #87

Earlier quoted context omitted.

How exactly is Latin-1 privileged in this regard? Note its suffix - writing this from a region where other single-byte encodings proliferated in the 1990s, before sort-of settling to Latin-2 and/or Windows-1250 in the aughts (no, they're not the same, and the mapping is subtly broken). If you mean "ASCII was good for our grandfathers", say so - but don't pretend that Latin-n somehow was not a bastardized set of hacks…

> How exactly is Latin-1 privileged in this regard? Latin-1 is sufficient for languages which countries representing more than 50% of global GDP speak. It might not be fair but I’m not sure this is a question of fairness to begin with.

I beg to differ, considering that it doesn't contain the € sign.
Post reply on HN