MySQL Bugs: #77418: EER: Differences in data-type lengths between Forward Engineer and Sync Model

Bug #77418	EER: Differences in data-type lengths between Forward Engineer and Sync Model
Submitted:	19 Jun 2015 19:56	Modified:	19 Jun 2015 23:54
Reporter:	Martin Oyola	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S5 (Performance)
Version:	6.3.4	OS:	MacOS (OS X 10.10.x Yosemite)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
----[For better reports, please attach the log file after submitting. You can find it in /Users/r1/Library/Application Support/MySQL/Workbench/log/wb.log]

When you use UNSIGNED integer data types and you don't set the length (Eg. BIGINT instead BIGINT(20)) and do a "Database->forward engineer" the schema gets created with BIGINT(20). But if after that you do a Database->Syncronize Model the generator will generate ALTER TABLE statements for those columns with BIGING(19) instead 20.

How to repeat:
1) Create a model
2) Create a table named `table1`
3) Add a column `id`, set to BIGINT --- NO BIGINT(20) ---
4) Check "Unsigned" for this column
5) Save Model
6) Forward Engineer the model to create schema
7) This will output 

CREATE TABLE IF NOT EXISTS `mydb`.`table1` (
  `id` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

8) Go to Database->Synchronize Model
9) Follow the wizard
10) Check the Alter script generated in the "Select changes to apply" step.

You should see 

ALTER TABLE `mydb`.`table1` 
CHANGE COLUMN `id` `id` BIGINT(19) UNSIGNED NOT NULL 

There is the bug.

Suggested fix:
Synchronize detects the column as unsigned and uses the datatype length according to this, but forward engineering does not.. If you check the DB with PhpMyAdmin  BEFORE the alter you will see that it is unsigned with 20 in the length.. So there is three possible fixes

A) Make data-lengths mandatory, this way there will be no errors of "interpretation"

B) Forward engineer should use length=19 if no length specified and unsigned is checked

C) Syncronize model should not substract 1 to the datalenght read from the schema if column is unsigned

Thank you for the bug report.

[20:48:17][miguel-mac:]~ miguel$ mysql -uroot -p mydb
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 80
Server version: 5.7.7-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create table table1;
+--------+------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                           |
+--------+------------------------------------------------------------------------------------------------------------------------+
| table1 | CREATE TABLE `table1` (
  `id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- MySQL Workbench Synchronization
-- Generated: 2015-06-19 20:46
-- Model: New Model
-- Version: 1.0
-- Project: Name of the project
-- Author: XXXXXXXXXXXXXXXXXXXX

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

ALTER TABLE `mydb`.`table1` 
CHANGE COLUMN `id` `id` BIGINT(19) UNSIGNED NOT NULL COMMENT '' ;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

I had updated to 6.3.4 and the bug still there. I see that you have already changed the afected version to 6.3.4 also.

No problem, I use this great free tool, why not contribute a little :)

Forward engineer is not working on the mac. Once i click it no prompt show.