MySQL Bugs: #90795: Migration of BIT data from MSSQL to MySQL

Bug #90795	Migration of BIT data from MSSQL to MySQL
Submitted:	9 May 2018 1:23	Modified:	9 Jun 2018 8:22
Reporter:	Gavin Lambert	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench: Migration	Severity:	S2 (Serious)
Version:	6.3.9	OS:	Windows (8.1)
Assigned to:		CPU Architecture:	x86 (64-bit)

Description:
I have a third-party tool that can use an external database provided either by MSSQL or MySQL. It can generate the expected schema for each. I have an existing MSSQL database that contains data I care about, and have recently generated an otherwise empty MySQL database using the app-provided schema.

I want to migrate the data from the MSSQL database into the matching tables in the MySQL database. I am trying to use the Migration wizard in MySQL Workbench to do so. During the schema transfer step I am choosing to leave the existing schema alone, as it is already in the format that the application wants.

However during the data transfer part of the migration, I get failures like this (affecting several tables):

12:05:21 [INF][ copytable]: Truncating table `boi`.`report_entry`

ERROR: `boi`.`report_entry`:Type mismatch fetching field 2 (should be char, was MYSQL_TYPE_BIT)
ERROR: `boi`.`report_entry`:Failed copying 96 rows

The columns in the MSSQL tables are defined as type BIT. The columns in the MySQL tables are defined as type bit(1). This ought to be compatible.

How to repeat:
Define tables with BIT columns as above; try migrating data.

If I go back and look at the "Manual Editing" step, it appears that it has decided that the BIT columns should be converted to TINYINT(1), which of course doesn't actually happen since I am trying to keep the existing schema.

I tried changing this back to BIT manually on that page, but it didn't help.

Suggested fix:
It should be able to migrate BIT column data.

The process still fails even if I do allow it to create the schema as it wishes, although for a different reason; I get corrupted log output like the below:

...
13:39:24 [DB1][      copytable]: Executing queryPROGRESS:`boi`.`report_entry`:96:96

: SELECT END:`boi`.`report_entry`:Finished copying 96 rows in 0m11s
...
- `boi`.`report_entry` has FAILED (0 of 96 rows copied)

The corrupted logging is I assume the result of logging from multiple threads or processes into the same output without proper synchronisation.

I don't know what the actual error is as it does not appear to be logged.  Unless the interleaving is also a cause.

So I tried reducing the number of worker tasks to 1.  This succeeded and all data was copied, although I still have the wrong schema, and I'm not sure what to do about that.

FWIW I finally managed to get the data into the right schema, though it took some finagling.

I first had to migrate to a separate newly created database as above, then export "data-only" to an sql file, then manually edit the file to correct some of the table names (the migration had lowercased them all, while a couple of the "real" tables had all-uppercase names, and it wouldn't import until this was fixed), and then finally import that script to the "real" database.

I am not 100% certain yet that this has imported all the data correctly, but it at least executed without errors (even on those BIT fields, which again suggests that the migration changing their type was unnecessary).

Thank you for the bug report. Please provide the SQL Server create table statement which fails to be migrated. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".