| Bug #58002 | same command, different behavior | ||
|---|---|---|---|
| Submitted: | 4 Nov 2010 23:20 | Modified: | 17 Nov 2010 9:00 |
| Reporter: | Adrian Benko | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.1.51-community | OS: | Windows (7) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | INSERT ... SELECT | ||
[5 Nov 2010 4:22]
Valeriy Kravchuk
First of all, format of string is NOT the same as you use in str_to_date(), so problem is expected. Look:
mysql> select str_to_date('04-02-0709 00:00:00', '%c/%e/%Y %k:%i') as d;
+------+
| d |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1411
Message: Incorrect datetime value: '04-02-0709 00:00:00' for function str_to_date
1 row in set (0.00 sec)
When we use exact format there is no problem:
mysql> select str_to_date('04-02-0709 00:00:00', '%c-%e-%Y %k:%i:%s') as d;
+---------------------+
| d |
+---------------------+
| 0709-04-02 00:00:00 |
+---------------------+
1 row in set (0.00 sec)
If I'll remove %s, we get different warning:
mysql> select str_to_date('04-02-0709 00:00:00', '%c-%e-%Y %k:%i') as d;
+---------------------+
| d |
+---------------------+
| 0709-04-02 00:00:00 |
+---------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1292
Message: Truncated incorrect datetime value: '04-02-0709 00:00:00'
1 row in set (0.00 sec)
All these results are expected. Now, why INSERT ... SELECT works in Workbench but NOT in stored procedure? I think it is because of different SQL modes used in these environments. Check with:
select @@sql_mode;
Stored procedure inherits its SQL mode from the session where it was compiled.
[5 Nov 2010 6:45]
Adrian Benko
If everything is expected then I need an advise. I have table with 100000 records and TEXT columns. All records have column Expected_Date_73 in format '%c/%e/%Y %k:%i' except two records which have value in Expected_Date_73 like '04-02-0709 00:00:00'. I need to find these two records and move them to another table. How can I do it if sqlmode=STRICT_TRANS_TABLES?
[17 Nov 2010 9:00]
Susanne Ebrecht
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug. Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ Thank you for your interest in MySQL.
Description: When I run this command through ColdFusion Page or within a stored procedure, it produces such an error (not always ... once it is running, it is running till restart PC, after restart it is not running again). But when I run this command through MySQL Workbench, it works. Engine is MyISAM. command: INSERT INTO rejections SELECT *, Concat('Wrong value for ','Expected_Date_73') FROM staging_area WHERE File_ID = 7 AND STR_TO_DATE(Expected_Date_73, "%c/%e/%Y %k:%i") is null AND length(Expected_Date_73) > 0 error: java.sql.SQLException: Incorrect datetime value: '04-02-0709 00:00:00' for function str_to_date at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:741) How to repeat: I can repeated within my big MyISAM database.