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

[20 Apr 2005 22:45] Lachlan Mulcahy
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.
[22 Apr 2005 1:47] Lachlan Mulcahy
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.
[22 Apr 2005 1:50] Lachlan Mulcahy
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.
[4 Aug 2005 21:40] Jim Winstead
You may want to give this another try with 4.1.14 -- my fix for another bug has relaxed this a little bit.
[22 Feb 2006 11:38] Valeriy Kravchuk
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.053 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.