Bug #30486 SQL Server migration yields empty table with no errors for IDENTITY key=0
Submitted: 17 Aug 2007 22:02 Modified: 26 Aug 2014 5:31
Reporter: Martin Kultermann Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:1.2.12 OS:Microsoft Windows (XP)
Assigned to: CPU Architecture:Any
Tags: Auto-increment, IDENTITY
Triage: Triaged: D3 (Medium) / R3 (Medium) / E3 (Medium)

[17 Aug 2007 22:02] Martin Kultermann
Description:
Migration from SQL Server to MySQL runs successfully reporting no errors but leaves certain tables empty.

The tables that fail to migrate have an Identity (auto-increment) column that contains a row with a value of 0 and a row with a value of 1. The migration inserts the 0 row with a value of 1 and the next insert that specifies the value of 1 then fails as a duplicate entry.

This should result in an error message in the migration.

How to repeat:
Create a SQL Server table with an Identity column and insert 2 rows into it with the SET IDENTITY_INSERT ON settings as follows:

CREATE TABLE idtest
(id int IDENTITY PRIMARY KEY, 
 something_else varchar(10))
GO
SET IDENTITY_INSERT idtest ON
GO
INSERT INTO idtest (id, something_else) VALUES (0, 'whatever')
GO
INSERT INTO idtest (id, something_else) VALUES (1, 'whatever')
GO
SET IDENTITY_INSERT idtest OFF
GO

This data in this table should fail to migrate without error messages.

Suggested fix:
Trap the "duplicate entry 'n' for key 1" error and report it as other errors are.
[20 Aug 2007 11:56] Miguel Solorzano
Thank you for the bug report.
[22 Jul 2014 21:17] Alfredo Kojima
Posted by developer:
 
The Workbench migration wizard was fixed so that SQL_MODE=NO_AUTO_VALUE_ON_ZERO is set before copying the data, which will cause 0 auto_increment field values to be copied as 0.
[22 Aug 2014 17:59] Johannes Taxacher
Posted by developer:
 
fix confirmed in 6.2.2
[26 Aug 2014 5:31] Philip Olson
Fixed as of the upcoming MySQL Workbench 6.2.2 release, and here's the changelog entry:

The migration wizard now sets "SQL_MODE=NO_AUTO_VALUE_ON_ZERO" before
copying data, which causes auto_increment field values of 0 to be copied
as 0.

Thank you for the bug report.