MySQL Bugs: #70102: \ escaped comments make MySQL Server to silently ignore foreign keys creation

Bug #70102	\ escaped comments make MySQL Server to silently ignore foreign keys creation
Submitted:	21 Aug 2013 0:26	Modified:	21 Aug 2013 15:50
Reporter:	Vincent Courcelle	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.5.32	OS:	Any (Tested Linux+Windows)
Assigned to:		CPU Architecture:	Any
Tags:	comment, escape, foreign keys, innodb

Description:
I'm using MySQL workbench to design my database tables/relationships.
Engine is InnoDB, character set is utf8_general_ci.
Comments containing "'" are escaped with \ when generating .sql files (for example : "it's" => "it\'s".
When running the .sql files, I see that \ escaped comments on fields are correctly interpreted by the server (they appear correctly on the field when running SHOW CREATE TABLE Tests;), but that the server then silently ignore the foreign keys of the table!

This is very serious as it leads to a different database schema than what expected.

Tested under Windows (5.5.32) and Linux Debian (5.5.31, which is the current version in the "testing" distrib).

How to repeat:
DROP TABLE IF EXISTS `Tests` ;

CREATE  TABLE IF NOT EXISTS `Tests` (
  `Id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `ParentTestId` INT UNSIGNED NULL DEFAULT 1 COMMENT 'it\'s a bug' ,
  PRIMARY KEY (`Id`) ,
  INDEX `ParentTestId` (`ParentTestId` ASC) ,
  CONSTRAINT `ParentTest_Tests_Tests`
    FOREIGN KEY (`ParentTestId` )
    REFERENCES `Tests` (`Id` )
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB;

DROP TABLE IF EXISTS `Tests2` ;

CREATE  TABLE IF NOT EXISTS `Tests2` (
  `Id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `ParentTest2Id` INT UNSIGNED NULL DEFAULT 1 COMMENT 'it''s a bug' ,
  PRIMARY KEY (`Id`) ,
  INDEX `ParentTest2Id` (`ParentTest2Id` ASC) ,
  CONSTRAINT `ParentTest2_Tests2_Tests2`
    FOREIGN KEY (`ParentTest2Id` )
    REFERENCES `Tests2` (`Id` )
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB;

SHOW CREATE TABLE Tests;
SHOW CREATE TABLE Tests2;

=> foreign key is not present in table Tests but is present in table Tests2 (the only difference is the comment on the fireld not being escaped the same

Suggested fix:
- Not ignoring the foreign keys when a \ escaped comment is present
- Or displaying an error message stating an error in the declaration

Testcase:

set foreign_key_checks=0;
drop table if exists t1,t2;
drop view if exists t1,t2;
create table t1(a bigint primary key)engine=innodb;
show warnings;
create table t2(a bigint comment 'it\'s a bug',foreign key(a) references b(a) on delete cascade)engine=innodb;
show warnings;
show create table t1;
show create table t2;

seems fixed in 5.5.33, please check it yourself.
change log references http://bugs.mysql.com/bug.php?id=69062 also...

Thanks Shane for your comments.
I'm not sure bug #69062 has anything is related to this bug.
I just upgraded my developer machine to 5.6.13 and the bug is not present anymore.
I just see that my production schemes are all messed up because of this bug (my production server is running debian 7 which features 5.5.31 as current version).

It's incredible to leave this buggy version of mysql server as the stable one!