Marko Mäkelä added a comment - 2018-02-06 12:35 oschonrock , I believe that the MySQL 5.7.2 change may have introduced this corruption, but nobody noticed until now. For what it is worth, if you look at the commit, it is changing the file mysql-test/suite/innodb/r/innodb-index-online-fk.result . This result change looks acceptable to me, as there are multiple FOREIGN KEY constraints between the tables, and the order of evaluating SET NULL and CASCADE is affected based on the code change.

Oliver Schonrock added a comment - 2018-02-06 12:55 - edited marko , you're right. Notwithstanding my limited understanding of that code, that does indeed look like it might have been the trigger for the cascade delete break. Wow, so that means potentially many users since GA of MySQL 5.7 and MariaDB 10.2, who are using such FK structures have these "failed cascade delete" orphan records lying around. Perhaps what we are doing is more unusual than I thought, or perhaps it is down to "not being noticed". As stated in the original report, finding the "orphaned" record is not obvious. By FK doesn't work: MariaDB [test]> select * from payment_method; + ----+-----------+-----------------------+ | id | member_id | cardholder_address_id | + ----+-----------+-----------------------+ | 3 | 1 | NULL | + ----+-----------+-----------------------+ 1 row in set (0.00 sec)   MariaDB [test]> select * from payment_method where member_id = 1; Empty set (0.00 sec) and a LEFT JOIN won't find it either as shown in original report. As I explained, the only way I found it, is by mysqldump followed by inserting that dump and then running the LEFT JOIN . If this gets fixed by merging the upstream fix/revert to stack based recursion for FK cascade, would it make sense to alert MariaDB users (and MySQL?) that they may have these orphaned records silently lying around in their DB? BTW: I also double confirmed that MySQL 5.7.20 exhibits the broken behaviour (while 5.7.21 does not). Thanks for your help.

Oliver Schonrock added a comment - 2018-02-06 13:42 marko For what it's worth, once the broken CASCADE DELETE has occurred, a server restart does not restore sanity. -- server restart performed here   Server version: 10.2.12-MariaDB FreeBSD Ports   MariaDB [test]> select * from payment_method where member_id = 1; Empty set (0.00 sec)   MariaDB [test]> select * from payment_method; + ----+-----------+-----------------------+ | id | member_id | cardholder_address_id | + ----+-----------+-----------------------+ | 3 | 1 | NULL | + ----+-----------+-----------------------+ 1 row in set (0.00 sec)   MariaDB [test]> select * from member; Empty set (0.00 sec)

Oliver Schonrock added a comment - 2018-02-06 14:16 marko I had a quick try to see what other methods might detect the problem, so users can see if they have been affected. And then what options exist to fix broken DBs without dump/reload. CHECK TABLE reports the problem: MariaDB [test]> check table payment_method; + ---------------------+-------+----------+----------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | + ---------------------+-------+----------+----------------------------------------------------------------------+ | test.payment_method | check | Warning | InnoDB: Index 'payment_method_FI_1' contains 0 entries, should be 1. | | test.payment_method | check | error | Corrupt | + ---------------------+-------+----------+----------------------------------------------------------------------+ 2 rows in set (0.00 sec) and obviously, so does mysqlcheck $ mysqlcheck -uroot -p test test .address OK test .member OK test .payment_method Warning : InnoDB: Index payment_method_FI_1 is marked as corrupted error : Corrupt For InnoDB, the only suggested option for repair is: If you need to rebuild an InnoDB table because a CHECK TABLE operation indicates that a table upgrade is required, use mysqldump to create a dump file and mysql to reload the file. What I did on our production DB (not wanting to take it down, dump & insert) is a I manually deleted the orphaned record by its PK (which is hard to know in a real dataset). Having done Some trial and error shows that this manual DELETE is easier if you do a "null alteration" first (which makes CHECK TABLE happy), then run the LEFT JOIN, which now works, and then you can easily clean up the orphans.   MariaDB [test]> check table payment_method; + ---------------------+-------+----------+----------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | + ---------------------+-------+----------+----------------------------------------------------------------------+ | test.payment_method | check | Warning | InnoDB: Index 'payment_method_FI_1' contains 0 entries, should be 1. | | test.payment_method | check | error | Corrupt | + ---------------------+-------+----------+----------------------------------------------------------------------+ 2 rows in set (0.01 sec)   -- "null alteration"   MariaDB [test]> ALTER TABLE payment_method ENGINE = InnoDB; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0   MariaDB [test]> check table payment_method; + ---------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | + ---------------------+-------+----------+----------+ | test.payment_method | check | status | OK | + ---------------------+-------+----------+----------+ 1 row in set (0.00 sec)   -- CHECK TABLE is happy, now we can use LEFT JOIN to find orphans   SELECT payment_method.id, member_id FROM payment_method LEFT JOIN member ON member.id=payment_method.member_id WHERE payment_method.member_id IS NOT NULL AND member.id IS NULL ; + ----+-----------+ | id | member_id | + ----+-----------+ | 3 | 1 | + ----+-----------+ 1 row in set (0.00 sec)   -- and delete them   MariaDB [test]> delete from payment_method where id = 3; Query OK, 1 row affected (0.02 sec)

Oliver Schonrock added a comment - 2018-02-23 22:13 marko Thank for fixing this so quickly. We have now rolled this out on production via 10.2.13. Works great! By the time we rolled it out we had dozens of broken FKs on our production DB which we cleaned up by technique I described above. I still find it astonishing that no-one since 10.2.4 (GA since > 2 years!) had come across this. I suspect there must be quite a few users out there with broken FKs without realising it (since the broken LEFT JOIN hides them).