Pointless Whining about Bad Dates

[James Cobban]

After upgrading MySQL the enhanced validation of DATEs has been causing me problems. Web pages that have been working for years suddenly throw exceptions because the current release of MySQL is picky. The problem is that many of the dates in my database are imprecise. That is I do not *know* the exact date of the event in question. If I only know the year that something happened why does the server whine when I do not supply a month and day? I literally do not have values to put in those portions of the date. Since the only purpose of the instances of DATE in my database was for sorting I decided to bypass the problem by replacing the instances of DATE with an INT field. But:

mysql> alter table Deaths modify column d_calcdate INT(10); ERROR 1292 (22007): Incorrect date value: '1824-00-00' for column 'D_CalcBirth' at row 1

Hunh? My command did not say ANYTHING about D_CalcBirth so why did MySQL waste my time by complaining about something that was completely irrelevant to the action which I requested?

So I fixed that by:

mysql> update Deaths set d_calcdate=makedate(year(d_calcdate),182) where month(d_calcdate) = 0;
Query OK, 3649 rows affected (0.22 sec)
Rows matched: 3649 Changed: 3649 Warnings: 0
mysql> update Deaths set d_calcbirth=makedate(year(d_calcbirth),182) where month(d_calcbirth) = 0;
Query OK, 3687 rows affected (0.21 sec)
Rows matched: 3687 Changed: 3687 Warnings: 0
mysql> alter table Deaths modify column d_calcdate INT(10); ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'D_ChangeDate' at row 10150

Again I did not ask the server to look at D_ChangeDate so why is it wasting my time with a pointless complaint about something that is irrelevant to what I did ask the server to do?

So I tried to fix the "incorrect" values in the database by:

mysql> update Deaths set D_ChangeDate=null where D_ChangeDate='0000-00-00';
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'D_ChangeDate' at row 1

I am not asking the server to INSERT an invalid date value into the database so why does it object when I ask it to look for date values THAT ARE ALREADY IN THE DATABASE!

So I got around that by:

mysql> update Deaths set D_ChangeDate=null where Month(D_ChangeDate)=0;
Query OK, 40 rows affected (0.12 sec)
Rows matched: 40 Changed: 40 Warnings: 0

mysql> alter table Deaths modify column d_calcdate INT(10);
Query OK, 27317 rows affected (2.62 sec)
Records: 27317 Duplicates: 0 Warnings: 0

mysql> alter table Deaths modify column d_calcbirth INT(10);
Query OK, 27317 rows affected (2.91 sec)
Records: 27317 Duplicates: 0 Warnings: 0

It is annoying that MySQL complains about date values that are *already* in the database because an earlier release did not validate them. If a value is already in the database, even if the current release would not PUT that value into the record it should not force the programmer to deal with pointless whining.

This pointless unproductive whining by your product encourages me to migrate to MariaDB which does NOT waste me time!