web analytics

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…

Enable warnings in MySQL

2 thoughts on “Enable warnings in MySQL

  • 2010-05-17 at 18:17
    Permalink

    Thanks, this was really helpful. The show warnings command doesnt quite help when I am running a big SQL script.

  • 2011-09-26 at 12:17
    Permalink

    To anonymous above. although an old post I thought it helpful to highlight that you can output the results:

    source /path/to/whatever/big.sql > /path/to/wherever/name.txt

    This will create a log of the query results.

Comments are closed.

Buy me a coffee

%d bloggers like this: