MySQL Bugs: #61656: Foreign keys not constructed when apostrophes are escaped with backslash

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

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.

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 >

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.

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:~$

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)

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?

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)?

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.

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).

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?

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)

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

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.

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

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.

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.

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>

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)

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.

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

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