MySQL Bugs: #77759: mysqlpump --defer-table-indexes mishandles in specific condition

Bug #77759	mysqlpump --defer-table-indexes mishandles in specific condition
Submitted:	17 Jul 2015 8:12	Modified:	19 Aug 2015 13:51
Reporter:	Tsubasa Tanaka (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.7.8, 5.7.9	OS:	CentOS
Assigned to:		CPU Architecture:	Any
Tags:	mysqlpump

Description:
mysqlpump with --defer-table-indexes mishandles when table has PRIMARY KEY and *NOT* has SECONDARY KEY and has FOREIGN KEY.

Displayed CREATE TABLE statement makes syntax error.

How to repeat:
mysql> CREATE TABLE t1 (num int PRIMARY KEY);
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE t2 (num int PRIMARY KEY, FOREIGN KEY (num) REFERENCES t1 (num));
Query OK, 0 rows affected (0.05 sec)

$ mysqlpump d1 t2
..
CREATE TABLE `d1`.`t2` (
`num` int(11) NOT NULL,
PRIMARY KEY (`num`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;
..

mysql> CREATE TABLE `d1`.`t2` (
    -> `num` int(11) NOT NULL,
    -> PRIMARY KEY (`num`),
    -> ) ENGINE=InnoDB 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 ') ENGINE=InnoDB DEFAULT CHARSET=latin1' at line 4

Hello Tanaka-San,

Thank you for the report.

Thanks,
Umesh

Noted in 5.7.9, 5.8.0 changelogs.

When mysqlpump was invoked with the --defer-table-indexes option, it 
could generate incorrect CREATE TABLE statements for tables with a
foreign key and a primary key but not a secondary index.