MySQL Bugs: #72650: VARBINARY translation from MSSQL 2005 to MySQL does not work.

Bug #72650	VARBINARY translation from MSSQL 2005 to MySQL does not work.
Submitted:	14 May 2014 15:32	Modified:	26 Aug 2014 5:50
Reporter:	Lewis Tanzos	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Migration	Severity:	S1 (Critical)
Version:	6.1.4	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	varbinary

Description:
This is possibly a duplicate of bug 68007, but that's marked "can't duplicate"

Migrating tables with timestamps doesn't work correctly for me.

Running SQL Server 2005 SP2 on Windows 2003/32 SP2

The full version of Workbench is "6.1.4.11773 build 1454"

The Notes table is defined this way on the MSSQL side (transcribed from Microsoft SQL Server Management Studio):
     EmployeeID (int, null),
     Date (datetime, null),
     Note (ntext, null),
     UniqueID (PK, int, not null),
     upsize_ts (timestamp, null)

I am aware that upsize_ts field comes from the Microsoft Upsizing Migration Wizard and why it is there.

The table in question is defined this way on the MySQL side by the migration script:
CREATE TABLE IF NOT EXISTS `CompanySQL`.`Notes` (
  `EmployeeID` INT NULL DEFAULT 0,
  `Date` DATETIME NULL,
  `Note` LONGTEXT NULL,
  `UniqueID` INT NOT NULL AUTO_INCREMENT,
  `upsize_ts` VARBINARY(8) NULL,
  PRIMARY KEY (`UniqueID`),
  INDEX `EmployeeID` (`EmployeeID` ASC),
  INDEX `UniqueID` (`UniqueID` ASC));

The error message that I get during migration for this table:

ERROR: `CompanySQL`.`Notes`:SQLExecDirect(SELECT [EmployeeID], [Date], [Note], [UniqueID], VARBINARY as [upsize_ts] FROM [CompanySQL].[dbo].[Notes]): 42S22:1207:[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'VARBINARY'.

A similar error appears for each table with the upsize_ts field.

How to repeat:
It happens reliably for every table that includes one of these fields.

Suggested fix:
I'm thinking that the phrase "VARBINARY as" shouldn't be in that select.

Thank you for the bug report. Please provide the SQL Server create table command (a test case) which fails to be immigrated?. Thanks.

Typed in SQLCMD:
 use TestDatabase
 create table test ( junk int, upsize_ts timestamp null);
 insert into test (junk) values (1);
 insert into test (junk) values (2);
 insert into test (junk) values (3);
 go

Migration returns this error:

Error: `TestDatabase`.`Test`:SQLExecDirect(SELECT[junk],VARBINARY as [upsize_ts] FROM [TestDatabase].[dbo].[junk]): 42522:1207[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'VARBINARY'.

Note that if you create the upsize_ts filed as "not null", it works.

Thank you for the feedback.

Posted by developer:
 
Fixed

Posted by developer:
 
Fixed

Bug fix patch

Attachment: bug_72650.diff (text/x-patch), 1.56 KiB.

I attach a patch that fixes this bug. You can apply it by following simple command executed in <WB inst dir>/modules/db.mssql/ directory: 
patch db_mssql_migration_grt.py < bug_72650.diff

Posted by developer:
 
fix confirmed in 6.2.2

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

Migrating SQL Server 2005 tables with timestamps would not import the
timestamp column data.

Thank you for the bug report.