Vous n’avez pas pu assister à notre dernier Mobitalks PHP ? Voici un récap’ (PART II) !

Pour lire le récap' de 1ère partie du meetup, c'est par ici !

"Update your MySQL Schema & a Million Rows Headache-free", par Sylvain Fabre (CTO de AssoConnect)

     

Issues

• Queries crash because the production code relies on a column that does not exist in your database
• INSERT statements crash because the production code is not aware of a new column in your database
• A long running query locks down your database
• There is a downtime when the database schema is not compatible with the code

   

Why is MySQL slow to perform the query?

Several possible causes

• A lot of data to move/go through
• An old version of MySQL
• Uncommon edge cases

   

Prevent Locking

 

ALGORITHM=INPLACE & LOCK=NONE

• ALGORITHM=INPLACE prevents the engine from reorganizing the data
• LOCK=NONE prevents any locking mechanism

Use these clauses to block the execution of the query

• You won’t lock down your table
• The error message will tell you what to do to run the query

   

Schema Update

 

Add a new column

1. Add the new column with a default value set
2. Release a new version of your code to start writing into the column
3. Run any migration script
4. Release a new version of your code to read from & write into the column

 

Drop a column

1. Set a default value to the column
2. Release a new version of the code that won’t use this column
3. Drop the column

 

Rename a column

We want to rename the column from old_name to new_name

• Add a column new_name (don’t forget the default value)
• Release a new version of your code

*setOldName() will call setNewName() *getOldName() is still used *getNewName() is not used

• Copy values from old_name to new_name

       

• Set a default value to the old_name column
• Release a new version of your code

*Replace setOldName() with setNewName() *Replace getOldName() with getNewName() *Update raw SQL queries

• Drop the old column

       

Add a Foreign Key Constraint

• Slow because MySQL blocks writes while it checks each row for invalid values
• Solution

*Disable this check with SET FOREIGN_KEY_CHECKS=0; *Create the foreign key constraint *Check consistency with a SELECT query with a JOIN clause

   

Update a million Rows

 

MySQL procedure

• Batch the rows update only a few of them at the same time
• Ensure your row filtering is efficient
• Run it with the CLI client