MySQL Bugs: #16384: MS SQL TEXT datatypes are incorrectly converted to VARCHAR(16)

Bug #16384	MS SQL TEXT datatypes are incorrectly converted to VARCHAR(16)
Submitted:	11 Jan 2006 19:33	Modified:	12 Jan 2006 14:09
Reporter:	Andrzej Imiolek	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S3 (Non-critical)
Version:	1.0.21-rc	OS:	Windows (Windows 2003 SP1)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
When migrating a table from MS SQL 7.0 with a column of type 'TEXT', MT converts it to a datatype of VARCHAR(16).  The 16 comes from MS SQL's default length value for its 'text' datatype which represents 2^16.  I think a column of text type should translate to a column of text type, not varchar.

How to repeat:
This seems to occur for any table I try to migrate with a column of 'text' type.

Suggested fix:
Manually setting the datatype to 'TEXT' seems to resolve the issue.

I was unable to repeat migrating from SQL Server 2005 to MySQL 5.0.19:

--------------------------------------------------------------------------------
-- MySQL Migration Toolkit Report                                             --
--                                                                            --
-- Title:   Summary Of The Migration Process                                  --
-- Date:    2006-01-11 18:54                                                  --
--------------------------------------------------------------------------------

1. Schema Migration
-------------------

  Number of migrated schemata: 1

  Schema Name:            test_dbo
  - Tables:               1
  - Views:                0
  - Routines:             0
  - Routine Groups:       0
  - Synonyms:             0
  - Structured Types:     0
  - Sequences:            0

  Details:

  - Tables
      `test_dbo`.`tb1`
      ----------------
  - Views
  - Routines
  - Routine Groups
  - Synonyms
  - Structured Types
  - Sequences

2. Data Bulk Transfer
---------------------

      `test_dbo`.`tb1`
      ----------------
          0 row(s) transfered.

End of report.
--------------------------------------------------------------------------------

mysql> use test_dbo
Database changed
mysql> show tables;
+--------------------+
| Tables_in_test_dbo |
+--------------------+
| tb1                |
+--------------------+
1 row in set (0.00 sec)

mysql> show create table tb1\G
*************************** 1. row ***************************
       Table: tb1
Create Table: CREATE TABLE `tb1` (
  `col1` longtext
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.06 sec)

Which MySQL server are you using?

I am using MySQL 5.0.18

Could you please provide the SQL server create table statement for to try
on my side.

This will create a table identical to what I have in SQL 7.  

CREATE TABLE [pR2].[dbo].[Families] (
[FamilyID] int PRIMARY KEY, 
[FamilyName] varchar (255) NOT NULL, 
[FamilyDescription] text NULL, 
[Manufacturer] varchar (64) NULL, 
[DisplayStyle] varchar (55) NULL, 
[Archived] int NOT NULL
)

I was unable to repeat regarding the TEXT -> varchar issue. Sorry I don't
have the SQL 7 for test.

--------------------------------------------------------------------------------
-- MySQL Migration Toolkit Report                                             --
--                                                                            --
-- Title:   Summary Of The Migration Process                                  --
-- Date:    2006-01-11 20:20                                                  --
--------------------------------------------------------------------------------

1. Schema Migration
-------------------

  Number of migrated schemata: 1

  Schema Name:            bug16384_dbo
  - Tables:               1
  - Views:                0
  - Routines:             0
  - Routine Groups:       0
  - Synonyms:             0
  - Structured Types:     0
  - Sequences:            0

  Details:

  - Tables
      `bug16384_dbo`.`Families`
      -------------------------
  - Views
  - Routines
  - Routine Groups
  - Synonyms
  - Structured Types
  - Sequences

2. Data Bulk Transfer
---------------------

      `bug16384_dbo`.`Families`
      -------------------------
          0 row(s) transfered.

End of report.

C:\mysql\bin>mysql -uroot bug16384_dbo
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16 to server version: 5.0.19-nt

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

mysql> show create table families;
+----------+-------------------------------------------------------------------------
-------------------------------------------------------------------------------------
----------------------------------------------------------------+
| Table    | Create Table

                                                                |
+----------+-------------------------------------------------------------------------
-------------------------------------------------------------------------------------
----------------------------------------------------------------+
| families | CREATE TABLE `families` (
  `FamilyID` int(10) NOT NULL,
  `FamilyName` varchar(255) NOT NULL,
  `FamilyDescription` longtext,
  `Manufacturer` varchar(64) default NULL,
  `DisplayStyle` varchar(55) default NULL,
  `Archived` int(10) NOT NULL,
  PRIMARY KEY  (`FamilyID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+-------------------------------------------------------------------------
-------------------------------------------------------------------------------------
----------------------------------------------------------------+
1 row in set (0.02 sec)

The problem seems to be isolated to SQL 7.  The same table migrated from SQL 2000 maps the column to the LONGTEXT datatype.