pt-archiver Incorrect date value: '0000-00-00' for column

This was a weird one. I had a bug that inserted records with 0000-00-00 for a short while many years ago on an older version of MySQL.

The strange thing here, is you can't actually search for the records with 0000-00-00 since newer versions of MySQL consider this an invalid date, they reject the query.

To find the invalid records use:

SELECT * FROM table WHERE CAST(dateField AS CHAR(10)) = '0000-00-00'

Or just fix the problem in one query:

UPDATE table SET dateField = NULL WHERE CAST(dateField AS CHAR(10)) = '0000-00-00';