Bug #71253 Data Migration Failure: VARCHAR(-1)

• Submitted: 30 Dec 2013 18:23
• Modified: 13 Mar 2014 5:49
• Reporter: Matthew Sanders
• Status: Closed
• Category: MySQL Workbench: Migration
• Severity: S2 (Serious)
• Version: 6.08.11354
• OS: Any
• CPU Architecture: Any
• Tags: data, migration, MSSQL, MySQL

[30 Dec 2013 18:23] Matthew Sanders

Description:
Regarding schema migration this bug was fixed with #67289, but data migration still returns this error.

According to the users in #67289, VARCHAR(MAX) from a MSSQL database reads as VARCHAR(-1) and fails.

Migrating data is half of the operation of migrating from one DB to another.

= = =

CAST([picClientEndorseXMLData] as NVARCHAR(-1)) as [picClientEndorseXMLData]
FROM [dbUPIC].[dbo].[tblPIC_ClientEndorsements]):
42000:1102:
[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '-'.

How to repeat:
Have a column in MSSQL that is type "VARCHAR(MAX)" and try to migrate data from it to a target MySQL database.

Suggested fix:
During data migration, if the source column type is "VARCHAR(MAX)" make sure that it does not read it as "VARCHAR(-1)".

[20 Jan 2014 16:01] Milosz Bodzek

confirm, i can repeat.

[13 Feb 2014 12:49] Milosz Bodzek

Posted by developer:
 
Fixed

[13 Feb 2014 12:56] Milosz Bodzek

Patch whitch fixes this bug

Attachment: cast_to_nvarchar_max.diif (application/octet-stream, text), 1.27 KiB.

[15 Feb 2014 16:10] MySQL Verification Team

bugs.mysql.com/bug.php?id=7172 marked as duplicate of this one

[15 Feb 2014 16:12] MySQL Verification Team

http://bugs.mysql.com/bug.php?id=71728 the correct duplicate #.

[13 Mar 2014 5:49] Philip Olson

Fixed as of the upcoming MySQL Workbench 6.1.3 release, and here's the changelog entry:

When migrating MySQL SQL Server, "Varchar(MAX)" is now imported as
"LONGTEXT", instead of the erroneous "VARCHAR(-1)".

Thank you for the bug report.

[13 Mar 2017 16:03] Seb Seb

The issue can be resolved by changing db_mssql_migration_grt.py file:

Line 221:
+ 		    if target_column.length == -1:
+ 			type_cast_expression = "CAST(? as NVARCHAR(MAX))