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

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.

Wrong behavior

Private bugs

Oracle makes many bugs private short after they’re reported. In that case, no details about the bug are available. While in some cases some non-Oracle resources post a link to the bug with its title, it is impossible to see the test cases, comments, and the bug status. Though it is possible to find references to private bugs related to foreign keys, it would be useless to post them here.

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 (#3558, MDEV-10393).
    • 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 generated 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.
  • Partitioned tables cannot have foreign keys, and cannot be referenced by foreign keys. Plans to fix this in MariaDB: MDEV-12483
    • …or at least to allow foreign keys in the current partition of a system-versioned table: MDEV-19191.
  • 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.

Kudos

You may contribute this page by writing comments in this page. But some people may prefer to use social networks or emails, so I feel the necessity to thank them here.

  • Georgi D. Sotirov reminded me on Twitter that partitioned tables do not support foreign keys.

Comments (3)

  1. For older bugs that are still “Verified” (and should have a clear test case) I’d suggest to check them on recent MySQL 8.0.18 and whatever MariaDB you prefer (10.4.x) and specify if recent versions are still affected. It’s probably the case, but additional verification would add more value for the post.

    1. Hi VK (are you you?)
      I’ve done this for some bugs – not all of them TBH. When I could reproduce the bug on 8.0, the bug was added to this list. When I couldn’t, I’ve added a comment (which received no answer) to ask if the bug was fixed, because it’s still possible that the bug is there but I’m missing something. Those bugs are not in this list, even if I know they could be in MySQL 5.7, 5.6 or MariaDB.
      I didn’t test any MariaDB version.

Leave a Reply

Your email address will not be published. Required fields are marked *