SQL statement execution fails in a procedure

Problem description

An SQL statement executes successfully when run directly, possibly with a warning, but fails with an error when it is run in a procedure.

For example:

An UPDATE statement attempts to update a column with the date property to an empty string:

UPDATE t1 SET start_date="" where id=2;

Executing the statement directly succeeds and returns a warning:

+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1265 | Data truncated for column 'start_date' at row 2 |
+---------+------+--------------------------------------------------------+

Executing the statement in a procedure fails and returns an error:

ERROR 1292 (22007): Incorrect date value: '' for column 'start_date' at row 2

Possible causes

When you create a procedure, MySQL records the value of the sql_mode variable and saves it in the mysql.proc system table. When the procedure runs, it uses the sql_mode value from its creation, not the current runtime sql_mode value. This problem occurs if the sql_mode value set at creation is more restrictive than the runtime value.

In the example, the sql_mode value at the time of procedure creation was 'STRICT_TRANS_TABLES', while the runtime sql_mode value was ''. The 'STRICT_TRANS_TABLES' mode is more restrictive than the '' mode, which causes the problem.

Solutions

Choose one of the following solutions:

• Set the sql_mode value to the desired value, and then recreate the procedure.

• Modify the sql_mode value for the existing procedure in the mysql.proc table.

  UPDATE mysql.proc SET sql_mode='xxx' WHERE Procedure='xxx';