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

[17 Jul 2015 8:12] Tsubasa Tanaka
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
[17 Jul 2015 8:34] MySQL Verification Team
Hello Tanaka-San,

Thank you for the report.

Thanks,
Umesh
[19 Aug 2015 13:51] Paul DuBois
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.