[HN Gopher] Database character sets and collations explained - w...
___________________________________________________________________
Database character sets and collations explained - why utf8 is not
UTF-8
Author : flokoe
Score : 62 points
Date : 2022-01-04 12:05 UTC (1 days ago)
(HTM) web link (www.hellodevops.blog)
(TXT) w3m dump (www.hellodevops.blog)
| tzs wrote:
| > For example utf8mb4_general_ci does not knwo how to sort s and
| the German character ss ("sharp S") in contrast to
| utf8mb4_unicode_520_ci which sorts ss just fine
|
| If I use utf8mb4_general_ci now, is it guaranteed to produce the
| same results on a given dataset each time, and to keep producing
| those same results under future versions of MariaDB?
|
| Or is using it on data that contains characters for which it does
| not define a sort order like using undefined behavior in C, and
| it is free to produce different results from run to run and from
| version to version?
| avgcorrection wrote:
| > Even though 128 characters are enough to cover the English
| language and a few basic symbols
|
| You can communicate just fine in English using ASCII. But plenty
| of English words use non-ASCII letters.
| DemocracyFTW wrote:
| AEsthetics do play a role here
| kingcharles wrote:
| To think otherwise would be naive.
| [deleted]
| Karellen wrote:
| * "Why MySQL/MariaDB's 'utf8' is not UTF-8"
| flokoe wrote:
| I changed the title to be more specific. Thanks!
| koolba wrote:
| It really should have MySQL in the title as it's not a
| generic database abnormality. It's specific. To MySQL (and by
| extension MariaDB).
| [deleted]
| acabal wrote:
| utf8 vs utf8mb4 bit me in one of the most frustrating bugs I've
| ever had to debug.
|
| At Scribophile members can write critiques for people's writing,
| with comments inserted inline. The underlying software dates back
| to the PHP5 days when MySQL only had the utf8 option. Everything
| was working fine for years, when all of a sudden users started
| complaining that from time to time, they would submit a critique
| and it would be mysteriously cut off at random places.
|
| The problem was very intermittent, didn't happen very often, and
| there was no seeming rhyme or reason to it. But when it _did_
| happen, it was catastrophic because members would lose hours of
| work, seemingly at random!
|
| All kinds of testing scaffolding and logging was put in place to
| try to find the problem with no luck. Then, after quite some
| time, we realized what the problem was.
|
| At that time, emoji keyboards were brand new; most phones/tablets
| had limited support, and people didn't yet use phones and tablets
| for everything like they do now. But they were out there. Some
| users who were using these new emoji keyboards were inserting
| emoji smiley faces as they were writing critiques. In
| Scribophile's web interface, everything looked fine; but when the
| user submitted the critique, MySQL tried to insert a multibyte
| Unicode character into a regular utf8 field, and _SILENTLY threw
| that character and all of the data after it away!!_
|
| Boy were we upset at MySQL about that one. But at least we
| figured it out!
| gpderetta wrote:
| You probably mean outside of the basic multilingual plane. Any
| non-ascii character is multibyte in utf8.
| webtopf wrote:
| I had the pleasure of finding that same behaviour in an
| application around 2015. When someone commented on a food order
| "Extra cheese please :folded hands: but no shrimp, I'm
| allergic."
|
| But one thing I haven't found out is why some emojis did go
| through. The basic ones like a simple :smile: it seemed to me.
| Could it be that some only need 3 bytes and when more and more
| emojis got released, they went into 4 bytes?
|
| Edit: HN stripped the emojis from my comment... I would put a
| rolling eyes emoji here if I could.
| bombcar wrote:
| Exactly that - three byte emojis work fine in utf8 but 4 byte
| ones need utf8mb4 - and the four byte ones are the new ones
| that support the "color variations" and other similar things.
|
| Apple adding those probably caused more upgrades to utf8mb4
| than any amount of pleading from languages that actually
| needed them ever would have.
| kingcharles wrote:
| I looked through the master emoji list and I can't see that
| any expand into the 4th byte, but this stuff is REALLY
| complicated and I have no idea.
|
| https://unicode.org/Public/emoji/14.0/emoji-test.txt
| chrismorgan wrote:
| I believe you're confusing two distinct concepts:
|
| Sounds like you're looking at how many scalar values are
| in an extended grapheme cluster (e.g. U+1F635 U+200D
| U+1F4AB dizzy face + zero width joiner + dizzy symbol,
| that's one "character" made up of three scalar values).
|
| The thing in question here is UTF-8 code units (bytes),
| and how many UTF-8 needs to encode a single scalar value.
| UTF-8 needs four bytes for anything above U+FFFF, and
| almost all emoji are above that.
| kingcharles wrote:
| I'm confused because U+1F635 can be stored in 3 bytes? I
| see nothing above 0xFFFFFF which would need the 4th byte?
| Or am I being dense?
| kangalioo wrote:
| In UTF-8, only some bits are used for the actual
| character codes. The rest are control bits for the
| decoder to know where a character starts and ends.
|
| https://en.m.wikipedia.org/wiki/UTF-8#Encoding
| [deleted]
| riffraff wrote:
| I hit the same issue in some app I wrote. It's what convinced
| me never to use MySQL ever again.
| BoxOfRain wrote:
| It's stories like this that stop me complaining online about a
| long day of futile debugging!
| chrismorgan wrote:
| utf8mb3 allows representing up to U+FFFF, but not
| U+10000-U+1FFFF.
|
| It's like UCS-2 is to UTF-16, only without any actual technical
| rationale.
| petee wrote:
| The 2002 diff got me interested in the context of why that change
| was made, which lead me to this 2006 bug which I think might shed
| light on why '3' was chosen:
|
| _" The UTF-8 encoding is a variable byte length encoding. The
| ASCII subrange of characters is one byte long, alphabetic
| writings 2 bytes long, Far Eastern writings 3 bytes long per
| character (and some rare historical writings are mapped using 4
| bytes)."_
|
| https://bugs.mysql.com/bug.php?id=18560#c61151
|
| UTF-8 is still something I'm learning the details about, but it
| looks like back in 2002 they simply didn't see the need for the
| supplemental plane characters, ie, playing cards, arrows, or
| ancient Greek numbers. It might be short sighted but we shouldn't
| judge so far in the future, recalling that emoji wasn't even in
| Unicode until 2010.
| [deleted]
| chmod600 wrote:
| What we can judge harshly is that it accepted the 4-byte
| sequences and failed silently, corrupting data.
|
| A sane system would reject the 4-byte sequences so you could
| try again without the emojis.
|
| But if users got errors, they might investigate, and it would
| slow down the rapid growth mysql was aiming for. So they just
| decided not to throw errors.
| Diggsey wrote:
| UTF-8 used to support up to 6 bytes to encode a unicode scalar
| value. Only later was the unicode range restricted such that a
| max of 4 bytes was required to encode any scalar value using
| UTF-8.
|
| It looks like when UTF-8 was originally added to MySQL it still
| supported up to 6 bytes per "character", so the decision to
| restrict it was actually made by both MySQL and RFC 3629 -
| unfortunately they chose different limits :(
| tialaramex wrote:
| What happened is Unicode committed forever to a range zero to
| U+10FFFF and RFC 3629 follows that. You don't need 5 or 6
| byte UTF-8 to express this range, and so they're forbidden.
|
| So RFC 3629 isn't choosing arbitrarily, the choice in MySQL
| isn't entirely arbitrary either, it's just wrong (and should
| not have been named "utf8").
|
| You could use the 3-byte UTF-8-like encoding MySQL has to do
| CESU-8 but that's not UTF-8. If MySQL wanted to do CESU-8
| they could have called the type cesu8, giving away what's
| actually going on and necessitating a _lot_ of extra work by
| MySQL. They did not choose to do that.
| tialaramex wrote:
| If it was called just "string" or something and then it quietly
| explained somewhere that it's actually only the BMP and not all
| of Unicode that's not so surprising, but they deliberately
| called it UTF-8 _even though it just isn 't_.
|
| Imagine if you bought a USB mouse and then you discovered, oh,
| they didn't bother implementing USB, this is just a PS/2 mouse
| with the USB connector. Works fine so long as you use the
| supplied PS/2 "adaptor" but it isn't actually USB even though
| it says it is on the box. That mouse is defective. MySQL is
| defective as supplied. Which won't come as a surprise to many
| experienced MySQL developers.
|
| I actually rather like MySQL, but it's like bash or Excel, it
| is a good tool for quick estimates or prototyping, and a poor
| tool for actual engineering. For the cost of all the work
| needed to get reliable high quality results with MySQL you
| could have used a different database where the baseline effort
| to get it working was higher but what you get is more solid,
| and "utf8mb4" is an example of that extra work in MySQL.
| petee wrote:
| While searching yesterday, I recall the mysql documentation
| did specify this this quirk.
|
| As I gather, this change happened prior to or at the change
| to 4 char max utf; was supplemental plane support required at
| that time? 3 char is still utf8, just not fully supported,
| but I really do not know.
|
| Not to defend, just saying things were new and fluid back
| then, though it should have been fixed properly alot sooner.
| marginalia_nu wrote:
| > Regarding the different collation algorithms: The unicode
| variants should be slower than the general variant due to the
| more complex algorithms, but this was years ago when computers
| were much weaker than now. With modern hardware, the speed gain
| should be hardly notable.
|
| ...
|
| > Furthermore, we should prefer accuracy over speed. There is no
| need to use an old and quirky collation like utf8mb4_general_ci
| with modern hardware.
|
| This is kind of oversimplified and depends a lot on your use
| case. Performance can absolutely matter even with modern
| hardware. There is no one-size-fits-all collation that is best in
| all scenarios, if there was, we really wouldn't need the option
| to choose.
|
| If you have a thousand rows in your table, sure, use whatever. If
| you have a billion rows in your table, things are different and
| you may not be able to afford leaving performance on the table.
| flokoe wrote:
| Thanks for your critique!
|
| I admit it may be a simple view, but I still believe it is the
| best choice for most of us.
|
| However, if you are in a position where you have to handle such
| an extensive dataset, you probably know about this detail and
| can decide if this is a suitable option or not.
| captainmuon wrote:
| I understand why this would affect collation and indices, but why
| can't I even store the four byte characters in an utf8mb3 table?
| The SQL parser and printer seems to deal with the characters
| fine, because it is independent from the table. I would assume it
| would just stuff the UTF-8 bytes in there as they are. Is it
| trying to validate them? Or is it using UTF-32 (or a truncated
| version) internally and saving everything as 3-4 bytes?
| orthoxerox wrote:
| I wonder who that "bar@bar.mysql.r18.ru" was.
| tpoacher wrote:
| Is it me or does the author seem to be using utf8 and unicode
| interchangeably (at least in the intro)?
| TedShiller wrote:
| aka why I switched to Postgres
| somat wrote:
| What I am curious about is why couldn't they fix it?
|
| At one point the change was made from 6 to 3 bytes, I expect this
| made the on disk structure incompatible with prior versions. why
| were they able to make this change but unable to make a later
| change say from 3 to 4 bytes.
|
| I am not as familiar with mysql but note that postgres major
| versions are not disk compatible with each other(you have to dump
| and restore). I expect this is the exact reason. you want a
| system where it is possible to make changes.
|
| see also: the drama around linux abi changes vs openbsd abi
| changes.
|
| To meme off of raul julia: openbsd to linux: "For you, the day
| your abi changed was the most important day of your life. But for
| us, it was Tuesday."
| evanelias wrote:
| VARCHAR(N) can store N _characters_. So with utf8mb3, that 's a
| max of 3N bytes worst-case. But if these columns were
| silently/automatically converted to utf8mb4 upon upgrading
| MySQL, they would now need to store max 4N bytes, which (with a
| high N) can exceed internal limits such as maximum length of an
| index key.
|
| IIRC, there were additional problems in older versions of
| MySQL, situations where sort buffers were sized to a fixed
| length equal to the value's worst-case size or something like
| that. So sorting a large number of utf8mb4 values would use a
| lot more memory than utf8mb3 values (again, iirc, I might be
| wrong on this).
|
| So the safer and more backwards-compatible approach was to
| introduce utf8mb4 as a new separate charset, and allow users to
| choose. MySQL 8 is now transitioning towards deprecating
| utf8mb3, and will finally make the utf8 alias point to utf8mb4
| sometime in the near future.
|
| That said, there are still a bunch of unpleasant uses of
| utf8mb3 internally in things like information_schema. I develop
| schema management tooling and recently lost a week to writing
| work-arounds for some of the more obscure ones, see
| https://github.com/skeema/skeema/commit/bf38edb :)
| lalaithion wrote:
| What's the problem with allowing VARCHAR(N) store a max of 3N
| bytes worst-case, but still allow the 4-byte characters to be
| present in the string? Sure, it means that you can only store
| 0.75 * N emojis in a VARCHAR(N), but that seems less bad than
| the alternative of silently corrupting data.
| evanelias wrote:
| 4-byte characters are simply not valid in the utf8mb3
| character set. The column type defines what values may be
| stored in the column, and the character set is inherently
| part of the column type for textual data.
|
| So your question is similar to asking, what's the problem
| with allowing storage of negative numbers in an unsigned
| int column. The answer is those bytes _inherently have a
| different meaning_ , or may be completely invalid,
| depending on the column type.
|
| > that seems less bad than the alternative of silently
| corrupting data
|
| MySQL has defaulted to a strict sql_mode out-of-the-box
| since MySQL 5.7, released over 6 years ago. With strict
| sql_mode, no silent corruption of data occurs. If you
| attempt to insert a 4-byte character into a utf8mb3 column,
| your insert fails with an error.
|
| Everything older than 5.7 has hit end-of-life, so there is
| literally no supported version of MySQL which doesn't
| default to enabling strict mode.
|
| The only problem is that database-as-a-service providers
| use nonstandard defaults, so e.g. AWS RDS doesn't come with
| strict mode enabled, for whatever completely insane reason.
| But that's technically an Amazon problem, not a MySQL
| problem.
| chmod600 wrote:
| "those bytes inherently have a different meaning"
|
| Are you sure? Utf-8 is a variable-width encoding so it
| should just work, right?
| kevin_thibedeau wrote:
| Everything that decodes that utf-8 has to support 21-bit
| codepoints. If they used a uint16_t anywhere in the
| processing chain then it's restricted to the BMP.
| evanelias wrote:
| Yes, you're correct that utf8mb3 is strictly a subset of
| utf8mb4, and the binary value of any valid utf8mb3
| character is identical to its utf8mb4 representation. But
| I'd argue that's irrelevant.
|
| I meant my statement generically: the meaning of the
| bytes stored in a column value depend entirely on the
| column type, as does the range of values that may be
| stored or converted.
|
| If a column's type is, say, VARCHAR(100) CHARACTER SET
| utf8mb3, this means the column can store up to 100 utf8
| characters _and_ each character has a maximum length of 3
| bytes. It is simply not legal to store 4-byte characters
| in this data type, just as it isn 't legal to store 101
| characters in it.
|
| The proposal in this subthread seems to be, instead of
| introducing utf8mb4, why didn't MySQL allow "utf8"
| (utf8mb3) columns store 4-byte characters, just without
| affecting the maximum number of _bytes_ that can be
| stored in the column? So, a VARCHAR(100) CHARACTER SET
| utf8 might only be able to store max 75 characters if
| they 're all emoji. Additionally, it would mean that the
| range of values allowed in a column would depend on the
| DB server version, and would change upon upgrading. This
| just seems completely counterintuitive and wrong to me,
| relative to how all other column type definitions work.
| The modern progression of MySQL has been to _remove_
| these obvious-WTF problems, not introduce more of them :)
|
| The proposal was described as "seems less bad than the
| alternative of silently corrupting data", but that isn't
| the alternative (silent data corruption doesn't occur
| with strict mode which is enabled by default), so this
| comparison doesn't make sense.
| chrismorgan wrote:
| > _they would now need to store max 4N bytes, which (with a
| high N) can exceed internal limits such as maximum length of
| an index key._
|
| Specifically, indexes are limited to 767 bytes (some say
| 768?), which means 191 utf8mb4 characters. So your otherwise-
| innocuous VARCHAR(255) primary key breaks. I've seen this
| multiple times, e.g. django-celery probably _still_ has this:
| https://github.com/celery/django-celery/issues/259.
| evanelias wrote:
| fwiw the limit is effectively 3072 bytes nowadays, default
| since MySQL 5.7.
|
| You could actually bump it up to 3072 in 5.5 and 5.6 too,
| but it required changing several settings
| (innodb_large_prefix, innodb_file_format,
| innodb_file_per_table, and the table's ROW_FORMAT).
| emmelaich wrote:
| Probably just better off storing as a blob and doing all
| interpretation / normalization outside the db.
| morgo wrote:
| You are correct, it is a disk-format issue, _and_ MySQL
| officially supports in-place upgrade between versions.
|
| It's actually quite hard to fix bugs in charset/collations,
| because any changes to the sort order implicitly could affect
| the on-disk format (indexes are sorted).
| alserio wrote:
| > One last concern is the size stored on the disk because UTF-8
| supports four bytes per character. But since UTF-8 is a variable-
| width character encoding, it only uses as many bytes as needed.
| For example, the first 128 characters like ASCII only use one
| byte
|
| I'm not sure this is true if you use CHAR instead of VARCHAR.
| Moreover if I'm not misremembering indexes still store the full
| fixed size 3 or 4 byte representation and index size might
| matter. However, I'll still use mb4 to avoid the fun times other
| have already described in the comments
___________________________________________________________________
(page generated 2022-01-05 23:01 UTC)