6 ms·
Now presumably there is some drawback from doing this, otherwise "utf8mb4" would be the default (right?) but I'll be damned if I can figure out what the drawbac
by ggreer 13y ago
Now presumably there is some drawback from doing this, otherwise "utf8mb4" would be the default (right?) but I'll be damned if I can figure out what the drawback is.
There are a couple, and they are insidious. From http://geoff.greer.fm/2012/08/12/character-encoding-bugs-are-%F0%9D%92%9Cwesome/ http://geoff.greer.fm/2012/08/12/character-encoding-bugs-are...:
This is when I discovered that InnoDB limits index columns to 767 bytes. Why is this suddenly an issue? Because changing the charset also changes the number of bytes needed to store a given string. With MySQL’s utf8 charset, each character could use up to 3 bytes. With utf8mb4, that goes up to 4 bytes. If you have an index on a 255 character column, that would be 765 bytes with utf8. Just under the limit. Switching to utf8mb4 increases that index column to 1020 bytes (4 * 255).
And later:
You might say, “OK, now we’re finished, right?” Ehh… it’s not so simple. MySQL’s utf8 charset is case-insensitive. The utf8mb4 charset is case-sensitive. The implications are vast. This change in constraints forces you to sanitize the data currently in your database, then make sure you don’t insert anything with the wrong casing. At work, there was much weeping and gnashing of teeth.
Good luck. The real solution is to switch to something that isn't MySQL. The project in that post ended up switching to Cassandra. For new SQL stuff, I now use PostgreSQL.
Edit:
To those of you who point out that utf8mb4_unicode_ci is case-insensitive, we did try that. This whole fiasco happened two years ago, and I don't remember what the show-stopper was, but we decided not to use that collation. Whatever issues we had with it were bad enough to warrant switching to utf8mb4_bin and sanitizing everything. Really, the solution is to switch away from MySQL and forget about collations and encodings.
- the_mitsuhiko 13y agoThere is a case insensitive collation for utf8mb4 so half of the problems there are just a misconfiguration. The index size restrictions are a problem though.
- ggreer 13y agoThere is a utf8mb4_unicode_ci collation, but we had big problems with it. I'm sorry, but this happened two years ago and I can't remember what the exact issues were. Whatever they were, they were bad enough that we ended up using utf8mb4_bin and sanitizing everything. Edit: Apologies, I wrote utf8mb4_general_ci when I meant to say utf8mb4_bin. I think the issue we had was conflicting rows on unique constraints due to utf8mb4_unicode_ci having different comparison rules than utf8_unicode_ci. Don't want to be confused by utf8 vs utf8mb4 charsets or deal with the subtle differences between utf8_general_ci, utf8_unicode_ci, utf8mb4_general_ci, utf8mb4_unicode_ci, and utf8mb4_bin? Switch to PostgreSQL. You will not regret it.
- ars 13y agoA vague problem like this is really not cool. At the minimum your readers deserve that you update the article. Say that there is a case insensitive version. If you want add that you had some problem but that you don't know if it had anything to do with it or not. The problem you had was probably because of the difference between utf8mb4_general_ci and utf8mb4_unicode_ci.
- the_mitsuhiko 13y agoThat sounds very much like FUD. There is nothing inherently magical about utf8mb4_unicode_ci.
- ars 13y agoThe case sensitive part is incorrect, the author made a mistake and corrects it in the comments, but not in the article (don't know why). Case insensitive works fine with mb4. Erm, what? You are the author! So why would you copy/paste a section you know is incorrect? And an even bigger question is why would you leave it on your website to confuse lots and lots of people! Fix it both here and in the article!
- oxymoron 13y agoSomeone pointed out in the comments on that link that utf8mb4 isn't necessarily case sensitive. The author believed that due to the fact that he was using utf8mb4_bin_ci as collation, which is a binary collation. It works as expected when using a "true" collation like utf8mb4_general_ci. The index problem is mildly annoying, but limiting index length isn't a big problem. For most applications, you can uniquely identify a row with much less than 191 characters, and it might be a good optimization to limit index length regardless to prevent the index size from growing out of control. If it's truly a problem, hacks like adding a sha1 or md5 hash column with a value derived from the original column and indexing that instead solves the problem. Edit: Ops. Didn't realize parent was the article author. :)
- metafunctor 13y agoFor anyone thinking of using utf8_general_ci, it's also seriously broken if you care about sameness of characters at all. For example, utf8_general_ci treats u and Ü as the same character. They are very much not the same. Your sort orders for international texts will be all over the place if you use utf8_general_ci. Use utf8_unicode_ci instead.
- joshfraser 13y agoutf8_general_ci is a lot faster than utf8_unicode_ci and it's good enough for whenever you don't need to sort international strings.
- Larrikin 13y agoGood enough when you ignore non English speakers is not good enough
- coldtea 13y agoFor a lot of uses cases it very much is. Even ascii can work for some. Not all tables/DBs have to do with spoken language data.
- dmethvin 13y agoBut a lot of databases have to deal with people's names. To paraphrase an old saying, "I don't care why I'm in the database, as long as they spell my name right."
- joshfraser 13y agoutf8_general_ci will still spell your name correctly, it just might not sort it correctly if you use an ORDER BY
- _delirium 13y ago
- Svip 13y agoThis is also the reason why MediaWiki have been using binary blobs for text data in their MySQL database. And have been for years.
- jmnicolas 13y agoAt that point they should really consider switching to another DB imo.
- Svip 13y agoPerhaps. But I think they are keeping MySQL support both for legacy reasons and compatibility reasons. You could also argue they should stop implementing it in PHP, if they are willing to sacrifice its ease of install. But MediaWiki does support other DBs, such as Postgresql and Sqlite.
- jamroom 13y agoMight not be any different, but I believe MediaWiki runs on MariaDB: http://blog.wikimedia.org/2013/04/22/wikipedia-adopts-mariadb/ http://blog.wikimedia.org/2013/04/22/wikipedia-adopts-mariad...
- vog 13y ago> For new SQL stuff, I now use PostgreSQL. That's so true! Years ago I switched almost all projects to PostgreSQL. I never looked back. I use MySQL only for very legacy applications. MySQL has so many bugs an inconsistencies, unicode support is only one of many issues. (Unsafe GROUP BY, silently cutting concatenated text fields when the result grows too big, silently converting invalid dates to '0000-00-00', and so on ...) In the past, MySQL had some performance advantages over PostgreSQL, but only for MyISAM, i.e. without real transaction safety. Nowadays, if you want to trade ACID for speed, you wouldn't use MySQL but some "NoSQL" database instead. So even from a performance point of view, I see no point in using MySQL for anything but legacy stuff.
- mephi5t0 13y agovery legacy?
- lsb 13y agomuch inconsistent. One nifty feature of Postgresql is that if you have a group by a,b,c,d, you need to aggregate everything else in your select that isn't a/b/c/d, or the statement won't prepare, whereas if you don't select an aggregate in MySQL you'll just get one random column, which is probably not what you want.
- pjungwir 13y agoThis is mostly true, but in recent Postgres versions (since 9.1 I believe), if you GROUP BY the primary key, you can still include other columns in the SELECT statement. For instance: SELECT a.name, COUNT(b.id) FROM a LEFT OUTER JOIN b ON b.a_id = a.id GROUP BY a.id Pretty nice!
- contingencies 13y agoI switched to sqlite. I've done my time on SQL servers. In my view, they simply have too much complexity for many scenarios. Basically, one big database server equals one big downtime when issues occur. Sharded data architecture on the other hand can design for failure and maintain graceful degraded performance, not to mention optimized resource allocation and the option of adopting vastly different security and backup policies across different parts of the datastore (eg. encryption, snapshots, etc.). Yes, you lose database-internal consistency guarantees. No, that doesn't mean you have to lose consistency.
- a3voices 13y agoIs there a phpmyadmin thing for PostgreSQL?
- AlterEgo20 13y agophppgadmin?
- a3voices 13y agoCool, thanks for the pointer