Bug #11099 Bug selecting wrang primary keys....
Submitted: 4 Jun 2005 17:44 Modified: 26 Oct 2005 21:51
Reporter: Peter Fassev Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Migration Toolkit Severity:S2 (Serious)
Version: OS:Microsoft Windows (Win 2000, SQL Server 2000)
Assigned to: CPU Architecture:Any

[4 Jun 2005 17:44] Peter Fassev 
Description:
Hi,

having a very simple table with 3 columns (note, the order is important):

1) questionId
2) choice
3) indx

where the primary key is build on 1) and 3) (i.e. 'questionId' and 'indx').

The trasformed table looks like:

Creating table questionchoices ...
CREATE TABLE `dbo`.`questionchoices` (
  `questionId` BIGINT(19) NOT NULL,
  `choice` TEXT NOT NULL,
  `indx` INTEGER(10) NOT NULL,
  PRIMARY KEY (`questionId`, `choice`),
  CONSTRAINT `FK_questionchoices_questions` FOREIGN KEY `FK_questionchoices_questions` (`questionId`)
    REFERENCES `dbo`.`questions` (`id`)

)
ENGINE = INNODB
CHARACTER SET utf8 COLLATE utf8_general_ci;

Please note, that the primary key is translated as:

  PRIMARY KEY (`questionId`, `choice`),

Wich is wrong!

Regards
Peter

How to repeat:
See the supplied log.

Suggested fix:
Fix the primary key creation, look for the order of the colums.
[20 Jun 2005 18:18] Marc Hale 
i get the same error if the Primary Key is not the first column it makes whatever is in the first column the primary key. 

also during the migration if it get an error creating a table it does not tell you. and when it trys to import the data it just hangs because table does not exist.
[25 Jun 2005 16:13] Jorge del Conde 
Hi!

Thanks for your bug report.

I was able to reproduce this bug using the supplied information.
[19 Oct 2005 17:02] Michael G. Zinner 
Jorge, could you see if this bug is still existing in the latest release?

I tested with

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test2]
GO

CREATE TABLE [dbo].[test2] (
	[test1] [int] NULL ,
	[test2] [int] NOT NULL ,
	[test3] [int] NULL ,
	[test4] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test2] WITH NOCHECK ADD 
	CONSTRAINT [PK_test2] PRIMARY KEY  CLUSTERED 
	(
		[test2],
		[test4]
	)  ON [PRIMARY] 
GO

which produced 

CREATE TABLE `Northwind_dbo`.`test2` (
  `test1` INT(10) NULL,
  `test2` INT(10) NOT NULL,
  `test3` INT(10) NULL,
  `test4` INT(10) NOT NULL,
  PRIMARY KEY (`test2`, `test4`)
)
ENGINE = INNODB;

and that is correct.
[26 Oct 2005 21:51] Jorge del Conde 
I was unalbe to reporoduce this bug using the lasest version of MT