MySQL Bugs: #114882: Unexpected incompatibility in InnoDB Foreign Key constraint with Error 6125

Bug #114882	Unexpected incompatibility in InnoDB Foreign Key constraint with Error 6125
Submitted:	5 May 8:00	Modified:	7 May 4:45
Reporter:	Tsubasa Tanaka (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.4.0	OS:	CentOS (7.9)
Assigned to:		CPU Architecture:	x86
Tags:	ER_FK_NO_UNIQUE_INDEX_PARENT, Failed to add the foreign key constraint, Missing unique key

Description:
I can't create table with FOREIGN KEY which parent-table doesn't have UNIQUE/PRIMARY KEY, in MySQL 8.4.0 by Error 6125 (ER_FK_NO_UNIQUE_INDEX_PARENT),

This limitation is not in MySQL 8.0.37, but no release-note and what-is-new refer it.

Is it "by-design"? or bug?

If "by-design", this is incompatible between 8.0 and 8.4, this have to be described in what-is-new and have to be implemented in MySQL Shell util.checkForServerUpgrade()

https://dev.mysql.com/doc/mysql-shell/8.4/en/mysql-shell-utilities-upgrade.html

How to repeat:
## MySQL 8.4.0

mysql -vve "SELECT @@version; DROP DATABASE IF EXISTS d1;CREATE DATABASE d1; CREATE TABLE d1.t1 (num int, KEY(num)); CREATE TABLE d1.t2 (num int, KEY(num), FOREIGN KEY (num) REFERENCES t1(num)); DROP DATABASE d1;"
--------------
SELECT @@version
--------------

+-----------+
| @@version |
+-----------+
| 8.4.0     |
+-----------+
1 row in set (0.00 sec)

--------------
DROP DATABASE IF EXISTS d1
--------------

Query OK, 1 row affected (0.01 sec)

--------------
CREATE DATABASE d1
--------------

Query OK, 1 row affected (0.01 sec)

--------------
CREATE TABLE d1.t1 (num int, KEY(num))
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
CREATE TABLE d1.t2 (num int, KEY(num), FOREIGN KEY (num) REFERENCES t1(num))
--------------

ERROR 6125 (HY000) at line 1: Failed to add the foreign key constraint. Missing unique key for constraint 't2_ibfk_1' in the referenced table 't1'
Bye

## MySQL 8.0.37

$ mysql -vve "SELECT @@version; DROP DATABASE IF EXISTS d1;CREATE DATABASE d1; CREATE TABLE d1.t1 (num int, KEY(num)); CREATE TABLE d1.t2 (num int, KEY(num), FOREIGN KEY (num) REFERENCES t1(num)); DROP DATABASE d1;"
--------------
SELECT @@version
--------------

+-----------+
| @@version |
+-----------+
| 8.0.37    |
+-----------+
1 row in set (0.00 sec)

--------------
DROP DATABASE IF EXISTS d1
--------------

Query OK, 2 rows affected (0.03 sec)

--------------
CREATE DATABASE d1
--------------

Query OK, 1 row affected (0.00 sec)

--------------
CREATE TABLE d1.t1 (num int, KEY(num))
--------------

Query OK, 0 rows affected (0.02 sec)

--------------
CREATE TABLE d1.t2 (num int, KEY(num), FOREIGN KEY (num) REFERENCES t1(num))
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
DROP DATABASE d1
--------------

Query OK, 2 rows affected (0.01 sec)

Bye

I have the same problem with docker mysql:latest. At first I thought it was caused by docker or something else, and google it can't get result. Finally find problem here.

I also test for 8.0 - 8.3 by docker, they don't have this problem.

It happens also on Windows, I cannot restore a dump created with MySQL 8.0.

Hello tanaka-San,

Thank you for the report and feedback.
If you have no objections then will mark this as a duplicate of Bug #114838( even though Bug #114882 has a test case but since same issue was reported earlier here Bug #114838). Thank you.

regards,
Umesh