3 ms·

One of the main reasons I use Postgres is I've rarely(never?) seen an article like this posted about it. Every time I've touched MySQL I've found a new footgun.

ahofmann 8mo ago
MySQL is the PHP of databases. It was free, easy to setup and had the spotlight at the right time. The bad decisions that are baked into MySQL are plenty and really sad (like the botched utf8 defaults, the myisam storage engine, strange stuff around replication and mucu more)
- booi 8mo ago
  It still blows my mind that they called that crappy partial buggy characterset “utf8”. Then later came out with actual utf8 and called it “utf8mb4”. Makes no sense
  - evanelias 8mo ago
    They should have addressed it much earlier, but it makes way more sense in historical context: when MySQL added utf8 support in early 2003, the utf8 standard originally permitted up to 6 bytes per char at that time. This had excessive storage implications, and emoji weren't in widespread use at all at the time. 3 bytes were sufficient to store the majority of chars in use at that time, so that's what they went with. And once they made that choice, there was no easy fix that was also backwards-compatible. MySQL avoids breaking binary data compatibility across upgrades: aside from a few special cases like fractional time support, an upgrade doesn't require rebuilding any of your tables.
    - nofriend 8mo ago
      Your explanation makes it sound like an incredibly stupid decision. I imagine what you're getting at is that 3 bytes were/are sufficient for the basic multilingual plane, which is incidentally also what can be represented in a single utf-16 byte pair. So they imposed the same limitation as utf-16 had on utf-8. This would have seemed logical in a world where utf-16 was the default and utf-8 was some annoying exception they had to get out of the way.
      - evanelias 8mo ago
        OK, but that makes perfect sense given utf-16 was actually quite widespread in 2003! For example, Windows APIs, MS SQL Server, JavaScript (off the top of my head)... these all still primarily use utf-16 today even. And MySQL also supports utf-16 among many other charsets. There wasn't a clear winner in utf-8 at the time, especially given its 6-byte-max representation back then. Memory and storage were a lot more limited. And yes while 6 bytes was the maximum, a bunch of critical paths (e.g. sorting logic) in old MySQL required allocating a worst-case buffer size, so this would have been prohibitively expensive.
        booi 7mo ago
        This still makes no sense. The UTF-8 standard was adopted really in 1998-ish and the standard was already variable using 1 to 4 bytes. MySQL 4.1, which introduced the utf8 charset, was released in 2004. Even if there were no codepoints in the 4-byte range yet, they could and should have implemented it anyway. It literally does not take any more storage because it is a variable width encoding.
        evanelias 7mo ago
        > The UTF-8 standard was adopted really in 1998-ish and the standard was already variable using 1 to 4 bytes. No, it was 1 to 6 bytes until RFC 3629 (Nov 2003). AFAIK development of MySQL 4.1 began prior to that, despite the release not happening until afterwards. Again, they absolutely should have addressed it sooner. But people make mistakes, especially as we're talking about a venture-funded startup in the years right after the dot-com crash. > It literally does not take any more storage because it is a variable width encoding. I already addressed that in my previous comment: in old versions of MySQL, a number of critical code paths required allocating worst-case buffer sizes, or accounting for worst-case value lengths in indexes, etc. So if a charset allows 6 bytes per character, that means multiplying max length by 6, in order to handle the pathological case.
- evanelias 8mo ago
  InnoDB became the default storage engine over 15 years ago. MyISAM is barely used by anyone today. What "strange stuff around replication" are you referring to?
  - ahofmann 8mo ago
    I don't have all the details anymore. But one of the non obvious things for me was that foreign key cascades where not in the binlogs. I also think that some changes in the database layout could lead to strange things on the replicas.
    - evanelias 8mo ago
      > foreign key cascades where not in the binlogs. True, but that's finally solved now. > I also think that some changes in the database layout could lead to strange things on the replicas. I've been using MySQL for 23 years and have no idea what you're referring to here, sorry. But it's not like other DBs have quirk-free replication either. Postgres logical replication doesn't handle DDL at all, for example.
      - derekperkins 7mo ago
        I appreciate you carrying the torch for MySQL here, since most opinions are based on setups over a decade old, with little to no bearing on how it runs today

ahofmann 8mo ago

MySQL is the PHP of databases. It was free, easy to setup and had the spotlight at the right time. The bad decisions that are baked into MySQL are plenty and really sad (like the botched utf8 defaults, the myisam storage engine, strange stuff around replication and mucu more)

booi 8mo ago
It still blows my mind that they called that crappy partial buggy characterset “utf8”. Then later came out with actual utf8 and called it “utf8mb4”. Makes no sense
- evanelias 8mo ago
  They should have addressed it much earlier, but it makes way more sense in historical context: when MySQL added utf8 support in early 2003, the utf8 standard originally permitted up to 6 bytes per char at that time. This had excessive storage implications, and emoji weren't in widespread use at all at the time. 3 bytes were sufficient to store the majority of chars in use at that time, so that's what they went with. And once they made that choice, there was no easy fix that was also backwards-compatible. MySQL avoids breaking binary data compatibility across upgrades: aside from a few special cases like fractional time support, an upgrade doesn't require rebuilding any of your tables.
  - nofriend 8mo ago
    Your explanation makes it sound like an incredibly stupid decision. I imagine what you're getting at is that 3 bytes were/are sufficient for the basic multilingual plane, which is incidentally also what can be represented in a single utf-16 byte pair. So they imposed the same limitation as utf-16 had on utf-8. This would have seemed logical in a world where utf-16 was the default and utf-8 was some annoying exception they had to get out of the way.
    - evanelias 8mo ago
      OK, but that makes perfect sense given utf-16 was actually quite widespread in 2003! For example, Windows APIs, MS SQL Server, JavaScript (off the top of my head)... these all still primarily use utf-16 today even. And MySQL also supports utf-16 among many other charsets. There wasn't a clear winner in utf-8 at the time, especially given its 6-byte-max representation back then. Memory and storage were a lot more limited. And yes while 6 bytes was the maximum, a bunch of critical paths (e.g. sorting logic) in old MySQL required allocating a worst-case buffer size, so this would have been prohibitively expensive.
      - booi 7mo ago
        This still makes no sense. The UTF-8 standard was adopted really in 1998-ish and the standard was already variable using 1 to 4 bytes. MySQL 4.1, which introduced the utf8 charset, was released in 2004. Even if there were no codepoints in the 4-byte range yet, they could and should have implemented it anyway. It literally does not take any more storage because it is a variable width encoding.
        evanelias 7mo ago
        > The UTF-8 standard was adopted really in 1998-ish and the standard was already variable using 1 to 4 bytes. No, it was 1 to 6 bytes until RFC 3629 (Nov 2003). AFAIK development of MySQL 4.1 began prior to that, despite the release not happening until afterwards. Again, they absolutely should have addressed it sooner. But people make mistakes, especially as we're talking about a venture-funded startup in the years right after the dot-com crash. > It literally does not take any more storage because it is a variable width encoding. I already addressed that in my previous comment: in old versions of MySQL, a number of critical code paths required allocating worst-case buffer sizes, or accounting for worst-case value lengths in indexes, etc. So if a charset allows 6 bytes per character, that means multiplying max length by 6, in order to handle the pathological case.
evanelias 8mo ago
InnoDB became the default storage engine over 15 years ago. MyISAM is barely used by anyone today. What "strange stuff around replication" are you referring to?
- ahofmann 8mo ago
  I don't have all the details anymore. But one of the non obvious things for me was that foreign key cascades where not in the binlogs. I also think that some changes in the database layout could lead to strange things on the replicas.
  - evanelias 8mo ago
    > foreign key cascades where not in the binlogs. True, but that's finally solved now. > I also think that some changes in the database layout could lead to strange things on the replicas. I've been using MySQL for 23 years and have no idea what you're referring to here, sorry. But it's not like other DBs have quirk-free replication either. Postgres logical replication doesn't handle DDL at all, for example.
    - derekperkins 7mo ago
      I appreciate you carrying the torch for MySQL here, since most opinions are based on setups over a decade old, with little to no bearing on how it runs today