MySQL Bugs: #21071: Invalid conversion of MSSQL datatypes

Bug #21071	Invalid conversion of MSSQL datatypes
Submitted:	14 Jul 2006 20:46	Modified:	23 Oct 2006 10:16
Reporter:	Stewart Gateley	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S2 (Serious)
Version:	1.0.25, 1.1.1	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	conversion, migrate, MSSQL, MySQL

Description:
When migrating from SQL Server 7 to MySQL 5.0.22 SQL datatype NTEXT is mistakingly converted to VARCHAR(16).

Additionally, when data-consistency is selected in the migration options the migration toolkit will double the data storage requirements of any SQL Server VARCHAR type.

Examples (s)
SQL SERVER: Notes NVTEXT NULL -> MYSQL: VARCHAR(16) CHARSET UTF-8
SQL SERVER: TITLE NVARCHAR(30) -> MYSQL: VARCHAR(60) CHARSET UTF-8

How to repeat:
Create any SQL Server 7.0 schema with unicode character data types and attempt to use the migration wizard to migrate to MySQL.

Thank you for a problem report. Please, try to repeat with a newer version of MT, 1.1, from http://dev.mysql.com/downloads/gui-tools/5.0.html. Inform about the results.

Thank you Valeriy for investigating this issue, I have downloaded the latest set of MySQL GUI Tools and tried to convert the same schema, with the same results as before. For your reference below is the schema of one particular table that shows both issues I am referring to (double data size for nvarchar and invalid conversion of text types to varchar). I am showing the entire create script for each, however only the two character fields are of interest for this bug.

MSSQL Create Script:

CREATE TABLE [dbo].[Agreements_SectionsAssoc] (
	[RecordID] [int] NOT NULL ,
	[Created] [datetime] NULL ,
	[Modified] [datetime] NULL ,
	[AgreementID] [int] NULL ,
	[SectionID] [int] NULL ,
	[Title] [nvarchar] (100) NULL ,
	[Content] [ntext] NULL ,
	[ShortVersion] [bit] NULL ,
	[SectionID_Link] [int] NULL ,
	[DisplayOrder] [int] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

MySQL Create Script:
CREATE TABLE `pams`.`Agreements_SectionsAssoc` (
  `RecordID` INT(10) NOT NULL,
  `Created` DATETIME NULL,
  `Modified` DATETIME NULL,
  `AgreementID` INT(10) NULL,
  `SectionID` INT(10) NULL,
  `Title` VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `Content` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `ShortVersion` TINYINT(1) NULL,
  `SectionID_Link` INT(10) NULL,
  `DisplayOrder` INT(10) NULL
)

What exact version of JDBC driver you use to work with MS SQL?

Valeriy,

The MySQL Migration Toolkit does not mention the version of the MS SQL JDBC driver in the message log. The Java source file reports version 1.0, 05/21/05. I am assuming this is the source file for the actual class the toolkit is loading.

Thanks,

Stewart Gateley

I do not have an MS SQL Server 7.0 here. Could you please give the following information.

1) After the reverse engineering step, please press F4.

2) In the GRT Shell, expand the Object Tree to get to the following path
   /migration/sourceCatalog/schemata/YOUR_SCHEMA/tables/Agreements_SectionsAssoc/columns/Content

3) Press the right mousebutton on the Param/Value grid and select [Copy All Values], paste the text here.

4) The characterSetName should be "Unicode", datatypeName should be "ntext" and the simpleType should be {73903640-5E47-4182-87FF-C5A69372F0E6}

Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Michael,

Please see the values you requested below.

---
adffaf33da60b7fe:6d779b89:10db29ffb3c:-7f7e6d779b89:10db29ffb3c:-7f7d
iso_1
LIST
nocase_iso
0
ntext
0
LIST
0
1
16
Content
adffaf33da60b7fe:6d779b89:10db29ffb3c:-7f8c6d779b89:10db29ffb3c:-7f8b
0
0
{73903640-5E47-4182-87FF-C5A69372F0E6}
---

The only difference here is that the characterSetName is iso_1 rather than the unicode you had expcected. This may be a SQL Server 7 default for the table collation? If I find some time I will play around with table properties to see if I can get the collation and characterset to be unicode.

Thanks,

Stewart

Please, try to repeat with a newer version, 1.1.3 rc from GUI Tools 5.0 r3 and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".