Bug #31206 Can not import date fields of where source value is 0000-00-00
Submitted: 26 Sep 2007 14:23 Modified: 6 Feb 2009 12:18
Reporter: ed healey Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Migration Toolkit Severity:S3 (Non-critical)
Version:1.1.12 OS:Windows (2003)
Assigned to: CPU Architecture:Any

[26 Sep 2007 14:23] ed healey
Description:
Failure to migrate data (direct migration where destination was on host server)from a mysql server 4.1.13-nt-log table to mysql server 5.0.45-community-nt table if data in source table contains value 0000-00-00 in date field.   Migration process appears successful untill end and the migration reports:

The following error occurred while transferring data from gallery_diary
            Cannot convert value '0000-00-00' from column 6 to TIMESTAMP.

The table is created on destination server and is correctly given data type DATA not null  but no data can be imported.

How to repeat:
migrate a mysql table that has a date field that contains 0000-00-00 in some of the tables records into desination table  with identical schema 

Suggested fix:
Possible jdbc issue - maybe create a jdbc connection with ammened parameters but dont know enough about this. 

I imported data in to table in the end using mysql administrator - backup and restore.
[27 Sep 2007 4:04] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of:

show variables like 'sql_m%';

from your MySQL 5.0.45 server.
[27 Sep 2007 8:13] ed healey
Results of show variables like 'sql_m%' as requested.

Variable_name,Value
sql_mode,[STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION]
[27 Sep 2007 18:30] Valeriy Kravchuk
Please, send the SHOW CREATE TABLE results for the table you are trying to migrate from 4.1.x.
[28 Sep 2007 8:32] ed healey
Show Create table as requested.
CREATE TABLE `gallery_diary` (

  `id` int(11) NOT NULL auto_increment,

  `order_no` smallint(6) NOT NULL default '0',

  `is_active` tinyint(4) NOT NULL default '0',

  `name` varchar(255) NOT NULL default '',

  `position` varchar(50) default NULL,

  `end_date` date default NULL,

  `pic_ref` varchar(50) default NULL,

  `statement` text,

  `email` varchar(50) default NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1
[28 Oct 2007 21:41] Jim Keller
Is there any word on this bug? It's a dangerous problem, especially since the migration manager says that the migration completed successfully despite the fact that whole tables might be missing if there was a zero date in one of the rows.
[6 Feb 2009 12:18] Susanne Ebrecht
MySQL migration tool only support MySQL server version 5.0 and higher.

Also the migration tool is not for migration from older MySQL versions to newer versions.
Therefor we have upgrade scripts.

More information about upgrade you will find here:
http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html
http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html
http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-5-0.html