MySQL Bugs: #30486: SQL Server migration yields empty table with no errors for IDENTITY key=0

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:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	Auto-increment, IDENTITY

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.

Thank you for the bug report.

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.

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:

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.