MySQL Bugs: #67289: Migration from MSSQL to MySQL fails for data type 'nvarchar(max)'

Bug #67289	Migration from MSSQL to MySQL fails for data type 'nvarchar(max)'
Submitted:	18 Oct 2012 15:46	Modified:	28 Nov 2012 1:24
Reporter:	Emo Mosley	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Migration	Severity:	S3 (Non-critical)
Version:	5.2.44	OS:	Windows (Microsoft Windows 7 Service Pack 1 (build 7601), 64-bit)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
When migrating a database from a local copy of MSSQL Express via ODBC to a MySQL server, if a data type of 'nvarchar(max)' is encountered, the table creation is formed using 'varchar' with no size parameter, resulting in a syntax error upon table creation at the destination:

WARNING: Error executing 'CREATE  TABLE IF NOT EXISTS `MigTest`.`migTable` (
  `bigText` VARCHAR NULL )
COLLATE = utf8_general_ci'
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL )

----- Developer Notes -----

MySQL Workbench CE for Windows version 5.2.44  revision 9933
Configuration Directory: C:\Users\emo\AppData\Roaming\MySQL\Workbench
Data Directory: C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE
Cairo Version: 1.8.8
OS: Microsoft Windows 7  Service Pack 1 (build 7601), 64-bit
CPU: 8x Intel(R) Core(TM) i7 CPU         930  @ 2.80GHz, 8.0 GiB RAM
Active video adapter (0): NVIDIA GeForce 8400GS
Installed video RAM: 512 MB
Current video mode: 1280 x 1024 x 4294967296 colors
Used bit depth: 32
Driver version: 8.17.12.9610
Installed display drivers: nvd3dumx.dll,nvwgf2umx.dll,nvwgf2umx.dll,nvd3dum,nvwgf2um,nvwgf2um
Current user language: English (United States)

How to repeat:
Using Microsoft SQL Express (free download from Microsoft - MS SQL Server Management Studio), create a test database 'MigTest'.  Create a table 'migTable' with a single field named 'bigText' with data type 'nvarchar(max)'.  Insert a value into the field, such as 'this is a test'.

Create an ODBC instance to allow access to the MSSQL database.

Using MySQL Workbench, use the Migration tool, using the ODBC as the source, and an available MySQL server as the destination.  Proceed to use the default settings to attempt to migrate the data.

Migration will fail with a "SQL syntax" error during the Create Schemata step.

Thank you for the bug report.

+1. I have identical OS and Workbench versions.

Further to this, I attempted to resolve the problem by adding a data type mapping (figuring this is exactly what this is for). 
therough the menu "Database/Edit Type Mappings for Generic Database"
I added one, tried the migration again and this failed.

I went back in the the data type mapping form in to remove what I had added - It had disappeared from the list. 

I then tried to delete any of the existing String values already there. The form says they are gone, but close and open the form again and they're back again.

Seems you can't add a mapping to map VARCHAR(MAX) to TEXT (which is what I was attempting to achieve, to resolve the reported problem).

Probably a separate problem, I know, but it may be related, so I've added it here.

Hi guys:

Thank you very much for reporting this issue. The problem behind this is that the query for the column lenghts returns -1 in such cases. This value wasn't been handled the right way.

I'm attaching a patch with a quick fix that you can apply to the db_mssql_migration_grt.py file. Otherwise just wait for the next Workbench release.

Apply this patch to db_mssql_migration_grt.py and restart Workbench to fix this issue.

Attachment: patch_bug_67289.diff (text/x-patch), 1.40 KiB.

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

Migrating a Microsoft SQL Server database would fail if "nvarchar(max)"
was used. These are now converted to "LONGTEXT".

Thank you for the bug report.

It appears that migrating the schema has been fixed, but I'm experiencing this issue when migrating data (I assume the -1 being returned for varchar(max)'s length is the issue), it just needs to be handled somewhere else as well.

ERROR: `SMI_ODS`.`ErrorLog`:SQLExecDirect(SELECT [ixError], CAST([sOriginFile] as NVARCHAR(50)) as [sOriginFile], CAST([sErrorText] as NVARCHAR(-1)) as [sErrorText], [dtErrorLogTime], [iSeverityLevel], CAST([sStackTrace] as NVARCHAR(-1)) as [sStackTrace] FROM [SMI_ODS].[dbo].[ErrorLog]): 42000:1102:[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '-'.

Migrating schema has been fixed, but migrating data still does not work and is what I actually need. The severity of this bug should be raised as half of the operation fails.

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 '-'.

Can we get an ETA on fixing this bug?

I can confirm this bug still exist with MySQL Workbench 6.0.8 and MySQL Workbench 6.1.1 beta. Tested with SQL Server 2005 and mysql-5.6.13-winx64.

Does someone have an alternative to import data from SQL Server?

Thank you for your report. Colud you check on version 6.1.4 this bug still exists?

we are using 8.0.33 workbench version, when we tried to migrate from ms sql server, we are facing data type of 'nvarchar(max)' or 'varchar(max)' is encountered , the table creation is formed using 'varchar' with no size parameter and it is returning varchar(0), resulting in a syntax error upon table creation at the destination.