MySQL Bugs: #14405: MMT doesn't handle MySQL4.1 imports properly

Bug #14405	MMT doesn't handle MySQL4.1 imports properly
Submitted:	27 Oct 2005 18:27	Modified:	5 Dec 2005 13:39
Reporter:	Kevin Benton (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S1 (Critical)
Version:	1.0.20	OS:	Microsoft Windows (WinXP)
Assigned to:		CPU Architecture:	Any

Description:
I strongly encourage the developers to work hard on making sure they do a better job of testing the MySQL Migration Toolkit.  It seems they forgot to check significant sections of interaction with MySQL 4.1.  In my case, I was actually migrating a live Bugzilla database.  The bugs table in MySQL 4.1 had the following a number of elements including…

  bug_id mediumint(11) not null default 0 auto_increment primary key,
  …
  creation_ts timestamp not null default current time,
  …

When I tried to use the Toolkit to import the tables and data into MySQL 5, the Toolkit tried to add the bug_id without the auto_increment flag, and current time in MySQL4.1 was translated to ‘current time’ in MySQL 5 (with the apostrophes).  It seems it would be easier to use mysqldump instead.

How to repeat:
See Description

Suggested fix:
See Description

Sorry all...

The bugs table in MySQL 4.1 had the
following a number of elements including…

should have read...

The bugs table in MySQL 4.1 had a number of elements including...

Hi Kevin,

Can you please send us a copy of your create table statements so that we can reproduce this bug ?

Thanks !

Here's a sample - note that userid is an auto_increment field.

CREATE TABLE `profiles` (
  `userid` mediumint(9) NOT NULL auto_increment,
  `login_name` varchar(255) NOT NULL default '',
  `cryptpassword` varchar(34) default NULL,
  `realname` varchar(255) default NULL,
  `disabledtext` mediumtext NOT NULL,
  `mybugslink` tinyint(4) NOT NULL default '1',
  `emailflags` mediumtext,
  `refreshed_when` datetime NOT NULL default '0000-00-00 00:00:00',
  `extern_id` varchar(64) default NULL,
  PRIMARY KEY  (`userid`),
  UNIQUE KEY `login_name` (`login_name`)
) TYPE=MyISAM

Another was...

CREATE TABLE `task` (
  `id` mediumint(9) unsigned NOT NULL auto_increment,
  `owner` mediumint(9) NOT NULL default '0',
  `assigned_to` mediumint(9) NOT NULL default '0',
  `when_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_
TIMESTAMP,
  `when_due` datetime default '2005-01-01 00:00:00',
  `when_completed` timestamp NOT NULL default '0000-00-00 00:00:00',
  `is_private` tinyint(1) NOT NULL default '0',
  `percent_complete` int(3) NOT NULL default '0',
  `summary` varchar(255) NOT NULL default '',
  `description` mediumtext,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Kevin, thanks for your bug report.  I was able to reproduce this bug using the statements that you provided.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html