MySQL Bugs: #23298: timestamp and Date issues

Bug #23298	timestamp and Date issues
Submitted:	14 Oct 2006 21:43	Modified:	17 Nov 2006 23:44
Reporter:	Jeremy Umali	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S3 (Non-critical)
Version:	1.1.4RC	OS:	Windows (WinXP)
Assigned to:		CPU Architecture:	Any
Tags:	date, error, timestamp

Description:
I'm trying to do a Bulk Data Transfer between two identical servers (MySQL 5.0.22/Win2000) from my desktop machine, but the Migration tool kit does seems to be either changing the field type or unable to use '0000-00-00' as a value.

The error I received was as follows:

************************************************

Transfering data from table employeetable (65/65 rows)
SELECT `empTableID`, `entityID`, `fName`, `lName`, `emailAddress`, `officeLocation`, `beginDate`, `endDate`, `timestamp` FROM `data_mart`.`employeetable`

ERROR: The following error occured while transfering data from employeetable
Cannot convert value '0000-00-00' from column 7 to TIMESTAMP.

************************************************

First, column 7 is not a TIMESTAMP field it is a DATE field. Second, the data currently does not have any other values other than '0000-00-00' in them, and should remain '0000-00-00' until user changes them. Third, column 7 should remain a DATE type and column 9 should remain a TIMESTAMP type.

How to repeat:
Use MySQL Migration Toolkit to transfer table with the following columns:

`empTableID`-bigInt, 
`entityID`-bigInt, 
`fName`-varchar, 
`lName`-varchar, 
`emailAddress`-varchar, 
`officeLocation`-varchar, 
`beginDate`-DATE, 
`endDate`-DATE, 
`timestamp` -TIMESTAMP

beginDate and endDate have the value '0000-00-00' for every entry
timestamp has the timestamp of inserted values.

run MySQL Migration Toolkit like normal.

Suggested fix:
Keep DATE field type as DATE and not covert to TIMESTAMP and if the values are '0000-00-00' accept value as DATE.

Thank you for a problem report. Please, try to repeat with a newer version, 1.1.4 rc, and inform about the results.

I received the same error when i upgraded to 1.1.4RC

Getting the number of rows of table employeetable
SELECT count(*) AS total_num FROM `data_mart`.`employeetable`

Transfering data from table employeetable (65/65 rows)
SELECT `empTableID`, `entityID`, `fName`, `lName`, `emailAddress`, `officeLocation`, `beginDate`, `endDate`, `timestamp` FROM `data_mart`.`employeetable`

ERROR: The following error occured while transfering data from employeetable
Cannot convert value '0000-00-00' from column 7 to TIMESTAMP.

Getting the number of rows of table entitydetails
SELECT count(*) AS total_num FROM `data_mart`.`entitydetails`

Transfering data from table entitydetails (154626/154626 rows)
SELECT `EntityDetailID`, `EntityBaseID`, `DetailDefID`, `EntityDetailData`, `DetailDataDesc`, `Prime`, `Active`, `Deleted`, `TStamp`, `prodDate`, `endDate`, `version` FROM `data_mart`.`entitydetails`

ERROR: The following error occured while transfering data from entitydetails
Cannot convert value '0000-00-00' from column 10 to TIMESTAMP.

Could you please provide a small dump file with table definition plus
some insert commands. Thanks in advance.

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".

This issue is closed?  I got the same error here.

I also can't get this darn  MySQL Migration Tool to do its job because it keeps trying to convert my three DATE (0000-00-00) fields to TIMESTAMP without my consent during the data transfer and then crashes.

Why is it trying to do this?  It 'reverse engineered' everything as DATEs.... It should just move the data and be finished.  Sombody tried to make this program way more complicated that it needs to be.

This would be a great tool if it actually worked!!!

This bug is still in the wild. I am getting the same results with v1.1.10. My scenario is slightly different, I am trying to upgrade version4.x to 5.x. I also tried just saving to files, not actually inserting into the database, but I get the same error, and no records are written into the text file for the table in question.

I also noticed that the table create scripts define the data as date, not timestamp. Here is the table create script;

DROP TABLE IF EXISTS `nccnsurvey`.`tbldemographics`;
CREATE TABLE `nccnsurvey`.`tbldemographics` (
  `intSiteRef` INT(11) NOT NULL DEFAULT '0',
  `intSeq` INT(11) NOT NULL AUTO_INCREMENT,
  `dteCompleteDate` DATE NULL DEFAULT '0000-00-00',
  `dteStartDate` DATE NOT NULL DEFAULT '0000-00-00',
  `dteEndDate` DATE NOT NULL DEFAULT '0000-00-00',
  `txtPeriodType` VARCHAR(20) NOT NULL,
  `intNonProgramAlign` INT(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
  `intProgramAlign` INT(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
  `intGTPeerReview` INT(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
  `txtNIHPostDocRate` CHAR(1) NOT NULL,
  `txtInstPayPlan` CHAR(1) NOT NULL,
  `txtFringeBenefit` CHAR(1) NOT NULL,
  `decClinicalFed` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decClinicalNonFed` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decNonClinicalFed` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decNonClinicalNonFed` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decStaffFed` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decStaffNonFed` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decNoClinical` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decNoNonClinical` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decNoStaff` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `intUserRef` INT(11) NOT NULL DEFAULT '0',
  `intReportingYear` INT(4) NOT NULL DEFAULT '2006',
  `intPACore` INT(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
  `intPANon` INT(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
  `intNPACore` INT(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
  `intNPANon` INT(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
  `decPenClin` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decPenNonClin` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decPenStaff` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decHealthClin` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decHealthNonClin` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decHealthStaff` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decDentClin` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decDentNonClin` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decDentStaff` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decDisClin` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decDisNonClin` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decDisStaff` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decLifeClin` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decLifeNonClin` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decLifeStaff` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decEdClin` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decEdNonClin` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decEdStaff` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decFICAClin` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decFICANonClin` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decFICAStaff` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decOthClin` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decOthNonClin` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `decOthStaff` DECIMAL(4, 2) unsigned zerofill NOT NULL DEFAULT '00.00',
  `txtCompPerc` VARCHAR(100) NOT NULL,
  `txtCCType` CHAR(1) NOT NULL,
  `intAlliance` INT(11) NOT NULL DEFAULT '0',
  `intNCCN` INT(11) NOT NULL DEFAULT '0',
  `intNCI` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`intSeq`),
  INDEX `idxDemographics` (`intSiteRef`, `intSeq`)
)
ENGINE = INNODB;

I cannot send the data as it is proprietary.

This would be a great tool if it actually worked!!!

Can't this be corrected in the connection string?  It seems like I ran across this in documentation or a blog.  I'll post if I can find it.