MySQL Bugs: #94066: MySQL Workbench Migration Treats MSSQL UUID field as UNIQUE

Bug #94066	MySQL Workbench Migration Treats MSSQL UUID field as UNIQUE
Submitted:	25 Jan 2019 17:42	Modified:	19 Nov 2019 19:10
Reporter:	Mike Mallett	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Migration	Severity:	S2 (Serious)
Version:	8.0.14/8.0.15	OS:	Windows (Windows Server 2012 R2)
Assigned to:		CPU Architecture:	x86 (64 bit)

Description:
I am unable to complete a MSSQL to MySQL Migration and I believe this si caused by a misinterpretation of Source Schema on the part of MySQL Workbench.

Since MSSQL and MySQL schema do not have complete 1:1 mappings, it seems someone decided that the MSSQL cdata type called "UNIQUEIDENTIFIER" should become in MySQL a column of type "VARCHAR(64) UNIQUE".

This is incorrect.

MSSSQL UNIQUEIDENTIFIER is a data type for holding UUIDs (or "GUIDs" in MS-speak). There is nothing unique about data in the source table - It's just a name for describing a UUID column.

When this creates Target MySQL schema of type "VARCHAR(64) UNIQUE" suddenly MySQL cannot hold all the source data any longer.

In my case the column is being used for a "User ID". Making the Target column UNIQUE means that only one record belonging to each User ID can be inserted. All subsequent records belonging to that User ID fail, because the Target column is UNIQUE.

UNIQUEIDENTIFIER == UUID != UNIQUE

How to repeat:
1. Create a MSSQL column of type UNIQUEIDENTIFIER.

2. Insert two records with the same UUID into that column. This is valid since UNIQUEIDENTIFIER is just a UUID field.

3. Use MySQL Workbench Migration Wizard to migrate this database to MySQL.

4. Target Schema cannot hold both records, because the target schema marked the equivalent column as UNIQUE in MySQL.

Suggested fix:
Don't make the target column UNIQUE just because that word happened to appear in the MSSQL DB; It means something different there.

VARCHAR(64) should be fine by itself.

Thank you for the bug report. Please provide the SQL script to create the table test case for SQL Server. Thanks.

I don't know the first thing about creating MS SQL Server anythings.

That is why I am attempting to use the MySQL Workbench application - so I can convert an old MS SQL Server db to MySQL instead.

@Miguel Solorzano I'm not sure what to provide beyond the description I've given. I am attempting to migrate a MS SQL Server database into MySQL and hitting a "Duplicate Key" issue (MySQL) for every single "UNIQUEIDENTIFIER" Column (SQL Server).

Asking the SQL Server sql script to try to repeat the issue you are having, using the WorkBench Migration, otherwise how I could verify it?. I already checked Migration report issue in such way.

To be clear I want to create the SQL Server table you have to try to migrate it to MySQL table using WorkBench.

Well, I have a bunch of confidential data in my database so posting it to this bug tracker wouldn't be very appropriate.

I believe you will be able to reproduce this issue as follows:

1. Make a new SQL Server table with a Column of type UNIQUEIDENTIFIER.
2. Put two records into this table with the same UUID in the Column made in Step 1. This is a legal move, since there should be no UNIQUE constraint on this Column.
3. Run MySQL Workbench Migration Wizard to move this db to MySQL.
4. Allow the Workbench tool to perform the step "Reverse Engineer Schema".
5. When you reach the "Manual Editing" step, see that Workbench intends to create an equivalent Column of type "VARCHAR(64) UNIQUE" to hold data from Step 2.

This will fail, since the UNIQUE constraint on the Target DB is incorrect.

Thank you for the feedback.

Posted by developer:
 
commit it: 13c060cff85c846a634b44c7eb7838ee9ff6710a

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 8.0.19 release, and here's the changelog entry:

The uniqueidentifier data type in a Microsoft SQL Server source table was
interpreted during migration as type VARCHAR(64) UNIQUE in the MySQL
target column, instead of a VARCHAR column to represent a GUID (UUID) that
is not unique.

Thank you for the bug report.