| Bug #39793 | Foreign keys not constructed when column has a '#' in a comment or default value | ||
|---|---|---|---|
| Submitted: | 1 Oct 2008 23:47 | Modified: | 26 Jun 0:27 |
| Reporter: | Lawrence Holtsclaw | ||
| Status: | Closed | ||
| Category: | Server: InnoDB | Severity: | S2 (Serious) |
| Version: | 5.0.67-community, 5.1.30-bzr | OS: | Linux (CentOS 5.2) |
| Assigned to: | Sergey Gluhov | Target Version: | 5.1+ |
| Tags: | foreign key, #, CREATE TABLE | ||
| Triage: | Triaged: D2 (Serious) | ||
[2 Oct 2008 0:07]
Miguel Solorzano
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 >
[2 Oct 2008 0:19]
Miguel Solorzano
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>
[2 Oct 2008 0: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 6:11]
Valeriy Kravchuk
It is clearly related to Linux/readline used by mysql command line client.
[2 Oct 2008 6: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 16: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 16: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 23:28]
Konstantin Osipov
A readline bug.
[7 Oct 2008 23:32]
Konstantin Osipov
Checked in the debugger, repeatable in the server, without effect of readline.
[13 May 9: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 10: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 9: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 9:41]
Bugs System
Pushed into 5.0.83 (revid:joro@sun.com-20090528073529-q9b8s60vlpu28fny) (version source revid:sergey.glukhov@sun.com-20090519063221-y5h10oavajlwnrkv) (merge vers: 5.0.83) (pib:6)
[28 May 10:13]
Bugs System
Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:satya.bn@sun.com-20090519083129-b14cdtxib1w2a6pz) (merge vers: 5.1.36) (pib:6)
[1 Jun 21: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 21:21]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:sergey.glukhov@sun.com-20090519065226-xiqkneemx6qce6ih) (merge vers: 6.0.12-alpha) (pib:11)
[26 Jun 0:27]
Paul DuBois
Noted in 5.4.4 changelog.
[13 Aug 0: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 4:07]
Paul DuBois
Ignore previous comment about 5.4.2.
[26 Aug 15:45]
Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 15:46]
Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 15:48]
Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 18:32]
Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[8 Oct 21:42]
Paul DuBois
The 5.4 fix has been pushed to 5.4.2.
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?