MySQL Bugs: #36227: Table will not create when migrating from SQL Server 2005

Bug #36227	Table will not create when migrating from SQL Server 2005
Submitted:	21 Apr 2008 11:32	Modified:	22 Oct 2008 15:31
Reporter:	Declan O Loughlin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S2 (Serious)
Version:	1.1.12	OS:	Windows (XP/Service Pack 2)
Assigned to:	Mike Lischke	CPU Architecture:	Any

Description:
I am new to MySQL, and I would like to evaluate the product. As a first step I wanted to port an existing MS SQL Server 2005 database.

The database I am migrating contains SQL Server schemas to which the tables belong.

When I excute the migration the script for example is -

DROP TABLE IF EXISTS `AwardsBA_Dev_GeneralTables`.`Company`;
CREATE TABLE `AwardsBA_Dev_GeneralTables`.`Company`
)
ENGINE = INNODB;

The table definition for SQL Server 2005 is -

USE [AwardsBA_Dev]
GO
/****** Object:  Table [GeneralTables].[Company]    Script Date: 04/21/2008 12:31:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [GeneralTables].[Company](
	[CompID] [nvarchar](12) NOT NULL,
	[AddrID] [nvarchar](12) NULL,
	[CompName] [nvarchar](50) NULL,
	[CompRegNo] [nvarchar](20) NULL,
	[CurrencyID] [nvarchar](12) NULL,
	[CompCD] [nvarchar](4) NULL,
	[RecordStatus] [int] NULL,
	[UseDecQtys] [tinyint] NULL,
	[VatNo] [varchar](25) NULL,
	[TStamp] [timestamp] NULL,
 CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED 
(
	[CompID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

How to repeat:
If I can provide any more information or log files please let me know.

Thank you for the bug report. I just created a similar table of your test
case and I was able to migrate:

c:\dbs>5.0\bin\mysql -uroot bugtest_dbo
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.60-nt Source distribution

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

mysql> show create table company\G
*************************** 1. row ***************************
       Table: company
Create Table: CREATE TABLE `company` (
  `CompID` varchar(12) character set utf8 NOT NULL,
  `AddrID` varchar(12) character set utf8 default NULL,
  `CompName` varchar(50) character set utf8 default NULL,
  `CompRegNo` varchar(20) character set utf8 default NULL,
  `CurrencyID` varchar(12) character set utf8 default NULL,
  `CompCD` varchar(4) character set utf8 default NULL,
  `RecordStatus` int(10) default NULL,
  `UseDecQtys` tinyint(3) default NULL,
  `VatNo` varchar(25) default NULL,
  `TStamp` timestamp NULL default NULL,
  PRIMARY KEY  (`CompID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

Could you please provide the file of one database which presents the behavior
reported and I will attach it to test?. Thanks in advance.

Hi,

Thanks for the update. The tables will migrate if the are in the dbo schema area of SQL Server 2005. They will not migrate if they are in the GeneralTables schema area as in the example below.

Thanks.
Declan

Thank you for the feedback. Verified according last comment instructions.

Any ETA on a fix for this? We're currently evaluating Oracle and MySQL as targets for a migration of an enterprise system aware from MSSQL and this issue blocks evaluation of MySQL.

"aware" above obviously should have read "away" - as far away as possible in fact.

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html