MySQL Bugs: #44456: MySQL Dump inco

Bug #44456	MySQL Dump inco
Submitted:	24 Apr 2009 12:56	Modified:	21 Jun 2009 9:55
Reporter:	Chuck Handshy	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.0.77	OS:	Linux (Mysqldump dates are not being restored properly if you use a date condition in the where)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	mysqldump timezone date

Description:
mysqldump dumps dates in UTC. Using the -where="date_column between '2009-01-01' and '2009-01-01 23:59:59'" will give you a file that has all the records but they are DST off in time. When this file is restored the DST offset isn't corrected.

Example
EST -5 hours

Record 
mysql table date_column = '2009-01-01 00:00:00'

mysqldump date_column = '2008-12-31 19:00:00'

mysql table restore date_column = '2008-12-31 19:00:00'

It also seem to be apply the where after the UTC so that if -where = "date_column between '2009-01-01' and '2009-01-01 23:59:59'" you would get a dump file that contains all the records from 5AM of the start date to 5AM of the next date.

How to repeat:
Create a table with a datetime column.

Populate

Mysqldump -where="date_column between 'startdate' and 'todate'

truncate table

restore dump file

Suggested fix:
Apply UTC conversion to dates in the -where option

Thank you for the report.

MySQL keeps timezone information only for TIMESTAMP columns. Please provide output of SHOW CREATE TABLE, so we are sure this is so in your case.

mysql> show create table cdr_042009\G
*************************** 1. row ***************************
       Table: cdr_042009
Create Table: CREATE TABLE `cdr_042009` (
  `idx` bigint(20) unsigned NOT NULL auto_increment,
  `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `marked` tinyint(3) unsigned NOT NULL default '0',
  `did` varchar(30) NOT NULL default '',
  `name` varchar(30) NOT NULL default '',
  `cid` int(6) unsigned NOT NULL default '0',
  `lid` int(6) unsigned NOT NULL default '0',
  `uid` int(6) unsigned NOT NULL default '0',
  `bill` varchar(8) NOT NULL default '',
  `did_use` char(2) NOT NULL default '',
  `leg_type` char(2) NOT NULL default '',
  `leg_start` time NOT NULL default '00:00:00',
  `leg_end` time NOT NULL default '00:00:00',
  `leg_duration` time NOT NULL default '00:00:00',
  `leg_charge` decimal(9,5) NOT NULL default '0.00000',
  `call_time` timestamp NOT NULL default '1970-01-01 00:00:00',
  `call_mgr` tinyint(3) unsigned NOT NULL default '0',
  `leg_id` tinyint(3) unsigned NOT NULL default '0',
  `call_key` binary(16) NOT NULL default '                ',
  PRIMARY KEY  (`idx`),
  UNIQUE KEY `unique_key` (`leg_id`,`cid`,`call_key`),
  KEY `cid_call_time` (`cid`,`call_time`),
  KEY `cid_lid_call_time` (`cid`,`lid`,`call_time`),
  KEY `cid_uid_call_time` (`cid`,`uid`,`call_time`),
  KEY `leg_type_cid_call_time` (`leg_type`,`cid`,`call_time`),
  KEY `call_time` (`call_time`),
  KEY `did` (`did`),
  KEY `marked` (`marked`),
  KEY `stamp` (`stamp`),
  KEY `cid_lid_uid_call_time` (`cid`,`lid`,`uid`,`call_time`),
  KEY `leg_type_cid_lid_uid_call_time` (`leg_type`,`cid`,`lid`,`uid`,`call_time`),
  KEY `cid_lid_did_uid_call_time` (`cid`,`lid`,`did`,`uid`,`call_time`),
  KEY `call_key` (`call_key`)
) ENGINE=InnoDB AUTO_INCREMENT=538921188 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Thank you for the feedback.

I can not repeat described behavior if I search in stamp column. Please indicate which column you use as "date_column".

Additionally I can not create table as described because error "1067: Invalid default value for 'call_time'" How did you create the table? What is value of SQL_MODE?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".