Unicode support in MySQL is...
codeka.com.au
codeka.com.au
There are a couple, and they are insidious. From 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.
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.
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.
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!
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. :)
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.
The ICU project - http://site.icu-project.org/ - has open source implementations of the collation algorithm including appropriate information for different locales. ie you shows those Danish names to a Danish user in their expected sort order, while also showing them to an American in their expected sort order.
i18n and l10n is hard. But it is also largely solved fairly well, and there is no excuse to avoid it all together or not use the ICU code.
It's also worth mentioning that by design, UTF-8 doesn't use any more space for storage than ASCII. There are exceptions when databases need to pre-allocate storage, but in general, you should just be using UTF-8 everywhere.
Don't hardcode utf8_unicode_ci either.
But MediaWiki does support other DBs, such as Postgresql and Sqlite.
http://blog.wikimedia.org/2013/04/22/wikipedia-adopts-mariad...
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.
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.
SELECT a.name, COUNT(b.id)
FROM a
LEFT OUTER JOIN b ON b.a_id = a.id
GROUP BY a.id
You could argue I shouldn't be doing that, and you may be right. But I just switched back to MySQL, and the problem went away.
It issues a warning, but most frameworks don't care to inform the caller if a warning has happened (some don't even provide a way to access them).
Yes. You should not be sending invalid data to your database, but holy sh*t, your database shouldn't (mostly) silently alter the data you entrust it with.
If the input data is wrong and you can't deal with it, blow up in the face of the user. Don't try to "fix" it by corrupting it.
Disclaimer: This happened to me in 2008 (http://pilif.github.io/2008/02/failing-silently-is-bad/), so it might have been fixed since, but stuff like this made me lose my trust in MySQL long ago.
sub connect_call_set_strict_mode {
my $self = shift;
# the @@sql_mode puts back what was previously set on the session handle
$self->_do_query(q|SET SQL_MODE = CONCAT('ANSI,TRADITIONAL,ONLY_FULL_GROUP_BY,', @@sql_mode)|);
$self->_do_query(q|SET SQL_AUTO_IS_NULL = 0|);
}
Then I would consider the framework broken. Warnings about truncated data are the norm in other RDBMSs.
I no longer develop software to run on mysql because I like my sanity.
I've been keeping a list of MySQL gotchas, but steering clear of the MySQL take on the SQL standard itself: http://williamedwardscoder.tumblr.com/post/25080396258/oh-my...
I still contemplate MySQL for new projects, because better the enemy you know and all that... OMG, I'm mad! Postgres rah rah rah!
http://betteratoracle.com/posts/18-limiting-query-results-to...
The lack of limit sucks, but the thing that annoys me most regularly is that Oracle refuses to make the 0 length string / null behavior configurable to allow for ANSI compliance ( MySQL null handling is worse IMO, makes me insane.... )
http://randomtechnicalstuff.blogspot.com.au/2009/11/nasty-or...
With postgres and mysql you can find many companies competing to offer great service, and who can contribute their fixes and improvements directly should they so choose. The stronger the output of these communities and the organizations that support them, the more pressure for even the companies like Oracle to improve ( or perish :))
Oracle is like COBOL; the only techies who like it are those whose job security depends upon it ;)
I started off my career as a PHP/MySQL developer. PostgreSQL is definitely years ahead of MySQL. It used to be that MySQL had far superior read performance for simple queries, but that hasn't been true since around 2006. Also Postgres' SQL optimiser is really good.
> PostgreSQL is definitely years ahead of MySQL
That is against all history, not only in software. Postgres is obvectively better (safer, better engineered, easier to use, etc.) than MySql, just as Python is objectively better than PHP, and an AK47 was better than most other assault rifles of its time.
These concurrency issues don't fully go away by adding the feature, which is why it is taking time to work out the desired behavior there.
I prefer to work with Postgres when I can, but I also have found instances where MySQL better fit my needs, a heap table, even with more recently added index only scans ( and even reordering the table with cluster may not be desirable) you may want an index organized table (oracle term ) (mysql would say clustered index )
I am curious why even in the context of freely available engines we focus on competition, which I think can lead to great things, but I don't understand the animosity the communities feel towards one another, isn't it just a matter of horses for courses? Are we really interested in competitive kills or improving both the tools we use and our understanding thereof ?
I don't mean to single your comment out gbog, and I know this is certainly not exclusive to databases either ( witness almost any discussion of various programming languages on HN )
I do think many (most?) people are interested in seeing all tools get 'better' over time, but what needs to be improved, or how it should be improved, often don't get agreed on.
The difference is, you don't have to do crazy things to run into truly insane behavior on MySQL ;-)
Even a perfect database, should such a thing ever exist, is not sufficient to protect us from a determined disaster
This is a confusion of concepts. That an encoding can encode a particular value does not mean all applications using that encoding will support that value. You can store any size number you like into a JSON number, but if you send me a 256-bit value in a field for temperature in celsius, I'm going to give you back an error.
Encodings ease data exchange, they do not alter the requirements or limitations of the applications using them. MySQL's original unicode implementation supported only the Basic Multilingual Plane, so unsurprisingly, characters outside that plane are rejected. That would be the case regardless of the particular encoding used.
What you want is support for all Unicode characters. This is a reasonable request, but it is decoupled from the encoding used to communicate those characters to MySQL.
The fact that MySQL itself gained support for all of unicode, but did not use this support in the 'utf8' encoding, shows that it is not actually UTF-8.
And yet you have to change the encoding of a column to support storage of non-BMP characters, even though the UTF-8 encoding supports them just fine. Where's the sense in that?
I don't see how I should imagine that putting utf8 as a column encoding should be interpreted as "I will read and discard utf8".
This is different from declaring utf8 as the connection encoding.
When someone says they accept UTF-8, UCS-2, or any other encoding, my first question is always "Are characters outside the Basic Multilingual Plane supported?". There is a long history quite apart from MySQL of the answer to that question being "no", particularly if the project in question originated in the 90s (or even early 2000s).
But then again, masochism is a reasonably common, if not normal, thing.
Which is unfortunate as the "complicate" solution is often just the right solution but if the wrong solution doesn't blow up into their faces in 99% to 90% of the use cases, many people are satisfied enough with it.
I think these reasons also play a role in PHP's ongoing popularity.
It has a good treatment of how to migrate encoding-wise which includes additional precautions not mentioned in the originally linked article.
(Though I have to admit, based on the title I was hoping for a deeper problem. MySQL has plenty of stupid gotchas like this, but it's (marginally) easier to deal with them than to cluster postgres)
AFAICR, there is performance issues about this. Western text does not "need" this representation and thus the MySQL utf8 handling could be faster.
I was also shocked to learn this while using MySQL 5.3 (where utf8mb4 is missing), which led me to upgrade to the 5.5 alpha.
From https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8...
"The character set named utf8 uses a maximum of three bytes per character and contains only BMP characters.
As of MySQL 5.5.3, the utf8mb4 character set uses a maximum of four bytes per character supports supplemental characters"
PS. I have migrated to MariaDB. Take this chance to move away from Oracle MySQL!
But it's not really all that much of an issue - just use the mb4 character encoding.
The hard part is knowing you need to :)
[quote] 😸 works just fine in PostgreSQL by default (Python too).
The UTF-8 issue is pretty complex, so it's not really a surprise that implementations (like MySQL's) are incomplete. The basic encoding from the code points into UTF-8 bytes could handle up to 30 bits of data (first byte of 0xFC indicating 5 following bytes with 6 bits of the code point each (6 bytes for 36 bit code points are only impossible because UTF-8 bans 0xFF and 0xFE as byte values other than for the Byte Order Mark). Yet various standards attempt to restrict the range, currently with RFC 3629 putting the ceiling at 0x10FFFF (4 bytes per code point). That RFC doesn't bother to justify the constraint, other than as a backwards compatibility band-aid with the more limited UTF-16. The point isn't to rag on UTF-16, which made sense once, but to express sympathy for the various attempts made to avoid coping with the full, 30-bit range the underlying encoding can actually handle.
Not only is there a wash of hodgepodgery on the range, but UTF-8 (and other similar encodings) can put small values in large byte encodings. There's nothing stopping someone from just using a 6-byte long, 30-bit codepoint block of RAM for each character, even just ASCII (obviously this violates a bunch of RFCs, but the coding system does provide for it). The result would confuse many UTF-8 parsers (and blocked by the more complete ones, a Pyrrhic victory), since ASCII characters are expected to be exactly one byte long in UTF-8, not seven. Even in RFC-compliant UTF-8, an ASCII character codepoint value can be encoded in four different ways. Example: an "a" (ASCII 97, hex value 0x61) can be encoded as any of (pardon any bit errors, I'm doing this off-the-cuff, late at night...):
00111101 - 0x61 - conventional ASCII 11000000 10111101 - 0xCO 0xBD - two-byte UTF-8 11100000 10000000 10111101 - 0xE0 0x80 0xBD - three byte UTF-8 11110000 10000000 10000000 10111101 - 0xF0 0x80 0x80 0xBD - four byte UTF-8
The latter three overlong encodings aren't considered canonical, and software devs have to fix them to make string matching efficient and so forth, to save space, and most worrisomely to prevent attackers from sliding special characters into strings to crack systems - say by using larger, noncanonical encodings to evade filters that would catch and block the canonical, shorter encodings.
Some developers would try to limit UTF-8 characters to four bytes because that's a power of two, and fits comfortably into a 32-bit long int.
UTF-16 also complicates UTF-8 with the banning of codepoints between 0xd000 and 0xdfff in UTF-8, used as "surrogate pairs" in UTF-16. See http://www.unicode.org/version... for an update that mentions this explicitly)
Anyway, the general idea is that I can sympathize with MySQL having incomplete UTF-8 support, most implementations are incomplete in some way, and one could argue the RFC's variant is pointlessly incomplete itself (no 24 and 30 bit code points, though apparently UNICODE's Annex D does allow the use of those two larger sizes for characters outside of the UNICODE range, perhaps widely interpreted as a constraint of UTF-8 itself instead of being about the UNICODE subrange of UTF-8). Implementations vary enormously, and with good reason (see http://www.unicode.org/L2/L200... for some of them).
Fortunately, MySQL's limitation doesn't trouble me, since I do almost all my work in PostgreSQL or some non-SQL database or another anyway. ;-P [/quote]
> The latter three overlong encodings aren't considered canonical, and software devs have to fix them to make string matching efficient and so forth, to save space, and most worrisomely to prevent attackers from sliding special characters into strings to crack systems - say by using larger, noncanonical encodings to evade filters that would catch and block the canonical, shorter encodings.
This is misleading. "Software devs" don't have to "fix them". Overlong encodings in UTF-8 are invalid since Unicode 3.1, precisely because of the security considerations, and conformant implementations are required "not to interpret any ill-formed code unit subsequences" (Unicode Standard section 3.9). RFC 3629 also states that "[i]mplementations of the decoding algorithm above MUST protect against decoding invalid sequences."
Fortunately, it's pretty straightforward to achieve this, and the Unicode standard contains a nine-row table listing all valid combinations of ranges of one to four bytes.
Unicode codepoint U+1F638 a perfectly valid Emoji character
http://flamerobin.blogspot.ro/2014/02/utf-8-is-handled-corre...
On iOS 7.0.1 it's a colorful thumbs down.
Internet Explorer 11 shows a black/white thingie which might be a thumbs down with a lot of imagination. Firefox shows the same bitmap.
I didn't spend much time reading the cited document, but it looks like some existing glyphs may be merged with newer ones under certain conditions. What a mess... I feel Unicode has tried to compass way too many things (even italics!). Go figure...
I'm guessing it's implemented like this for performance reasons and calling it utf-8 was just a marketing ploy as everyone knows we should always use utf-8 for everything...
From what I understand these have the same interface as MySQL but internally are different.
It is still a very informative piece and Joel was way ahead of the curve by supporting and evangelizing Unicode at all in 2003. But it is not the best article to point the OP at, as it does not mention the BMP or discuss proper handling of characters beyond the BMP.
See:
What is the maximum number of bytes for a UTF-8 encoded character?
http://stackoverflow.com/questions/9533258/what-is-the-maxim...