Fun MySQL fact of the day: IGNORE at your own peril

2019-03-15 11:11UTC

Yesterday, we saw how INSERT ... ON DUPLICATE KEY UPDATE can unknowingly result in not-completely-expected situations. To get around that, you may have thought to use INSERT IGNORE, instead. Often, INSERT IGNORE is used when attempting to insert a row that may already exist. The underlying expectation a developer often has is that INSERT IGNORE means "do nothing if the row already exists, otherwise, insert the row". And, well, INSERT IGNORE definitely does that ... and more.

In MySQL, INSERT IGNORE really means "treat errors as warnings" (did you know you can SHOW WARNINGS?). Uh oh.

Right. Let's say you have a table with a phrase VARCHAR(10) column and you use INSERT IGNORE to insert a phrase like 'this is a really long message'. You may expect to get an error like Data truncated for column 'phrase' at row 1. But you won't. Instead, MySQL will truncate the value to 10 characters, insert the row, and pretend everything is fine.

And this isn't just String types: it will figure out the closest approximation of a valid value on your behalf. So, 2019-03-15 24:00:00? Didn't you mean 0000-00-00 00:00:00? A TINYINT value of 128? I think you mean 127. And the fun doesn't stop there! You can UPDATE IGNORE, DELETE IGNORE, and even CREATE TABLE ... SELECT IGNORE.

My advice? Same as yesterday: instead of using INSERT IGNORE, do a SELECT (preferably on a replica) and INSERT or UPDATE by primary key accordingly, safely handling the rare duplicate key error in your code.