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