| Bug #59197 | double quote in field comment prevents foreign key constraint creation | ||
|---|---|---|---|
| Submitted: | 28 Dec 2010 18:45 | Modified: | 11 Jan 2011 1:16 |
| Reporter: | Scott Noyes | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.5.8, 5.1 | OS: | Any |
| Assigned to: | Jimmy Yang | CPU Architecture: | Any |
[4 Jan 2011 8:04]
Jimmy Yang
While dict_strip_comments() handles "\'" as quote, function dict_scan_to() only recognize `` or "". So in dict_create_foreign_constraints_low(), following statement would not find FOREIGN keyword, as it recognize the single " character and consider everything after it are in quote, as it will not able to find the ending " character:
#1 0x085dbb81 in dict_create_foreign_constraints (trx=0x90a9ea8,
sql_string=0x90b9438 "CREATE TABLE t (a int COMMENT '\"', FOREIGN KEY (a) REFERENCES doesNotExist (a)) ENGINE=InnoDB", sql_length=93,
name=0xb2c6458c "test/t", reject_fks=0)
dict_create_foreign_constraints_low() -> ptr2 = dict_scan_to(ptr, "FOREIGN");
Fix would be make dict_scan_to() handle "\'" as quote.
[5 Jan 2011 3:27]
Jimmy Yang
From 5.1 plugin, the table structure is allocated with mem_heap_zalloc(), so it is not necessary explicitly set the variable to zero in dict_mem_table_create() any more.
[5 Jan 2011 6:46]
Jimmy Yang
Fix checked in 5.1 built-in, plugin.
[8 Jan 2011 15:08]
Bugs System
Pushed into mysql-trunk 5.6.2 (revid:vasil.dimov@oracle.com-20110108150732-8qygun7nuaqf9d3u) (version source revid:vasil.dimov@oracle.com-20110108150646-44c9j7ck64ocey31) (merge vers: 5.6.2) (pib:24)
[8 Jan 2011 15:09]
Bugs System
Pushed into mysql-5.5 5.5.9 (revid:vasil.dimov@oracle.com-20110108150508-gpanhz48z8069qot) (version source revid:vasil.dimov@oracle.com-20110108150048-b1y9m8xe72hay0ch) (merge vers: 5.5.9) (pib:24)
[8 Jan 2011 15:11]
Bugs System
Pushed into mysql-5.1 5.1.55 (revid:vasil.dimov@oracle.com-20110108145923-0tjqdpa5w3d6tuwn) (version source revid:vasil.dimov@oracle.com-20110108145923-0tjqdpa5w3d6tuwn) (merge vers: 5.1.55) (pib:24)
[11 Jan 2011 1:16]
John Russell
Added to changelog: The presence of a double quotation mark inside the COMMENT field for a column could prevent a foreign key constraint from being created properly.
[21 May 2012 22:45]
John Russell
Changelog entry appears in 5.6.2, 5.5.9, 5.1.55.
Description: A double quote (") in a field comment prevents foreign key constraints from being created properly. Instead they are created as simple keys. Similar to http://bugs.mysql.com/bug.php?id=39793 How to repeat: -- With no double quote, we get the expected behavior (the error is expected because the referenced table doesn't exist): mysql> CREATE TABLE t (a int COMMENT '', FOREIGN KEY (a) REFERENCES doesNotExist (a)) ENGINE=InnoDB; ERROR 1005 (HY000): Can't create table 'test.t' (errno: 150) -- ought to fail just the same as the above, but doesn't: mysql> CREATE TABLE t (a int COMMENT '"', FOREIGN KEY (a) REFERENCES doesNotExist (a)) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> SHOW CREATE TABLE t; +-------+-------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------+ | t | CREATE TABLE `t` ( `a` int(11) DEFAULT NULL COMMENT '"', KEY `a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)