Problem description

An SQL statement that can be successfully executed with a warning fails to be executed in a procedure, and an error is reported.

Example:

The UPDATE statement is executed to change values in the column of the date attribute to empty strings:

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

The statement is successfully executed and a warning is reported:

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

The statement fails to be executed in a procedure and an error is reported:

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

Causes

When you create a procedure, MySQL records the value of the sql_mode variable and stores the value in the mysql.proc system table. When the procedure is running, it uses the value of the sql_mode variable that is specified when you create the procedure rather than the value in the runtime. If the former value has more limits than the latter value, the error is reported.

For example, the value of the sql_mode variable that is specified when you create the procedure is 'STRICT_TRANS_TABLES', and the value is '' in the runtime. The value 'STRICT_TRANS_TABLES' has more limits than the value ''. As a result, this error occurs.

Solutions

You can use one of the following methods to resolve the error:

• Configure the sql_mode variable based on your business requirements and create the procedure again.

• Modify the value of the sql_mode variable that corresponds to the procedure in the mysql.proc system table based on your business requirements.

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