I program in, write about, and present on PHP, Python, Django and scaling websites.

From time to time I get lazy and use REPLACE INTO instead of checking for records and then only inserting the ones that don’t exist. It works really well for importing records where some records might be already in the database. Today, I ran into a little snag due to my laziness.

MySQL uses the following algorithm for
REPLACE (and LOAD DATA ...
REPLACE):

1. Try to insert the new row into the table
2. While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
   1. Delete from the table the conflicting row that has the duplicate key value
   2. Try again to insert the new row into the table

When you combine this with foreign keys that are defined with ON DELETE CASDCADE you end up nuking child records when you REPLACE INTO a parent record. It only took me an hour and a half to debug this insanity.