Enable warnings in MySQL

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!