MySQL Bugs: #55502: Renaming column with FK results in invalid script

Bug #55502	Renaming column with FK results in invalid script
Submitted:	23 Jul 2010 10:42	Modified:	3 Sep 2010 14:28
Reporter:	Andrei Frolov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.25 CE	OS:	Windows
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	foreign key, rename column

Description:
Renaming column with FK results in invalid script.

How to repeat:
1. Create new EER model
2. Add new EER diargam
3. Add 2 tables: 

 table1 (ID INT)
 Primary Key ID

 table2 (table1_ID INT)
 Foreign Key table1_ID references table1(ID)

4. forward engineer or synchronize model with database
5. rename column table2.table1_ID to table2.table1_ID1
6. synchronize model

created script failed to execute because of FK presence.

Suggested fix:
remove FK, rename comlumn, restore FK

What version of server, x.y.z, are you working with?

For me the following SQL was generated after renaming column:

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';

ALTER SCHEMA `mydb`  DEFAULT CHARACTER SET latin1  DEFAULT COLLATE latin1_swedish_ci ;

USE `mydb`;

ALTER TABLE `mydb`.`table2` DROP FOREIGN KEY `fk1` ;

ALTER TABLE `mydb`.`table1` COLLATE = latin1_swedish_ci ;

ALTER TABLE `mydb`.`table2` COLLATE = latin1_swedish_ci , CHANGE COLUMN `idtable2` `id` INT(11) NOT NULL  , 
  ADD CONSTRAINT `fk1`
  FOREIGN KEY (`id` )
  REFERENCES `mydb`.`table1` (`idtable1` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
, DROP PRIMARY KEY 
, ADD PRIMARY KEY (`id`) 
, DROP INDEX `fk1` 
, ADD INDEX `fk1` (`id` ASC) ;

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

and it was executed successfully by 5.1.48.

My server version is 5.0.45

Here's script generated by WB after renaming column:

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';

ALTER TABLE `mydb`.`table2` CHANGE COLUMN `table1_ID` `table1_ID1` INT(11) NOT NULL  

, DROP INDEX `table1_ID` 

, ADD INDEX `table1_ID` (`table1_ID1` ASC) ;

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

And here's log content:

Executing SQL script in server

ERROR: Error 1025: Error on rename of '.\mydb\#sql-7f0_a5' to '.\mydb\table2' (errno: 150)

ALTER TABLE `mydb`.`table2` CHANGE COLUMN `table1_ID` `table1_ID1` INT(11) NOT NULL  

, DROP INDEX `table1_ID` 

, ADD INDEX `table1_ID` (`table1_ID1` ASC) 

SQL script execution finished: statements: 3 succeeded, 1 failed

Verified when synchronizing with MySQL 5.0.89:

Executing SQL script in server

ERROR: Error 1025: Error on rename of '.\mydb\#sql-184_17' to '.\mydb\table2' (errno: 150)

ALTER TABLE `mydb`.`table2` DROP COLUMN `table1_id` , ADD COLUMN `table1_id2` INT(11) NOT NULL  AFTER `idtable2` 

, DROP INDEX `fk_table2_table1` 

, ADD INDEX `fk_table2_table1` (`table1_id2` ASC) 

SQL script execution finished: statements: 3 succeeded, 1 failed

fix confirmed in repository

An entry has been added to the 5.2.27 changelog:

Renaming a column with a foreign key resulted in an invalid script being generated on forward engineering or synchronization with a live database.