MySQL Bugs: #67881: single quote in field comment prevents foreign key constraint creation

Bug #67881	single quote in field comment prevents foreign key constraint creation
Submitted:	12 Dec 2012 5:28	Modified:	17 May 2013 8:27
Reporter:	Christopher Santora	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
Similar to http://bugs.mysql.com/bug.php?id=59197 , an escaped single quote in a comment generated by a Workbench export (COMMENT ' \\\' ') prevents foreign keys from getting added in a CREATE TABLE statement.

How to repeat:
DROP SCHEMA IF EXISTS `mytest`;
CREATE SCHEMA IF NOT EXISTS `MyTest` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `MyTest` ;

CREATE  TABLE IF NOT EXISTS `MyTest`.`tblClient` (
  `clientId` BIGINT NOT NULL ,
  `name` VARCHAR(80) NOT NULL ,
  PRIMARY KEY (`clientId`) ,
  UNIQUE INDEX `clientId_UNIQUE` (`clientId` ASC)
)
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `MyTest`.`tblBranchLocation` (
  `clientId` BIGINT NOT NULL ,
  `locationId` BIGINT NOT NULL ,
  `locationName` VARCHAR(25) NULL ,
  `addressLine1` VARCHAR(45) NOT NULL ,
  `addressLine2` VARCHAR(45) NULL ,
  `city` VARCHAR(45) NOT NULL ,
  `state` VARCHAR(2) NOT NULL ,
  `zip` INT NOT NULL ,
  `phone` VARCHAR(10) NOT NULL COMMENT ' \\\' ' ,
  PRIMARY KEY (`clientId`, `locationId`) ,
  INDEX `fk_tblBranchLocation_idx` (`clientId` ASC) ,
  CONSTRAINT `fk_tblBranchLocation`
    FOREIGN KEY (`clientId` )
    REFERENCES `MyTest`.`tblClient` (`clientId` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;

.. so is this a server bug or a Workbench bug?

Peter
(not a MySQL/Oracle person)

Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE SCHEMA IF NOT EXISTS `MyTest` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
Query OK, 1 row affected (0.07 sec)

mysql> USE `MyTest` ;
Database changed
mysql>
mysql> CREATE  TABLE IF NOT EXISTS `MyTest`.`tblClient` (
    ->   `clientId` BIGINT NOT NULL ,
    ->   `name` VARCHAR(80) NOT NULL ,
    ->   PRIMARY KEY (`clientId`) ,
    ->   UNIQUE INDEX `clientId_UNIQUE` (`clientId` ASC)
    -> )
    -> ENGINE = InnoDB;
Query OK, 0 rows affected (0.40 sec)

mysql>
mysql> CREATE  TABLE IF NOT EXISTS `MyTest`.`tblBranchLocation` (
    ->   `clientId` BIGINT NOT NULL ,
    ->   `locationId` BIGINT NOT NULL ,
    ->   `locationName` VARCHAR(25) NULL ,
    ->   `addressLine1` VARCHAR(45) NOT NULL ,
    ->   `addressLine2` VARCHAR(45) NULL ,
    ->   `city` VARCHAR(45) NOT NULL ,
    ->   `state` VARCHAR(2) NOT NULL ,
    ->   `zip` INT NOT NULL ,
    ->   `phone` VARCHAR(10) NOT NULL COMMENT ' \\\' ' ,
    ->   PRIMARY KEY (`clientId`, `locationId`) ,
    ->   INDEX `fk_tblBranchLocation_idx` (`clientId` ASC) ,
    ->   CONSTRAINT `fk_tblBranchLocation`
    ->     FOREIGN KEY (`clientId` )
    ->     REFERENCES `MyTest`.`tblClient` (`clientId` )
    ->     ON DELETE RESTRICT
    ->     ON UPDATE CASCADE)
    -> ENGINE = InnoDB;
Query OK, 0 rows affected (0.15 sec)

mysql> CREATE  TABLE IF NOT EXISTS `MyTest`.`tblBranchLocation` (
    ->   `clientId` BIGINT NOT NULL ,
    ->   `locationId` BIGINT NOT NULL ,
    ->   `locationName` VARCHAR(25) NULL ,
    ->   `addressLine1` VARCHAR(45) NOT NULL ,
    ->   `addressLine2` VARCHAR(45) NULL ,
    ->   `city` VARCHAR(45) NOT NULL ,
    ->   `state` VARCHAR(2) NOT NULL ,
    ->   `zip` INT NOT NULL ,
    ->   `phone` VARCHAR(10) NOT NULL COMMENT ' \\\' ' ,
    ->   PRIMARY KEY (`clientId`, `locationId`) ,
    ->   INDEX `fk_tblBranchLocation_idx` (`clientId` ASC) ,
    ->   CONSTRAINT `fk_tblBranchLocation`
    ->     FOREIGN KEY (`clientId` )
    ->     REFERENCES `MyTest`.`tblClient` (`clientId` )
    ->     ON DELETE RESTRICT
    ->     ON UPDATE CASCADE)
    -> ENGINE = InnoDB;
Query OK, 0 rows affected (0.15 sec)

mysql> SHOW CREATE TABLE
    -> `MyTest`.`tblBranchLocation`\G
*************************** 1. row ***************************
       Table: tblBranchLocation
Create Table: CREATE TABLE `tblbranchlocation` (
  `clientId` bigint(20) NOT NULL,
  `locationId` bigint(20) NOT NULL,
  `locationName` varchar(25) DEFAULT NULL,
  `addressLine1` varchar(45) NOT NULL,
  `addressLine2` varchar(45) DEFAULT NULL,
  `city` varchar(45) NOT NULL,
  `state` varchar(2) NOT NULL,
  `zip` int(11) NOT NULL,
  `phone` varchar(10) NOT NULL COMMENT ' \\'' ',
  PRIMARY KEY (`clientId`,`locationId`),
  KEY `fk_tblBranchLocation_idx` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.03 sec)

drop table if exists t1;
create table t1(a int comment '\'',
foreign key(a) references t1(a))engine=innodb;
show create table t1;

Bug #68367 is probably a duplicate of this bug, at least it's related.

This is a duplicate of bug#61656 "Foreign keys not constructed when apostrophes are escaped with backslash".  Please track the status of this bug via bug#61656.