MySQL Bugs: #10048: 4.1 datetime fields aren't as 'relaxed' as 4.0; can cause replication issues

Bug #10048	4.1 datetime fields aren't as 'relaxed' as 4.0; can cause replication issues
Submitted:	20 Apr 2005 22:45	Modified:	22 Feb 2006 11:38
Reporter:	Lachlan Mulcahy	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	4.1.11, 5.0.x	OS:	Any (Any)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The behaviour of this kind of mixed delimiter/no delimiter input is not documented, however, in 4.0 a DATETIME inserted as '20040101 05:00:00' is accepted and recognised as '2004-01-01 05:00:00', whereas in 4.1 the value is treated as invalid and is thus converted to '0000-00-00 00:00:00'.

This is not _strictly_ a bug, though it is perhaps a behaviour that should be changed for the good of replication compatibility between versions.

ie. A 4.0 master will accept the malformed query and INSERT the data the user desires, whereas 4.1 will not.

How to repeat:
on 4.0:
CREATE TABLE t1 (a datetime);
INSERT INTO t1 VALUES ('20040101 05:00:00');
SELECT a FROM t1;

on 4.1:
CREATE TABLE t1 (a datetime);
INSERT INTO t1 VALUES ('20040101 05:00:00');
SELECT a FROM t1;

Compare output.

Suggested fix:
Adjust the 'relaxed' acceptance of dates in 4.1 to be the same as in 4.0.

I must correct myself as it looks as though this _is_ indeed  documented here: 
http://dev.mysql.com/doc/mysql/en/news-4-1-0.html

--snip--
"DATE/DATETIME checking is now a bit stricter to support the ability to automatically distinguish between date, datetime, and time with microseconds. For example, dates of type YYYYMMDD HHMMDD are no longer supported; you must either have separators between each DATE/TIME part or not at all. "
--snip--

I'm changing this to a feature request and updating the original information.

For the feature request -- instead of simply changing the behaviour of 4.1 to mirror that of 4.0 with 'relaxed' date/datetime conversions, add an option to 4.1 that will allow it to work in the old fashion for cross-version replication purposes.

You may want to give this another try with 4.1.14 -- my fix for another bug has relaxed this a little bit.

The same problem will occur during replcation from 4.0.x to 5.0.x (many tries this way):

mysql> CREATE TABLE t1 (a datetime);
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO t1 VALUES ('20040101 05:00:00');
ERROR 1292 (22007): Incorrect datetime value: '20040101 05:00:00' for column 'a'  at row 1
mysql> SELECT a FROM t1;
Empty set (0.02 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.18-nt |
+-----------+
1 row in set (0.00 sec)

So, command line option and/or server variable (or, maybe, SQL MODE) to provide a "relaxed" check of dates, compatible with 4.0, will be really useful.