MySQL Bugs: #91952: Upgrade fail: Failed to add the foreign key constraint. Missing index for constr

Bug #91952	Upgrade fail: Failed to add the foreign key constraint. Missing index for constr
Submitted:	9 Aug 2018 12:42	Modified:	12 Apr 2019 18:34
Reporter:	Rahul Agarkar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.13	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In place upgrade from 5.7 to 8.0.13 fails with the following error:

2018-08-09T12:02:42.894900Z 2 [ERROR] [MY-013140] [Server] Failed to add the foreign key constraint. Missing index for constraint 'pc' in the referenced table 't1196'
2018-08-09T12:02:43.892388Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2018-08-09T12:02:43.892425Z 0 [ERROR] [MY-010119] [Server] Aborting

The issue seems to be caused because unlike 5.7, 8.0 does not consider the hidden part of the secondary key while looking for the index in the parent table corresponding to the foreign key.

How to repeat:
1. Start 5.7
2. Import the test schema: mysql -uroot --force < repro.sql
3. Shutdown the server
4. Start the server with 8.0.13 binary and point to this datadir
5. Check for failure with error message:
"ERROR 1822 (HY000) at line 62: Failed to add the foreign key constraint. Missing index for constraint 'pc' in the referenced table 't1196'"

Posted by developer:
 
This issue boils down to the fact that foreign keys like below are supported in 5.7:

CREATE TABLE parent (a INT PRIMARY KEY, b INT, KEY(b));
CREATE TABLE child (i INT, j INT, FOREIGN KEY (i, j) REFERENCES parent (b, a));

but not in 8.0.

Code in 8.0 doesn't take into account hidden part of secondary key when determining
if it can serve as parent key.

Taking into account that:

1) This is a corner case which is not likely to be met in any real world scenario
(even non-unique parent keys are non-standard and parent keys like above should
be extremely rare).

2) Workaround exists, for the above case you just need to create KEY(b, a) instead
of KEY(b). It will be usable by FK as parent key and will be internally the same
key as KEY(b) (except that 'a' part won't be hidden).

3) Fixing this issue is non-trivial and will increase complexity of some of our code.

We decided to close this bug as "Won't fix", after discussion. At least until we will
hear about any real-world scenario in which it causes problem to our users.

Posted by developer:
 
Fixed in 8.0.17.

MySQL 5.7 supported foreign keys on InnoDB tables with a parent key
for which part of the referenced columns were hidden, but MySQL 8.0
did not. MySQL 8.0 now supports this capability.