MySQL 5.6+ in a tale of Pirates

Fact is that this little tale bothered me so much that I couldn’t stop until I could find a solution for it and it was a great idea from a co-worker of mine that in the end solved the issue.

First things first, consider a database that has 2 tables that relates to each other and this database was migrated from MySQL 5.1 to MySQL 5.6, like this database here, for example:

https://gist.github.com/mcloide/78d3b695364f0f18d813

So far, regardless of how bad the data is, the database would be migrated without an issue and that is mainly because the initial sql_mode is set to

You see the sql_mode and decide that is just not good enough and change it to something a bit more structured, similar to MySQL 5.7:

Here is where the tale of the pirates start. If you check the table structure that is on the example database we have table piratees that has a relationship with the table piratee_login that will have 0 or many rows referencing the table piratees. So far a classic example of a person to person_login table set.

The trouble with these 2 tables is that both carries legacy data.

So Captain Jack Sparrow has been 2 times on the Black Pearl and in one of the times the column for ship_in is set with ‘0000–00–00 00:00:00’

When you run a select, this data wont be much of a problem, but when you run an update, even when you aren’t updating that column at all, this data will be an issue (same for any kind of write).

Note that it requires a sub-select as join because of the use of MIN

Running that query will return this, correctly:

Now the same, if you simply adjust the query to update the piratees start_date as the 1st ship_in that has been recorded there is when some things get’s in stranger tides.

When you execute this query you will get:

ERROR 1292 (22007): Incorrect date value: ‘0000–00–00’ for column ‘start_date’ at row 1

That doesn’t happens because the sub-select doesn’t return rows, or even because the query is malformed. It simply happens because during an write MySQL 5.6+ using a more strict sql_mode will consider the conditions on the where clause that are checking for that specific value of 0 in the columns for filtering as an invalid date and strangely it only happens on writes (haven’t tested with deletes, but my guess goes to it will also happens).

As I have mentioned on the beginning of the article there is a solution for it that is somewhat elegant. Rewrite the update query using epoch date instead of zeros and compare against it.

This will correctly update the column to the right values:

The part of the question that still unresolved is why only on writes that validation kicks in. Honestly I haven’t asked anyone from the MySQL community but it does brings a light over MySQL 5.6 to MySQL 5.7 migration path.

The data should have been validated before migration but you know when dealing with large sums of data finding a good migration path is never simple.

I haven’t found any reason why it only happens on write but, if you know something, please ping me up. I’m really curious of a good explanation on this.