5 ms·

Along the same lines of Unicode horror stories, my personal favorite is the MySQL's original 3-byte non-standard unicode implementation, called "utf8" and then

by devy 8y ago

Along the same lines of Unicode horror stories, my personal favorite is the MySQL's original 3-byte non-standard unicode implementation, called "utf8" and then later renamed to "utf8mb3". [1] It only covers the Basic Multilingual Plane (BMP). And it felt like the designed decision were made similar like those on MyISAM db engine that in retrospective, those were the wrong optimization that caused more trouble than bring benefits. And they didn't fix it until MySQL version 5.5.3 via introduction of "utf8mb4" charset. In a very obvious way, if you use emojis in your application with MySQL backend and not using the full 4-byte "utf8mb4" charset, you will absolutely get bitten by that gotcha. [2][3][4] [1]: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8... [2]: https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e434 https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-u... [3]: https://stackoverflow.com/questions/202205/how-to-make-mysql-handle-utf-8-properly https://stackoverflow.com/questions/202205/how-to-make-mysql... [4]: https://www.eversql.com/mysql-utf8-vs-utf8mb4-whats-the-difference-between-utf8-and-utf8mb4/ https://www.eversql.com/mysql-utf8-vs-utf8mb4-whats-the-diff...

tracker1 8y ago
There should be a big FAT warning in the mysql/mariadb docs that "utf8" should NEVER be used, and "utf8mb4" is likely the intended use... A future full version cut should probably change the alias for "utf8" to the proper implementation. Came across this the last time I used mysql, fortunately before it was too far into the project. In any case, every time I've ever used mysql, there's something that pisses me off about it. Like indexing on a BINARY field is using case-insensitive comparisons, like it's actually text.
dvtrn 8y ago
if you use emojis in your application with MySQL backend and not using the full 4-byte "utf8mb4" charset, you will absolutely get bitten by that gotcha. Did we use to work together? Because I've been one of those poor DBAs to inherit a system that was not using utf8mb4 charsets, and sure damn enough an emoji took down said system for hours affecting every client on the books. They recovered, but limped along for another two years before eventually getting bought out and effectively soft-shutting down. I was hired post-Armageddon to keep the lights on and eventually left once the acquisition completed.
- k__ 8y ago
  Doesn't seem to be a rare problem. When I did a web project in university in 2014, the assistant professor warned me at the beginning that this will happen with MySQL and Emoji, because he had the problem the last semester. And then in 2017, years after I learned that this was a huge problem, I got a freelance app project where they already had a back-end developer. The app crashed multiple times after release when I finally realized the back-end dev didn't know anything about this and simply clicked together the DB in phpmyadmin with all the default settings...
  - dvtrn 8y ago
    You're probably right that it's not a rare problem, but at the right scale it can be a business ending problem.
    - k__ 8y ago
      True. I'm just amazed that there seems to be a fair amount of back-end developers who don't know anything about this. Probably because SQL systems are used at more conservative companies with older customers?
      - dvtrn 8y ago
        That's a good question. I think part of this can be attributed to the efforts taken by the database World to demystify database operations and abstract some of the processes to the point where infrastructure by click became easy enough for these problem to be completely forgotten about or overlooked by your modern developer doing business as DBA (swidt?). Can insert? Can query? Nothing more to do here.
- devy 8y ago
  Did we use to work together? Because I've been one of those poor DBAs to inherit a system that was not using utf8mb4 charsets, and sure damn enough an emoji took down said system for hours affecting every client on the books. Perhaps ;) My first major project out of current job was spending two months figure out why our system doesn't support emojis. And MySQL was the blame, I want my two months back.
avar 8y ago
I wonder if anyone else has the experience of complaining about and pointing this problem out for a decade or more before emojis become widespread, and pretty much nobody cared ("they speak what language?"). But as soon as emojis became more widespread suddenly proper Unicode support became everyone's problem.
- pkulak 8y ago
  Pretty smart to put the fun stuff at the end so that everyone has to worry about everything in the middle.
  - bdamm 8y ago
    There must be a name for this approach to software design. That is, creating a forcing function that must correctly handle the popular case which by proxy handles a majority of less common but still important cases. (I'm aghast that MySQL had this issue!)
    - marcodave 8y ago
      it might be called ️-dd (aka emoji-driven-design)
- devy 8y ago
  There are tons of unicode characters that are outside of BMP that MySQL's original 3-byte implementation didn't support. [1] It just happened that emojis have been becoming wider spread than those non-BMP unicode characters. [1]: https://stackoverflow.com/questions/5567249/what-are-the-most-common-non-bmp-unicode-characters-in-actual-use https://stackoverflow.com/questions/5567249/what-are-the-mos...
Dylan16807 8y ago
Also, mysqldump has to be separately forced to use utf8mb4 or your backups will be silently corrupted.
- nouseforaname 8y ago
  That I did not know. Fun.
mschuster91 8y ago
> In a very obvious way, if you use emojis in your application with MySQL backend and not using the full 4-byte "utf8mb4" charset, you will absolutely get bitten by that gotcha. Hello JIRA Servicedesk, which at least half a year ago still preferred "old" utf8. Over the winter holidays I'll have to, basically, reinstall the entire fucking thing.
ubernostrum 8y ago
When I worked at Mozilla, on MDN, we had this mysterious bug: https://bugzilla.mozilla.org/show_bug.cgi?id=776048 https://bugzilla.mozilla.org/show_bug.cgi?id=776048 The tl;dr is that MDN is a multilingual site, and supports categorization in a few ways, including by tagging. An example of the bug: the English MDN's CSS reference tagged articles with "CSS Reference". The French MDN's CSS reference tagged them "CSS Référence". Sometimes, the French tag appeared on English articles. The source of the issue was MySQL's utf8_general_ci collation, which did not see "e" and "é" as distinct, so it was a toss-up as to which tag you'd get back from the database. The solution was to build and install a custom collation to teach MySQL to see accented and unaccented characters as distinct.
- valesco 8y ago
  Good job on improving the system! However it seems like using 'Référence CSS' would have been more correct grammatically and wouldn't have caused the problem.
  - ubernostrum 8y ago
    The authors/editors for different languages were the ones who chose the styling of the tags. Don't know why they put it that way, but they did. Our job was just to make it work.

by devy 8y ago

tracker1 8y ago

There should be a big FAT warning in the mysql/mariadb docs that "utf8" should NEVER be used, and "utf8mb4" is likely the intended use... A future full version cut should probably change the alias for "utf8" to the proper implementation. Came across this the last time I used mysql, fortunately before it was too far into the project. In any case, every time I've ever used mysql, there's something that pisses me off about it. Like indexing on a BINARY field is using case-insensitive comparisons, like it's actually text.

dvtrn 8y ago

if you use emojis in your application with MySQL backend and not using the full 4-byte "utf8mb4" charset, you will absolutely get bitten by that gotcha. Did we use to work together? Because I've been one of those poor DBAs to inherit a system that was not using utf8mb4 charsets, and sure damn enough an emoji took down said system for hours affecting every client on the books. They recovered, but limped along for another two years before eventually getting bought out and effectively soft-shutting down. I was hired post-Armageddon to keep the lights on and eventually left once the acquisition completed.

k__ 8y ago
Doesn't seem to be a rare problem. When I did a web project in university in 2014, the assistant professor warned me at the beginning that this will happen with MySQL and Emoji, because he had the problem the last semester. And then in 2017, years after I learned that this was a huge problem, I got a freelance app project where they already had a back-end developer. The app crashed multiple times after release when I finally realized the back-end dev didn't know anything about this and simply clicked together the DB in phpmyadmin with all the default settings...
- dvtrn 8y ago
  You're probably right that it's not a rare problem, but at the right scale it can be a business ending problem.
  - k__ 8y ago
    True. I'm just amazed that there seems to be a fair amount of back-end developers who don't know anything about this. Probably because SQL systems are used at more conservative companies with older customers?
    - dvtrn 8y ago
      That's a good question. I think part of this can be attributed to the efforts taken by the database World to demystify database operations and abstract some of the processes to the point where infrastructure by click became easy enough for these problem to be completely forgotten about or overlooked by your modern developer doing business as DBA (swidt?). Can insert? Can query? Nothing more to do here.
devy 8y ago
Did we use to work together? Because I've been one of those poor DBAs to inherit a system that was not using utf8mb4 charsets, and sure damn enough an emoji took down said system for hours affecting every client on the books. Perhaps ;) My first major project out of current job was spending two months figure out why our system doesn't support emojis. And MySQL was the blame, I want my two months back.

avar 8y ago

I wonder if anyone else has the experience of complaining about and pointing this problem out for a decade or more before emojis become widespread, and pretty much nobody cared ("they speak what language?"). But as soon as emojis became more widespread suddenly proper Unicode support became everyone's problem.

pkulak 8y ago
Pretty smart to put the fun stuff at the end so that everyone has to worry about everything in the middle.
- bdamm 8y ago
  There must be a name for this approach to software design. That is, creating a forcing function that must correctly handle the popular case which by proxy handles a majority of less common but still important cases. (I'm aghast that MySQL had this issue!)
  - marcodave 8y ago
    it might be called ️-dd (aka emoji-driven-design)
devy 8y ago
There are tons of unicode characters that are outside of BMP that MySQL's original 3-byte implementation didn't support. [1] It just happened that emojis have been becoming wider spread than those non-BMP unicode characters. [1]: https://stackoverflow.com/questions/5567249/what-are-the-most-common-non-bmp-unicode-characters-in-actual-use https://stackoverflow.com/questions/5567249/what-are-the-mos...

Dylan16807 8y ago

Also, mysqldump has to be separately forced to use utf8mb4 or your backups will be silently corrupted.

nouseforaname 8y ago
That I did not know. Fun.

mschuster91 8y ago

> In a very obvious way, if you use emojis in your application with MySQL backend and not using the full 4-byte "utf8mb4" charset, you will absolutely get bitten by that gotcha. Hello JIRA Servicedesk, which at least half a year ago still preferred "old" utf8. Over the winter holidays I'll have to, basically, reinstall the entire fucking thing.

ubernostrum 8y ago

When I worked at Mozilla, on MDN, we had this mysterious bug: https://bugzilla.mozilla.org/show_bug.cgi?id=776048 https://bugzilla.mozilla.org/show_bug.cgi?id=776048 The tl;dr is that MDN is a multilingual site, and supports categorization in a few ways, including by tagging. An example of the bug: the English MDN's CSS reference tagged articles with "CSS Reference". The French MDN's CSS reference tagged them "CSS Référence". Sometimes, the French tag appeared on English articles. The source of the issue was MySQL's utf8_general_ci collation, which did not see "e" and "é" as distinct, so it was a toss-up as to which tag you'd get back from the database. The solution was to build and install a custom collation to teach MySQL to see accented and unaccented characters as distinct.

valesco 8y ago
Good job on improving the system! However it seems like using 'Référence CSS' would have been more correct grammatically and wouldn't have caused the problem.
- ubernostrum 8y ago
  The authors/editors for different languages were the ones who chose the styling of the tags. Don't know why they put it that way, but they did. Our job was just to make it work.