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:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: varbinary

[14 May 2014 15:32] Lewis Tanzos
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.
[14 May 2014 16:32] Miguel Solorzano
Thank you for the bug report. Please provide the SQL Server create table command (a test case) which fails to be immigrated?. Thanks.
[14 May 2014 17:59] Lewis Tanzos
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.
[14 May 2014 20:18] Miguel Solorzano
Thank you for the feedback.
[15 May 2014 17:47] Milosz Bodzek
Posted by developer:
 
Fixed
[8 Aug 2014 14:58] Milosz Bodzek
Posted by developer:
 
Fixed
[8 Aug 2014 15:15] Milosz Bodzek
Bug fix patch

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

[8 Aug 2014 15:16] Milosz Bodzek
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
[22 Aug 2014 18:30] Johannes Taxacher
Posted by developer:
 
fix confirmed in 6.2.2
[26 Aug 2014 5:50] Philip Olson
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.