Description:
Reverse engineered database using MySQL Workbench using "Database, Reverse Engineer" misses NOT NULL attribute for generated column.
The column appears correctly in a script generated by mysqldump.
How to repeat:
CREATE TABLE `generated` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`gen` CHAR(32) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (md5('This is a bug')) STORED NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Using MySQL Workbench, connect to database as root, then use Database, Reverse Engineer. Complete the steps, select table in a EER diagram, use "Copy SQL to Clipboard," paste the text into text editor or browser field. It appears as below, and the " NOT NULL" attribute on the "gen" column is missing:
CREATE TABLE IF NOT EXISTS `bug_report`.`generated` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`gen` CHAR(32) GENERATED ALWAYS AS (md5('This is a bug')) STORED,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci
Additionally, right click on the table in EER diagram, select "Edit" and observe unchecked NN checkbox for the "gen" column.
The table script obtained using mysqldump appears as following:
DROP TABLE IF EXISTS `generated`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `generated` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`gen` char(32) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (md5('This is a bug')) STORED NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
Description: Reverse engineered database using MySQL Workbench using "Database, Reverse Engineer" misses NOT NULL attribute for generated column. The column appears correctly in a script generated by mysqldump. How to repeat: CREATE TABLE `generated` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `gen` CHAR(32) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (md5('This is a bug')) STORED NOT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; Using MySQL Workbench, connect to database as root, then use Database, Reverse Engineer. Complete the steps, select table in a EER diagram, use "Copy SQL to Clipboard," paste the text into text editor or browser field. It appears as below, and the " NOT NULL" attribute on the "gen" column is missing: CREATE TABLE IF NOT EXISTS `bug_report`.`generated` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `gen` CHAR(32) GENERATED ALWAYS AS (md5('This is a bug')) STORED, PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci Additionally, right click on the table in EER diagram, select "Edit" and observe unchecked NN checkbox for the "gen" column. The table script obtained using mysqldump appears as following: DROP TABLE IF EXISTS `generated`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `generated` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `gen` char(32) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (md5('This is a bug')) STORED NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */;