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

Enabling Transparent Data Encryption (TDE) in Oracle RAC on ODA with Data Guard – The Correct and Secure Approach

-
Image
Transparent Data Encryption (TDE) is a critical security feature in Oracle Database that protects sensitive data at rest by encrypting datafiles, tablespaces, or specific columns. In modern enterprise environments especially Oracle RAC deployments on Oracle Database Appliance (ODA) with Data Guard TDE implementation must be done carefully to avoid service disruption, Data Guard lag, or wallet inconsistencies. This article walks through the correct, production-safe method to enable TDE with Auto-Login Wallet while ensuring: ·                No exposure of sensitive credentials ·                Consistency across RAC nodes ·                Seamless synchronization with the physical standby   ⚠️ Important: This guide assumes Oracle Database 19c on ODA, RAC Primary, ...
Read more

[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