MySQL Bugs: #50938: synchronization generates ALTER for identical PK

Bug #50938	synchronization generates ALTER for identical PK
Submitted:	5 Feb 2010 8:53	Modified:	18 Jun 2010 10:30
Reporter:	Andrii Nikitin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.1.18, 5.2.15	OS:	Any
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any

Description:
Model synchronization generates ALTER statements where no difference exists (tested with mysqld 5.1.37)

How to repeat:
1. create tables like following in test database:
create table a(id int unsigned Primary key) engine = innodb;
CREATE TABLE `b` (
`id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
CONSTRAINT `pk_b` PRIMARY KEY (`id`)
)
ENGINE=INNODB;

2. create sql file with statements above

3. create new model in WB for database 'test' and Import->"Reverce Engineer MySQL Create Script..."

4. Use "Database"->"Synchronize Model..." to sync tables a and b

5. WB reports difference:

ALTER TABLE `test`.`a` ENGINE = innodb , CHANGE COLUMN `id` `id` INT(10) UNSIGNED NULL DEFAULT NULL  ;

ALTER TABLE `test`.`b` ENGINE = INNODB 
, ADD PRIMARY KEY (`id`) 
, DROP PRIMARY KEY ;

Suggested fix:
'No difference' should be reported

This happens only when model file is created by rev. engineering the script file (not when model is directly rev. engineered from DB)

the first issue (alter of the ID column in table a) is caused by the fact that in the script, the PK column in table is specified without the NOT NULL option. MySQL Server though always creates PK fields implicitly as NOT NULL. WB does not mimic server behavior like that because we would have to do so for a large amount of (maybe even engine specific) settings/options - therefore when creating the model from script, the PK column does not match the one in the DB on server. WB outputs the ALTER script to change the column to be NULLable (Server accepts that alter code but silently ignores it).

The second problem (recreation of the index in table b) is also only happening when Workbench model is imported from script and synced against DB on server. This needs to be investigated further.

Bug #54363 has been marked as duplicate of this one

fix confirmed in repository

An entry has been added to the 5.2.24 changelog:

Synchronizing a model with a live database generated ALTER statements, even when there appeared to be no difference between the database and the model.