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
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