MySQL Bugs: #64539: Cannot create additional Foreign Keys

Bug #64539	Cannot create additional Foreign Keys
Submitted:	4 Mar 2012 19:49	Modified:	8 Aug 2012 21:28
Reporter:	Nik Rivers	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	5.2.38	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	1064, foreign key, regression

Description:
Creating an additional FK on a table, when one already exists on that table, is unsuccessful due to MySQL Workbench generating invalid SQL.

Workbench generates SQL to drop the first FK in its FK listview, then recreate it along with the new FK being added - however the statement for the new FK is missing column names. Interestingly, if more than one FKs already exist on the table, the generated SQL still only drops and recreates the *first* FK in the Workbench FK list before attempting to create the new FK.

The following generated SQL, a FK was added from table1 to table3, named fk_failure.

ALTER TABLE `schema`.`table1` DROP FOREIGN KEY `fk_table1_table2` ;
ALTER TABLE `schema`.`table1`
ADD CONSTRAINT `fk_table1_table2`
FOREIGN KEY (`table2_id` )
REFERENCES `schema`.`table2` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_failure`
FOREIGN KEY ()
REFERENCES `schema`.`table3` ()
ON DELETE NO ACTION
ON UPDATE NO ACTION
, ADD INDEX `fk_failure` () ;

Clicking the Apply button results in the following error:

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
REFERENCES `schema`.`table3` ()
ON DELETE NO ACTION
ON UPDATE NO ACTION
,' at line 8

A workaround is to copy the generated SQL, cancel the execution of the alter, then paste the SQL into a new query window and edit as necessary before applying manually.

How to repeat:
1. Create three tables with suitable ID columns.
2. Create a FK from table1 to table2 (successful).
3. Create a FK from table1 to table3 (unsuccessful).

I can confirm that this behaviour did not occur in 5.2.34.2.

Please, send .mwb file that demonstrates the problem.

Simple .mwb file showing minimum necessary schema to recreate bug.

Attachment: bug-64539.mwb (application/x-zip-compressed, text), 5.94 KiB.

I've attached the requested file.

The schema contains three tables (table1, table2 and table3).  A FK already exists from table1 to table2 (table1.table2_id onto table2.id).  Attempting to create a second FK (table1.table3_id onto table3.id) results in the behaviour I have described.

(I've moved this bug to the main "MySQL Workbench" category because I don't think "Modeling" was quite right).

I can not repeat this problem with 5.2.38 on Windows XP even with your .mwb. After adding new FK the following code is generated:

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

CREATE SCHEMA IF NOT EXISTS `fktest` DEFAULT CHARACTER SET utf8 ;
USE `fktest` ;

...

-- -----------------------------------------------------
-- Table `fktest`.`table1`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `fktest`.`table1` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `table2_id` INT(11) NOT NULL ,
  `table3_id` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_table1_table2` (`table2_id` ASC) ,
  INDEX `fk_table1_table3` (`table3_id` ASC) ,
  CONSTRAINT `fk_table1_table2`
    FOREIGN KEY (`table2_id` )
    REFERENCES `fktest`.`table2` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_table1_table3`
    FOREIGN KEY (`table3_id` )
    REFERENCES `fktest`.`table3` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

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

I've uploaded the file reproduction-bug-64539.swf to the ftp site.  It is a small video showing the successful creation of the first FK, fk_table1_table2, followed by the unsuccessful creation of a second FK, fk_table1_table3.

The same behaviour occurs on multiple machines, all running 5.2.38 on Windows 7 (a mix of 32- and 64-bit, and Professional and Ultimate editions).

Hi
I can't find the file you mention, is it in some folder?

Not sure what happened to the file, I couldn't be sure it uploaded ok.

Here it is, hosted with a third-party: http://screencast.com/t/kqbuAnu3N

Thank you for the link. As far as I can see from the video, you are altering table on a live server, not in the model. What exact version of server, x.y.z, are you working with?

The MySQL server is running on Fedora 15, and is version 5.5.20 from package mysql.x86_64 (5.5.20-1.fc15).

Thanks for your report Nik.
Reproduced in:
Client - Windows 7x64
Server - Fedora 15x64
Steps:
1. Load the .mwd file provided by the reporter.
2. Forward engineer it to MySQL Server.
3. Alter Table 1 and add a FK as showed in the video.

****Results****
SQL Statement:

ALTER TABLE `fktest`.`table1` DROP FOREIGN KEY `fk_table1_table2` ;

ALTER TABLE `fktest`.`table1` 

  ADD CONSTRAINT `fk_table1_table2`

  FOREIGN KEY (`table3_id` )

  REFERENCES `fktest`.`table2` (`id` )

  ON DELETE NO ACTION

  ON UPDATE NO ACTION, 

  ADD CONSTRAINT `fk_table1_table3`

  FOREIGN KEY ()

  REFERENCES `fktest`.`table3` ()

  ON DELETE NO ACTION

  ON UPDATE NO ACTION

, ADD INDEX `fk_table1_table3` () ;

Error:

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')

  REFERENCES `fktest`.`table3` ()

  ON DELETE NO ACTION

  ON UPDATE NO ACTION

' at line 8

SQL Statement:

ALTER TABLE `fktest`.`table1` 

  ADD CONSTRAINT `fk_table1_table2`

  FOREIGN KEY (`table3_id` )

  REFERENCES `fktest`.`table2` (`id` )

  ON DELETE NO ACTION

  ON UPDATE NO ACTION, 

  ADD CONSTRAINT `fk_table1_table3`

  FOREIGN KEY ()

  REFERENCES `fktest`.`table3` ()

  ON DELETE NO ACTION

  ON UPDATE NO ACTION

, ADD INDEX `fk_table1_table3` ()

ERROR: Error when running failback script. Details follow.

ERROR 1046: No database selected

SQL Statement:

CREATE TABLE `table1` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `table2_id` int(11) NOT NULL,

  `table3_id` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `fk_table1_table2` (`table2_id`),

  CONSTRAINT `fk_table1_table2` FOREIGN KEY (`table2_id`) REFERENCES `table2` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Fixed as of 5.2.41, and here's the changelog entry:

Under certain conditions, adding a foreign key could generate
invalid SQL code.