Bug #39793 Foreign keys not constructed when column has a '#' in a comment or default value

• Submitted: 1 Oct 2008 21:47
• Modified: 19 Jun 2010 17:48
• Reporter: Lawrence Holtsclaw
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.0.67-community, 5.1.30-bzr
• OS: Linux (CentOS 5.2)
• Assigned to: Sergei Glukhov
• CPU Architecture: Any
• Tags: #, CREATE TABLE, foreign key

[1 Oct 2008 21:47] Lawrence Holtsclaw

Description:
Creating a table with a comment containing a '#' character causes the foreign key constraint to be omitted.

Observations:

1. Adding the constraint via ALTER TABLE works properly.
2. Loading an sql dump works properly as does piping the declaration into the client from a file.
3. No errors or warnings are generated.
4. Required indexes are still constructed even though the foreign key is not.
5. Fails programmatically (eg: with Qt) in the same way as interactively.

Workarounds:

1. Remove '#' characters from all field comments.
2. Create constraints separately from the tables.

How to repeat:
mysql> create table foo (f1 integer primary key comment 'My 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;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                    |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| foo   | CREATE TABLE `foo` (
  `f1` int(11) NOT NULL COMMENT 'My 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)

mysql> \s
--------------
mysql  Ver 14.12 Distrib 5.0.67, for redhat-linux-gnu (i686) using readline 5.1

Connection id:          388
Current database:       testfoo
Current user:           dholtsclaw@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.67-community MySQL Community Edition (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 5 days 8 hours 58 min 51 sec

Threads: 2  Questions: 18820  Slow queries: 2  Opens: 10568  Flush tables: 1  Open tables: 9  Queries per second avg: 0.041
--------------

Suggested fix:
Check for differences in handling character sets?

[1 Oct 2008 22:07] MySQL Verification Team

I couldn't repeat with Windows server with current source. I will test with older version:

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.72-nt-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.0 > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DB_test            |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.09 sec)

mysql 5.0 > create database yy;
Query OK, 1 row affected (0.00 sec)

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

mysql 5.0 > show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `f1` int(11) NOT NULL COMMENT 'My 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.02 sec)

mysql 5.0 >

[1 Oct 2008 22:19] MySQL Verification Team

Also I wasn't able to repeat with 5.0.67 version. Need to test on Linux:

C:\bug\mysql-5.0.67-win32>bin\mysql -uroot yy
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.67-community MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

mysql> show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `f1` int(11) NOT NULL COMMENT 'My 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.03 sec)

mysql>

[1 Oct 2008 22:36] Lawrence Holtsclaw

This was also tested against 5.0.27-Max, also under Linux (Fedora Core). I do not have a Windows machine to check it against. The problem was originally discovered via a Qt-based utility that maintains/updates schemata via XML descriptions and I boiled that down to a test case that failed at the command line. Needless to say, it took quite some time to identify the comment as the source of the problem (and I have "ID#" in a bazillion field description comments).

[2 Oct 2008 4:11] Valeriy Kravchuk

It is clearly related to Linux/readline used by mysql command line client.

[2 Oct 2008 4:26] Valeriy Kravchuk

Verified on Linux, also with 5.1.30 from bzr:

openxs@suse:/home2/openxs/dbs/5.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

mysql> show create table foo\G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `f1` int(11) NOT NULL COMMENT 'My 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)

mysql> create table foo2 (f1 integer primary key, 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 foo2\G
*************************** 1. row ***************************
       Table: foo2
Create Table: CREATE TABLE `foo2` (
  `f1` int(11) NOT NULL,
  `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)

[2 Oct 2008 14:29] Lawrence Holtsclaw

This problem also exists when a "#" exists in a DEFAULT value.

I think I've found the key -- if there is a line-break (0x0a) following the comma after the comment, it works. No line-break and it fails. Why superfluous white-space affects the interpretation of a 0x23 in a comment field is beyond me.

[2 Oct 2008 14:39] Lawrence Holtsclaw

On further testing it appears it will work as long as there is any line-break BEFORE the constraint. A line-break after the constraint does not affect it.

This may also explain why Miguel Solorzano was unable to replicate it under Windows -- they injected a line-break before the constraint.

I suggest retesting the Windows environment without a line-break and discount the relevance of Linux/readline. It does appear to be a server issue and not client related.

[7 Oct 2008 21:28] Konstantin Osipov

A readline bug.

[7 Oct 2008 21:32] Konstantin Osipov

Checked in the debugger, repeatable in the server, without effect of readline.

[13 May 2009 7:50] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/73902

2740 Sergey Glukhov	2009-05-13
      Bug#39793 Foreign keys not constructed when column has a '#' in a comment or default value
      Internal InnoDN FK parser does not recognize '\'' as qoutation symbol.
      Suggested fix is to add '\'' symbol check for quotation condition
      (dict_strip_comments() function).
     @ innobase/dict/dict0dict.c
        Internal InnoDN FK parser does not recognize '\'' as qoutation symbol.
        The fix is to add '\'' symbol check for quotation condition
        (dict_strip_comments() function).
     @ mysql-test/r/innodb_mysql.result
        test result
     @ mysql-test/t/innodb_mysql.test
        test case

[13 May 2009 8:39] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/73908

2740 Sergey Glukhov	2009-05-13
      Bug#39793 Foreign keys not constructed when column has a '#' in a comment or default value
      Internal InnoDN FK parser does not recognize '\'' as qoutation symbol.
      Suggested fix is to add '\'' symbol check for quotation condition
      (dict_strip_comments() function).
     @ innobase/dict/dict0dict.c
        Internal InnoDN FK parser does not recognize '\'' as qoutation symbol.
        Suggested fix is to add '\'' symbol check for quotation condition
        (dict_strip_comments() function).
     @ mysql-test/r/innodb_mysql.result
        test result
     @ mysql-test/t/innodb_mysql.test
        test case

[19 May 2009 7:37] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/74449

2749 Sergey Glukhov	2009-05-19
      Bug#39793 Foreign keys not constructed when column has a '#' in a comment or default value
      Internal InnoDN FK parser does not recognize '\'' as quotation symbol.
      Suggested fix is to add '\'' symbol check for quotation condition
      (dict_strip_comments() function).
     @ innobase/dict/dict0dict.c
        Internal InnoDN FK parser does not recognize '\'' as quotation symbol.
        Suggested fix is to add '\'' symbol check for quotation condition
        (dict_strip_comments() function).
     @ mysql-test/r/innodb_mysql.result
        test result
     @ mysql-test/t/innodb_mysql.test
        test case

[28 May 2009 7:41] Bugs System

Pushed into 5.0.83 (revid:[e-mail]-20090528073529-q9b8s60vlpu28fny) (version source revid:[e-mail]-20090519063221-y5h10oavajlwnrkv) (merge vers: 5.0.83) (pib:6)

[28 May 2009 8:13] Bugs System

Pushed into 5.1.36 (revid:[e-mail]-20090528073639-yohsb4q1jzg7ycws) (version source revid:[e-mail]-20090519083129-b14cdtxib1w2a6pz) (merge vers: 5.1.36) (pib:6)

[1 Jun 2009 19:12] Paul DuBois

Noted in 5.0.83, 5.1.36 changelogs.

Creating an InnoDB table with a comment containing a '#' character
caused foreign key constraints to be omitted.

Setting report to NDI pending push into 6.0.x.

[17 Jun 2009 19:21] Bugs System

Pushed into 5.4.4-alpha (revid:[e-mail]-20090616183122-chjzbaa30qopdra9) (version source revid:[e-mail]-20090519065226-xiqkneemx6qce6ih) (merge vers: 6.0.12-alpha) (pib:11)

[25 Jun 2009 22:27] Paul DuBois

Noted in 5.4.4 changelog.

[12 Aug 2009 22:50] Paul DuBois

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

[15 Aug 2009 2:07] Paul DuBois

Ignore previous comment about 5.4.2.

[26 Aug 2009 13:45] Bugs System

Pushed into 5.1.37-ndb-7.0.8 (revid:[e-mail]-20090826132541-yablppc59e3yb54l) (version source revid:[e-mail]-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

[26 Aug 2009 13:46] Bugs System

Pushed into 5.1.37-ndb-6.3.27 (revid:[e-mail]-20090826105955-bkj027t47gfbamnc) (version source revid:[e-mail]-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

[26 Aug 2009 13:48] Bugs System

Pushed into 5.1.37-ndb-6.2.19 (revid:[e-mail]-20090825194404-37rtosk049t9koc4) (version source revid:[e-mail]-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

[27 Aug 2009 16:32] Bugs System

Pushed into 5.1.35-ndb-7.1.0 (revid:[e-mail]-20090827163030-6o3kk6r2oua159hr) (version source revid:[e-mail]-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

[8 Oct 2009 19:42] Paul DuBois

The 5.4 fix has been pushed to 5.4.2.

[5 May 2010 15:01] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 16:02] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[28 May 2010 5:53] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:22] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 6:50] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 22:35] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[15 Jun 2010 8:08] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:23] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615080558-cw01bzdqr1bdmmec) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (pib:16)

[17 Jun 2010 11:53] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:31] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:19] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)