MySQL Forums
Forum List  »  MySQL Workbench

Migration Wizard uses INDEX..... VISIBLE statement, resulting in CREATETABLE error
Posted by: Stuart Keasley
Date: March 14, 2019 08:45AM

First time posting here, be kind :-)

I'm experiencing some problems with the Migration Wizard, migrating from an MS SQL database to MYSQL Database.

The schema creation fails on a number of tables, drilling down to detail, this is because the generated script appends VISIBLE to the end of INDEX commands eg:


CREATE TABLE IF NOT EXISTS `dev_db`.`TABLE_ITEMS` (
`ITEM_ID1` VARCHAR(15) NOT NULL,
`ITEM_ID2` VARCHAR(15) NULL,
`FIELD_INDEX` INT NULL,
`FIELD_VALUE` VARCHAR(255) NULL,
PRIMARY KEY (`ITEM_ID1`),
INDEX `ITEM01` (`FIELD_INDEX` ASC, `FIELD_VALUE`(255) ASC) VISIBLE)

The MySQL database I need to migrate to is running MariaDB, serverversion reported as follows under Advanced in connection properties:

serverVersion=10.2.22-MariaDB-log-cll-lve

I tried a test migration to a different MySQL server running on Ubuntu, serverversion reported as:

serverVersion=5.7.25-0ubuntu0.18.04.2

In that instance, the script generator omited the VISIBLE statement from the INDEX command, ie the CREATE query became;

CREATE TABLE IF NOT EXISTS `dev_db`.`TABLE_ITEMS` (
`ITEM_ID1` VARCHAR(15) NOT NULL,
`ITEM_ID2` VARCHAR(15) NULL,
`FIELD_INDEX` INT NULL,
`FIELD_VALUE` VARCHAR(255) NULL,
PRIMARY KEY (`ITEM_ID1`),
INDEX `ITEM01` (`FIELD_INDEX` ASC, `FIELD_VALUE`(255) ASC))


Default MySQL Version is set to 5.7.25 within preferences on MYSQL WorkBench, so it would seem that the reported version (10.2.22) overrides this, and pushes/defaults the script generation to Version 8 syntax.

The work around for me was fairly simply, i.e.

create and save an SQLScript whilst connected to the test 5.7 MySQL server
run the script on the on the target MariaDB server
run the migration wizard, deselecting create script and create schema, effectively bypassing this section and moving straight on to data migration populating the existing tables.

However it would be good to know if there was a way to configure or correct the root problem, I'd like to simplify the migration process as much as possible (it may need to be repeated by others!).

Options: ReplyQuote


Subject
Views
Written By
Posted
Migration Wizard uses INDEX..... VISIBLE statement, resulting in CREATETABLE error
1753
March 14, 2019 08:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.