MySQL Bugs: #17388: Auto Increment setting lost during migration

Bug #17388	Auto Increment setting lost during migration
Submitted:	14 Feb 2006 16:25	Modified:	18 Jun 2006 18:32
Reporter:	Craig Williams	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S2 (Serious)
Version:	1.0.23 rc	OS:	Windows (XP Pro SP2)
Assigned to:	Michael G. Zinner	CPU Architecture:	Any

Description:
I was migration data from a mySql 4.x database to the latest 5.x release. Everything migrated great except for the columns that had an auto increment set. That was lost - the columns were still set as primary keys, integer etc but I had to re-add that setting on each table.

Approx 15 tables migrated and very little data.

How to repeat:
Migrate a table with a column that is auto-inc from 4.x to 5.x database.

Suggested fix:
The auto-inc setting should be persisted.

Thank you for a bug report. MT generated the following SQL script for me while migrating from 4.1.18 to 5.0.18:

-- ----------------------------------------------------------------------
-- MySQL Migration Toolkit
-- SQL Create Script
-- ----------------------------------------------------------------------

SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `test`
  CHARACTER SET latin1 COLLATE latin1_swedish_ci;
-- -------------------------------------
-- Tables

DROP TABLE IF EXISTS `test`.`t1`;
CREATE TABLE `test`.`t1` (
  `c1` INT(11) NOT NULL,
  `c2` CHAR(20) NULL,
  PRIMARY KEY (`c1`)
)
ENGINE = INNODB;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------------------------------------------------
-- EOF

The original table was:

C:\Documents and Settings\openxs>mysql -uroot -p -P3306 test
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.1.18-nt

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

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) NOT NULL auto_increment,
  `c2` char(20) default NULL,
  PRIMARY KEY  (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

I've had similar issues with transfers between MySQL v4->v5 and v5 -> v5.

Another serious setting not transfered is the UNSIGNED attribute. Less serious is the lack of table comments.

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

Additional info:

Both the auto_increment issue and the UNSIGNED issue have been fixed.