The infamous 20 year old MySQL Bug #11472 has been fixed.

[-]

keketi_@reddit

WTF? I was relying on this feature! Change it back please.

[-]

pixelbart@reddit

Bit fr, if it’s been there for so long, there must be countless workarounds in the wild and I wouldn’t be surprised if at least one of them breaks because of this fix.

[-]

mikeblas@reddit

The moat common workaround is: don't use MySQL.

[-]

lurked@reddit

Oh right, I forgot about the "Stack Exchange" way... change your tech stack for something else.

[-]

No, in this particular case, it's the "Use a database without 1000 'Gotchas' way" - this one is nothing compared to dozens they had for 20 years, and their fix was letting the client turn it on or off.

[-]

Scottz0rz@reddit

Yeah, it's only the most common FOSS database technology.

We should all use MongoDB, it handles web-scale and became ACID compliant in 2018.

MongoDB is web scale: https://youtu.be/b2F-DItXtZs

[-]

mikeblas@reddit

If you didn't start using it, it isn't a change. MySQL is deficient and deviant, and always has been.

[-]

pelrun@reddit

Found the Oracle consultant

[-]

nickcash@reddit

Oracle owns mysql, why would they call it deficient?..

obviously they were suggesting the one true lord and savior, postgresql

[-]

Xacor@reddit

Why use their FOSS software when you can pay them licensing for Oracle DB?

[-]

danstermeister@reddit

"Oh noes, we've lost yet ANOTHER open-source 'customer' to a paying one! Whatever shall we do????"

[-]

mikeblas@reddit

One reason might be a requirement for referential integrity.

[-]

mikeblas@reddit

Take inventory.

[-]

hughperman@reddit

Just become a lumberjack

[-]

94358io4897453867345@reddit

Never depend on undefined or broken behavior

[-]

manofsticks@reddit

I'd also bet someone has triggers that have been installed, but unfired for 20 years. If those start firing unexpectedly, or have 20 year old bugs themselves, it's going to be a bad day for someone trying to track that down.

[-]

Resident-Trouble-574@reddit

To be fair, most of those are probably still using mysql 5, and will never update it.

[-]

Ok-Membership-3635@reddit

Anyone screwed over by this unexpectedly has only themselves to blame for not following proper change management processes and testing new updates before deploying to prod. If something weird happens in the dev environment and you have to figure it out that's just an interesting day not a bad day.

[-]

ArtOfWarfare@reddit

Multiple commenters said there was no workaround in that entire comment thread on the bug. Several people mentioned that they moved to Postgres or Oracle to avoid the bug.

[-]

ChilllFam@reddit

Hyrum’s Law

[-]

Rulmeq@reddit

:) https://xkcd.com/1172/

[-]

ktoks@reddit

I was just thinking about this one yesterday.

[-]

shoter0@reddit

There is polish GPS app that is called "Yanosik". During this winter it had a bug where it was using too much CPU cycles and my phone was very hot while using it.

My car is very bad at heating when you want to defrost the front windshield. I was using this application as additional heating for my car to defrost it quicker.

I was really disapointed when they fixed the bug because it made defrosting harder/longer XD.

[-]

new2bay@reddit

https://lawsofsoftwareengineering.com/laws/hyrums-law/

[-]

MindStalker@reddit

In 9.7 (Released last month), you have to set enable_cascade_triggers=on otherwise it defaults to the old method. This will eventually depreciate in some future version, but I'm sure thats 10+ years away.

[-]

Chuu@reddit

In all seriousness this is going to break a lot of databases.

[-]

superyorch@reddit

They fixed the bug and included a setting to turn it on explicitly, so it won't break workaround code if in place. Here is a detailed article from the engineer that worked on it. https://blogs.oracle.com/mysql/no-more-silent-foreign-key-cascades-mysql-9-7-lets-child-triggers-speak-up

[-]

Potential_Financial@reddit

I think it’s interesting that the text of the article and the graphic disagree about the order of updates to parent vs child tables (if I’m understanding the graphic correctly).

It looks like a slide from a presentation. I wonder if that was a miscommunication with the person making the graphic, or whether the implementation changed.

[-]

mccalli@reddit

It’s long since vanished but in the very early days of MySQL they had a web page up patronisingly explaining that they didn’t support foreign keys because “foreign keys are bad and make application programming harder”.

Got it. A database that doesn’t understand data modelling and referential integrity. Noted.

Left it and never used it again.

[-]

TheBazlow@reddit

Now there’s an old time capsule! This is part of the MySQL manual for version 3.23 way back in 2000.

For those who would rather skip digging through old FTP servers, here’s the section:

5.4.5.1 Reasons NOT to use foreign keys

There are so many problems with FOREIGN KEYs that we don't know where to start:

Foreign keys make life very complicated, because the foreign key definitions must be stored in a database and implementing them would destroy the whole ``nice approach'' of using files that can be moved, copied and removed.
The speed impact is terrible for INSERT and UPDATE statements, and in this case almost all FOREIGN KEY checks are useless because you usually insert records in the right tables in the right order, anyway.
There is also a need to hold locks on many more tables when updating one table, because the side effects can cascade through the entire database. It's MUCH faster to delete records from one table first and subsequently delete them from the other tables.
You can no longer restore a table by doing a full delete from the table and then restoring all records (from a new source or from a backup).
If you have foreign keys you can't dump and restore tables unless you do so in a very specific order.
It's very easy to do ``allowed'' circular definitions that make the tables impossible to recreate each table with a single create statement, even if the definition works and is usable.

The only nice aspect of FOREIGN KEY is that it gives ODBC and some other client programs the ability to see how a table is connected and to use this to show connection diagrams and to help in building applicatons.

MySQL will soon store FOREIGN KEY definitions so that a client can ask for and receive an answer how the original connection was made. The current `.frm' file format does not have any place for it.

[-]

Hougaiidesu@reddit

Madness. That has not aged well

[-]

mccalli@reddit

That’s it! That’s the beast. Been looking for that for years.

What a misguided pile of utter garbage.

[-]

kenfar@reddit

Oh yeah the head of MySQL, Monte, would explain to people how foreign keys, views, subselects, triggers, unions, transactions, etc were all unnecessary for 90% of developers. People just didn't need them - and they made programming harder for zero gain.

Also listened to him at a data conference in Denver about seven years ago present how there was no such thing as big data. That no actual data exceeded what one could process on a single large mysql server.

Almost everything he said was a bullshit to justify the limits of the crappy mysql product.

[-]

LiftingRecipient420@reddit

Oracle owns MySQL, They also have a commercial SQL product: DB2.

Oracle is commercially incentivized to keep MySQL shitty.

Knowing those facts, I can't fathom why anyone would ever choose MySQL over literally any other rdbms.

[-]

poizan42@reddit

And then came the NoSQL movement who had that as a selling point, and suddenly a lack of referential integrity became what the cool kids did.

[-]

Venthe@reddit

NoSQL is amazing... For a very specific set of problems. Yet, as always, people thought that hype==use it everywhere.

[-]

coworker@reddit

Real world applications store data across multiple data stores so formal referential integrity is impossible. Yes, it's useful for subsets to be able to rely on foreign keys but at the end of the day you will always have to rely on code to check referential integrity.

[-]

mccalli@reddit

Oh god yes. So, so many terrible things because they didn't understand SQL.

SQL isn't a standard procedural programming language, no matter what Oracle's PL/SQL wanted it to be. It's an expression of set theory, and if you understand it as such you will suddenly get a lot more out of it. Neither too complex nor too simple - it's just trying to encompass the literal data set that you're working with. Not as jargon, not as a colloquial phrase for "the bits I need to deal with", but as a literal mathematical set.

Grasp this, and SQL is your friend.

[-]

gnuban@reddit

An SQL database manifesting early NoSQL energy, interesting

[-]

summerteeth@reddit

Anyone have an archive of this page? I’d love to read the rationale for not supporting a core relational db feature

[-]

mccalli@reddit

I've been searching for years for it. I see the odd reference from other people to the same page, but cannot see the page itself anywhere.

We're talking late 90s/early 2000s for the time period I'm thinking of.

[-]

GreenWoodDragon@reddit

I've worked with so many software engineers who have cluelessly incorporated this viewpoint into their solutions.

[-]

CanSpice@reddit

A company I worked at hired a guy who tried to explain that primary keys were too expensive to implement. We fired him after three weeks.

[-]

GreenWoodDragon@reddit

Shame that wasn't picked up in interview. Oh well.

[-]

jewdai@reddit

From a certain perspective all data modeling (if using an orm) should be done in code as that's the source of truth. Their is a cost to foreign keys that certain developers don't want to pay due to being web scale /s

[-]

Pjb3005@reddit

Oh so this is why I've run into multiple PHP stacks that had 0 foreign keys?

[-]

GreenWoodDragon@reddit

You are not alone!

[-]

ByronEster@reddit

My first introduction to it was like back in 2001 when we were using MSSQL and I was trialling Postgres on the side. The sysadmin who installed it was surprised by how fast it was, but also said it was only good for small amounts of data. It definitely outgrew it's shoes

[-]

cajunjoel@reddit

I use it only under duress these days.

[-]

segfaultsarecool@reddit

I figure the tech lead/pm/whatever in charge of a software project should be fired when a critical bug like this isn't fixed within 12 months of reporting. I mean, think of how many people died waiting for this bug to be fixed, and how many customers were lost or never acquired.

They weren't ACID compliant for 20 years....that's fucking insane...

[-]

Venthe@reddit

At the same time, people used and are using mysql to this date.

Which means... That this bug wasn't as critical as people think. Most of the bugs aren't.

[-]

HalfSarcastic@reddit

Unacceptable! What's next?! GTA 6??

[-]

Venthe@reddit

I'd settle for half life 3. Now that I think of it; there are people who are legally adult and were born after half life...

[-]

PersianMG@reddit

One day, not today, but one day.

[-]

prehensilemullet@reddit

Lol so glad I never had to do enough mysql to stumble upon this

[-]

hallman76@reddit

If we’re fixing old bugs can we get proper logging in hibernate now?

[-]

Physical-Compote4594@reddit

How many people rely on this behavior now?

[-]

GirlInTheFirebrigade@reddit

Ummmmm, I have at least one place were I thought it was super clever to use a trigger to make sure only fk constraints can delete entries from that table…..

[-]

Chuu@reddit

I don't think anyone is relying on this per-se, but having triggers going off when before they were not is definitely going to break some things out in the wild.

[-]

twisteriffic@reddit

Read the attached work log. It's disabled by default- you have to turn it on explicitly.

[-]

Physical-Compote4594@reddit

I was half joking but half serious.

The old behavior was “wrong” but has been around for 20 years. The new behavior is “right” but completely different. I’m keen to see what happens.

[-]

argh523@reddit

Probably not a lot. Because of things like this bug, anyone doing serious work realizes quite quickly why they need to keep things simple, or just move to Postgres or maybe MariaDB

[-]

stipo42@reddit

Probably not relying on it, but expecting it, so workarounds exist that will probably double the behavior

[-]

w1n5t0nM1k3y@reddit

Definitely going to be one of those things that requires a setting in the config file like allowing invalid dates.

[-]

Physical-Compote4594@reddit

😆

[-]

GirlInTheFirebrigade@reddit

and here I am thinking it was a feature

[-]

ScrungulusBungulus@reddit

I was there, Gandalf. Three thousand years ago...

[-]

nucLeaRStarcraft@reddit

[15 Jul 2020 12:28] Jay Godara

Guys my girlfriend says that she will marry me once this bug is resolved. Do we have any update on this?

P.S: We've been waiting since 2017 and she's now consider to Gary.

P.S 2: Gary you're a prick!

lol

[-]

eritter688@reddit

The comment section is so good!

[-]

stamatt45@reddit

30 Jun 2005 19:04] We will fix this in 5.1

LOL

[-]

Ticmea@reddit

No way!!!

[-]

psaux_grep@reddit

Only for version 9 and up.

[-]

otac0n@reddit

Good?

[-]

mothzilla@reddit

Hmm. Not even sure I would have called this a bug. Setting a foreign key to null when the reference is deleted is just maintaining integrity.

[-]

BaNyaaNyaa@reddit

That's not the bug. The bug is that when that foreign key is updated due to the cascade, the UPDATE triggers on that table weren't executed.

[-]

mothzilla@reddit

I know. I wouldn't have expected a change to a foreign key field when the target is deleted to fire.

[-]

Lonsdale1086@reddit

Did the table not get updated?

[-]

mothzilla@reddit

Kind of. It got updated so that the reference remained valid (null being valid to say that this is now an orphan). But it didn't get updated through an UPDATE.

[-]

Lonsdale1086@reddit

So what?

The data still got updated, so update triggers on the table should fire.

This is the case in practically every DMBS?

[-]

mothzilla@reddit

It's an update for the database to maintain its own integrity. It's not an update for the user/application.

If you've got to the point where you're relying on db internals then you might have made a mistake.

[-]

triplej_official@reddit

Bi

[-]

Fuzzy_Paul@reddit

Been working at least 2 decades with MySQL. Never encountered a flaw in the tables. All depends on the programming. Glad i stuck with it all thise years.

[-]

Extras@reddit

OMG I literally have this bug's birthday on my calendar to celebrate yearly. I've run into this thing like three times in my career, thank you to the devs for fixing this!! Looks like triggers are back on the menu

[-]

DMayr@reddit

What a joke of a DBMS

[-]

qascevgd@reddit

Haha, the most recent comment is from the original reporter as well.

[-]

mccoyn@reddit

It’s probably locked for most users because it is too old.

[-]

Business_Ant_5641@reddit

wild how this one became almost a piece of internet folklore at this point. every few years someone would rediscover it, joke about its age, swear MySQL would never touch it, and then… it just kept living on like some undead ticket nobody wanted to poke.

[-]