Bug #61656 Foreign keys not constructed when apostrophes are escaped with backslash
Submitted: 27 Jun 2011 14:08 Modified: 21 May 2013 12:55
Reporter: Lawrence Holtsclaw Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.1+, 5.5.15 OS:Any
Assigned to: CPU Architecture:Any
Tags: ', CREATE TABLE, foreign key, regression

[27 Jun 2011 14:08] Lawrence Holtsclaw
Description:
Creating a table with a comment or default textual value containing an apostrophe escaped with a backslash causes the InnoDB storage engine to silently omit foreign key definitions. There are no warnings or diagnostics given. Unlike bug #39793, inserting a new-line does not change the behavior.

Observations:

1. Works correctly in version 5.1.49-1ubuntu8.1 (Ubuntu)
2. Fails in general release versions 5.1.39, 5.5.9 and 5.5.13 (current)
3. Since this issue relates to quoting there could be security ramifications.

Workarounds:

1. Use double-apostrophes for escaping instead of a backslash
2. Omit apostrophes from comments and default values

How to repeat:
FAILURE CASE:

mysql> create table foo (f1 integer primary key comment 'foo\'s ID#', f2 integer default null, constraint f2_ref foreign key (f2) references foo (f1)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `f1` int(11) NOT NULL COMMENT 'foo''s ID#',
  `f2` int(11) DEFAULT NULL,
  PRIMARY KEY (`f1`),
  KEY `f2_ref` (`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

===========================================================================
SUCCESS CASE:

mysql> create table foo (f1 integer primary key comment 'foo''s ID#', f2 integer default null, constraint f2_ref foreign key (f2) references foo (f1)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `f1` int(11) NOT NULL COMMENT 'foo''s ID#',
  `f2` int(11) DEFAULT NULL,
  PRIMARY KEY (`f1`),
  KEY `f2_ref` (`f2`),
  CONSTRAINT `f2_ref` FOREIGN KEY (`f2`) REFERENCES `foo` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Suggested fix:
Examine the Internal InnoDB FK parser's handling of backslashes WRT escaping quotation characters. Since the Ubuntu build works, they must have added their own patch for this but I could not find any record of it being reported in the MySQL bugs system.
[27 Jun 2011 14:28] MySQL Verification Team
I couldn't repeat with 5.5 current source on Windows:

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.15-log Source distribution

Copyright (c) 2000, 2010, 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 5.5 >use test
Database changed
mysql 5.5 >create table foo (f1 integer primary key comment 'foo\'s ID#', f2 integer default
    -> null, constraint f2_ref foreign key (f2) references foo (f1)) engine=innodb;
Query OK, 0 rows affected (0.12 sec)

mysql 5.5 >show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `f1` int(11) NOT NULL COMMENT 'foo''s ID#',
  `f2` int(11) DEFAULT NULL,
  PRIMARY KEY (`f1`),
  KEY `f2_ref` (`f2`),
  CONSTRAINT `f2_ref` FOREIGN KEY (`f2`) REFERENCES `foo` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql 5.5 >
[27 Jun 2011 14:51] Lawrence Holtsclaw
5.5.15 isn't a GA version. If someone could point me towards a RHEL 5 RPM for 5.5.15, I would be happy to retest the problem. Alternatively, try to reproduce it using one of your own 5.5.13 builds. If it's already fixed, that's great but I see no mention of any related issues in the 5.5.14 or 5.5.15 release notes available online.
[27 Jun 2011 15:07] MySQL Verification Team
I couldn't repeat on Ubuntu Linux with older 5.5 source:

miguel@lempa:~$ dbs/5.5/bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.13-debug Source distribution

Copyright (c) 2000, 2010, 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> use test
Database changed
mysql> create table foo (f1 integer primary key comment 'foo\'s ID#', f2 integer default
    -> null, constraint f2_ref foreign key (f2) references foo (f1)) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `f1` int(11) NOT NULL COMMENT 'foo''s ID#',
  `f2` int(11) DEFAULT NULL,
  PRIMARY KEY (`f1`),
  KEY `f2_ref` (`f2`),
  CONSTRAINT `f2_ref` FOREIGN KEY (`f2`) REFERENCES `foo` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> exit
Bye
miguel@lempa:~$ uname -a
Linux lempa 2.6.38-8-generic #42-Ubuntu SMP Mon Apr 11 03:31:24 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux
miguel@lempa:~$
[27 Jun 2011 15:22] MySQL Verification Team
Not repeatable on Windows with 5.1 too:

C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.58-Win X64-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql 5.1 >use test
Database changed
mysql 5.1 >create table foo (f1 integer primary key comment 'foo\'s ID#', f2 integer default
    -> null, constraint f2_ref foreign key (f2) references foo (f1)) engine=innodb;
Query OK, 0 rows affected (0.12 sec)

mysql 5.1 >show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `f1` int(11) NOT NULL COMMENT 'foo''s ID#',
  `f2` int(11) DEFAULT NULL,
  PRIMARY KEY (`f1`),
  KEY `f2_ref` (`f2`),
  CONSTRAINT `f2_ref` FOREIGN KEY (`f2`) REFERENCES `foo` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[27 Jun 2011 16:02] Lawrence Holtsclaw
Ahh ... inserting a newline between "DEFAULT" and the value appears to be another workaround. I had tried doing this between a KEY and the first CONSTRAINT but did not try it again elsewhere in the statement.

Since you modified the test case trying to reproduce bug #39793 also, might I suggest you not inject newlines when trying to reproduce a problem?
[9 Jul 2011 10:50] Sveta Smirnova
Thank you for the feedback.

I can not repeat described behavior too. Which client do you use? If it is command line client which shell do you use? Which exact MySQL package do you use (file name you downloaded)?
[11 Jul 2011 13:07] Lawrence Holtsclaw
Using server and client version 5.5.13 from the MySQL-*-5.5.13-1.rhel5.i386.rpm files as downloaded from mysql.com and running under CentOS 5.6. The same behavior was observed with versions 5.5.9 and 5.1.39 (all of which were stock rhel5 RPM's from the mysql.com site).

The failure case occurs with the following (EXACTLY AS GIVEN - NO LINE BREAKS):

create table foo (f1 integer primary key comment 'foo\'s ID#', f2 integer default null, constraint f2_ref foreign key (f2) references foo (f1)) engine=innodb;

Inserting an arbitrary newline within the create body appears to avoid the problem. It also does not occur if the apostrophe is quoted by repetition instead of the backslash (ie: 'foo''s ID#' instead of 'foo\'s ID#'). Thus my best guess is that the backend InnoDB parser is getting confused by the backslash similar to the problem which occurred with Bug#39793.

Note that the client is NOT relevant to this problem. It was originally encountered with SQL dynamically generated and executed with the Qt 4.x libraries -- the above is simply the best test-case I was able to construct using the command-line client.
[11 Jul 2011 13:17] Peter Laursen
With 5.1.58 (Windoss 64 biot server) I get (with any client):

Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.1.58-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> use test;
Database changed
mysql> DROP TABLE IF EXISTS foo;
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> CREATE TABLE foo (f1 INTEGER PRIMARY KEY COMMENT 'foo\'s ID#', f2 INTEGER
 DEFAULT NULL,CONSTRAINT f2_ref FOREIGN KEY (f2) REFERENCES foo (f1)) ENGINE=INN
ODB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> SHOW CREATE TABLE foo;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------+
| Table | Create Table

                           |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------+
| foo   | CREATE TABLE `foo` (
  `f1` int(11) NOT NULL COMMENT 'foo''s ID#',
  `f2` int(11) DEFAULT NULL,
  PRIMARY KEY (`f1`),
  KEY `f2_ref` (`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------+
1 row in set (0.00 sec)

mysql>

Peter
(not a MySQL person).
[11 Jul 2011 13:43] Valeriy Kravchuk
I also can not repeat this with current code:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.15-debug Source distribution

Copyright (c) 2000, 2010, 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 table foo (f1 integer primary key comment 'foo\'s ID#', f2
    -> integer default null, constraint f2_ref foreign key (f2) references foo
    -> (f1)) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `f1` int(11) NOT NULL COMMENT 'foo''s ID#',
  `f2` int(11) DEFAULT NULL,
  PRIMARY KEY (`f1`),
  KEY `f2_ref` (`f2`),
  CONSTRAINT `f2_ref` FOREIGN KEY (`f2`) REFERENCES `foo` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Had you check with recent released versions, 5.5.14 and/or 5.1.58?
[11 Jul 2011 13:53] MySQL Verification Team
5.5.14:

mysql> create table foo (f1 integer primary key comment 'foo\'s ID#', f2 integer default null, constraint f2_ref foreign key (f2) references foo (f1)) engine=innodb;
Query OK, 0 rows affected (0.33 sec)

mysql> show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `f1` int(11) NOT NULL COMMENT 'foo''s ID#',
  `f2` int(11) DEFAULT NULL,
  PRIMARY KEY (`f1`),
  KEY `f2_ref` (`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
[11 Jul 2011 13:56] Peter Laursen
Also with 5.5.13 it is reproducible for me.

USE test;
DROP TABLE IF EXISTS foo;
-- NOTE: create statement on ONE line
CREATE TABLE foo (f1 INTEGER PRIMARY KEY COMMENT 'foo\'s ID#', f2 INTEGER DEFAULT NULL,CONSTRAINT f2_ref FOREIGN KEY (f2) REFERENCES foo (f1)) ENGINE=INNODB;
SHOW CREATE TABLE foo;

/*returns
CREATE TABLE `foo` (
  `f1` int(11) NOT NULL COMMENT 'foo''s ID#',
  `f2` INT(11) DEFAULT NULL,
  PRIMARY KEY (`f1`),
  KEY `f2_ref` (`f2`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
*/

If I break the CREATE statement into more lines it will *sometimes* work to create the FK - depending on where the linebreak(s) is/are
[11 Jul 2011 14:06] Valeriy Kravchuk
With line breaks or without:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.15-debug Source distribution

Copyright (c) 2000, 2010, 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> drop table foo;
Query OK, 0 rows affected (0.01 sec)

mysql> create table foo (f1 integer primary key comment 'foo\'s ID#', f2 integer default null, constraint f2_ref foreign key (f2) references foo(f1)) engine=innodb;
Query OK, 0 rows affected (0.07 sec)

I see no problems with current code. 

So, while I apologize for being less careful than needed when pasting my testing results, the same request to you remains:

Please, check if any form of this statement leads to error messages in current GA versions, 5.1.58 or 5.5.14.
[11 Jul 2011 14:13] MySQL Verification Team
5.5.8  -> works
5.5.9+ -> doesn't show FK.

Cut 'n paste testcase:

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

The 5.5.9 change history mentions
[11 Jul 2011 14:14] Lawrence Holtsclaw
The CREATE statement does NOT generate an error -- the foreign key just isn't generated by InnoDB. Try performing a SHOW CREATE TABLE `foo` following the construction and see if the foreign key exists.
[11 Jul 2011 14:17] Valeriy Kravchuk
OK, I've got it:

mysql> show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `f1` int(11) NOT NULL COMMENT 'foo''s ID#',
  `f2` int(11) DEFAULT NULL,
  PRIMARY KEY (`f1`),
  KEY `f2_ref` (`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

(this is executed after my previous statement). This is a verified regression bug.

Thank you for your patience and Shane for regression checking.
[29 Nov 2012 23:08] John Rocha
I reproduced on 5.5.28 running on Linux RedHat 5.8 and Suse SLES10.

---------------------------------------------------------------------------
ERRORED SCENARIO
---------------------------------------------------------------------------
DROP TABLE IF EXISTS `UMS`.`event_trigger` ;

CREATE  TABLE IF NOT EXISTS `UMS`.`event_trigger` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `event_type_value` SMALLINT UNSIGNED NOT NULL ,
  `uid` VARCHAR(128) NOT NULL COMMENT 'The user\'s handle to for an event trigger' ,
  `source` VARCHAR(128) NOT NULL ,
  `primary_ms` VARCHAR(128) NOT NULL ,
  `redundant_ms` VARCHAR(128) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `description_UNIQUE` (`uid` ASC) ,
  INDEX (`event_type_value` ASC) ,
    FOREIGN KEY (`event_type_value` )
    REFERENCES `UMS`.`event_type` (`enum_value` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  INDEX `IDX_primaryMS` (`primary_ms` ASC) ,
  INDEX `IDX_redundantMS` (`redundant_ms` ASC)
)
ENGINE = InnoDB;

Results in this:
mysql> show create table event_trigger\G
*************************** 1. row ***************************
       Table: event_trigger
Create Table: CREATE TABLE `event_trigger` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_type_value` smallint(5) unsigned NOT NULL,
  `uid` varchar(128) NOT NULL COMMENT 'The user''s handle to for an event trigger',
  `source` varchar(128) NOT NULL,
  `primary_ms` varchar(128) NOT NULL,
  `redundant_ms` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `description_UNIQUE` (`uid`),
  KEY `event_type_value` (`event_type_value`),
  KEY `IDX_primaryMS` (`primary_ms`),
  KEY `IDX_redundantMS` (`redundant_ms`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> 

---------------------------------------------------------------------------
If I delete the comment for event_tyep_value as shown below
---------------------------------------------------------------------------
DROP TABLE IF EXISTS `UMS`.`event_trigger` ;

CREATE  TABLE IF NOT EXISTS `UMS`.`event_trigger` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `event_type_value` SMALLINT UNSIGNED NOT NULL ,
  `uid` VARCHAR(128) NOT NULL,
  `source` VARCHAR(128) NOT NULL ,
  `primary_ms` VARCHAR(128) NOT NULL ,
  `redundant_ms` VARCHAR(128) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `description_UNIQUE` (`uid` ASC) ,
  INDEX (`event_type_value` ASC) ,
    FOREIGN KEY (`event_type_value` )
    REFERENCES `UMS`.`event_type` (`enum_value` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  INDEX `IDX_primaryMS` (`primary_ms` ASC) ,
  INDEX `IDX_redundantMS` (`redundant_ms` ASC)
)
ENGINE = InnoDB;

Then it works fine

mysql> show create table event_trigger\G
*************************** 1. row ***************************
       Table: event_trigger
Create Table: CREATE TABLE `event_trigger` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_type_value` smallint(5) unsigned NOT NULL,
  `uid` varchar(128) NOT NULL,
  `source` varchar(128) NOT NULL,
  `primary_ms` varchar(128) NOT NULL,
  `redundant_ms` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `description_UNIQUE` (`uid`),
  KEY `event_type_value` (`event_type_value`),
  KEY `IDX_primaryMS` (`primary_ms`),
  KEY `IDX_redundantMS` (`redundant_ms`),
  CONSTRAINT `event_trigger_ibfk_1` FOREIGN KEY (`event_type_value`) REFERENCES `event_type` (`enum_value`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>
[29 Nov 2012 23:09] John Rocha
Being more specific with the version. I've reproduced with:

Server version: 5.5.28-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
[21 May 2013 12:55] Bugs System
Added changelog entry for 5.5.33, 5.6.13, 5.7.2:

"Creating a table with a comment or default textual value containing an
apostrophe that is escaped with a backslash would sometimes cause the
InnoDB storage engine to omit foreign key definitions."

Thank you for the bug report.
[21 May 2013 16:06] John Rocha
Hello Daniel,

You indicated that you added a change log entry for 5.5.33, 5.6.13 and 5.7.2.

Does that mean it's been fixed in these specific named releases? Your statement isn't clear to me.

Thank you.

-=John
[29 Aug 2013 13:09] Bugs System
Hi John,

Sorry for the later response. I just saw your question today. For future reference, yes, it means that the patch is applied to those releases (5.5.33, 5.6.13 and 5.7.2). If you view the release notes for these releases, you will see this bug listed under "Bugs Fixed".

For example, search on "6156" in the 5.5.33 release notes:
http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-33.html