Foreign Key bugs in MySQL and MariaDB

Stage IV shows what will happen if bugs decide to attack humanity

Hint: it will not be good for us

Foreign keys are a controversial topic. Some developers love them, because they make the database more robust. Some DBAs forbid them , because they propagate locks , increase the number of reads, and pollute the buffer pool.

This article doesn’t discuss the foreign key problem globally. It is about the MySQL and MariaDB implementations : more specifically, it is a list of the most important bugs and limitations that involve foreign keys.

For each bug you can see its year, and the current state – in many cases, we can probably assume that the bug will never be fixed, and consider those bugs as permanent limitations.

I didn’t include bugs fixed in 8.0. Some of them were still “verified”, which probably means that they’re not fixed in old versions. I added comments to ask Oracle if those bugs are fixed, but Oracle didn’t care to answer or change the bug status until now.

Other considerations, or maybe a quick foreign key tutorial, may follow in other posts.

Contribute

I will try periodically review the list to keep it up to date. I don’t want it to get obsolete.

Please use the comments to inform me about bugs that are important for you. I may include them in the list. As usually, you are very welcome to report my mistakes, outdated information and state your opinions.

A personal note… this it the first time publish a post of this type. I confess that I’m worried about the judgement of a famous MySQL entomologist who does this kind of stuff much better than I. If you’re into bugs, follow his blog.

Bugs

Data inconsistencies

I’ve chosen to put this category of bugs first, because having foreign keys that generate data inconsistencies defies their purpose. This includes inconsistencies in replication, foreign keys not working, foreign keys preventing other features from working, or missing errors / wrong error handling (without a proper errors the application cannot handle anomalies).

• #4919 – Bad Foreign Key Definition does not work nor does it produce an error (verified, 2004)
• 6254 – IGNORE: should allow transactional tables to do partial transactions (verified, 2004)
• #11232 – Return a warning if creating a FOREIGN KEY on storage engines w/out support (verified, 2005)
• #11472 – Triggers not executed following foreign key updates/deletes (verified, 2005; now this is also documented, in the unrealistic hope that customers read the documentation in great detail before using any feature)
• #13977 – InnoDB foreign keys with multiple parent rows act incorrectly (open, 2005) Explanation: When an InnoDB foreign key reference has several parent rows, unexpected behaviour occurs: attempts to delete one of many parent rows in a RESTRICT relationship fail, even when the relationship would still be satisfied. This is documented .
  Personal note: While the documentation defined what the correct behavior is, there’s must be a logic behind a behavior. A documented bug is still a bug.
• #24668 – CASCADING FOREIGN KEYS do not work on same TABLE (verified, 2010, formally a feature request)
• #34932 – CREATE TABLE xyz like zyx (verified 2008, but probably informally considered “won’t fix”). The problem is that CREATE TABLE LIKE doesn’t copy foreign keys, and the motivation for not fixing this is “so no special code is needed for this”. Not creating a foreign key without any warning will probably lead to data corruption.
• #35358 – updating an incorrect foreign key to the same value does not raise error (verified, 2008)
• #44207 – LEFT JOIN Multi-table DELETE fails to delete records when using foreign keys (verified, 2009)

Crashes and corruption

This class of problem is slightly less important, because there is not a risk of a permanent or impossible to fix inconsistency. However, these bugs cause outages when you hit them.

• #80821 – Replication breaks if multi-table DELETE is used in conjunction with Foreign Key (verified, 2016)
• 85605 – Corrupted index for ON DELETE CASCADE with self-referential ON DELETE SET NULL (verified, 2017)
• 86774 – DELETE CASCADE on record with another DELETE SET NULL results in corrrupt tables (verified, 2017)

Wrong behavior

• #10992 – MyISAM foreign key definitions create indexes (verified, 2005)
• #38585 – Foreign key constraint for column in same table fails on delete when same row (verified, 2008, but also a documented limitation)
• #39495 – Incorrect result of “show create table” with constraints (verified, 2008)
• #45290 – Cant convert innodb tables from latin1 to utf8 if there is a varchar foreign key (verified, 2009)

Limitations

Foreign key limitations are documented . But the page dedicated to limitations seems to assume that we already know some of them from other documentation pages. I’ll try to maintain a more complete list, unless this particular section of the great documentation is fixed.

• Foreign keys are implemented at storage engine level. To my knowledge, only InnoDB supports foreign keys.
• Even if other storage engines will support foreign keys at some point, it will not be possible to “link” tables that use different storage engines. We saw this in the past, with the PBXT storage engine, now discontinued.
• SET DEFAULT is treated by InnoDB as an alias for RESTRICT .
  • Historical curiosity: it worked as expected in PBXT. This wasn’t documented: maybe the authors never noticed that it didn’t work in InnoDB?
• The MATCH clause has no effect.
• Integrity constraints, including foreign keys, are always checked row by row in MySQL. Other DBMSs validate constraints at the end of a statement by default, and the user can defer them to the end of the current transaction.
• Errors that appear when trying to create a foreign key in a wrong way are obscure and frustrating before MySQL 8 and in MariaDB.
• MySQL and MariaDB allow to create identical foreign keys with different names. I saw duplicate foreign keys in some production databases – so draw your own conclusions about the correctness of this behavior, but in any case, be careful when you create foreign keys..
• When MySQL or MariaDB implement some cool new feature that you’d like to use, don’t assume that it can be used in conjunction with foreign keys. Test extensively and report bugs, so other users will know about those problems.
• From the docs:

If there are several rows in the parent table that have the same referenced key value, InnoDB acts in foreign key checks as if the other parent rows with the same key value do not exist. For example, if you have defined a RESTRICT type constraint, and there is a child row with several parent rows, InnoDB does not permit the deletion of any of those parent rows.
InnoDB performs cascading operations through a depth-first algorithm, based on records in the indexes corresponding to the foreign key constraints. 

• From the docs:

If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the same cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep. 

• From the docs:

In an SQL statement that inserts, deletes, or updates many rows, foreign key constraints (like unique constraints) are checked row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records that it must examine. MySQL checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. This means that it is not possible to delete a row that refers to itself using a foreign key.