My Journey Upgrading MySQL 5.7 to 8.4 – and the Foreign Key Surprise

-

I recently had a project where I needed to upgrade a database from MySQL 5.7 to MySQL 8.4.

As with most MySQL upgrades, I had two options in front of me:

  • Do an in-place upgrade, swapping the binaries and running mysql_upgrade.

  • Go with a logical upgrade, using mysqldump to export everything and then import it into the new version.

I decided to take the logical path. It felt safer for this use case, since it gives you a fresh environment and avoids any messy leftovers from the old version.

So far, so good. I dumped my 5.7 database and began the import into MySQL 8.4. That’s when things went sideways.

The Error That Stopped Everything

The import was moving along nicely until I hit this:

ERROR 6125 (HY000): Failed to add the foreign key constraint. 
Missing unique key for constraint 'my_foriegn_FK1' in the referenced table 'my_parent_table'.

My first thought was: “Wait… what? This schema has been running in production for years without a problem. Why would it suddenly break now?”

I double-checked the parent table, indexes, and constraints. Everything looked fine. Still, MySQL 8.4 refused to accept it.

Digging Deeper

After some research and community input, I realized this wasn’t my fault at all.

Turns out, this only happens on MySQL 8.4.0. If you try the same dump on 8.0.37, it works without issues.

  • On 8.4.0, any foreign key that references columns without a unique key will fail.

  • The new error code 6125 (ER_FK_NO_UNIQUE_INDEX_PARENT) even shows up only in the 8.4 documentation.

  • It’s not a schema design change I made – it’s a behavior change/bug in MySQL 8.4.0.

Funny thing: if you’re using Docker or CI and pulling mysql:8 or mysql:latest, you may have suddenly ended up on 8.4.0 without realizing it. That’s exactly what happened to me.

The Unexpected Fix

After some trial and error, I stumbled across a solution. And honestly, I didn’t expect it to work at first.

I ran:

SET restrict_fk_on_non_standard_key = OFF;

Boom 💥 — the dump imported cleanly!

The strange part?

  • This system variable isn’t even properly documented.

  • It’s already deprecated, and disabling it throws a warning.

  • But with it turned off, MySQL happily created all the foreign keys that 5.7 had no issue with.

Once the dump finished, I ran application tests — and everything worked fine. Crisis averted.

Looking Back

This upgrade reminded me of a few important things:

  • Never assume “latest” is safe. Always check the exact MySQL version you’re pulling.

  • Newer versions sometimes introduce stricter checks that weren’t enforced before.

  • Quick fixes (like disabling a variable) can save you in the moment, but long term, it’s worth redesigning schema constraints to match modern standards.

In the end, I got the upgrade done, the application tested out fine, and I walked away with a good story to tell — and a new respect for reading release notes carefully.

✨ Have you ever hit a strange version-specific MySQL bug during an upgrade? I’d love to hear your stories in the comments.

Comments

Post a Comment

Popular posts from this blog

[FATAL] [DBT-10503] Template file is not specified

-
I faced an error [FATAL] [DBT-10503] Template file is not specified and terminated operation when I was going to create 12c instance in silent mode .   ERROR: [FATAL] [DBT-10503] Template file is not specified.   REASON: This is due to the you are not set the “templateName” in response file.   SOLUTION: Set templateName=General_Purpose.dbc and execute the dbca again   ACTION: 1.        Open the dbca.rsp and set templateName as General_Purpose.dbc 2.        Save the dbca.rsp 3.        Execute the instance create statement again (dbca)
Read more

Resolving ORA-10635 and ORA-39171 Errors During BLOB Tablespace Maintenance

-
Recently, I came across an interesting situation while performing a space management task on one of our databases. The challenge involved a table that contained BLOB data distributed across multiple tablespaces . What seemed like a simple shrink operation ended up turning into a deeper learning experience that I’d like to share. 🧩 Background In this environment, the main table BINARY_ARCHIVE was stored in the MAIN_DATA tablespace. Its BLOB segment was placed separately in the ATTACHMENTS_DATA tablespace to improve performance and manage large object storage efficiently. Additionally, another table called BINARY_OBJECT_LINK maintained a foreign key reference to the BLOB data and resided in the LINK_DATA tablespace. When I attempted to reclaim space from the BLOB segment using the SHRINK SPACE command, Oracle returned the following error. ⚠️ The Error ORA -10635 : Invalid segment or tablespace type 10635. 00000 - " Invalid segment or tablespace type " *Ca...
Read more

How to resolve RMAN-06035: wrong version of recover.bsq, expecting 11.2.0.4, found 11.2.0.2

-
In my case there are two oracle homes. so when I schedule a cron job for archive clearing following error was raised. Recovery Manager: Release 11.2.0.4.0 - Production on Wed May 17 13:41:49 2023 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed RMAN-03000: recovery manager compiler component initialization failed RMAN-06035: wrong version of recover.bsq, expecting 11.2.0.4, found 11.2.0.2 SOLUTION: I have added following line to the oraenv file.  export PATH=$ORACLE_HOME/bin:$PATH
Read more