Bug #69533 Warning changed for invalid dates from 5.5 to 5.6
Submitted: 20 Jun 2013 21:28 Modified: 21 Jun 2013 10:57
Reporter: Josh Unger Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Errors Severity:S2 (Serious)
Version:5.6.10 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[20 Jun 2013 21:28] Josh Unger
Description:
The warning reported for certain invalid dates has changed from 5.5 to 5.6.  We use the warning and code in our product which is now broken.  Was this on purpose or a regression?  The warning changed from 1264 "Out of range value for column" to 1265 "".  Thanks.

How to repeat:
Execute the following in both MySQL 5.5 and MySQL 5.6 -

create table datetimewarning(d datetime);
insert into datetimewarning(d) values ('1/1/2011');
show warnings;

Using MySQL 5.5, you receive the following warning -

+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'd' at row 1 |
+---------+------+--------------------------------------------+

Using MySQL 5.6, you receive the following warning -

+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'd' at row 1 |
+---------+------+----------------------------------------+
[21 Jun 2013 10:57] Miguel Solorzano
Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.97-debug Source distribution
<cut>
mysql> create table datetimewarning(d datetime);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into datetimewarning(d) values ('1/1/2011');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'd' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
-----------------------------------------------------------------------
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.71-debug Source distribution
<cut>
mysql> create table datetimewarning(d datetime);
Query OK, 0 rows affected (0.10 sec)
mysql> insert into datetimewarning(d) values ('1/1/2011');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'd' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)
-----------------------------------------------------------------------
Server version: 5.5.33-debug Source distribution
<cut>
mysql> create table datetimewarning(d datetime);
Query OK, 0 rows affected (0.16 sec)
mysql> insert into datetimewarning(d) values ('1/1/2011');
Query OK, 1 row affected, 1 warning (0.07 sec)
mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'd' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)
-----------------------------------------------------------------------
Server version: 5.6.13-debug Source distribution
<cut>
mysql> create table datetimewarning(d datetime);
Query OK, 0 rows affected (0.75 sec)
mysql> insert into datetimewarning(d) values ('1/1/2011');
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'd' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)