24 ms·

In MySQL, use “utf8mb4” instead of “utf8” (2016)

643 points by goranmoomin 5y ago

patrickcteng 5y ago
I've had to "upgrade" a couple to utf8, and varchar(255) bites me all the time.
mwattsun 5y ago
Has anyone ever successfully converted a large legacy Apache/PHP/MySQL site to UTF-8? If so you deserve an award. utf8mb4 is just one issue and not the worst.
- usbqk 5y ago
  Sure, you just prepend mb_ to all php string calls >:)
- onli 5y ago
  https://github.com/s9y/Serendipity/blob/05f58f90d743fe9ade24f3fdfe9a934d0b87c6a1/include/db/mysqli.inc.php#L320 https://github.com/s9y/Serendipity/blob/05f58f90d743fe9ade24... is just the detection function I wrote for a PHP blog engine to detect whether it would even be possible to use utf8mb4 on the system. We completely ran into this issue and didn't know how to handle it for literally years. Lots of blogs to migrate, and who knows whether it worked for all of them...
  - mwattsun 5y ago
    The craziest issue I had was I couldn't predict what char encoding the text in my database was in. Most users entered Windows-1252, some text blobs were UTF-16, others were European character sets, and some were UTF-8. Some were Japanese SHIFT_JIS. Don't ask me how any of this happened. I retrospect, I should have dumped all the tables from MySQL and used the excellent PyPy Chardet [1] library to see what I was dealing with, do the conversions and then re-import the data. But then someone could copy UTF-16 from a Windows document and paste it in, so you have to convert going in to the database. You have set Apache to UTF-8, PHP to UTF-8, MySQL to UTF-8, and the MySQL driver you are using to UTF-8. It's not clear how these setting interact. Are there silent conversions happening or do you always have to detect the encoding on data coming from the server? HTML pages have a character encoding specifier, but the BOM at the start of the file takes precedence (I think.) I got it to work by always detecting encoding for any text coming from the database and using iconv, but this turned out to be really slow and unreliable. It was truly the biggest mess by an order of magnitude than any other programming problem I faced in my career. Would not attempt again. [1] https://github.com/chardet/chardet https://github.com/chardet/chardet
- sam_lowry_ 5y ago
  Yes, but I have chosen utf8_bin as encoding and lived happily with this choice for a dozen years.
- ospzfmbbzr 5y ago
  Yes. I had the exact same issue with '<' in HTML content stored in the database -- cms pages and also some JS that was stored in the DB and injected into the HEAD of all pages.
- charrondev 5y ago
  Technical lead for vanillaforums. It’s a huge PHP MySQL app. Probably 10b+ rows in production DBs. We did the the migration in 2017 if I remember correctly.
  - mwattsun 5y ago
    Not to take anything away but by legacy I meant late last century sites. VanillaForums was released in July 2006 according to Wikipedia, which makes it Web 2.0. Wikipedia also says "UTF-8 has been the most common encoding for the World Wide Web since 2008" I'm glad you posted though because now I know that sites are ok starting in that time frame, not before and then never touch the old ones again.
- ars 5y ago
  Been there, done that, with a phpbb installation. I manually converted all the non-UTF data in the database to UTF8, and updated the PHP code and database settings. It worked fine, no major issues, just some work.
- fleddr 5y ago
  At least once, but it's been a while. From the top of my head: - set encoding at HTML head - also at any HTML form element - Set database collation correctly - I believe it also needs to be done per table - The PHP -> MySQL connection itself may need to have the encoding set - All string manipulation to be done with mb_ - Even ensure your code files themselves are in UTF8
kyralis 5y ago
> Database systems have subtle bugs and oddities, and you can avoid a lot of bugs by avoiding database systems. Wat? That is... not a great takeaway from this experience. "Oh, this library had bug reported in it! I know, that means I should just write my own, because clearly they're all buggy and my software is always perfect."
- cbg0 5y ago
  I'm pretty sure you missed the joke.
- jerf 5y ago
  Well, I mean, the good news is that if you write your own database system, you'll have so many other bugs in the way that you'll likely never reach this particular one! I suggest it can be called the Sirius Database. "It is very easy to be blinded to the essential uselessness of [the new database you just wrote] by the sense of achievement you get from getting them to work at all. In other words—and this is the rock solid principle on which the whole of the Corporation's Galaxy-wide success is founded—their fundamental design flaws are completely hidden by their superficial design flaws."
  - piyh 5y ago
    >their fundamental design flaws are completely hidden by their superficial design flaws That hit me right in my 9-5 "not invented here" nightmare.
- acdha 5y ago
  It's especially specific to MySQL — switching to Postgres is great because in addition to better performance and features there are all of these rakes in the grass you can forget about. Unicode just works, you never lose data because someone didn't scrupulously check the warnings after every query, etc.
  - TedDoesntTalk 5y ago
    Doesn't Postgres have its own set of unique of problems?
tapoxi 5y ago
Maybe this is a cynical take - but we used https://pgloader.io/ https://pgloader.io/ a few years ago to migrate to Postgres, and have never been happier. MySQL has a lot of stupid decisions like this.
- dijit 5y ago
  I'm a big posgresql fan, but I'd maybe argue that you're making tradeoffs. PG has weird behaviour sometimes too. I don't think it's always smart to just change out your data layer. But, if you're starting a new project, I do think PG is one of the better options and tends to follow the principle of least surprise. (hence: big fan)
  - leetrout 5y ago
    As a huge Postgres fan working at a shop using MySQL I'll point out the first thing I saw MySQL does that Postgres doesn't: Index hints. If you aren't hitting an index in Postgres you have to dig in to table stats and figure out what is wrong but MySQL gives you more control. However, I would still rather work with Postgres AND have to juggle a connection pooler than deal with MySQL. Transactions on DDL are _great_ and the ability to use foreign keys across partitioned tables is how it should be.
    - spookthesunset 5y ago
      But at least Postgres has much better tools for learning about the query plans it might use. The Postgres output of “EXPLAIN” is much much better than MySQL. It’s a shame that almost every job I worked at uses MySQL and not Postgres. But that could be because those companies all got their start like a decade or more ago when Postgres was not as well known.
    - pzduniak 5y ago
      > As a huge Postgres fan working at a shop using MySQL I'll point out the first thing I saw MySQL does that Postgres doesn't: > Index hints. Eh, it's an extension. One you shouldn't use, but it's there.
capitainenemo 5y ago
This is also a security vulnerability because MySQL/MariaDB silently truncate strings at the first invalid character. This can result in data manipulation attacks where a higher level layer validates the complete input as UTF-8 for insertion into the DB, but the database only stores half the string. https://bugzilla.mozilla.org/show_bug.cgi?id=1253201 https://bugzilla.mozilla.org/show_bug.cgi?id=1253201
- wanderr 5y ago
  Any data in your database that can come from external input should be treated as untrusted and validated before it's used. Otherwise validation bugs or bypasses will result in bad data and exploits that persist beyond the fix. Edit: I’m not arguing against the need to use utf8mb4
  - capitainenemo 5y ago
    Agreed. The confusing part here as I see it is where validation layer A (correctly) asserts the data is valid UTF-8 and safe then assumes the database persists what it passes to it, since no error is reported. Then, subsystem B trusts reading the database field (since it passed validation layer A). Obviously more validation layers can be added, but at this point validation layer C called by subsystem B needs to know what the initial input from layer A is in order to differentiate it from the db value which was manipulated - a rather tricky thing to do sometimes. (I guess you could add a hash to the db to check the db is storing your strings, but really.. come on) Upgrading to utf8mb4 is probably safer than hoping enough validation layers thrown at it solves the problem.
    - wanderr 5y ago
      Yes. I’ve been burned by this exact issue in the past, not realizing that utf8 was not really utf8 in mysql-land. That is a major, major WTF IMO. But I’ve also seen people relying on validation at the time of insertion so many times that I wanted to warn against that, too. Not in argument against the need for utf8mb4.
  - 5y ago
PrimeDirective 5y ago
The author mentioned MariaDB in the intro and the went on about MySQL, not sure which he actually used. I think the latest recommendation for MySQL was utf8mb4_0900_ai_ci and utf8mb4_general_ci for MariaDB. While I agree about the Postgres recommendation, it's a non-starter in the PHP world, unless doing something from scratch. Even then, the support for MySQL/MariaDB is much better.
- timw4mail 5y ago
  What's that mean, non-starter in the PHP world? Postgres is well supported by frameworks and libraries in PHP, not to mention PHP itself.
  - PrimeDirective 5y ago
    For one, it's out of reach for lots of folks who use shared hosting services, where there's a managed LAMP stack. It's rare if they have Postgres. If you use AWS, of course, everything is up to you. There's also a connection pooling and different startup for MySQL/Mariadb vs Postgres. PHP engine generates the page and closes itself and connections. Postgres assumes you are more persistent. Also, there's a difference in how GROUP BY works in MySQL/Mariadb vs Postgres, and wrapping your head around it, after using it the MySQL way for years, can take time. So not a drop-in replacement.
    - isotopp 5y ago
      ONLY_FULL_GROUP_BY is the default in MySQL since 5.7.5.
davidjfelix 5y ago
Does this even fully fix the problem? It looks like utf8mb4 is limited to 4 byte sequences, but as far as I understand, utf-8 is variable width. Can utf8mb4 even encode the scottish flag https://www.iemoji.com/view/emoji/2476/flags/scotland https://www.iemoji.com/view/emoji/2476/flags/scotland ?
- fredoralive 5y ago
  Legal UTF8 is limited to 4 bytes, as Unicode only uses ranges that fit the limits of UTF16. AFAIK The flags are a weird multi-code point encoding of the ISO country codes, and each individual code point is less than 4 bytes.
- jerf 5y ago
  Flags are not single code points. UTF-8 refers to how code points are stored. If you look in your link at "Hex Code Point(s)", it is that first one that would be a problem with "utf8" in MySQL, because of the 1 in the 0x10000 position. The other six code points required would fit in fine. Unicode is developing more and more things that require code points. I'm not sure what the longest legal non-redundant series of code points that can validly represent a glyph somewhere is, but it's getting up there with all the emoji skin modifiers and such.
  - jeroenhd 5y ago
    Emoji modifiers, both for gender and skintone, do not produce that many extra code points. Combinations are made in the same way ¨ + e can combine into ë: a code point, followed by a combination code point, followed by a modifier. During text rendering, these code points are converted back into a single glyph. All UTF-8 codepoints in use today can be encoded with four bytes. Theoretically the Unicode system can be used to create 6 byte code points if that ever becomes necessary, but it won't be for a while. Crossing the 4 byte boundary would also introduce compatibility issues with UTF-16, so I'm sure the Unicode Consortium will do their best to prevent this from happening as long as they can.
    - jerf 5y ago
rubyist5eva 5y ago
god I hate mysql so much - long live postgres, one of the greatest pieces of software ever created
hn_throwaway_99 5y ago
Wow, never knew this, this is really bad, especially in this day and age where emojis are so prevalent. MySQL should deprecate utf8 and give a warning if you try to use it.
- daneel_w 5y ago
  Bad idea. If they deprecate "utf8" they will deprecate a standard. It was designed and specified at a time when the Unicode code space had a 21 bit limit. There are some technical (storage space-related) considerations with the suggestion to "just use utf8mb4 everywhere instead" because of how InnoDB's indices work.
  - loeg 5y ago
    MySQL "utf8" is still "utf8mb3", which is not a standard anywhere except MySQL. It cannot store the full range of 21-bit code points; I don't know why you keep repeating this. The maximum codepoint in 3-byte UTF-8 is 0xFFFF, which is 16 bits.
  - hn_throwaway_99 5y ago
    > If they deprecate "utf8" they will deprecate a standard. No, they won't. They will be deprecating something they created that never conformed to the UTF-8 standard. That fact that unicode didn't have codes beyond 21 bits at that point is pretty irrelevant. It was an invalid implementation from day 1, at least with the name of "utf8".
  - faho 5y ago
    >It was designed and specified at a time when the Unicode code space had a 21 bit limit 21 bits is the current Unicode limit. Unfortunately UTF-8 in 3 Bytes only has 16 usable bits - the first byte starts with 1110 and the two continuation bytes start with 10, so it's 4 + 6 + 6 = 16 bits.
- loeg 5y ago
  It is deprecated: > You should also be aware that the utf8mb3 character set is deprecated and you should expect it to be removed in a future MySQL release. Please use utf8mb4 instead. 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...
daneel_w 5y ago
This is an old article and it was incorrect when written, and it's incorrect today. The author may possibly have learned since writing it that UTF-8, by design, encodes up to 21 bits of character code point in at most 4 bytes of data. It works and behaves as it should.
- echelon 5y ago
  Well, that sounds great, but I've had to migrate numerous fields to utf8mb4 because we kept getting paged on emoji.
  - daneel_w 5y ago
    Same. Inbound SMSes with emojis. Outbound SMSes with emojis. Names and labels with emojis etc. But the implementation of "utf8" still isn't wrong, nor a bug - just like the 20 year old specs of UTF-8, they just failed to take the future into account.
  - chris_wot 5y ago
    The OP is wrong about the article being incorrect.
- loeg 5y ago
  UTF-8 was not restricted to U+10FFFF until November 2003. Prior to that, implementers had to assume it could encode up to 6 bytes of data. The "utf8" mode in MySQL can only encode 3 bytes of data (up to 16 bit codepoints), which is less than 4 (less than 21 bit codepoints). "utf8mb4" correctly encodes all UTF-8 codepoints by current standards.
  - daneel_w 5y ago
    > "utf8mb4" correctly encodes all UTF-8 codepoints by current standards. Yes, all good and known, but not really what the misunderstanding is about. "utf8" follows the definitions of UTF-8 as they stood at the time, adhering to Unicode's former code space limit.
clon 5y ago
The plain utf8 covers the "basic multilingual plane" (x0000-xFFFF), so it will get you very far, actually. In our app, we finally went for utf8mb4 to allow people to enter emoji. As a side "bonus" you will also soon see some clever people entering their names to stand out, such as "𝙹𝚘𝚑𝚗". Note that this is not "John". It is actually a series of mathematical symbols: U+1D679 MATHEMATICAL MONOSPACE CAPITAL J U+1D698 MATHEMATICAL MONOSPACE SMALL O U+1D691 MATHEMATICAL MONOSPACE SMALL H U+1D697 MATHEMATICAL MONOSPACE SMALL N
- RL_Quine 5y ago
- capitainenemo 5y ago
  One fun thing you can do in MariaDB is define a collation based off of the Unicode confusables list. The result of this is that select * from usernames where user = 'John' would still work :) It also avoids registering both 'John' and 'John'. (changed the o) Hedgewars user registration does this.
  - sam_lowry_ 5y ago
    That's a great feature. I was running a community site once where impersonation became a popular game. So we had dozens of users registering accounts with Cyrillic i,o,a,e etc.
    - stragies 5y ago
      For looking up relevant further material, this is called a 'homograph' or 'homoglyph attack'. It was also popular for site-spoofing by sending links with these cyrillic character to lead people to a fake bank/search_engine page, until that became widely used, and then owners of the tech-stack (browsers, registrars, dns-operators, etc) shut down this attack vector mostly.
- Ndymium 5y ago
  This is unfortunate as it makes things unreadable for screen readers. I have seen many tweets from companies and organisations that use these characters to replace funny "fonts" or create emphasis effects in the text, rendering them inaccessible in the process. In my own service I plan to offer users the ability to define a "stylised" nickname and an alternative text for screen readers, should they be interested in it (like an alt text for images). If Twitter had such a feature, I'd use it because I really like the effects, even if they're cheesy. :)
smarx007 5y ago
Of course, nobody "refused" to fix a "bug". Instead, a non-conformant behavior was already relied upon by legacy systems out in the wild and the "fix" was added in a backwards-compatible way. Edit: Three bytes are enough to fit nearly any of the chars in use in any language, including Chinese and Japanese, so I can only assume someone "smart" in the MySQL dev team decided to "save space" (before emoji were a thing).
- cblconfederate 5y ago
  couldnt emoji fit in 3 bytes as well? I don't think there are that many ...
  - rakoo 5y ago
    utf8 is an encoding for unicode codepoints. Those codepoints are spread on a space that is extremely vast (up to ~4 billion) for which that can be represented with up to 4 bytes. It turns out emojis are positioned in a place where the first byte will never be 0, so even if there were only one it would require the full 4 bytes to encode them.
    - the_mitsuhiko 5y ago
      > Those codepoints are spread on a space that is extremely vast (up to ~4 billion) You are off by a lot. The maximum code point is about 21bit high (0x10FFFF). The space is only 1.1 million large.
      - capitainenemo 5y ago
        It could be he's thinking of the historical definition which included support for 5 character encoding with a maximum codepoint of U+7FFFFFFF or ~2 billion. https://en.wikipedia.org/wiki/UTF-8#History https://en.wikipedia.org/wiki/UTF-8#History That was restricted I believe primarily for compatibility with more limited encodings like UTF-16. I guess it's possible that at some future point in human history when UTF-16 has been purged from memory, the 5 character encoding might be allowed again. :)
maxfurman 5y ago
Let's say for the sake of discussion that your MySQL db has a lot of tables encoded as "utf8." Are there any known drawbacks or gotchas to converting them en masse to "utf8mb4"? Is this a lengthy operation?
- chris_wot 5y ago
  I can’t imagine there would be too many, other than it is a tedious operation not easily automated. Depending on your data, might take a while. Can’t believe that neither MySQL or MariaDB has created a utility to do the conversions for you automatically.
- spookthesunset 5y ago
  Schema changes to large tables are always huge pain in the ass in MySQL. Every team I’ve worked on goes to great lengths to avoid changing the schema, which leads to all kinds of exciting anti-patterns like recycling old fields even if their name no longer matches what is stored in them.
  - sshine 5y ago
    I suspect this is because for large databases, schema changes lock the tables for extended periods of time, and if this time exceeds a reasonable time for a service window, you need good database replication. And you probably don’t have good MySQL database replication unless you’re on AWS or similar.
    - spookthesunset 5y ago
      That’s exactly what it is. If you have a big table you are gonna have a pretty long outage while you update that schema. Like in the last place I worked our largest table would take more than an hour for a schema update. So we never did schema updates.
      - cerved 5y ago
        sounds like how I approach laundry
ninju 5y ago
This HN posting should be marked with (2016) Is the issue still an issue?
- loeg 5y ago
  Yes and yes. https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8... > The utf8 Character Set (Alias for utf8mb3)
- Deukhoofd 5y ago
  Yes: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8...
- dang 5y ago
  Year added above. Thanks!
bambax 5y ago
> If you need a database, don’t use MySQL or MariaDB. Use PostgreSQL. Sure. But for many use cases, SQLite is enough.
- Spivak 5y ago
  This is a forum of professionals though, how many production apps can actually run a database with no HA, no replication, single process access, no remote access, and is weakly typed? And if you suggest "$project that's built on SQLite" to do all that then congrats you just reinvented MySQL or Postgres but with SQLite as the DB engine.
  - isotopp 5y ago
    Anything that is shipped on Android, on iOS, or as a macOS app, apparently. Sqlite is incredibly useful and much more prevalent that you imagine.
DonHopkins 5y ago
>Of course, they never advertised this (probably because the bug is so embarrassing). Not as embarrassing as being owned by Oracle.
fredoralive 5y ago
According to the docs, MySQL is a bit more explicit and calls the old "utf8" "utf8mb3" nowadays, and notes that it may be removed at some point. Although "utf8" is still aliased to the footgun at the moment. https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8...
cblconfederate 5y ago
will something terrible happen if i just convert my tables to utf8mb4? Will i have to upgrade each column?
leros 5y ago
In addition to emojis, I've also had issues with certain characters copied from Microsoft Word being 4 bytes.
lsllc 5y ago
A "hello devops" article from Jan 3, 2022 discussing this topic was posted by /u/flokoe a week ago: https://www.hellodevops.blog/posts/database-character-sets-and-collations-explained/ https://www.hellodevops.blog/posts/database-character-sets-a... And the ensuing discussion: https://news.ycombinator.com/item?id=29793916 https://news.ycombinator.com/item?id=29793916
wonder_er 5y ago
a while ago, in my first engineering job, I was tracking down a strange bug related to chinese character sets. Turns out some of the db tables were using utf8 and latin1; changing them to `utf8mb4` fixed it right up! https://josh.works/troubleshooting-chinese-character-sets-in-mysql https://josh.works/troubleshooting-chinese-character-sets-in... It was a fun bug to work on. I learned a lot about character encoding, and enjoyed bringing a refined "process" to the table.
mrcarruthers 5y ago
And here's the commit that changed it: https://github.com/mysql/mysql-server/commit/43a506c0ced0e6ea101d3ab8b4b423ce3fa327d0 https://github.com/mysql/mysql-server/commit/43a506c0ced0e6e.... It was originally set to max 6 bytes but for some reason someone dropped it to 3. They also seem to be slowly phasing it out. Internally it's utf8mb3 and utf8 is just an alias. The idea is to eventually make utf8 an alias to utf8mb4.
- isotopp 5y ago
  Back then MySQL used the memory engine for implicit temporary tables, and memory engine has no variable width data types. So a "varchar" becomes a "char", and a "varchar(255) charset utf8" becomes an allocation of six times 255 = 1530 bytes (filled with a lot of padding). Reducing this to 3 bytes is still bad, but only half as bad as before. Only MySQL 8 introduced innodb typed temporary tables (unlogged, even), so with MySQL 8 this is no longer a problem.
donatj 5y ago
Is Medium secretly mining crypto in the background? I have no idea what it is but their site makes my CPU spike and my fans turn on. https://jdon.at/JTHj2G https://jdon.at/JTHj2G Update - here's a video, it's making hundreds of graphql requests a minute… https://jdon.at/z05ImC https://jdon.at/z05ImC
deleted 5y ago
[deleted]
sshine 5y ago
In MySQL, if you want to make sure that your Unicode fields are mangled, never use ‘utf8’, use ‘utf8mb3’. Since ‘utf8’ is an alias to ‘utf8mb3’, that alias might eventually be updated to point to ‘utf8mb4’ which won’t mangle your characters. The only safe choice here is to explicitly use ‘utf8mb3’.
TedDoesntTalk 5y ago
> Back in 2002, MySQL gave users a speed boost if users could guarantee that every row in a table had the same number of bytes. To do that, users would declare text columns as “CHAR” This database type has existed in many databases since at least the 1980s
webkike 5y ago
Genuine question: why would anyone prefer MySQL over Postgres?
- kstrauser 5y ago
  Job security. I can’t think of any technical reasons.
  - ars 5y ago
    Other way around - Postges will give you more job security, since it's rarer and takes more time and work to administer. People use MySQL because it's easy, and they want to spend their effort in other places.
    - kstrauser 5y ago
      That's like saying Python is rarer than JavaScript. Even if it's true, it's not actually rare at all. In the last 6 years, I've spent approximately 2 hours administering PostgreSQL, and I'd be the one who had to do it.
    - jiggawatts 5y ago
      It’s “easy” until it silently shreds data and gets your org hacked through truncation attacks. Don’t confuse lazy with easy.
- deleted 5y ago
  [deleted]
- lewiscollard 5y ago
  In the WordPress world, it is the only database.[0] If you do WP, you do MySQL. You may sniff at WP (I certainly do), but it is used for 43% of all websites, which is not a trivial number of deployments, and not a small market. I suspect it has a large market share in the "started as a trivial PHP program, grew to large application, too committed to MySQL to migrate to something else" market, too. There's that one website with the blue F logo that went this path, but there's probably thousands more. That's not the same thing as anyone actually preferring it, but that's two possible reasons why there's an awful lot of MySQL deployments out there. [0] https://codex.wordpress.org/Using_Alternative_Databases https://codex.wordpress.org/Using_Alternative_Databases
dark-star 5y ago
In the past, Unicode was assumed to be 64k of codepoints, so a 3-byte UTF-8 sequence was considered "long enough", especially since there were surrogate pairs for the rare cases where you have to encode higher code points. Only "recently" have longer UTF-8 sequences (aka. emojis) become widespread enough that this became a problem. Yes, it could have been avoided if they had allowed arbitrary-length UTF-8 sequences from the beginning, but I can see that they probably just wanted to optimize a bit. What I don't understand is why they had to create a different encoding (the utf8mb4) instead of just extending the existing utf8 encoding, since 4-byte UTF-8 is obviously backward-compatible with 3-byte UTF-8... (unless they always used 3 bytes for every character, which would be stupid as UTF-8 has been explicitly designed for variable-length encodings) Bonus: Many filesystems also do not allow 4+ byte UTF 8 code points in filenames. Test your company's file server to see if it allows you to save a file as "(some random emoji).doc". A few very expensive storage systems also have problems with that (and they have the same workaround: convert your filesystem to a different encoding, instead of simply extending the existing encoding to allow 4+ bytes)
- xigoi 5y ago
  It's not backward-compatible if someone relies on the errors. I'm guessing it's a case of spacebar heating.
  - Anthony-G 5y ago
    For those unfamiliar with the spacebar heating reference: https://xkcd.com/1172/ https://xkcd.com/1172/
- raphlinus 5y ago
  While non-BMP Unicode has only become popular somewhat recently due to emoji, it's been part of the relevant official specs for a quarter century. I know you were kinda joking by using scare quotes for "recently", but let me fill that in for people who might be less familiar with the history. Unicode broke out of the 16-bit limit as of version 2.0, released in July 1996, so yeah, a quarter century. The first RFC for UTF-8 (RFC 2044, Oct 1996) explicitly supports UCS-4. As far as I know, there has never been a time where UTF-8 restricted to the BMP (ie 3 bytes) has been in widespread use. In other words, I am questioning your assertion that 3-byte UTF-8 has ever been considered "long enough," except perhaps in sloppy implementations made without much care for following specs.
LinuxBender 5y ago
Would the DBA's on this thread consider committing code to the MySQL [1] and Postgres [2] tuner scripts that give new DBA's all your learned advise and battle hardening experience? This thread appears to be such an example. [1] - https://github.com/major/MySQLTuner-perl https://github.com/major/MySQLTuner-perl [2] - https://github.com/jfcoz/postgresqltuner https://github.com/jfcoz/postgresqltuner
pm90 5y ago
Another interesting “issue” is that Case sensitive character sets may lead to unexpected results with aggregations (eg https://blog.mallya.dev/2021/07/25/mysql-cases-sensitivity/ https://blog.mallya.dev/2021/07/25/mysql-cases-sensitivity/) MySQL has many such issues which have turned me off from recommending it. It’s a shame because it’s a solid technology that mostly works and has a really long operational history.
fareesh 5y ago
I decided to build all my new projects on postgres a few years ago. I am often reminded of why that was a good decision.
pkrumins 5y ago
Does anyone know if utf8mb4 is variable length or does each character take full 4 bytes?
- butlerm 5y ago
  It is variable length, by definition. If it were four bytes per code point it would be named something more like UCS-4 or UTF-32.
  - pkrumins 5y ago
    Thanks!
deleted 5y ago
[deleted]
qwerasdf0987 5y ago
[dead]
throw0101a 5y ago
Note: > The utf8mb3 character set is deprecated and you should expect it to be removed in a future MySQL release. Please use utf8mb4 instead. utf8 is currently an alias for utf8mb3, but it is now deprecated as such, and utf8 is expected subsequently to become a reference to utf8mb4. Beginning with MySQL 8.0.28, utf8mb3 is also displayed in place of utf8 in columns of Information Schema tables, and in the output of SQL SHOW statements. * https://dev.mysql.com/doc/mysql-g11n-excerpt/8.0/en/charset-unicode-utf8.html https://dev.mysql.com/doc/mysql-g11n-excerpt/8.0/en/charset-...
- ghusbands 5y ago
  Maybe they should rename/alias it to brokenutf8, to make it more likely that people will notice a problem.
  - ehsankia 5y ago
    That's not needed, what is needed is what they said they would do: > and utf8 is expected subsequently to become a reference to utf8mb4 Once utf8 points to the right thing, the mistake will stop happening as no one is explicitly using utf8mb3 by accident. Not sure why this wasn't done right away, and only will be "subsequently". Has it been done yet?
- ksec 5y ago
  I really wish they do this in a new, major version release. But they dont seems to be keen on releasing 9.0.
- lmm 5y ago
  Hmm, I wonder if they'll deprecate "LATIN1" as an alias for "CP1252 + 8 arbitrary characters" (a charset that literally only exists in MySQL) as well.
billpg 5y ago
Dear databases, please don't get hung up about string lengths when dealing with UTF8. If I ask for a UTF8 string with a max-length of 100, please don't apply the worse case scenario and allocate space for 100 emojis. Please give me a box of 100 bytes and allow me to write any UTF-8 string that can fit into 100 bytes in there. 100 ASCII characters. 20 emojis. Any mixture of the two. If I ask for UTF-8, it'll be because I'd like to make advantage of UTF-8 and I accept the costs. If that means I can't quickly jump to the character at index 84 in a string, no problem, I've accepted the trade-off.
- dchest 5y ago
  More like "100 ASCII characters. Unknown number of emojis." You'll get codepoints truncated if you use bytes.
  - billpg 5y ago
    I would hope a database engine would throw an error instead of truncating bytes.
- grogers 5y ago
  Yes! This and other charset oddities almost make me want to store all strings as varbinary and handle any conversions to/from a string type to utf8 bytes client side. It makes ad-hoc query access from a shell much more annoying though.
  - billpg 5y ago
    I wonder if you can do LIKE with a VARBINARY value. "SELECT * FROM Users WHERE Name LIKE @namePattern" @namePattern = UTF8.GetBytes("%billpg%")
- petergeoghegan 5y ago
  Why would the database "allocate space" unless it was truly necessary, in any case? While char(n) works that way, most applications don't actually use it, no matter the underlying DB system.
oleg_antonyan 5y ago
Thanks to emoji (almost)everything now supports unicode
dehrmann 5y ago
Memories... When I was at Amazon, my starter project was modifying Redshift to support four-byte UTF-8 characters, not just three-byte.
NelsonMinar 5y ago
In 2022 with a new release of MySQL or MariaDB, is it still possible to create a database with "utf8" encoding? Does it print a warning?
- hu3 5y ago
  I spun up a MariaDB cluster yesterday and each node came configured with utf8mb4 by default. Testing it now, manually chosing utf8 for new tables shows no warning.
  - NelsonMinar 5y ago
    Thanks for the report! Glad real UTF-8 is the default at least.
karsinkk 5y ago
This might be a tad unrelated to the original post, but the following article is one of my favorite primers on Character sets/Unicode : https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/ https://www.joelonsoftware.com/2003/10/08/the-absolute-minim...
- superasn 5y ago
  Excellent article, even though I already knew most of it, given it was written in 2003 must have been one of the most insightful and enlightening article written. Wonder if you have any more links like this? Please do share!
Animats 5y ago
That was set up when Microsoft and Java had standardized on UTF-16. So this can represent the UTF-16 subset of Unicode, which is Unicode Plane 0, the Basic Multilingual Plane (BMP). The higher-numbered "astral planes" of UTF-8 were rarely used. All modern languages with a significant user base are covered in Plane 0. Plane 1, the Supplementary Multilingual Plane, with Cretan Linear B, Egyptian hieroglyphics, and such, was seldom needed. Few people had fonts for those, anyway. Because of the way UTF-8 is encoded, it takes 3 bytes to represent the UTF-16 set. That's because it only takes one byte for ASCII characters. Hence, 3-byte MySQL data. Emoji, though, were put in Unicode Plane 1. That's where mass demand for the astral planes came from. More four byte UTF-8 characters started showing up in data.
- loulouxiv 5y ago
  UTF-16 can represent the full range of Unicode codepoints by using couples of surrogates
  - Animats 5y ago
    Sort of. Applications using UTF-16 have to be aware of pairs at the application level. Many are not.
    - int_19h 5y ago
      This isn't a consequence of using UTF-16 as such - Java, .NET etc could totally have an API around UTF-16 strings that handles surrogate pairs. The problem, rather, is that those languages introduced a 16-bit type that they called "character", even though it wasn't even a Unicode codepoint. And then used that type throughout all string APIs, including strings themselves (indexing etc). In .NET land you're now supposed to use https://docs.microsoft.com/en-us/dotnet/api/system.text.rune https://docs.microsoft.com/en-us/dotnet/api/system.text.rune instead. It transparently handles surrogate pairs, so the app needn't be aware of anything - and yet the internal encoding is still UTF-16.
  - mort96 5y ago
    You're right. Replace UTF-16 with UCS-2 and the comment sounds at least slightly more correct.
brightball 5y ago
I just went through the process of converting a 2TB MySQL database over the summer. Quite a few gotchas along the way that make the process a huge pain.
ipaddr 5y ago
No one talks about the downsides of changing. Moving from 255 max characters to 191 or 192 max means a lot of your data needs to be moved into a text fields which means things like this field can't be a primary key.
- Pxtl 5y ago
  I'm used to mssql where nvarchar (the UTF-16 datatype) supports basically unlimited length... you can't make long PKs in mysql?
  - ipaddr 5y ago
    You are limited by smaller types ints, dates and chars.
  - cerved 5y ago
    friends don't let friends pk on variable length columns
- cerved 5y ago
  non numeric primary keys are generally a bad idea can't you specify collation by column?
CountSessine 5y ago
That's kind of funny - Oracle has a similar issue. If you want industry-standard UTF-8, you have to specify "AL32UTF8" as your encoding. "UTF8" is kind of crazy - it's this monstrous abomination called CESU-8 (https://en.wikipedia.org/wiki/CESU-8 https://en.wikipedia.org/wiki/CESU-8), which isnt' UTF-8 at all - it's actually this weird "UTF-16 complete with surrogate pairs wrapped in a UTF-8 shell" thing.
- butlerm 5y ago
  That would be a serious problem if Oracle's idea of "UTF8" wasn't roundtrip compatible with UTF-8, but it is fortunately, so approximately no one should notice the difference.
jmnicolas 5y ago
It's because of things like that it's still hard for me to respect MySQL in 2022. I use Postgres since 2008. It has never bitten me even once.
- ars 5y ago
  For something that was fixed 12 years ago? If Postgres has never bitten you, then you aren't using it much.
  - mikl 5y ago
    It’s not fixed. MySQL still has a datatype called “utf8” that, in fact, is not utf-8 but a limited subset thereof. PostgreSQL has some sharp edges, sure, but a lot less obvious footguns like these.
iliketrains 5y ago
Fun story: When I was working at one of the FAANG companies, I have placed the (Night with stars, it does not seem to render here [0]) character in my preferred name. Some time later, I was notified by HR that I must change my preferred name and I can only use UTF-8 characters with maximum length of 3 bytes. I was quite confused why such specific demand, I am pretty sure it was exactly this issue. I replaced it with ᗧ···ᗣ··· [0] https://emojipedia.org/night-with-stars/ https://emojipedia.org/night-with-stars/
- jiggawatts 5y ago
  Are you called Bobby Tables by any chance?
  - urbandw311er 5y ago
    Fantastic response
deleted 5y ago
[deleted]
didip 5y ago
MySQL is very similar to PHP in this regard. The good function name is actually broken so a new function with similar name is needed. This creates confusion for new people when adopting the technology.
- egeozcan 5y ago
  Oh the good old "mysql_real_escape_string"[0] days... I forgot most of my PHP knowledge but that somehow got stuck. [0]: https://www.php.net/manual/en/function.mysql-real-escape-string.php https://www.php.net/manual/en/function.mysql-real-escape-str...
  - duskwuff 5y ago
    You can blame MySQL for that one. The function name is straight out of the MySQL C API: https://dev.mysql.com/doc/c-api/8.0/en/mysql-real-escape-string.html https://dev.mysql.com/doc/c-api/8.0/en/mysql-real-escape-str...
tomwojcik 5y ago
I need to share something as literally today I fixed a bug in our project that's somewhat related. MS SQL encodes everything with UTF-16. Emojis (code points) require up to 4 bytes. If it's a grapheme (emoji constructed from emojis), it will be even more. We are using Django. If you check length of an emoji, it will give you `1` and Django assumes utf8 everywhere. If you try to save it to PostgreSQL with char field `max_length=1` it will work just fine, but on MS SQL it will fail as it requires 2 characters (4 bytes) in the DB! I tried it with MS SQL 2017, 2019 with different collations on nvarchar and I'm pretty sure there's no way around it. > Because in CHAR(n) and VARCHAR(n) or in NCHAR(n) and NVARCHAR(n), the n defines the byte storage size, not the number of characters that can be stored, it's important to determine the data type size you must convert to, in order to avoid data truncation. https://docs.microsoft.com/en-US/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15#utf8 https://docs.microsoft.com/en-US/sql/relational-databases/co...
- gecko 5y ago
  Generically, anything by Microsoft will historically have used UCS-2, and will use UTF-16 these days, so this is utterly unsurprising to me as an experienced Windows dev. Conversely, Linux (and POSIX, more generally) deciding that filename encoding is a per-filename and untracked thing is a bit lit, from my perspective. Point being: when it comes to handling unicode and foreign characters, just, like...always read the documentation. Assume nothing.
  - capitainenemo 5y ago
    Yep. Microsoft is the main reason for 2 of my favourite unicode links. https://utf8everywhere.org/ https://utf8everywhere.org/ and https://simonsapin.github.io/wtf-8/ https://simonsapin.github.io/wtf-8/ And, apparently it's mildly inaccurate to say it uses UTF-16... it's more like UCS-2 with UTF-16 hacked in, with no validation. Thus WTF-8.
    - ChrisSD 5y ago
      No, it's UTF-16 with no validation at the kernel level. Invalid UTF-16 is also invalid UCS-2 as those code points were explicitly barred from use. In practice, only malware will create such broken names. High level software (e.g. Microsoft's own VSCode) will not handle broken UTF-16. And indeed the in-built UTF-8 code page will lossily decode UTF-16 (unpaired surrogates are replaced with the Unicode replacement character).
ashvardanian 5y ago
Wow! I was working on this issue in our DBMS product today! Fun suggestion, try making a JSON string with a NULL character somewhere in the middle. It will be encoded as \u0000 and is a a valid UTF-8 code, but most C based systems will truncate the string by estimating its length via strlen. Java community and some other software vendors designed the Modified UTF-8, which replaces the zero with a 2-byte code point. Sleek. Aside from the fact, that you are modifying the data that customer wants to stay consistent. Postres explicitly bans such cases in the VARCHAR, not sure if it can fit in their JSON columns. Who tried?
- cerved 5y ago
  Null-bytes are not valid characters in JSON strings. Nor any other control characters for that matter
  - lifthrasiir 5y ago
    They are valid if escaped, as explicitly noted in the Section 7 of RFC 7159 [1]. (Annoyingly enough it doesn't explicitly say JSON strings are Unicode strings, it just says that a certain subset of JSON strings is interoperable with Unicode.) GP means that the escaped null byte can still cause issues for C interoperability. [1] https://datatracker.ietf.org/doc/html/rfc7159#section-7 https://datatracker.ietf.org/doc/html/rfc7159#section-7
    - cerved 5y ago
      Exactly, null bytes aren't allowed. > All Unicode characters may be placed within the quotation marks, except for the characters that must be escaped: quotation mark, reverse solidus, and the control characters (U+0000 through U+001F). Any character may be escaped in this way > Any character may be escaped. I personally find the JSON spec very explicit while succinct
      - lifthrasiir 5y ago
        Oh, actually I made a mistake in the GP. The following sentence: > Annoyingly enough it doesn't explicitly say JSON strings are Unicode strings, [...] ...is false, I completely missed the very first section (I obviously searched for "Unicode", but failed to thoroughly check results). I have other valid criticisms of the JSON specification but that is not, so please ignore that part of rants since it was based on a wrong assumption. Thank you for (implicitly) pointing it out.
cryptos 5y ago
Best to be used with mysqli_real_escape_string ;-) https://www.php.net/manual/en/mysqli.real-escape-string.php https://www.php.net/manual/en/mysqli.real-escape-string.php
kolaente 5y ago
Also relevant about this: https://blog.koehntopp.info/2022/01/12/utf8mb4.html https://blog.koehntopp.info/2022/01/12/utf8mb4.html
Dylan16807 5y ago
Also make sure mysqldump is using utf8mb4 or you'll get character replacement in your backups. This often needs to be configured separately.
urbandw311er 5y ago
I enjoyed the article right up to the point where it concluded at the very end that the solution is to “switch to postgresql” without actually providing any rationale for that sudden and subjective ending.
talos2110 5y ago
So in mysql utf8 does not mean utf8. Reminds me of iso8601 in php, which does not mean iso8601. https://www.php.net/manual/en/class.datetimeinterface.php https://www.php.net/manual/en/class.datetimeinterface.php
- 77pt77 5y ago
  MySQL and PHP go hand in hand when it comes to these poor decisions.
fake-name 5y ago
In MySQL, use postgres instead.
riteshpatel 5y ago
We learned this a few years ago when clients starting using emojis when saving their content and it got truncated. Very quickly converted everything to utf8mb4!
- mayli 5y ago
  emojis like this？🆒
  - kortex 5y ago
    What is this voodoo? I thought HN didn't support emoji? It's sure 🆒 though.
77pt77 5y ago
MySQL is an awful piece of software. Try diving into the Time zone insanity, collations, and of course this UTF-8 gem that has lasted since at least 2011.
hashimotonomora 5y ago
Just use ASCII.
TedShiller 5y ago
use Postgres instead of MySQL
jijji 5y ago
i'll just stick latin1 charset for now... its slower and increases the size of ur tables to use utf8, etc [0] [0] https://stackoverflow.com/questions/12449336/utf-8-vs-latin1#12449477 https://stackoverflow.com/questions/12449336/utf-8-vs-latin1...
gurkwart 5y ago
MailNerd 5y ago
Another fun fact is that string comparisons are case insensitive by default: https://dev.mysql.com/doc/refman/8.0/en/case-sensitivity.html https://dev.mysql.com/doc/refman/8.0/en/case-sensitivity.htm... This can definitely catch you by surprise and cause bugs that are only detected late.
jamaicahest 5y ago
> Database systems have subtle bugs and oddities, and you can avoid a lot of bugs by avoiding database systems. This sounds like an offended developer. Avoiding database systems is not possible if you want to do a lot of programming tasks.
OliverJones 5y ago
WordPress has, of course, the lion's share of MySQL deployments by server count if not by row count. Yes, WordPress isn't the latest and greatest stuff. Yes, it's <bad thing> and <another bad thing>. But such is the curse of the customer base. They upgraded everybody from utf8 to utf8mb4 (excluding some users of ancient versions of MySQL) with their version 4.2. It went live on April 23, 2015. They announced the upgrade at the beginning of April 2015 here. https://make.wordpress.org/core/2015/04/02/the-utf8mb4-upgrade/ https://make.wordpress.org/core/2015/04/02/the-utf8mb4-upgra... There's some interesting stuff in there about the practical difficulties of the upgrade. The biggest issue was, and still is, the need for prefix indexes. With respect to Adam Hooper, his article was stale when it appeared just over a year after the WordPress schema upgrade. Here's right of course. But the biggest user base was well on their way to abandoning utfmb3 by that time.
ShaneMcGowan 5y ago
This one is very fun to debug in production when you're on MySQL 5.8
exabrial 5y ago
> Choose CHAR columns. (The CHAR format is a relic nowadays. Back then, MySQL was faster with CHAR columns. Ever since 2005, it’s not.) Is this vs VARCHAR? To me this is fascinating if so, I'd love to see a link or benchmark. My understanding that reading a stripe off a drive, then chopping it up indiscriminately at known intervals is faster than conditionally branching for strings of other lengths
btschaegg 5y ago
> And developers who wanted correctness were wrong to use “utf8”, because it can’t store “poo emoji”. This sentence deserves an award :) Edit: TIL: HN doesn't support emojis.

643 points by goranmoomin 5y ago

I've had to "upgrade" a couple to utf8, and varchar(255) bites me all the time.

Has anyone ever successfully converted a large legacy Apache/PHP/MySQL site to UTF-8? If so you deserve an award. utf8mb4 is just one issue and not the worst.

usbqk 5y ago
Sure, you just prepend mb_ to all php string calls >:)
onli 5y ago
https://github.com/s9y/Serendipity/blob/05f58f90d743fe9ade24f3fdfe9a934d0b87c6a1/include/db/mysqli.inc.php#L320 https://github.com/s9y/Serendipity/blob/05f58f90d743fe9ade24... is just the detection function I wrote for a PHP blog engine to detect whether it would even be possible to use utf8mb4 on the system. We completely ran into this issue and didn't know how to handle it for literally years. Lots of blogs to migrate, and who knows whether it worked for all of them...
- mwattsun 5y ago
  The craziest issue I had was I couldn't predict what char encoding the text in my database was in. Most users entered Windows-1252, some text blobs were UTF-16, others were European character sets, and some were UTF-8. Some were Japanese SHIFT_JIS. Don't ask me how any of this happened. I retrospect, I should have dumped all the tables from MySQL and used the excellent PyPy Chardet [1] library to see what I was dealing with, do the conversions and then re-import the data. But then someone could copy UTF-16 from a Windows document and paste it in, so you have to convert going in to the database. You have set Apache to UTF-8, PHP to UTF-8, MySQL to UTF-8, and the MySQL driver you are using to UTF-8. It's not clear how these setting interact. Are there silent conversions happening or do you always have to detect the encoding on data coming from the server? HTML pages have a character encoding specifier, but the BOM at the start of the file takes precedence (I think.) I got it to work by always detecting encoding for any text coming from the database and using iconv, but this turned out to be really slow and unreliable. It was truly the biggest mess by an order of magnitude than any other programming problem I faced in my career. Would not attempt again. [1] https://github.com/chardet/chardet https://github.com/chardet/chardet
sam_lowry_ 5y ago
Yes, but I have chosen utf8_bin as encoding and lived happily with this choice for a dozen years.
ospzfmbbzr 5y ago
Yes. I had the exact same issue with '<' in HTML content stored in the database -- cms pages and also some JS that was stored in the DB and injected into the HEAD of all pages.
charrondev 5y ago
Technical lead for vanillaforums. It’s a huge PHP MySQL app. Probably 10b+ rows in production DBs. We did the the migration in 2017 if I remember correctly.
- mwattsun 5y ago
  Not to take anything away but by legacy I meant late last century sites. VanillaForums was released in July 2006 according to Wikipedia, which makes it Web 2.0. Wikipedia also says "UTF-8 has been the most common encoding for the World Wide Web since 2008" I'm glad you posted though because now I know that sites are ok starting in that time frame, not before and then never touch the old ones again.
ars 5y ago
Been there, done that, with a phpbb installation. I manually converted all the non-UTF data in the database to UTF8, and updated the PHP code and database settings. It worked fine, no major issues, just some work.
fleddr 5y ago
At least once, but it's been a while. From the top of my head: - set encoding at HTML head - also at any HTML form element - Set database collation correctly - I believe it also needs to be done per table - The PHP -> MySQL connection itself may need to have the encoding set - All string manipulation to be done with mb_ - Even ensure your code files themselves are in UTF8

kyralis 5y ago

> Database systems have subtle bugs and oddities, and you can avoid a lot of bugs by avoiding database systems. Wat? That is... not a great takeaway from this experience. "Oh, this library had bug reported in it! I know, that means I should just write my own, because clearly they're all buggy and my software is always perfect."

cbg0 5y ago
I'm pretty sure you missed the joke.
jerf 5y ago
Well, I mean, the good news is that if you write your own database system, you'll have so many other bugs in the way that you'll likely never reach this particular one! I suggest it can be called the Sirius Database. "It is very easy to be blinded to the essential uselessness of [the new database you just wrote] by the sense of achievement you get from getting them to work at all. In other words—and this is the rock solid principle on which the whole of the Corporation's Galaxy-wide success is founded—their fundamental design flaws are completely hidden by their superficial design flaws."
- piyh 5y ago
  >their fundamental design flaws are completely hidden by their superficial design flaws That hit me right in my 9-5 "not invented here" nightmare.
acdha 5y ago
It's especially specific to MySQL — switching to Postgres is great because in addition to better performance and features there are all of these rakes in the grass you can forget about. Unicode just works, you never lose data because someone didn't scrupulously check the warnings after every query, etc.
- TedDoesntTalk 5y ago
  Doesn't Postgres have its own set of unique of problems?

tapoxi 5y ago

Maybe this is a cynical take - but we used https://pgloader.io/ https://pgloader.io/ a few years ago to migrate to Postgres, and have never been happier. MySQL has a lot of stupid decisions like this.

dijit 5y ago
I'm a big posgresql fan, but I'd maybe argue that you're making tradeoffs. PG has weird behaviour sometimes too. I don't think it's always smart to just change out your data layer. But, if you're starting a new project, I do think PG is one of the better options and tends to follow the principle of least surprise. (hence: big fan)
- leetrout 5y ago
  As a huge Postgres fan working at a shop using MySQL I'll point out the first thing I saw MySQL does that Postgres doesn't: Index hints. If you aren't hitting an index in Postgres you have to dig in to table stats and figure out what is wrong but MySQL gives you more control. However, I would still rather work with Postgres AND have to juggle a connection pooler than deal with MySQL. Transactions on DDL are _great_ and the ability to use foreign keys across partitioned tables is how it should be.
  - spookthesunset 5y ago
    But at least Postgres has much better tools for learning about the query plans it might use. The Postgres output of “EXPLAIN” is much much better than MySQL. It’s a shame that almost every job I worked at uses MySQL and not Postgres. But that could be because those companies all got their start like a decade or more ago when Postgres was not as well known.
  - pzduniak 5y ago
    > As a huge Postgres fan working at a shop using MySQL I'll point out the first thing I saw MySQL does that Postgres doesn't: > Index hints. Eh, it's an extension. One you shouldn't use, but it's there.

capitainenemo 5y ago

This is also a security vulnerability because MySQL/MariaDB silently truncate strings at the first invalid character. This can result in data manipulation attacks where a higher level layer validates the complete input as UTF-8 for insertion into the DB, but the database only stores half the string. https://bugzilla.mozilla.org/show_bug.cgi?id=1253201 https://bugzilla.mozilla.org/show_bug.cgi?id=1253201

wanderr 5y ago
Any data in your database that can come from external input should be treated as untrusted and validated before it's used. Otherwise validation bugs or bypasses will result in bad data and exploits that persist beyond the fix. Edit: I’m not arguing against the need to use utf8mb4
- capitainenemo 5y ago
  Agreed. The confusing part here as I see it is where validation layer A (correctly) asserts the data is valid UTF-8 and safe then assumes the database persists what it passes to it, since no error is reported. Then, subsystem B trusts reading the database field (since it passed validation layer A). Obviously more validation layers can be added, but at this point validation layer C called by subsystem B needs to know what the initial input from layer A is in order to differentiate it from the db value which was manipulated - a rather tricky thing to do sometimes. (I guess you could add a hash to the db to check the db is storing your strings, but really.. come on) Upgrading to utf8mb4 is probably safer than hoping enough validation layers thrown at it solves the problem.
  - wanderr 5y ago
    Yes. I’ve been burned by this exact issue in the past, not realizing that utf8 was not really utf8 in mysql-land. That is a major, major WTF IMO. But I’ve also seen people relying on validation at the time of insertion so many times that I wanted to warn against that, too. Not in argument against the need for utf8mb4.
- 5y ago

PrimeDirective 5y ago

The author mentioned MariaDB in the intro and the went on about MySQL, not sure which he actually used. I think the latest recommendation for MySQL was utf8mb4_0900_ai_ci and utf8mb4_general_ci for MariaDB. While I agree about the Postgres recommendation, it's a non-starter in the PHP world, unless doing something from scratch. Even then, the support for MySQL/MariaDB is much better.

timw4mail 5y ago
What's that mean, non-starter in the PHP world? Postgres is well supported by frameworks and libraries in PHP, not to mention PHP itself.
- PrimeDirective 5y ago
  For one, it's out of reach for lots of folks who use shared hosting services, where there's a managed LAMP stack. It's rare if they have Postgres. If you use AWS, of course, everything is up to you. There's also a connection pooling and different startup for MySQL/Mariadb vs Postgres. PHP engine generates the page and closes itself and connections. Postgres assumes you are more persistent. Also, there's a difference in how GROUP BY works in MySQL/Mariadb vs Postgres, and wrapping your head around it, after using it the MySQL way for years, can take time. So not a drop-in replacement.
  - isotopp 5y ago
    ONLY_FULL_GROUP_BY is the default in MySQL since 5.7.5.

davidjfelix 5y ago

Does this even fully fix the problem? It looks like utf8mb4 is limited to 4 byte sequences, but as far as I understand, utf-8 is variable width. Can utf8mb4 even encode the scottish flag https://www.iemoji.com/view/emoji/2476/flags/scotland https://www.iemoji.com/view/emoji/2476/flags/scotland ?

fredoralive 5y ago
Legal UTF8 is limited to 4 bytes, as Unicode only uses ranges that fit the limits of UTF16. AFAIK The flags are a weird multi-code point encoding of the ISO country codes, and each individual code point is less than 4 bytes.
jerf 5y ago
Flags are not single code points. UTF-8 refers to how code points are stored. If you look in your link at "Hex Code Point(s)", it is that first one that would be a problem with "utf8" in MySQL, because of the 1 in the 0x10000 position. The other six code points required would fit in fine. Unicode is developing more and more things that require code points. I'm not sure what the longest legal non-redundant series of code points that can validly represent a glyph somewhere is, but it's getting up there with all the emoji skin modifiers and such.
- jeroenhd 5y ago
  Emoji modifiers, both for gender and skintone, do not produce that many extra code points. Combinations are made in the same way ¨ + e can combine into ë: a code point, followed by a combination code point, followed by a modifier. During text rendering, these code points are converted back into a single glyph. All UTF-8 codepoints in use today can be encoded with four bytes. Theoretically the Unicode system can be used to create 6 byte code points if that ever becomes necessary, but it won't be for a while. Crossing the 4 byte boundary would also introduce compatibility issues with UTF-16, so I'm sure the Unicode Consortium will do their best to prevent this from happening as long as they can.
  - jerf 5y ago

rubyist5eva 5y ago

god I hate mysql so much - long live postgres, one of the greatest pieces of software ever created

hn_throwaway_99 5y ago

Wow, never knew this, this is really bad, especially in this day and age where emojis are so prevalent. MySQL should deprecate utf8 and give a warning if you try to use it.

daneel_w 5y ago
Bad idea. If they deprecate "utf8" they will deprecate a standard. It was designed and specified at a time when the Unicode code space had a 21 bit limit. There are some technical (storage space-related) considerations with the suggestion to "just use utf8mb4 everywhere instead" because of how InnoDB's indices work.
- loeg 5y ago
  MySQL "utf8" is still "utf8mb3", which is not a standard anywhere except MySQL. It cannot store the full range of 21-bit code points; I don't know why you keep repeating this. The maximum codepoint in 3-byte UTF-8 is 0xFFFF, which is 16 bits.
- hn_throwaway_99 5y ago
  > If they deprecate "utf8" they will deprecate a standard. No, they won't. They will be deprecating something they created that never conformed to the UTF-8 standard. That fact that unicode didn't have codes beyond 21 bits at that point is pretty irrelevant. It was an invalid implementation from day 1, at least with the name of "utf8".
- faho 5y ago
  >It was designed and specified at a time when the Unicode code space had a 21 bit limit 21 bits is the current Unicode limit. Unfortunately UTF-8 in 3 Bytes only has 16 usable bits - the first byte starts with 1110 and the two continuation bytes start with 10, so it's 4 + 6 + 6 = 16 bits.
loeg 5y ago
It is deprecated: > You should also be aware that the utf8mb3 character set is deprecated and you should expect it to be removed in a future MySQL release. Please use utf8mb4 instead. 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...

daneel_w 5y ago

This is an old article and it was incorrect when written, and it's incorrect today. The author may possibly have learned since writing it that UTF-8, by design, encodes up to 21 bits of character code point in at most 4 bytes of data. It works and behaves as it should.

echelon 5y ago
Well, that sounds great, but I've had to migrate numerous fields to utf8mb4 because we kept getting paged on emoji.
- daneel_w 5y ago
  Same. Inbound SMSes with emojis. Outbound SMSes with emojis. Names and labels with emojis etc. But the implementation of "utf8" still isn't wrong, nor a bug - just like the 20 year old specs of UTF-8, they just failed to take the future into account.
- chris_wot 5y ago
  The OP is wrong about the article being incorrect.
loeg 5y ago
UTF-8 was not restricted to U+10FFFF until November 2003. Prior to that, implementers had to assume it could encode up to 6 bytes of data. The "utf8" mode in MySQL can only encode 3 bytes of data (up to 16 bit codepoints), which is less than 4 (less than 21 bit codepoints). "utf8mb4" correctly encodes all UTF-8 codepoints by current standards.
- daneel_w 5y ago
  > "utf8mb4" correctly encodes all UTF-8 codepoints by current standards. Yes, all good and known, but not really what the misunderstanding is about. "utf8" follows the definitions of UTF-8 as they stood at the time, adhering to Unicode's former code space limit.

clon 5y ago

The plain utf8 covers the "basic multilingual plane" (x0000-xFFFF), so it will get you very far, actually. In our app, we finally went for utf8mb4 to allow people to enter emoji. As a side "bonus" you will also soon see some clever people entering their names to stand out, such as "𝙹𝚘𝚑𝚗". Note that this is not "John". It is actually a series of mathematical symbols: U+1D679 MATHEMATICAL MONOSPACE CAPITAL J U+1D698 MATHEMATICAL MONOSPACE SMALL O U+1D691 MATHEMATICAL MONOSPACE SMALL H U+1D697 MATHEMATICAL MONOSPACE SMALL N

RL_Quine 5y ago
capitainenemo 5y ago
One fun thing you can do in MariaDB is define a collation based off of the Unicode confusables list. The result of this is that select * from usernames where user = 'John' would still work :) It also avoids registering both 'John' and 'John'. (changed the o) Hedgewars user registration does this.
- sam_lowry_ 5y ago
  That's a great feature. I was running a community site once where impersonation became a popular game. So we had dozens of users registering accounts with Cyrillic i,o,a,e etc.
  - stragies 5y ago
    For looking up relevant further material, this is called a 'homograph' or 'homoglyph attack'. It was also popular for site-spoofing by sending links with these cyrillic character to lead people to a fake bank/search_engine page, until that became widely used, and then owners of the tech-stack (browsers, registrars, dns-operators, etc) shut down this attack vector mostly.
Ndymium 5y ago
This is unfortunate as it makes things unreadable for screen readers. I have seen many tweets from companies and organisations that use these characters to replace funny "fonts" or create emphasis effects in the text, rendering them inaccessible in the process. In my own service I plan to offer users the ability to define a "stylised" nickname and an alternative text for screen readers, should they be interested in it (like an alt text for images). If Twitter had such a feature, I'd use it because I really like the effects, even if they're cheesy. :)

smarx007 5y ago

Of course, nobody "refused" to fix a "bug". Instead, a non-conformant behavior was already relied upon by legacy systems out in the wild and the "fix" was added in a backwards-compatible way. Edit: Three bytes are enough to fit nearly any of the chars in use in any language, including Chinese and Japanese, so I can only assume someone "smart" in the MySQL dev team decided to "save space" (before emoji were a thing).

cblconfederate 5y ago
couldnt emoji fit in 3 bytes as well? I don't think there are that many ...
- rakoo 5y ago
  utf8 is an encoding for unicode codepoints. Those codepoints are spread on a space that is extremely vast (up to ~4 billion) for which that can be represented with up to 4 bytes. It turns out emojis are positioned in a place where the first byte will never be 0, so even if there were only one it would require the full 4 bytes to encode them.
  - the_mitsuhiko 5y ago
    > Those codepoints are spread on a space that is extremely vast (up to ~4 billion) You are off by a lot. The maximum code point is about 21bit high (0x10FFFF). The space is only 1.1 million large.
    - capitainenemo 5y ago
      It could be he's thinking of the historical definition which included support for 5 character encoding with a maximum codepoint of U+7FFFFFFF or ~2 billion. https://en.wikipedia.org/wiki/UTF-8#History https://en.wikipedia.org/wiki/UTF-8#History That was restricted I believe primarily for compatibility with more limited encodings like UTF-16. I guess it's possible that at some future point in human history when UTF-16 has been purged from memory, the 5 character encoding might be allowed again. :)

maxfurman 5y ago

Let's say for the sake of discussion that your MySQL db has a lot of tables encoded as "utf8." Are there any known drawbacks or gotchas to converting them en masse to "utf8mb4"? Is this a lengthy operation?

chris_wot 5y ago
I can’t imagine there would be too many, other than it is a tedious operation not easily automated. Depending on your data, might take a while. Can’t believe that neither MySQL or MariaDB has created a utility to do the conversions for you automatically.
spookthesunset 5y ago
Schema changes to large tables are always huge pain in the ass in MySQL. Every team I’ve worked on goes to great lengths to avoid changing the schema, which leads to all kinds of exciting anti-patterns like recycling old fields even if their name no longer matches what is stored in them.
- sshine 5y ago
  I suspect this is because for large databases, schema changes lock the tables for extended periods of time, and if this time exceeds a reasonable time for a service window, you need good database replication. And you probably don’t have good MySQL database replication unless you’re on AWS or similar.
  - spookthesunset 5y ago
    That’s exactly what it is. If you have a big table you are gonna have a pretty long outage while you update that schema. Like in the last place I worked our largest table would take more than an hour for a schema update. So we never did schema updates.
    - cerved 5y ago
      sounds like how I approach laundry

ninju 5y ago

This HN posting should be marked with (2016) Is the issue still an issue?

loeg 5y ago
Yes and yes. https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8... > The utf8 Character Set (Alias for utf8mb3)
Deukhoofd 5y ago
Yes: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8...
dang 5y ago
Year added above. Thanks!

bambax 5y ago

> If you need a database, don’t use MySQL or MariaDB. Use PostgreSQL. Sure. But for many use cases, SQLite is enough.

Spivak 5y ago
This is a forum of professionals though, how many production apps can actually run a database with no HA, no replication, single process access, no remote access, and is weakly typed? And if you suggest "$project that's built on SQLite" to do all that then congrats you just reinvented MySQL or Postgres but with SQLite as the DB engine.
- isotopp 5y ago
  Anything that is shipped on Android, on iOS, or as a macOS app, apparently. Sqlite is incredibly useful and much more prevalent that you imagine.

DonHopkins 5y ago

>Of course, they never advertised this (probably because the bug is so embarrassing). Not as embarrassing as being owned by Oracle.

fredoralive 5y ago

According to the docs, MySQL is a bit more explicit and calls the old "utf8" "utf8mb3" nowadays, and notes that it may be removed at some point. Although "utf8" is still aliased to the footgun at the moment. https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8...

cblconfederate 5y ago

will something terrible happen if i just convert my tables to utf8mb4? Will i have to upgrade each column?

leros 5y ago

In addition to emojis, I've also had issues with certain characters copied from Microsoft Word being 4 bytes.

lsllc 5y ago

A "hello devops" article from Jan 3, 2022 discussing this topic was posted by /u/flokoe a week ago: https://www.hellodevops.blog/posts/database-character-sets-and-collations-explained/ https://www.hellodevops.blog/posts/database-character-sets-a... And the ensuing discussion: https://news.ycombinator.com/item?id=29793916 https://news.ycombinator.com/item?id=29793916

wonder_er 5y ago

a while ago, in my first engineering job, I was tracking down a strange bug related to chinese character sets. Turns out some of the db tables were using utf8 and latin1; changing them to `utf8mb4` fixed it right up! https://josh.works/troubleshooting-chinese-character-sets-in-mysql https://josh.works/troubleshooting-chinese-character-sets-in... It was a fun bug to work on. I learned a lot about character encoding, and enjoyed bringing a refined "process" to the table.

mrcarruthers 5y ago

And here's the commit that changed it: https://github.com/mysql/mysql-server/commit/43a506c0ced0e6ea101d3ab8b4b423ce3fa327d0 https://github.com/mysql/mysql-server/commit/43a506c0ced0e6e.... It was originally set to max 6 bytes but for some reason someone dropped it to 3. They also seem to be slowly phasing it out. Internally it's utf8mb3 and utf8 is just an alias. The idea is to eventually make utf8 an alias to utf8mb4.

isotopp 5y ago
Back then MySQL used the memory engine for implicit temporary tables, and memory engine has no variable width data types. So a "varchar" becomes a "char", and a "varchar(255) charset utf8" becomes an allocation of six times 255 = 1530 bytes (filled with a lot of padding). Reducing this to 3 bytes is still bad, but only half as bad as before. Only MySQL 8 introduced innodb typed temporary tables (unlogged, even), so with MySQL 8 this is no longer a problem.

donatj 5y ago

Is Medium secretly mining crypto in the background? I have no idea what it is but their site makes my CPU spike and my fans turn on. https://jdon.at/JTHj2G https://jdon.at/JTHj2G Update - here's a video, it's making hundreds of graphql requests a minute… https://jdon.at/z05ImC https://jdon.at/z05ImC

deleted 5y ago

[deleted]

sshine 5y ago

In MySQL, if you want to make sure that your Unicode fields are mangled, never use ‘utf8’, use ‘utf8mb3’. Since ‘utf8’ is an alias to ‘utf8mb3’, that alias might eventually be updated to point to ‘utf8mb4’ which won’t mangle your characters. The only safe choice here is to explicitly use ‘utf8mb3’.

TedDoesntTalk 5y ago

> Back in 2002, MySQL gave users a speed boost if users could guarantee that every row in a table had the same number of bytes. To do that, users would declare text columns as “CHAR” This database type has existed in many databases since at least the 1980s

webkike 5y ago

Genuine question: why would anyone prefer MySQL over Postgres?

kstrauser 5y ago
Job security. I can’t think of any technical reasons.
- ars 5y ago
  Other way around - Postges will give you more job security, since it's rarer and takes more time and work to administer. People use MySQL because it's easy, and they want to spend their effort in other places.
  - kstrauser 5y ago
    That's like saying Python is rarer than JavaScript. Even if it's true, it's not actually rare at all. In the last 6 years, I've spent approximately 2 hours administering PostgreSQL, and I'd be the one who had to do it.
  - jiggawatts 5y ago
    It’s “easy” until it silently shreds data and gets your org hacked through truncation attacks. Don’t confuse lazy with easy.
deleted 5y ago
[deleted]
lewiscollard 5y ago
In the WordPress world, it is the only database.[0] If you do WP, you do MySQL. You may sniff at WP (I certainly do), but it is used for 43% of all websites, which is not a trivial number of deployments, and not a small market. I suspect it has a large market share in the "started as a trivial PHP program, grew to large application, too committed to MySQL to migrate to something else" market, too. There's that one website with the blue F logo that went this path, but there's probably thousands more. That's not the same thing as anyone actually preferring it, but that's two possible reasons why there's an awful lot of MySQL deployments out there. [0] https://codex.wordpress.org/Using_Alternative_Databases https://codex.wordpress.org/Using_Alternative_Databases

dark-star 5y ago

In the past, Unicode was assumed to be 64k of codepoints, so a 3-byte UTF-8 sequence was considered "long enough", especially since there were surrogate pairs for the rare cases where you have to encode higher code points. Only "recently" have longer UTF-8 sequences (aka. emojis) become widespread enough that this became a problem. Yes, it could have been avoided if they had allowed arbitrary-length UTF-8 sequences from the beginning, but I can see that they probably just wanted to optimize a bit. What I don't understand is why they had to create a different encoding (the utf8mb4) instead of just extending the existing utf8 encoding, since 4-byte UTF-8 is obviously backward-compatible with 3-byte UTF-8... (unless they always used 3 bytes for every character, which would be stupid as UTF-8 has been explicitly designed for variable-length encodings) Bonus: Many filesystems also do not allow 4+ byte UTF 8 code points in filenames. Test your company's file server to see if it allows you to save a file as "(some random emoji).doc". A few very expensive storage systems also have problems with that (and they have the same workaround: convert your filesystem to a different encoding, instead of simply extending the existing encoding to allow 4+ bytes)

xigoi 5y ago
It's not backward-compatible if someone relies on the errors. I'm guessing it's a case of spacebar heating.
- Anthony-G 5y ago
  For those unfamiliar with the spacebar heating reference: https://xkcd.com/1172/ https://xkcd.com/1172/
raphlinus 5y ago
While non-BMP Unicode has only become popular somewhat recently due to emoji, it's been part of the relevant official specs for a quarter century. I know you were kinda joking by using scare quotes for "recently", but let me fill that in for people who might be less familiar with the history. Unicode broke out of the 16-bit limit as of version 2.0, released in July 1996, so yeah, a quarter century. The first RFC for UTF-8 (RFC 2044, Oct 1996) explicitly supports UCS-4. As far as I know, there has never been a time where UTF-8 restricted to the BMP (ie 3 bytes) has been in widespread use. In other words, I am questioning your assertion that 3-byte UTF-8 has ever been considered "long enough," except perhaps in sloppy implementations made without much care for following specs.

LinuxBender 5y ago

Would the DBA's on this thread consider committing code to the MySQL [1] and Postgres [2] tuner scripts that give new DBA's all your learned advise and battle hardening experience? This thread appears to be such an example. [1] - https://github.com/major/MySQLTuner-perl https://github.com/major/MySQLTuner-perl [2] - https://github.com/jfcoz/postgresqltuner https://github.com/jfcoz/postgresqltuner

pm90 5y ago

Another interesting “issue” is that Case sensitive character sets may lead to unexpected results with aggregations (eg https://blog.mallya.dev/2021/07/25/mysql-cases-sensitivity/ https://blog.mallya.dev/2021/07/25/mysql-cases-sensitivity/) MySQL has many such issues which have turned me off from recommending it. It’s a shame because it’s a solid technology that mostly works and has a really long operational history.

fareesh 5y ago

I decided to build all my new projects on postgres a few years ago. I am often reminded of why that was a good decision.

pkrumins 5y ago

Does anyone know if utf8mb4 is variable length or does each character take full 4 bytes?

butlerm 5y ago
It is variable length, by definition. If it were four bytes per code point it would be named something more like UCS-4 or UTF-32.
- pkrumins 5y ago
  Thanks!

deleted 5y ago

[deleted]

qwerasdf0987 5y ago

[dead]

throw0101a 5y ago

Note: > The utf8mb3 character set is deprecated and you should expect it to be removed in a future MySQL release. Please use utf8mb4 instead. utf8 is currently an alias for utf8mb3, but it is now deprecated as such, and utf8 is expected subsequently to become a reference to utf8mb4. Beginning with MySQL 8.0.28, utf8mb3 is also displayed in place of utf8 in columns of Information Schema tables, and in the output of SQL SHOW statements. * https://dev.mysql.com/doc/mysql-g11n-excerpt/8.0/en/charset-unicode-utf8.html https://dev.mysql.com/doc/mysql-g11n-excerpt/8.0/en/charset-...

ghusbands 5y ago
Maybe they should rename/alias it to brokenutf8, to make it more likely that people will notice a problem.
- ehsankia 5y ago
  That's not needed, what is needed is what they said they would do: > and utf8 is expected subsequently to become a reference to utf8mb4 Once utf8 points to the right thing, the mistake will stop happening as no one is explicitly using utf8mb3 by accident. Not sure why this wasn't done right away, and only will be "subsequently". Has it been done yet?
ksec 5y ago
I really wish they do this in a new, major version release. But they dont seems to be keen on releasing 9.0.
lmm 5y ago
Hmm, I wonder if they'll deprecate "LATIN1" as an alias for "CP1252 + 8 arbitrary characters" (a charset that literally only exists in MySQL) as well.

billpg 5y ago

Dear databases, please don't get hung up about string lengths when dealing with UTF8. If I ask for a UTF8 string with a max-length of 100, please don't apply the worse case scenario and allocate space for 100 emojis. Please give me a box of 100 bytes and allow me to write any UTF-8 string that can fit into 100 bytes in there. 100 ASCII characters. 20 emojis. Any mixture of the two. If I ask for UTF-8, it'll be because I'd like to make advantage of UTF-8 and I accept the costs. If that means I can't quickly jump to the character at index 84 in a string, no problem, I've accepted the trade-off.

dchest 5y ago
More like "100 ASCII characters. Unknown number of emojis." You'll get codepoints truncated if you use bytes.
- billpg 5y ago
  I would hope a database engine would throw an error instead of truncating bytes.
grogers 5y ago
Yes! This and other charset oddities almost make me want to store all strings as varbinary and handle any conversions to/from a string type to utf8 bytes client side. It makes ad-hoc query access from a shell much more annoying though.
- billpg 5y ago
  I wonder if you can do LIKE with a VARBINARY value. "SELECT * FROM Users WHERE Name LIKE @namePattern" @namePattern = UTF8.GetBytes("%billpg%")
petergeoghegan 5y ago
Why would the database "allocate space" unless it was truly necessary, in any case? While char(n) works that way, most applications don't actually use it, no matter the underlying DB system.

oleg_antonyan 5y ago

Thanks to emoji (almost)everything now supports unicode

dehrmann 5y ago

Memories... When I was at Amazon, my starter project was modifying Redshift to support four-byte UTF-8 characters, not just three-byte.

NelsonMinar 5y ago

In 2022 with a new release of MySQL or MariaDB, is it still possible to create a database with "utf8" encoding? Does it print a warning?

hu3 5y ago
I spun up a MariaDB cluster yesterday and each node came configured with utf8mb4 by default. Testing it now, manually chosing utf8 for new tables shows no warning.
- NelsonMinar 5y ago
  Thanks for the report! Glad real UTF-8 is the default at least.

karsinkk 5y ago

This might be a tad unrelated to the original post, but the following article is one of my favorite primers on Character sets/Unicode : https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/ https://www.joelonsoftware.com/2003/10/08/the-absolute-minim...

superasn 5y ago
Excellent article, even though I already knew most of it, given it was written in 2003 must have been one of the most insightful and enlightening article written. Wonder if you have any more links like this? Please do share!

Animats 5y ago

That was set up when Microsoft and Java had standardized on UTF-16. So this can represent the UTF-16 subset of Unicode, which is Unicode Plane 0, the Basic Multilingual Plane (BMP). The higher-numbered "astral planes" of UTF-8 were rarely used. All modern languages with a significant user base are covered in Plane 0. Plane 1, the Supplementary Multilingual Plane, with Cretan Linear B, Egyptian hieroglyphics, and such, was seldom needed. Few people had fonts for those, anyway. Because of the way UTF-8 is encoded, it takes 3 bytes to represent the UTF-16 set. That's because it only takes one byte for ASCII characters. Hence, 3-byte MySQL data. Emoji, though, were put in Unicode Plane 1. That's where mass demand for the astral planes came from. More four byte UTF-8 characters started showing up in data.

loulouxiv 5y ago
UTF-16 can represent the full range of Unicode codepoints by using couples of surrogates
- Animats 5y ago
  Sort of. Applications using UTF-16 have to be aware of pairs at the application level. Many are not.
  - int_19h 5y ago
    This isn't a consequence of using UTF-16 as such - Java, .NET etc could totally have an API around UTF-16 strings that handles surrogate pairs. The problem, rather, is that those languages introduced a 16-bit type that they called "character", even though it wasn't even a Unicode codepoint. And then used that type throughout all string APIs, including strings themselves (indexing etc). In .NET land you're now supposed to use https://docs.microsoft.com/en-us/dotnet/api/system.text.rune https://docs.microsoft.com/en-us/dotnet/api/system.text.rune instead. It transparently handles surrogate pairs, so the app needn't be aware of anything - and yet the internal encoding is still UTF-16.
- mort96 5y ago
  You're right. Replace UTF-16 with UCS-2 and the comment sounds at least slightly more correct.

brightball 5y ago

I just went through the process of converting a 2TB MySQL database over the summer. Quite a few gotchas along the way that make the process a huge pain.

ipaddr 5y ago

No one talks about the downsides of changing. Moving from 255 max characters to 191 or 192 max means a lot of your data needs to be moved into a text fields which means things like this field can't be a primary key.

Pxtl 5y ago
I'm used to mssql where nvarchar (the UTF-16 datatype) supports basically unlimited length... you can't make long PKs in mysql?
- ipaddr 5y ago
  You are limited by smaller types ints, dates and chars.
- cerved 5y ago
  friends don't let friends pk on variable length columns
cerved 5y ago
non numeric primary keys are generally a bad idea can't you specify collation by column?

CountSessine 5y ago

That's kind of funny - Oracle has a similar issue. If you want industry-standard UTF-8, you have to specify "AL32UTF8" as your encoding. "UTF8" is kind of crazy - it's this monstrous abomination called CESU-8 (https://en.wikipedia.org/wiki/CESU-8 https://en.wikipedia.org/wiki/CESU-8), which isnt' UTF-8 at all - it's actually this weird "UTF-16 complete with surrogate pairs wrapped in a UTF-8 shell" thing.

butlerm 5y ago
That would be a serious problem if Oracle's idea of "UTF8" wasn't roundtrip compatible with UTF-8, but it is fortunately, so approximately no one should notice the difference.

jmnicolas 5y ago

It's because of things like that it's still hard for me to respect MySQL in 2022. I use Postgres since 2008. It has never bitten me even once.

ars 5y ago
For something that was fixed 12 years ago? If Postgres has never bitten you, then you aren't using it much.
- mikl 5y ago
  It’s not fixed. MySQL still has a datatype called “utf8” that, in fact, is not utf-8 but a limited subset thereof. PostgreSQL has some sharp edges, sure, but a lot less obvious footguns like these.

iliketrains 5y ago

Fun story: When I was working at one of the FAANG companies, I have placed the (Night with stars, it does not seem to render here [0]) character in my preferred name. Some time later, I was notified by HR that I must change my preferred name and I can only use UTF-8 characters with maximum length of 3 bytes. I was quite confused why such specific demand, I am pretty sure it was exactly this issue. I replaced it with ᗧ···ᗣ··· [0] https://emojipedia.org/night-with-stars/ https://emojipedia.org/night-with-stars/

jiggawatts 5y ago
Are you called Bobby Tables by any chance?
- urbandw311er 5y ago
  Fantastic response

deleted 5y ago

[deleted]

didip 5y ago

MySQL is very similar to PHP in this regard. The good function name is actually broken so a new function with similar name is needed. This creates confusion for new people when adopting the technology.

egeozcan 5y ago
Oh the good old "mysql_real_escape_string"[0] days... I forgot most of my PHP knowledge but that somehow got stuck. [0]: https://www.php.net/manual/en/function.mysql-real-escape-string.php https://www.php.net/manual/en/function.mysql-real-escape-str...
- duskwuff 5y ago
  You can blame MySQL for that one. The function name is straight out of the MySQL C API: https://dev.mysql.com/doc/c-api/8.0/en/mysql-real-escape-string.html https://dev.mysql.com/doc/c-api/8.0/en/mysql-real-escape-str...

tomwojcik 5y ago

I need to share something as literally today I fixed a bug in our project that's somewhat related. MS SQL encodes everything with UTF-16. Emojis (code points) require up to 4 bytes. If it's a grapheme (emoji constructed from emojis), it will be even more. We are using Django. If you check length of an emoji, it will give you `1` and Django assumes utf8 everywhere. If you try to save it to PostgreSQL with char field `max_length=1` it will work just fine, but on MS SQL it will fail as it requires 2 characters (4 bytes) in the DB! I tried it with MS SQL 2017, 2019 with different collations on nvarchar and I'm pretty sure there's no way around it. > Because in CHAR(n) and VARCHAR(n) or in NCHAR(n) and NVARCHAR(n), the n defines the byte storage size, not the number of characters that can be stored, it's important to determine the data type size you must convert to, in order to avoid data truncation. https://docs.microsoft.com/en-US/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15#utf8 https://docs.microsoft.com/en-US/sql/relational-databases/co...

gecko 5y ago
Generically, anything by Microsoft will historically have used UCS-2, and will use UTF-16 these days, so this is utterly unsurprising to me as an experienced Windows dev. Conversely, Linux (and POSIX, more generally) deciding that filename encoding is a per-filename and untracked thing is a bit lit, from my perspective. Point being: when it comes to handling unicode and foreign characters, just, like...always read the documentation. Assume nothing.
- capitainenemo 5y ago
  Yep. Microsoft is the main reason for 2 of my favourite unicode links. https://utf8everywhere.org/ https://utf8everywhere.org/ and https://simonsapin.github.io/wtf-8/ https://simonsapin.github.io/wtf-8/ And, apparently it's mildly inaccurate to say it uses UTF-16... it's more like UCS-2 with UTF-16 hacked in, with no validation. Thus WTF-8.
  - ChrisSD 5y ago
    No, it's UTF-16 with no validation at the kernel level. Invalid UTF-16 is also invalid UCS-2 as those code points were explicitly barred from use. In practice, only malware will create such broken names. High level software (e.g. Microsoft's own VSCode) will not handle broken UTF-16. And indeed the in-built UTF-8 code page will lossily decode UTF-16 (unpaired surrogates are replaced with the Unicode replacement character).

ashvardanian 5y ago

Wow! I was working on this issue in our DBMS product today! Fun suggestion, try making a JSON string with a NULL character somewhere in the middle. It will be encoded as \u0000 and is a a valid UTF-8 code, but most C based systems will truncate the string by estimating its length via strlen. Java community and some other software vendors designed the Modified UTF-8, which replaces the zero with a 2-byte code point. Sleek. Aside from the fact, that you are modifying the data that customer wants to stay consistent. Postres explicitly bans such cases in the VARCHAR, not sure if it can fit in their JSON columns. Who tried?

cerved 5y ago
Null-bytes are not valid characters in JSON strings. Nor any other control characters for that matter
- lifthrasiir 5y ago
  They are valid if escaped, as explicitly noted in the Section 7 of RFC 7159 [1]. (Annoyingly enough it doesn't explicitly say JSON strings are Unicode strings, it just says that a certain subset of JSON strings is interoperable with Unicode.) GP means that the escaped null byte can still cause issues for C interoperability. [1] https://datatracker.ietf.org/doc/html/rfc7159#section-7 https://datatracker.ietf.org/doc/html/rfc7159#section-7
  - cerved 5y ago
    Exactly, null bytes aren't allowed. > All Unicode characters may be placed within the quotation marks, except for the characters that must be escaped: quotation mark, reverse solidus, and the control characters (U+0000 through U+001F). Any character may be escaped in this way > Any character may be escaped. I personally find the JSON spec very explicit while succinct
    - lifthrasiir 5y ago
      Oh, actually I made a mistake in the GP. The following sentence: > Annoyingly enough it doesn't explicitly say JSON strings are Unicode strings, [...] ...is false, I completely missed the very first section (I obviously searched for "Unicode", but failed to thoroughly check results). I have other valid criticisms of the JSON specification but that is not, so please ignore that part of rants since it was based on a wrong assumption. Thank you for (implicitly) pointing it out.

cryptos 5y ago

Best to be used with mysqli_real_escape_string ;-) https://www.php.net/manual/en/mysqli.real-escape-string.php https://www.php.net/manual/en/mysqli.real-escape-string.php

kolaente 5y ago

Also relevant about this: https://blog.koehntopp.info/2022/01/12/utf8mb4.html https://blog.koehntopp.info/2022/01/12/utf8mb4.html

Dylan16807 5y ago

Also make sure mysqldump is using utf8mb4 or you'll get character replacement in your backups. This often needs to be configured separately.

urbandw311er 5y ago

I enjoyed the article right up to the point where it concluded at the very end that the solution is to “switch to postgresql” without actually providing any rationale for that sudden and subjective ending.

talos2110 5y ago

So in mysql utf8 does not mean utf8. Reminds me of iso8601 in php, which does not mean iso8601. https://www.php.net/manual/en/class.datetimeinterface.php https://www.php.net/manual/en/class.datetimeinterface.php

77pt77 5y ago
MySQL and PHP go hand in hand when it comes to these poor decisions.

fake-name 5y ago

In MySQL, use postgres instead.

riteshpatel 5y ago

We learned this a few years ago when clients starting using emojis when saving their content and it got truncated. Very quickly converted everything to utf8mb4!

mayli 5y ago
emojis like this？🆒
- kortex 5y ago
  What is this voodoo? I thought HN didn't support emoji? It's sure 🆒 though.

77pt77 5y ago

MySQL is an awful piece of software. Try diving into the Time zone insanity, collations, and of course this UTF-8 gem that has lasted since at least 2011.

hashimotonomora 5y ago

Just use ASCII.

TedShiller 5y ago

use Postgres instead of MySQL

jijji 5y ago

i'll just stick latin1 charset for now... its slower and increases the size of ur tables to use utf8, etc [0] [0] https://stackoverflow.com/questions/12449336/utf-8-vs-latin1#12449477 https://stackoverflow.com/questions/12449336/utf-8-vs-latin1...

gurkwart 5y ago

MailNerd 5y ago

Another fun fact is that string comparisons are case insensitive by default: https://dev.mysql.com/doc/refman/8.0/en/case-sensitivity.html https://dev.mysql.com/doc/refman/8.0/en/case-sensitivity.htm... This can definitely catch you by surprise and cause bugs that are only detected late.

jamaicahest 5y ago

> Database systems have subtle bugs and oddities, and you can avoid a lot of bugs by avoiding database systems. This sounds like an offended developer. Avoiding database systems is not possible if you want to do a lot of programming tasks.

OliverJones 5y ago

WordPress has, of course, the lion's share of MySQL deployments by server count if not by row count. Yes, WordPress isn't the latest and greatest stuff. Yes, it's <bad thing> and <another bad thing>. But such is the curse of the customer base. They upgraded everybody from utf8 to utf8mb4 (excluding some users of ancient versions of MySQL) with their version 4.2. It went live on April 23, 2015. They announced the upgrade at the beginning of April 2015 here. https://make.wordpress.org/core/2015/04/02/the-utf8mb4-upgrade/ https://make.wordpress.org/core/2015/04/02/the-utf8mb4-upgra... There's some interesting stuff in there about the practical difficulties of the upgrade. The biggest issue was, and still is, the need for prefix indexes. With respect to Adam Hooper, his article was stale when it appeared just over a year after the WordPress schema upgrade. Here's right of course. But the biggest user base was well on their way to abandoning utfmb3 by that time.

ShaneMcGowan 5y ago

This one is very fun to debug in production when you're on MySQL 5.8

exabrial 5y ago

> Choose CHAR columns. (The CHAR format is a relic nowadays. Back then, MySQL was faster with CHAR columns. Ever since 2005, it’s not.) Is this vs VARCHAR? To me this is fascinating if so, I'd love to see a link or benchmark. My understanding that reading a stripe off a drive, then chopping it up indiscriminately at known intervals is faster than conditionally branching for strings of other lengths

btschaegg 5y ago

> And developers who wanted correctness were wrong to use “utf8”, because it can’t store “poo emoji”. This sentence deserves an award :) Edit: TIL: HN doesn't support emojis.