Bug #36552 Forward Engineer SQL ALTER Script recreates indexes and columns

• Submitted: 7 May 2008 9:06
• Modified: 30 Jun 2008 17:52
• Reporter: Daniel Haas
• Status: Can't repeat
• Category: MySQL Workbench
• Severity: S3 (Non-critical)
• Version: 5.0.21
• OS: Windows (Vista)
• CPU Architecture: Any

[7 May 2008 9:06] Daniel Haas

Description:
When creating an SQL ALTER script, MySQL Workbench generates SQL-Code that drops primary keys just to create them again.
Example: 

ALTER TABLE `my`.`plans` DROP PRIMARY KEY , ADD PRIMARY KEY (`id`) ;

Also some columns are altered although they where never altered in the model:

ALTER TABLE `my`.`routes` CHANGE COLUMN `date` `date` DATE NULL  , DROP INDEX `route` , DROP PRIMARY KEY , ADD PRIMARY KEY (`id`) ;

This line is generated although the table routes was never even touched between the last version and this version.

How to repeat:
1. Generate a model with a table and a primary key.
2. Add a column with type date, leave NN unchecked and do not add a default
2. Export it using the Forward Engineer SQL CREATE Script export method
3. Add a new table to the model
4. Generate a SQL ALTER Script and see that it wants to drop the primary key of the first table although you did not modify it. It also wants to change the date column.

Suggested fix:
Unnecessary changes / non-existing changes should not be done when creating an SQL ALTER script.

[30 Jun 2008 17:52] MySQL Verification Team

Thank you for the bug report. I can't repeat with version 5.0.23 could you please upgrade and try again. Thanks in advance.

[16 Oct 2008 15:00] Voldemar Vologodsky

The same with foreign keys, version 5.0.26 OSS

Part of input script (generated by MySQL Administrator 1.2.12):

--
-- Definition of table `admin_msg`
--

DROP TABLE IF EXISTS `admin_msg`;
CREATE TABLE `admin_msg` (
  `id` int(11) NOT NULL auto_increment,
  `creation_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `msg` text,
  `sender_email` varchar(100) default NULL,
  `source_url` varchar(100) default NULL,
  `unread` tinyint(1) default '1',
  `user_id` int(11) default NULL,
  `admin_msg_type_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_admin_msg_user` (`user_id`),
  KEY `fk_admin_msg_admin_msg_type` (`admin_msg_type_id`),
  CONSTRAINT `fk_admin_msg_admin_msg_type` FOREIGN KEY (`admin_msg_type_id`) REFERENCES `admin_msg_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_admin_msg_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Part of generated alter script:

ALTER TABLE `myscheme`.`admin_msg` DROP FOREIGN KEY `fk_admin_msg_admin_msg_type` , DROP FOREIGN KEY `fk_admin_msg_user` ;

ALTER TABLE `myscheme`.`admin_msg` 
  ADD CONSTRAINT `fk_admin_msg_admin_msg_type`
  FOREIGN KEY (`admin_msg_type_id` )
  REFERENCES `myscheme`.`admin_msg_type` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION, 
  ADD CONSTRAINT `fk_admin_msg_user`
  FOREIGN KEY (`user_id` )
  REFERENCES `localratings`.`user` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;