MySQL Bugs: #35531: Foreign keys: comment clause misplaced

Bug #35531	Foreign keys: comment clause misplaced
Submitted:	24 Mar 2008 17:50	Modified:	20 Apr 2008 15:44
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	6.1.0-fk-debug	OS:	Linux (SUSE 10 \| 32-bit)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
I'm using mysql-6.1-fk.
I start mysqld with --foreign-key-all-engines=1.

I create a table with a column clause followed by
a references clause.
I say SHOW CREATE TABLE.
The column clause is now after the references clause.
I try to create the table again using the clauses
that SHOW CREATE TABLE produces.
I fail.

How to repeat:
Script:

/* Notice that the second CREATE TABLE statement is
   a cut-and-paste from the results of SHOW CREATE TABLE. */

create table b5 (s1 int comment 'x' references b5(s1));
show create table b5\G
drop table b5;
CREATE TABLE `b5` (
      `s1` int(11) DEFAULT NULL CONSTRAINT `fk_b5_1_9689` REFERENCES `b5` (`s1`) COMMENT 'x'
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Example run:

mysql> create table b5 (s1 int comment 'x' references b5(s1));
Query OK, 0 rows affected (0.07 sec)

mysql> show create table b5\G
*************************** 1. row ***************************
       Table: b5
Create Table: CREATE TABLE `b5` (
  `s1` int(11) DEFAULT NULL CONSTRAINT `fk_b5_1_9689` REFERENCES `b5` (`s1`) COMMENT 'x'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> drop table b5;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `b5` (
    ->   `s1` int(11) DEFAULT NULL CONSTRAINT `fk_b5_1_9689` REFERENCES `b5` (`s1`) COMMENT 'x'
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COMMENT 'x'
) ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 2

Thank you for the bug report. Verified as described.

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/45719

ChangeSet@1.2609, 2008-04-20 18:57:10+04:00, dlenev@mockturtle.local +3 -0
  Fix for bug #35531 "Foreign keys: comment clause misplaced".
  
  Clauses for column referential constraints were erroneously
  placed before comment clause in SHOW CREATE TABLE output.
  Since with current syntax referential clause must be last
  clause of column definition it was impossible to correctly
  restore table with columns having both comment and referential
  constraint clause from SHOW CREATE TABLE output.
  
  Fixed SHOW CREATE TABLE to produce correct output.

Fix for this bug was pushed into 6.1-fk tree. Since this tree is not publicly available yet I am simply closing this bug.