| Bug #99620 | Synchonize Model generates in SQL statements "ZEROFILL" twice | ||
|---|---|---|---|
| Submitted: | 18 May 2020 20:50 | Modified: | 9 Jun 2020 12:38 |
| Reporter: | Dominik Chvíla | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Workbench: Modeling | Severity: | S2 (Serious) |
| Version: | 8.0.20 | OS: | Windows (Microsoft Windows 10 Pro) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | WBBugReporter | ||
[9 Jun 2020 12:38]
MySQL Verification Team
Hello Dominik Chvíla, Thank you for the bug report. I tried to reproduce your issue on windows 10 with workbench 8.0.20 using dummy table and followed exact mentioned steps but I am not seeing any issues at my end. Regards, Ashwini Patil
[23 Aug 2020 18:20]
Brian Plunkett
This seems to be related to the issue where a User Defined Type has a flag set which duplicates the flag selected for the table in which the User Defined Type is applied. If my suspicions are correct, you might want to remove the setting from the table so that it only applies to the User Defined Type. This would allow you to appropriately replicate that type anywhere you wanted to use it. Example:... User Defined Type: `MyType` := INT(10) ZEROFILL Table Definition: CREATE TABLE `MyTable` (id `MyType` ZEROFILL)... Compiled as:... CREATE TABLE `MyTable` (id INT(10) ZEROFILL ZEROFILL)... Technique Correction:... User Defined Type: `MyType` := INT(10) ZEROFILL Table Definition: CREATE TABLE `MyTable` (id `MyType`)... Compiled as:... CREATE TABLE `MyTable` (id INT(10) ZEROFILL)... NOTE: The above code segments are *not* functional code, but shows you what might be happening in the macro expansive language constructs.
[23 Aug 2020 18:39]
Brian Plunkett
Additionally, the syntax of the following line illustrates the dependency bug which I was describing in my last comment. Sampled Code: `id_customer_invoice_data` INT(10) ZEROFILL ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT Code Indications: User Defined Type: `SomeType` := INT(10) ZEROFILL Table Attributes Set: `SomeType` ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT WARNING: The 'INT(10)' & 'UNSIGNED' type definitions conflict, and the parser couldn't resolve the conflict automatically based upon the present dependency matrix. Design Flaw: The dependency matrix should be such that the table definition inherits from type definitions, or the table definition exclusively overrides the type definition. This would also make the debugging & bug resolution process easier. Summary: If you're using User Defined Types, the only attributes which should be set at table level are those which are specific to table definitions only (i.e. key, index, and value generation attributes).
Description: When synchronizing Model with empty database (or updating the changes), SQL is wrongly generated. For columns, where ZEROFILL is defined, this definition is generated twice "`id_customer_invoice_data` INT(10) ZEROFILL ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT,", part of log attached: 21:36:50 [INF][ grt]: Fetching schema list. 21:36:50 [INF][ grt]: OK 21:36:52 [INF][ grt]: Fetching table list. 21:36:52 [INF][ grt]: 0 items from shop 21:36:52 [INF][ grt]: OK 21:36:52 [INF][ grt]: Fetching view list. 21:36:52 [INF][ grt]: 0 items from shop 21:36:52 [INF][ grt]: OK 21:36:52 [INF][ grt]: Fetching routine list. 21:36:52 [INF][ grt]: 0 items from shop 21:36:52 [INF][ grt]: OK 21:36:52 [INF][ grt]: Fetching trigger list. 21:36:52 [INF][ grt]: 0 items from shop 21:36:52 [INF][ grt]: OK 21:36:52 [INF][ grt_diff]: No sync profile found for Mysql@localhost:33063::shop 21:37:08 [INF][ grt]: Fetching schema list. 21:37:08 [INF][ grt]: OK 21:37:10 [INF][ grt]: Fetching table list. 21:37:10 [INF][ grt]: 30 items from shop 21:37:10 [INF][ grt]: OK 21:37:10 [INF][ grt]: Fetching view list. 21:37:10 [INF][ grt]: 0 items from shop 21:37:10 [INF][ grt]: OK 21:37:10 [INF][ grt]: Fetching routine list. 21:37:10 [INF][ grt]: 0 items from shop 21:37:10 [INF][ grt]: OK 21:37:10 [INF][ grt]: Fetching trigger list. 21:37:10 [INF][ grt]: 0 items from shop 21:37:10 [INF][ grt]: OK 21:37:11 [INF][ grt_diff]: Restoring oldNames and other sync state info for Mysql@localhost:33060::shop (catalog {63B36724-11CE-48EA-BE2D-3731CDF4730C}) 22:01:35 [INF][ grt]: Fetching schema list. 22:01:35 [INF][ grt]: OK 22:01:37 [INF][ grt]: Fetching table list. 22:01:37 [INF][ grt]: 0 items from shop 22:01:37 [INF][ grt]: OK 22:01:37 [INF][ grt]: Fetching view list. 22:01:37 [INF][ grt]: 0 items from shop 22:01:37 [INF][ grt]: OK 22:01:37 [INF][ grt]: Fetching routine list. 22:01:37 [INF][ grt]: 0 items from shop 22:01:37 [INF][ grt]: OK 22:01:37 [INF][ grt]: Fetching trigger list. 22:01:37 [INF][ grt]: 0 items from shop 22:01:37 [INF][ grt]: OK 22:01:38 [INF][ grt_diff]: No sync profile found for Mysql@localhost:33063::shop 22:01:40 [INF][ grt]: Applying synchronization scripts to server... 22:01:40 [INF][ grt]: Executing SQL script in server 22:01:40 [ERR][ grt]: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT, `company_name` VARCHAR(64) NULL ...' at line 2 SQL Code: CREATE TABLE IF NOT EXISTS `shop`.`customer_invoice_data` ( `id_customer_invoice_data` INT(10) ZEROFILL ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT, `company_name` VARCHAR(64) NULL DEFAULT NULL, `first_name` VARCHAR(24) NOT NULL, `last_name` VARCHAR(40) NOT NULL, `title` VARCHAR(16) NULL DEFAULT NULL, `id` VARCHAR(8) NULL DEFAULT NULL, `vat_id` VARCHAR(12) NULL DEFAULT NULL, `address` VARCHAR(48) NOT NULL, `postcode` VARCHAR(5) NOT NULL, `city` VARCHAR(32) NOT NULL, `state_id` TINYINT(3) UNSIGNED NOT NULL, `country` VARCHAR(32) NULL DEFAULT NULL, `last_used` DATETIME NOT NULL, `customer_id` INT(10) UNSIGNED ZEROFILL NOT NULL, PRIMARY KEY (`id_customer_invoice_data`), UNIQUE INDEX `fk_customer_invoice_data_customers_idx_UNIQUE` (`customer_id` ASC), INDEX `fk_customer_invoice_data_service_states_idx` (`state_id` ASC), CONSTRAINT `fk_customer_invoice_data_customers` FOREIGN KEY (`customer_id`) REFERENCES `shop`.`customers` (`id_customer`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_customer_invoice_data_service_states` FOREIGN KEY (`state_id`) REFERENCES `shop`.`service_states` (`id_state`) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 22:01:40 [INF][ grt]: SQL script execution finished: statements: 3 succeeded, 1 failed How to repeat: Edit table in Diagram editor or add new table requiring "ZEROFILL" column, from menu - Database -> Synchronize Model -> set server connection -> Next -> Next -> select schema -> Next -> Next -> Next -> wrong SQL statement is generated. When edited the table to not use ZEROFILL -> repeated steps above -> edit to use ZEROFILL, issue has disappeared.