| Bug #27524 | Certain warnings appear as errors during UPDATE, cause query to fail. | ||
|---|---|---|---|
| Submitted: | 29 Mar 2007 14:09 | Modified: | 29 Mar 2007 15:40 |
| Reporter: | Alex Hess | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Errors | Severity: | S2 (Serious) |
| Version: | 5.0.37-community-nt | OS: | Windows (Windows 2003 x64) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | cast, errors, UPDATE, warnings | ||
[29 Mar 2007 14:19]
MySQL Verification Team
Thank you for the bug report. Could you please provide the output of: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.37-community-nt MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select @@sql_mode; +----------------------------------------------------------------+ | @@sql_mode | +----------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +----------------------------------------------------------------+ 1 row in set (0.04 sec) mysql> Thanks in advance.
[29 Mar 2007 14:23]
Alex Hess
Certainly: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 38077 Server version: 5.0.37-community-nt MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select @@sql_mode; +----------------------------------------------------------------+ | @@sql_mode | +----------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
[29 Mar 2007 15:40]
MySQL Verification Team
Thank you for the feedback. Please read the Manual about sql_mode.
mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create database db4;
Query OK, 1 row affected (0.01 sec)
mysql> use db4
Database changed
mysql> create table t (col1 date);
Query OK, 0 rows affected (0.13 sec)
mysql> insert into t values (CAST('' as DATE));
ERROR 1292 (22007): Truncated incorrect datetime value: ''
mysql> set sql_mode="";
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values (CAST('' as DATE));
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql>
Description: Certain operations that generate a WARNING during normal use, instead generate an ERROR while being used in an UPDATE statement, causing the query to fail. How to repeat: For example: > SELECT CAST('' AS DATE); > SHOW WARNINGS; Returns: Warning, 1292, Truncated incorrect datetime value: '' However: > UPDATE table SET AFieldThatIsDateTime=CAST('' as DATE) WHERE SomeID='123'; > SHOW WARNINGS; Returns: Error, 1292, Truncated incorrect datetime value: '' For some reason, the exact same problem is now an ERROR instead of a WARNING, and the query fails, instead of inserting a NULL date into AFieldThatIsDateTime (this is the desired operation, and consistent with previous versions of mySQL) This problem also occurs for other casts that generate warnings, such as CAST('' AS DECIMAL). Suggested fix: Fix it so that the WARNINGS remain WARNINGS and not ERRORS, even during an UPDATE query.