Bug #86315 Workbench can't migrate table if generated column is located before json column

• Submitted: 15 May 2017 1:01
• Modified: 15 May 2017 9:59
• Reporter: Shinya Sugiyama
• Status: Verified
• Category: MySQL Workbench: Migration
• Severity: S3 (Non-critical)
• Version: 6.3.9
• OS: Any
• CPU Architecture: Any
• Tags: json, migration wizerd, workbench

[15 May 2017 1:01] Shinya Sugiyama

Description:
If we put generated column before json data type, migration wizard will be fail.

Error Code
------------------

ERROR: Error executing 'CREATE TABLE IF NOT EXISTS `ocode`.`T_JSON_CONST` (
      `id` INT(4) GENERATED ALWAYS AS (json_extract(`doc`,'$.id')) STORED)'
    Unknown column 'doc' in 'generated column function'.
    SQL Error: 1054

How to repeat:
Simply migrate tables by using migration wizard with Workbench.

1) Table Migration working fine with table definition

CREATE TABLE `T_JSON_CHK` (
  `doc` json DEFAULT NULL,
  `id` int(4) GENERATED ALWAYS AS (json_extract(`doc`,'$.id')) STORED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

2) Table Migration is failed with table definition

CREATE TABLE `T_JSON_CONST` (
  `id` int(4) GENERATED ALWAYS AS (json_extract(`doc`,'$.id')) STORED NOT NULL,
  `doc` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Migrate those tables from Server "A" to Server "B".

Suggested fix:
We can use mysqldump, mysqlbackup and other procedure; however, it should be also work with MySQL Workbench Migration wizard.

[15 May 2017 1:02] Shinya Sugiyama

Screen Capture of wizard

Attachment: workbench_check.PNG (image/png, text), 45.14 KiB.

[15 May 2017 9:59] MySQL Verification Team

Thank you for the bug report. Verified as described.