Bug #114882 Unexpected incompatibility in InnoDB Foreign Key constraint with Error 6125

Submitted: 5 May 2024 8:00 Modified: 7 May 2024 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

[5 May 2024 8:00] Tsubasa Tanaka
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
[6 May 2024 5:56] liu rundong
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.
[6 May 2024 10:20] Sinhuè Angelo Rossi
It happens also on Windows, I cannot restore a dump created with MySQL 8.0.
[7 May 2024 4:44] MySQL Verification Team
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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.