Bug #71253 Data Migration Failure: VARCHAR(-1)
Submitted: 30 Dec 2013 18:23 Modified: 13 Mar 2014 5:49
Reporter: Matthew Sanders Email Updates:
Status: Closed Impact on me:
Category:MySQL Workbench: Migration Severity:S2 (Serious)
Version:6.08.11354 OS:Any
Assigned to: CPU Architecture:Any
Tags: data, migration, MSSQL, MySQL

[30 Dec 2013 18:23] Matthew Sanders
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]):
[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:
[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))