MySQL Bugs: #114838: 8.4.0 requires unique constraint for FK

Bug #114838	8.4.0 requires unique constraint for FK
Submitted:	1 May 13:48	Modified:	19 Aug 12:10
Reporter:	Josh Coats	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	8.4.0	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

Description:
Starting today 2024-05-01(or maybe last night), I started getting the following error during our CI build process. This specific migration has been working since sometime in 2022. I noticed that 8.0.37 was released on 2024-04-30 so I tried specifying 8.0.36 on my GitHub Action. When I did that, the error goes away and things work as expected.

  SQLSTATE[HY000]: General error: 6125 Failed to add the foreign key constrai  
  nt. Missing unique key for constraint 'educational_programs_cip_id_foreign'  
   in the referenced table 'cip_soc'  

How to repeat:
Create table A that has an FK to table B where the table B does not have a unique constraint. This seems to only happen on 8.0.37.

I believe this bug is specific to MySQL 8.4.0, rather than 8.0.37. @Josh Coats, perhaps your previous CI config specified "mysql:8" or "latest", so you were actually getting 8.4.0? Both came out the same day (Apr 30).

On 8.4.0 this bug reproduces consistently, on any attempt to add a foreign key where the parent-side referenced columns are only covered by a non-unique key. I can't repro it on any other release though. Also note that error number 6125 (ER_FK_NO_UNIQUE_INDEX_PARENT) is described in the manual for 8.4 but not 8.0, so I assume this is a new error number in 8.4.

Nothing in the release notes for 8.4.0 suggest that this is an intentional change. So personally I do agree it is a critical bug, as this is a substantial behavior change from how FKs have worked previously in MySQL/InnoDB.

Looking back, I see that I am just declaring `mysql:8` on my GitHub Action. So it probably got the newest version. When I went to look for recent MySQL updates, I saw 8.0.37 and assumed that was it. If 8.4.0 came out at the same time, I'm guessing that is what my GitHub Action installed.

I just ran some more tests. When I use 8.0.37, everything works as expected. When I use 8.4.0, I get the error that I experienced before.

Sorry for the incorrect reporting. Thank you for testing on other versions and helping figure it out.

Updating the version and synopsis to have the correct version

Awesome, that makes sense, thanks!

I should have mentioned in my earlier comment how/why I found this -- basically similar situation to you, my test suite failed with that same foreign key error number when I tried running it against 8.4.0, but 8.0.37 was fine. (My product is a schema management tool with deep MySQL integration, and I run a large suite of DDL-heavy tests against each new MySQL release just to ensure everything is still compatible.)

So after seeing the CI failure, I went searching on here, and so far your bug report is the only match for "Missing unique key for constraint" :) Thank you for reporting this so fast, it saved me a lot of investigation time to see it wasn't just me / something wrong with my code.

Interestingly, most other relational DBs do actually require a unique constraint for FKs. InnoDB has previously always allowed non-unique indexes for this though. Some more info at https://dev.mysql.com/doc/mysql-reslimits-excerpt/8.0/en/ansi-diff-foreign-keys.html , see the 5th bullet. Hopefully the MySQL team can provide more clarity on whether this is intentionally changing in 8.4+.

Hello Josh Coats,

Thank you for the report and feedback.

regards,
Umesh

Bug #114882 marked as duplicate of this one

There is a seemingly undocumented variable introduced in MySQL 8.4 "RESTRICT_FK_ON_NON_STANDARD_KEY" which is enabled by default. 

See https://dev.mysql.com/doc/dev/mysql-server/latest/namespaceanonymous__namespace_02sys__var....

"Disallow the creation of foreign keys referencing non-unique key"

Wow, excellent find. Strangely, not only is that new variable undocumented, but it is also deprecated. If you disable it, a warning is issued:

mysql> SET restrict_fk_on_non_standard_key = OFF;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 4166
Message: 'restrict_fk_on_non_standard_key' is deprecated and will be removed in a future release. Foreign key referring to non-unique or partial keys is unsafe and may break replication.
1 row in set (0.00 sec)

Nice catch! Since this seems to be by design[1] can this behavior/parameter be documented? Also, since its a breaking change in defaults, should a deprecation warning for "creation of foreign keys that reference non-unique key and partial fields of composite keys" also be thrown on 80 releases so there is some warning ahead of upgrade? (default 80 behavior remains the same but warning issued)

[1] https://github.com/mysql/mysql-server/commit/3a3a0794a712f6adc772146be7c2fefaeed241bf

There are also two new status variables, could these be documented too?
Deprecated_use_fk_on_non_standard_key_count
Deprecated_use_fk_on_non_standard_key_last_timestamp

mysql [localhost:8400] {msandbox} (d1) > set restrict_fk_on_non_standard_key=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql [localhost:8400] {msandbox} (d1) > CREATE TABLE d1.t2 (num int, KEY(num), FOREIGN KEY (num) REFERENCES d1.t1(num));
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql [localhost:8400] {msandbox} (d1) > show global status like 'Deprecated_use_fk%';
+------------------------------------------------------+------------------+
| Variable_name                                        | Value            |
+------------------------------------------------------+------------------+
| Deprecated_use_fk_on_non_standard_key_count          | 1                |
| Deprecated_use_fk_on_non_standard_key_last_timestamp | 1715139686536024 |
+------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

Thanks!
Marc

https://dev.mysql.com/doc/refman/8.4/en/server-status-variables.html
https://dev.mysql.com/doc/refman/8.4/en/mysql-nutshell.html

Since this is seems to have been an intentional change but is verified: does this confirm its unexpected behavior OR should it be moved to "Category" documentation to have the docs updated on this breaking change during MVU?

Thanks,
Marc

So what's the plan for this? This is a breaking change without documentation, deprecation, or notification. Will this just stay as it is or will this change be reverted?

They have updated their documentation to include this variable.

https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_restrict_fk_on...

"""
Using non-unique or partial keys as foreign keys in a CREATE TABLE or ALTER TABLE statement is deprecated, and you should expect support for it to be removed in a future version of MySQL
"""

Looks like the 8.4.0 release notes were updated as well. The update there is good -- it references https://bugs.mysql.com/bug.php?id=97836 which provides a LOT more clarity into the motivation for this change. Seems non-unique indexes on the parent side of an FK can result in replication breakage when using multi-threaded applier with group replication.

Given that context, my assumption is this change will remain as-is in 8.4.1+, and this was all just an accidental omission from the docs and release notes.

Why would you reference a foreign key to a non-primary key?
I think it is a bad database design and foreign keys should always reference primary keys.
I stumpled upon the same issue and the application developer(s) will have to fix the issue.

Tobias Stock: doesn't matter if it's right or wrong. The point is that it was an unannounced breaking change.