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:Microsoft Windows (Windows Server 2012 R2)
Assigned to: CPU Architecture:x86 (64 bit)

[25 Jan 2019 17:42] Mike Mallett
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.
[26 Jan 2019 13:37] Miguel Solorzano
Thank you for the bug report. Please provide the SQL script to create the table test case for SQL Server. Thanks.
[11 Feb 2019 21:08] Mike Mallett
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).
[11 Feb 2019 21:22] Miguel Solorzano
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.
[11 Feb 2019 21:24] Miguel Solorzano
To be clear I want to create the SQL Server table you have to try to migrate it to MySQL table using WorkBench.
[11 Feb 2019 21:34] Mike Mallett
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.
[14 Feb 2019 13:51] Miguel Solorzano
Thank you for the feedback.
[19 Nov 2019 15:59] Miguel Tadeu
Posted by developer:
 
commit it: 13c060cff85c846a634b44c7eb7838ee9ff6710a
[19 Nov 2019 19:10] Christine Cole
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.