Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

MySQL foreign key cascade operations finally hit the binary log

readyset.io

Post reply on HN
Print view View original

21–28 of 28 posts

Re: MySQL foreign key cascade operations finally hit the binary log

#21

Post by evanelias »

ahofmann wrote: post #5
TexanFeller wrote: post #3

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.

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)

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?

Re: MySQL foreign key cascade operations finally hit the binary log

#24

Post by ahofmann »

evanelias wrote: post #21
ahofmann wrote: post #5

Earlier quoted context omitted.

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)

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?

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.

Re: MySQL foreign key cascade operations finally hit the binary log

#25

Post by evanelias »

ahofmann wrote: post #24
evanelias wrote: post #21

Earlier quoted context omitted.

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?

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.

> 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.

Re: MySQL foreign key cascade operations finally hit the binary log

#26

Post by derekperkins »

evanelias wrote: post #25
ahofmann wrote: post #24

Earlier quoted context omitted.

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.

> 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.

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

Re: MySQL foreign key cascade operations finally hit the binary log

#27

Post by booi »

evanelias wrote: post #12
nofriend wrote: post #10

Earlier quoted context omitted.

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…

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 limi…

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.

Re: MySQL foreign key cascade operations finally hit the binary log

#28

Post by evanelias »

booi wrote: post #27
evanelias wrote: post #12

Earlier quoted context omitted.

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 limi…

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.

> 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.

Post reply on HN