[HN Gopher] The problem with MySQL foreign key constraints in On...
       ___________________________________________________________________
        
       The problem with MySQL foreign key constraints in Online Schema
       Changes (2021)
        
       Author : prudentpomelo
       Score  : 37 points
       Date   : 2025-02-10 15:32 UTC (2 days ago)
        
 (HTM) web link (code.openark.org)
 (TXT) w3m dump (code.openark.org)
        
       | javier2 wrote:
       | Doesnt Postgres have the same issue?
        
         | aeyes wrote:
         | 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.
        
           | javier2 wrote:
           | wouldnt that take an awful long time when validating the
           | foreign keys again? You cant flip the tables again until all
           | foreign keys are valid.
           | 
           | I am asking out of curiosity, as I have used pt online schema
           | change extensively before, but am right now working mostly
           | with postgres
        
       | bearjaws wrote:
       | > 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.
        
         | evanelias wrote:
         | You're over-stating the issue. You can always simply try ALTER
         | TABLE ... ALGORITHM=INSTANT first, and this will just return an
         | error immediately if the requested alteration doesn't support
         | instant changes. Not a land mine at all.
         | 
         | The article is also four years old, and INSTANT now covers a
         | few additional cases which it didn't at that time.
         | 
         | If your alter doesn't support INSTANT, at that point you can
         | fall back to an online schema change tool, of which there are
         | several battle-tested options. All of the largest MySQL users
         | (including many of the biggest names in tech) do schema changes
         | any time of day without issue by automating these tools. In
         | comparison the equivalent tools for Postgres are far less
         | mature.
        
         | icedchai wrote:
         | I worked at a MySQL shop in the 5.x days. We'd regularly run
         | overnight "alter table" commands that would take forever...
         | some tables had 100's of millions of rows. We'd have to pause
         | any of the writers (batch jobs, etc.) We had a sharded system,
         | across about a half dozen DB servers, and large migrations
         | would need to be done in phases. Painful.
        
       ___________________________________________________________________
       (page generated 2025-02-12 23:01 UTC)