Bug #84519 Workbench Migration fails to generate the correct create table statement
Submitted: 16 Jan 2017 17:51 Modified: 27 Mar 2018 23:54
Reporter: John More Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: Migration Severity:S2 (Serious)
Version:6.3.8 OS:Ubuntu ("Ubuntu 14.04.3 LTS")
Assigned to: CPU Architecture:Any

[16 Jan 2017 17:51] John More
Description:
Migration generates the following create statement.
    CREATE TABLE IF NOT EXISTS `DevicesTelemetry`.`TMAlerts` (
      `deviceId` BINARY(16) NOT NULL DEFAULT ',
      `deviceIdText` VARCHAR(36) GENERATED ALWAYS AS (insert(insert(insert(insert(hex(`deviceId`),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')) VIRTUAL,
      `rtuID` BINARY(16) NULL DEFAULT NULL,
      `rtuIdText` VARCHAR(36) GENERATED ALWAYS AS (insert(insert(insert(insert(hex(`rtuID`),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')) VIRTUAL,
      `clientId` BINARY(16) NULL DEFAULT NULL,
      `clientIdText` VARCHAR(36) GENERATED ALWAYS AS (insert(insert(insert(insert(hex(`clientId`),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')) VIRTUAL,
      `depotId` BINARY(16) NULL DEFAULT NULL,
      `depotIdText` VARCHAR(36) GENERATED ALWAYS AS (insert(insert(insert(insert(hex(`depotId`),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')) VIRTUAL,
      `customerLink` BINARY(16) NULL DEFAULT NULL,
      `customerLinkText` VARCHAR(36) GENERATED ALWAYS AS (insert(insert(insert(insert(hex(`customerLink`),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')) VIRTUAL,
      `timestamp` DATETIME NULL DEFAULT NULL,
      `sequenceNumber` BIGINT(20) NULL DEFAULT NULL,
      `eventGenerator` VARCHAR(25) NULL DEFAULT NULL,
      `eventDescription` VARCHAR(25) NULL DEFAULT NULL,
      `eventValue` VARCHAR(25) NULL DEFAULT NULL)
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8

Workbench show create generates a different and correct statement as follows:
CREATE TABLE `TMAlerts` (
  `deviceId` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `deviceIdText` varchar(36) GENERATED ALWAYS AS (insert(insert(insert(insert(hex(`deviceId`),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')) VIRTUAL,
  `rtuID` binary(16) DEFAULT NULL,
  `rtuIdText` varchar(36) GENERATED ALWAYS AS (insert(insert(insert(insert(hex(`rtuID`),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')) VIRTUAL,
  `clientId` binary(16) DEFAULT NULL,
  `clientIdText` varchar(36) GENERATED ALWAYS AS (insert(insert(insert(insert(hex(`clientId`),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')) VIRTUAL,
  `depotId` binary(16) DEFAULT NULL,
  `depotIdText` varchar(36) GENERATED ALWAYS AS (insert(insert(insert(insert(hex(`depotId`),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')) VIRTUAL,
  `customerLink` binary(16) DEFAULT NULL,
  `customerLinkText` varchar(36) GENERATED ALWAYS AS (insert(insert(insert(insert(hex(`customerLink`),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')) VIRTUAL,
  `timestamp` datetime DEFAULT NULL,
  `sequenceNumber` bigint(20) DEFAULT NULL,
  `eventGenerator` varchar(25) DEFAULT NULL,
  `eventDescription` varchar(25) DEFAULT NULL,
  `eventValue` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

THE PROBLEM IS IN THE NOT NULL OF THE deviceId field.

How to repeat:
Create a table with the good definition above and try to migrate to another database.

Suggested fix:
Complete the definition of the binary field when it is NOT NULL
[16 Jan 2017 17:53] John More
More Detailed info on system..

MySQL Workbench Community (GPL) for Linux/Unix version 6.3.6 CE build 517 (64 bit)
Configuration Directory: /home/john/.mysql/workbench
Data Directory: /usr/share/mysql-workbench
Cairo Version: 1.13.1
OS: Linux 3.13.0-24-generic
CPU: 8x Intel(R) Xeon(R) CPU           W3520  @ 2.67GHz (1596.000MHz) - 11.73GiB RAM
Distribution: Linux Mint 17.3 Rosa
[16 Jan 2017 21:36] MySQL Verification Team
Thank you for the bug report. Verified on Windows 10 too.
[27 Mar 2018 23:54] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 8.0.11 release, and here's the changelog entry:

Migrating a table to a database generated an incomplete definition for a
binary field that was NOT NULL.

Thank you for the bug report.