| Bug #30361 | Incorrect datetime value '2003-03-09 02:33:07' | ||
|---|---|---|---|
| Submitted: | 10 Aug 2007 16:32 | Modified: | 14 Aug 2007 18:40 |
| Reporter: | Richard Ayotte | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.0.45 community | OS: | Windows (2003 SP2) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | daylight savings, Eastern Time, timezone | ||
[10 Aug 2007 16:46]
Jim Winstead
This could be a timezone issue, if 2003-03-09 was the day that the clocks advanced one hour for daylight savings (summer) time.
[10 Aug 2007 16:54]
MySQL Verification Team
Thank you for the bug report. I can't repeat with released version 5.0.45
and please see Jim last comment.
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test
Database changed
mysql> CREATE TABLE `creditinfo` (
-> `TStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
-> CURRENT_TIMESTAMP,
-> `Id` int(11) unsigned NOT NULL auto_increment,
-> `PatronId` int(11) unsigned NOT NULL default '0',
-> `CreditCardTypeId` int(11) unsigned default NULL,
-> `CardNumber` varchar(25) default NULL,
-> `ExpiryDate` datetime default NULL,
-> `NameOnCard` varchar(40) default NULL,
-> PRIMARY KEY (`Id`),
-> KEY `PatronId` (`PatronId`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=70434 DEFAULT CHARSET=latin1
-> PACK_KEYS=1;
Query OK, 0 rows affected (0.17 sec)
mysql> REPLACE INTO
-> `test`.`CreditInfo`(`TStamp`,`Id`,`PatronId`,`CreditCardTypeId`,`CardNumber`,`ExpiryDate`,`NameOnCard`)
-> VALUES('2003-03-09 02:33:07','34247','1962742','1','4444444444444444','2003-10-01 00:00:00','Sam Babe');
Query OK, 1 row affected (0.08 sec)
mysql>
[10 Aug 2007 17:46]
Richard Ayotte
I upgraded to 5.0.45 and the problem remains. Going on you daylight savings hint, I found other dates '2005-03-13 02:31:17' and '2006-03-12 02:31:17' that fail.
REPLACE INTO `res`.`CreditInfo`(`TStamp`,`Id`,`PatronId`,`CreditCardTypeId`,`CardNumber`,`ExpiryDate`,`NameOnCard`) VALUES('2005-03-13 02:31:17','67068','869505','1','4444 4444 4444 4444','2008-02-01 00:00:00','BOB SMITH');
[14 Aug 2007 18:40]
Richard Ayotte
The Canadian (Eastern Time) daylight savings in 2003 started April 6th, not March 9th. It would have been March 9th if the new 2007 daylight savings calculations would have been applied in 2003. iceborg:~# zdump -v /etc/localtime | grep 2003 /etc/localtime Sun Apr 6 06:59:59 2003 UTC = Sun Apr 6 01:59:59 2003 EST isdst=0 gmtoff=-18000 /etc/localtime Sun Apr 6 07:00:00 2003 UTC = Sun Apr 6 03:00:00 2003 EDT isdst=1 gmtoff=-14400 /etc/localtime Sun Oct 26 05:59:59 2003 UTC = Sun Oct 26 01:59:59 2003 EDT isdst=1 gmtoff=-14400 /etc/localtime Sun Oct 26 06:00:00 2003 UTC = Sun Oct 26 01:00:00 2003 EST isdst=0 gmtoff=-18000 iceborg:~# zdump -v /etc/localtime | grep 2007 /etc/localtime Sun Mar 11 06:59:59 2007 UTC = Sun Mar 11 01:59:59 2007 EST isdst=0 gmtoff=-18000 /etc/localtime Sun Mar 11 07:00:00 2007 UTC = Sun Mar 11 03:00:00 2007 EDT isdst=1 gmtoff=-14400 /etc/localtime Sun Nov 4 05:59:59 2007 UTC = Sun Nov 4 01:59:59 2007 EDT isdst=1 gmtoff=-14400 /etc/localtime Sun Nov 4 06:00:00 2007 UTC = Sun Nov 4 01:00:00 2007 EST isdst=0 gmtoff=-18000 This problem occurs only on the Windows implementation only and you'll need to set you country to Canada and Eastern Time in order to reproduce.
Description: When I run the following statement, it fails with "Incorrect datetime value: '2003-03-09 02:33:07' for column 'TStamp' at row 1" REPLACE INTO `res`.`CreditInfo`(`TStamp`,`Id`,`PatronId`,`CreditCardTypeId`,`CardNumber`,`ExpiryDate`,`NameOnCard`) VALUES('2003-03-09 02:33:07','34247','1962742','1','4444444444444444','2003-10-01 00:00:00','Sam Babe'); Here's the table definition. CREATE TABLE `creditinfo` ( `TStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `Id` int(11) unsigned NOT NULL auto_increment, `PatronId` int(11) unsigned NOT NULL default '0', `CreditCardTypeId` int(11) unsigned default NULL, `CardNumber` varchar(25) default NULL, `ExpiryDate` datetime default NULL, `NameOnCard` varchar(40) default NULL, PRIMARY KEY (`Id`), KEY `PatronId` (`PatronId`) ) ENGINE=MyISAM AUTO_INCREMENT=70434 DEFAULT CHARSET=latin1 PACK_KEYS=1; The statement works fine on Linux MySQL ver 5.0.19. How to repeat: 1. Create the table: CREATE TABLE `creditinfo` ( `TStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `Id` int(11) unsigned NOT NULL auto_increment, `PatronId` int(11) unsigned NOT NULL default '0', `CreditCardTypeId` int(11) unsigned default NULL, `CardNumber` varchar(25) default NULL, `ExpiryDate` datetime default NULL, `NameOnCard` varchar(40) default NULL, PRIMARY KEY (`Id`), KEY `PatronId` (`PatronId`) ) ENGINE=MyISAM AUTO_INCREMENT=70434 DEFAULT CHARSET=latin1 PACK_KEYS=1; 2. Run the following statement: REPLACE INTO `res`.`CreditInfo`(`TStamp`,`Id`,`PatronId`,`CreditCardTypeId`,`CardNumber`,`ExpiryDate`,`NameOnCard`) VALUES('2003-03-09 02:33:07','34247','1962742','1','4444444444444444','2003-10-01 00:00:00','Sam Babe');