By default my MySQL setup didn’t display warnings. So, when this morning I tried to update a record with a slightly invalid date value, I had the following result:
mysql> UPDATE people SET birth =”0000-09-30″ WHERE id=1234;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 1
One warning, zero rows changed! And no clue what went wrong!
OK, time to RTFM! By reading the man page (man mysql), I found that I can enable the warnings on starting the mysql client by issuing: “mysql –show-warnings dbname” or by adding “show-warnings” in the “[mysql]” section of ~/.my.cnf configuration file. Alternatively, without changing anything, I could see the warning by executing: “show warnings;” at the console prompt.
The warning was:
Warning (Code 1265): Data truncated for column ‘birth’ at row 1
which revealed nothing to me. By changing the “sql_mode” parameter to traditional
mysql> set sql_mode=”traditional”;
Query OK, 0 rows affected (0.06 sec)
I had finally a clue:
ERROR 1292 (22007): Incorrect date value: ‘0000-09-30’ for column ‘birth’ at row 1
So, I changed again the sql_mode parameter to ALLOW_INVALID_DATES and I solved my little problem!
Visit The Light of the LAMP blog for more…