Skip to content(if available)orjump to list(if available)

The problem with MySQL foreign key constraints in Online Schema Changes (2021)

bearjaws

> Isn’t there some Instant DDL?

Run into this way too many times at my last few jobs. MySQL has some serious land mines built in, which result in many companies just adopting the policy of never upgrading the schema during business hours.

You are basically required to implement pt-online-schema-change in order to reliabily do basic changes.

javier2

Doesnt Postgres have the same issue?

aeyes

You can create Foreign Keys as NOT VALID and then VALIDATE the constraint later. This only takes a RowShareLock.

Dropping the constraints takes an AccessExclusiveLock for a very short time in both tables so yes, that would interrupt traffic but the online migration tools also do this to switch the tables so I give it a pass.