3 ms·

If you are building software that depends on a SQL database, always specify your encodings (and explicitly name your indexes)! I previously built an enterprise

If you are building software that depends on a SQL database, always specify your encodings (and explicitly name your indexes)! I previously built an enterprise product that didn't specify our string encodings in its initial migration and spent years fielding support tickets and dealing with the hell of making migrations that could support the default encodings across platforms until we could justify copying the whole column. Postgres largely avoids this problem by using utf8 as the default pretty much everywhere, but MySQL definitely has platforms that ship with defaults as latin1.

masklinn 5y ago
> Postgres largely avoids this problem by using utf8 as the default pretty much everywhere That's… not actually really relevant to the issue. And you can configure postgres to use a non-utf8 encoding as its default. Postgres avoids the issue by automatically moving large value outside of the row itself ("TOAST" storage). That's also why you can have an unbounded vachar in postgres, and there's no real difference between varchar, varchar(n) and text. And char(n) is slower than the others: char(n) will always pad to (n), while varchar and text will only use whatever they need (+ some overhead). TOAST works in terms of bytes, so your UTF8 might move to the next storage location earlier than if you'd use a fixed-size single-byte encoding. It just doesn't matter that much (it'll just be a bit slower) unless you actually reach the limit of TOAST storage itself (1GB per value). And Postgres has its own issue with fixed-size storage e.g. identifiers (names of tables, columns, indexes, constraints, etc…) can't be more than 63 bytes, they'll be silently and implicitly truncated to that.
- jzelinskie 5y ago
  It's true that Postgres does this. But the problem I was highlighting was the developer-error of lacking to explicitly declare an encoding. It's only really related to the OP link insofar-as when you fail to do so on MySQL, you'll receive error messages that inform you of the dreaded 767-byte index.
  - masklinn 5y ago
    You can actually have a similar error in postgres, it's just that the index limit is at 2730 bytes, and it's unlikely you'll have indexed a column which can get content more than 2.5k. It does happen though, as I've discovered (repeatedly, because I'm a slow learner).
VintageCool 5y ago
MySQL finally ships with utf8mb4 by default in MySQL 8, but anything older still uses latin1 / latin1_swedish_ci by default and should be converted to utf8mb4.
- hinkley 5y ago
  It's been years and I still get mad every time I think about the fact that 'utf8' is a broken encoding and they had to make a new one called utf8mb4. I have to trust my database. If I can't trust the authors of the database, then how do I trust the database?
  - josephg 5y ago
    To save a Google for anyone like me - there’s nothing wrong with utf8. The problem was that MySQL had a broken implementation of utf8 (“utf8mb3”) which only supported Unicode characters which fit in 3 bytes. As a result you couldn’t save any data in your database which contained emoji. This is upsetting because until recently if you asked for a utf8 column, this is what you got instead. You got something which was almost, but not quite compatible with utf8.
    - ChrisSD 5y ago
      Interestingly, this was a mistake even the creators of UTF-8 wanted to make > The 4, 5, and 6 byte sequences are only there for political reasons. I would prefer to delete these Obviously 5 and 6 byte sequences are consigned to history but 4 byte sequences turned out to be useful for more than "political reasons". http://doc.cat-v.org/bell_labs/utf-8_history http://doc.cat-v.org/bell_labs/utf-8_history (scroll right to the bottom)
- jillesvangurp 5y ago
  I ran into this in Amazon's RDS not so long ago (3 years?) where I wasted a good few hours figuring out why mysql was mangling our emojis. A good test for this is trying to insert the character, which will only work with utf8mb4 as it uses the full 4 bytes, which mysql's broken 3 byte utf-8 implementation simply does not support. This is also the reason for 191 character limit as 191 is 3/4 of the 255 bytes you'd be able to store with their broken implementation. Which of course 1/3rd of the maximum entry size for innodb of 764 bytes (the last one is \0), or 4*191. They never changed the underlying implementation. Hence the weird limit.
lmm 5y ago
You also need to be aware that MySQL's "latin1" is not latin-1. It's a nonstandard variant of windows CP1252 with 8 arbitrary extra characters.

masklinn 5y ago

> Postgres largely avoids this problem by using utf8 as the default pretty much everywhere That's… not actually really relevant to the issue. And you can configure postgres to use a non-utf8 encoding as its default. Postgres avoids the issue by automatically moving large value outside of the row itself ("TOAST" storage). That's also why you can have an unbounded vachar in postgres, and there's no real difference between varchar, varchar(n) and text. And char(n) is slower than the others: char(n) will always pad to (n), while varchar and text will only use whatever they need (+ some overhead). TOAST works in terms of bytes, so your UTF8 might move to the next storage location earlier than if you'd use a fixed-size single-byte encoding. It just doesn't matter that much (it'll just be a bit slower) unless you actually reach the limit of TOAST storage itself (1GB per value). And Postgres has its own issue with fixed-size storage e.g. identifiers (names of tables, columns, indexes, constraints, etc…) can't be more than 63 bytes, they'll be silently and implicitly truncated to that.

jzelinskie 5y ago
It's true that Postgres does this. But the problem I was highlighting was the developer-error of lacking to explicitly declare an encoding. It's only really related to the OP link insofar-as when you fail to do so on MySQL, you'll receive error messages that inform you of the dreaded 767-byte index.
- masklinn 5y ago
  You can actually have a similar error in postgres, it's just that the index limit is at 2730 bytes, and it's unlikely you'll have indexed a column which can get content more than 2.5k. It does happen though, as I've discovered (repeatedly, because I'm a slow learner).

VintageCool 5y ago

MySQL finally ships with utf8mb4 by default in MySQL 8, but anything older still uses latin1 / latin1_swedish_ci by default and should be converted to utf8mb4.

hinkley 5y ago
It's been years and I still get mad every time I think about the fact that 'utf8' is a broken encoding and they had to make a new one called utf8mb4. I have to trust my database. If I can't trust the authors of the database, then how do I trust the database?
- josephg 5y ago
  To save a Google for anyone like me - there’s nothing wrong with utf8. The problem was that MySQL had a broken implementation of utf8 (“utf8mb3”) which only supported Unicode characters which fit in 3 bytes. As a result you couldn’t save any data in your database which contained emoji. This is upsetting because until recently if you asked for a utf8 column, this is what you got instead. You got something which was almost, but not quite compatible with utf8.
  - ChrisSD 5y ago
    Interestingly, this was a mistake even the creators of UTF-8 wanted to make > The 4, 5, and 6 byte sequences are only there for political reasons. I would prefer to delete these Obviously 5 and 6 byte sequences are consigned to history but 4 byte sequences turned out to be useful for more than "political reasons". http://doc.cat-v.org/bell_labs/utf-8_history http://doc.cat-v.org/bell_labs/utf-8_history (scroll right to the bottom)
jillesvangurp 5y ago
I ran into this in Amazon's RDS not so long ago (3 years?) where I wasted a good few hours figuring out why mysql was mangling our emojis. A good test for this is trying to insert the character, which will only work with utf8mb4 as it uses the full 4 bytes, which mysql's broken 3 byte utf-8 implementation simply does not support. This is also the reason for 191 character limit as 191 is 3/4 of the 255 bytes you'd be able to store with their broken implementation. Which of course 1/3rd of the maximum entry size for innodb of 764 bytes (the last one is \0), or 4*191. They never changed the underlying implementation. Hence the weird limit.

lmm 5y ago

You also need to be aware that MySQL's "latin1" is not latin-1. It's a nonstandard variant of windows CP1252 with 8 arbitrary extra characters.