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

91–100 of 170 posts

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

#91

Post by crazygringo »

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.

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.

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

#92

Post by chrismorgan »

philbarr wrote: post #70
chrismorgan wrote: post #62

Earlier quoted context omitted.

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…

> utf8mb3 is an alias for utf8 which was introduced after utf8 I didn't know this - and although I don't use MySQL much these days it good to know, thank you. > in SQL, each Unicode encoding is a CHARACTER SET.... I see what you mean. That's pretty confusing... > but in my experience if you open just that comment (via the “N minutes ago” link) the textbox for replying is there and works It totally does!

I didn't know about the utf8mb3 name until today.

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

#93

Post by jillesvangurp »

ofrzeta wrote: post #20
yoursql wrote: post #18

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

Ridiculous. They fixed it 7 years 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.

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

#95

Post by kimi »

skywhopper wrote: post #74

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 databa…

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.... :-)

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

#96

Post by simonh »

twoodfin wrote: post #72
gpvos wrote: post #51

Earlier quoted context omitted.

Windows NT 3.1 was released in July 1993. Early versions of Windows NT used UCS-2. At that time, UTF-8 was not very well known yet, and it was certainly too late to change all that code, and especially the APIs. I'm not sure about Java and Javascript, but I think they were developed before it became clear to most people that UCS-2 was not going to be enough (which was around, or likely even sometime after the time th…

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

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

#97

Post by sulam »

morgo wrote: post #80
masklinn wrote: post #59

Earlier quoted context omitted.

> While I’m contemplating why people might have thought utf8mb3 was wise in 2004 Because this is 2004 MySQL, they weren't exactly known for technical acumen and forward thinking. The root of their decision is that behind the scenes "UTF8MB4" stores codepoints in fixed-size areas. And MySQL could not index columns larger than 767 bytes. Guess what doesn't fit in 767 bytes? VARCHAR(255) in UTF8MB4. Anything above VARCH…

> Guess what doesn't fit in 767 bytes? VARCHAR(255) in UTF8MB4. Anything above VARCHAR(191) would not be indexable. The utf8mb3 optimization was made before InnoDB was the default storage engine (2010).

It was widely used before it was the default.

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

#98

Post by liveoneggs »

masklinn wrote: post #59
chrismorgan wrote: post #33

Earlier quoted context omitted.

While I’m contemplating why people might have thought utf8mb3 was wise in 2004, I’ll ask a similar question that I’d love to know the answer to, of events from my early childhood: why did UTF-16 gain popularity? I get UCS-2 existing, but once it became evident it wasn’t enough, why make UTF-16 and ruin Unicode with surrogate pairs (which still cause trouble today, leading to things like WTF-8 to make it possible for…

> While I’m contemplating why people might have thought utf8mb3 was wise in 2004 Because this is 2004 MySQL, they weren't exactly known for technical acumen and forward thinking. The root of their decision is that behind the scenes "UTF8MB4" stores codepoints in fixed-size areas. And MySQL could not index columns larger than 767 bytes. Guess what doesn't fit in 767 bytes? VARCHAR(255) in UTF8MB4. Anything above VARCH…

I like how your write this as if I'm not still running into these index column size issues today ;)

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

#99

Post by jeltz »

morgo wrote: post #79
jeltz wrote: post #48

Earlier quoted context omitted.

Is it really an optimization? I always assumed they picked 3 bytes to make sure a varchar(255) still would fit within the InnoDB field size limit of 768 bytes, so they could avoid/postpone having to fix InnoDB to support variable size fields.

This decision was made before InnoDB was the default. While the performance differences are historical now, consider the case that because of fixed sized buffers, one query could switch from a temp table in memory to a temp table on disk. The profile of the query has effectively changed.

Yes, but did it happen before InnoDB was widely used?
Post reply on HN