MySQL Bugs: #18716: Rejecting a valid datetime value in a timestamp field

Bug #18716	Rejecting a valid datetime value in a timestamp field
Submitted:	2 Apr 2006 9:38	Modified:	3 Apr 2006 13:32
Reporter:	Anthony Marston	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.19-nt	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I regularly export data from my live server and import into my test server, but I have just come across a peculiar error:

REPLACE INTO `accesslog` VALUES ( 1025955, '/php-mysql/workflow.html', '68.100.76.251', '20060326010040' ) ;

MySQL said: #1292 - Incorrect datetime value: '20060326010040' for column 'timestamp' at row 1 

That is a perfectly valid timestamp, so why is it being rejected?
I tried amending the timestamp to the same value as on the previous record (id=1025954), but then it reports that the 

timestamp on record with id=1025956 is wrong!

MySQL version 5.0.19
O/S = WindowsXP

Table schema:

CREATE TABLE IF NOT EXISTS `accesslog` (
  `id` int(11) NOT NULL auto_increment,
  `pageid` varchar(255) default NULL,
  `ip_address` varchar(16) default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `pageid` (`pageid`,`ip_address`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1025957 ;

How to repeat:
Create database and table.
Load data file.
It fails every time.

If it does not fail with just those records you may need all the previous 1,025,000 records which I can supply in a series of zip files.

Suggested fix:
Haven't a clue!

sql data file

Attachment: accesslog(1025000).zip (application/x-zip-compressed, text), 68.27 KiB.

Thank you for a problem report. Sorry, but I was not able to repeat with those statements you presented:

C:\Program Files\MySQL\MySQL Server 5.0\data\test>mysql -uroot -pmypass -P3307 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.19-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE IF NOT EXISTS `accesslog` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `pageid` varchar(255) default NULL,
    ->   `ip_address` varchar(16) default NULL,
    ->   `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
    -> CURRENT_TIMESTAMP,
    ->   PRIMARY KEY  (`id`),
    ->   KEY `pageid` (`pageid`,`ip_address`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1025957 ;
Query OK, 0 rows affected (0.09 sec)

mysql> REPLACE INTO `accesslog` VALUES ( 1025955, '/php-mysql/workflow.html',
    -> '68.100.76.251', '20060326010040' ) ;
Query OK, 1 row affected (0.03 sec) 

So, looks like some data for the table and/or additional information on how to repeat is needed.

Your limit for file uploads is 200k per file, but all my sample data takes up 12mb, so would take ages to split and upload. Do you have an e-mail address I can use instead?

We have FTP server for upload of large files, but I do not like the idea of 12Mb test case, anyway. So, can you, please, try to demonstrate this problem on a smallest possible set of rows? Please, send also your my.ini file contnent.

I deleted batches of records, ran 'optimise table', then tried the script again. I still got the same error even when I started with an empty table. Very strange!

I will attach my.ini separately

my.ini

Attachment: my.ini (application/octet-stream, text), 9.00 KiB.

I have the same value of sql-mode in my.ini, same version, also XP, but different results on empty table, as I demonstrated... Please, create another table with exactly the same structure, but different name, and run the same REPLACE statement you sent on it. Copy and paste results here. Will it fail?

I have tried deleting and recreating the accesslog table - same result.

I have tried creating a table with a different name - same result.

I have uninstalled MySQL, rebooted, re-installed, tried script again - still the same error.

Please, just copy and paste CREATE TABLE and REPLACE statements, with the results of their execution, as I did. I can not repeat the behaviour you described, neither on Windows XP with 5.0.19-nt, nor on Linux with newer versions.

Hi, Anthony!

Please also provide results returned by the following query for your system:

show variables like '%time_zone';

Also note that timestamp '20060326010040' can be invalid in certain time zones due to switches to daylight saving (AKA summer) time.

For example for Europe/London time zone switch happened at 2006-03-26 01:00:00 (local time). During the switch one hour was added to the current local time, therefor one hour gap was created. Thus there is no moment of time represented as 2006-03-26 01:00:40 in this time zone (this value falls into the gap), and such datetime value is invalid value for TIMESTAMP field in this time zone.

To avoid such problems you I either should set @@time_zone properly when you are importing your data (which probably comes from source in time zone different from the time zone in which you do your import operation) or use DATETIME type which is unaffected by MySQL time-zone settings.
See http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html for more information about time zone support in MySQL.

My timezone reads "GMT Standard Time" while my live data comes from a server in the USA.

I cannot see in the manual how to create a DATETIME or TIMEZONE column which is unaffected by timezone settings, but by inserting the line

SET SESSION sql_mode = 'allow_invalid_dates';

in the script before the REPLACE statements I got it to work.

So, looks like the real reason is identified. You should either set time zone according to data loaded, or use DATETIME (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html) data type (and triggers to keep it updated) that does not take timezone into account. It is not a bug, but an intended behaviour.