Bug #78593 mysqlpump creates incorrect ALTER TABLE statement for foreign key
Submitted: 27 Sep 2015 19:58 Modified: 26 Oct 2015 12:38
Reporter: Giuseppe Maxia (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqlpump Command-line Client Severity:S1 (Critical)
Version:5.7.9, 5.7.10, 8.0.0 OS:Any
Assigned to: CPU Architecture:Any

[27 Sep 2015 19:58] Giuseppe Maxia
Description:
when exporting a table with a FK, mysqlpump generates an incorrect ALTER TABLE statelemt.

While mysqldump does this:

CREATE TABLE `t2` (
  `ID` bigint(20) NOT NULL DEFAULT '0',
  `k` varchar(30) NOT NULL DEFAULT '',
  `v` bigint(20) NOT NULL,
  PRIMARY KEY (`ID`,`v`,`k`),
  CONSTRAINT `relation_tags_ibfk_1` FOREIGN KEY (`ID`, `v`) REFERENCES `t1` (`ID`, `v`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysqlpump, instead, does this:
CREATE TABLE `test`.`t2` (
`ID` bigint(20) NOT NULL DEFAULT '0',
`k` varchar(30) NOT NULL DEFAULT '',
`v` bigint(20) NOT NULL,
PRIMARY KEY (`ID`,`v`,`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;
USE `test`;
ALTER TABLE `test`.`t2` ADD CONSTRAINT `relation_tags_ibfk_1` FOREIGN KEY (`ID`, `v`) REFERENCES `t1` (`ID` `v`);

Notice that there is no comma between `ID' and `v' 

How to repeat:
DROP TABLE if exists t1;
CREATE TABLE t1 (
      ID bigint NOT NULL DEFAULT '0',
      v bigint NOT NULL,
      PRIMARY KEY (ID,v)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
      ID bigint NOT NULL DEFAULT '0',
      k varchar(30) NOT NULL DEFAULT '',
      v bigint NOT NULL,
      PRIMARY KEY (ID, v, k),
      CONSTRAINT relation_tags_ibfk_1 FOREIGN KEY (ID, v) REFERENCES t1 (ID, v)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
[28 Sep 2015 5:17] MySQL Verification Team
Hello Giuseppe Maxia,

Thank you for the report and test case.
Verified as described with 5.7.10 build.

Thanks,
Umesh
[28 Sep 2015 5:27] MySQL Verification Team
//

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: more 78593_mysqlpump.sql
-- Dump created by MySQL pump utility, version: 5.7.10, Linux (x86_64)
-- Dump start time: Mon Sep 28 07:15:17 2015
-- Server version: 5.7.10

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE TABLE `test`.`t1` (
`ID` bigint(20) NOT NULL DEFAULT '0',
`v` bigint(20) NOT NULL,
PRIMARY KEY (`ID`,`v`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;
CREATE TABLE `test`.`t2` (
`ID` bigint(20) NOT NULL DEFAULT '0',
`k` varchar(30) NOT NULL DEFAULT '',
`v` bigint(20) NOT NULL,
PRIMARY KEY (`ID`,`v`,`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;
USE `test`;
ALTER TABLE `test`.`t2` ADD CONSTRAINT `relation_tags_ibfk_1` FOREIGN KEY (`ID`, `v`) REFERENCES `t1` (`ID` `v`);
SET TIME_ZONE=@OLD_TIME_ZONE;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- Dump end time: Mon Sep 28 07:15:17 2015

### Try to restore it

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.10-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop database test;
Query OK, 2 rows affected (0.01 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `test`.`t1` (
    -> `ID` bigint(20) NOT NULL DEFAULT '0',
    -> `v` bigint(20) NOT NULL,
    -> PRIMARY KEY (`ID`,`v`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `test`.`t2` (
    -> `ID` bigint(20) NOT NULL DEFAULT '0',
    -> `k` varchar(30) NOT NULL DEFAULT '',
    -> `v` bigint(20) NOT NULL,
    -> PRIMARY KEY (`ID`,`v`,`k`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> USE `test`;
Database changed
mysql> ALTER TABLE `test`.`t2` ADD CONSTRAINT `relation_tags_ibfk_1` FOREIGN KEY (`ID`, `v`) REFERENCES `t1` (`ID` `v`);
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 '`v`)' at line 1
mysql>
[28 Sep 2015 5:29] MySQL Verification Team
// build used for tests

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: cat docs/INFO_SRC
commit: c70ec0ed106ddd0b9ec0adb551be63b99d969c5d
date: 2015-09-27 04:27:17 +0200
build-date: 2015-09-27 18:04:34 +0200
short: c70ec0e
branch: mysql-5.7

MySQL source 5.7.10
[28 Sep 2015 5:47] MySQL Verification Team
// 5.8.0 also affected

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.8.0: cat docs/INFO_SRC
commit: 839307e95f11d1ffa9829cbcc5300e9d319210b0
date: 2015-09-27 04:27:34 +0200
build-date: 2015-09-27 17:01:54 +0200
short: 839307e
branch: mysql-trunk

MySQL source 5.8.0

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.8.0: cat 78593_mysqlpump.sql
-- Dump created by MySQL pump utility, version: 5.8.0-m17, Linux (x86_64)
-- Dump start time: Mon Sep 28 07:44:52 2015
-- Server version: 5.8.0

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE TABLE `test`.`t1` (
`ID` bigint(20) NOT NULL DEFAULT '0',
`v` bigint(20) NOT NULL,
PRIMARY KEY (`ID`,`v`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;
CREATE TABLE `test`.`t2` (
`ID` bigint(20) NOT NULL DEFAULT '0',
`k` varchar(30) NOT NULL DEFAULT '',
`v` bigint(20) NOT NULL,
PRIMARY KEY (`ID`,`v`,`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;
USE `test`;
ALTER TABLE `test`.`t2` ADD CONSTRAINT `relation_tags_ibfk_1` FOREIGN KEY (`ID`, `v`) REFERENCES `t1` (`ID` `v`);
SET TIME_ZONE=@OLD_TIME_ZONE;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- Dump end time: Mon Sep 28 07:44:52 2015
[7 Oct 2015 16:04] Paul DuBois
Noted in 5.7.9, 5.8.0 changelogs.

mysqlpump generated incorrect ALTER TABLE statements for adding
foreign keys.
[25 Oct 2015 3:48] Giuseppe Maxia
The bug is still present in MySQL 5.7.9 GA.

(This uses docker Linux image mysql/mysql-server, but I get the same result with MySQL GA on OSX)

[root@388740e704e0 /]# mysqlpump test
-- Dump created by MySQL pump utility, version: 5.7.9, Linux (x86_64)
-- Dump start time: Sun Oct 25 03:44:33 2015
-- Server version: 5.7.9

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE TABLE `test`.`t1` (
`ID` bigint(20) NOT NULL DEFAULT '0',
`v` bigint(20) NOT NULL,
PRIMARY KEY (`ID`,`v`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;
CREATE TABLE `test`.`t2` (
`ID` bigint(20) NOT NULL DEFAULT '0',
`k` varchar(30) NOT NULL DEFAULT '',
`v` bigint(20) NOT NULL,
PRIMARY KEY (`ID`,`v`,`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;
Dump progress: 1/2 tables, 0/0 rows
USE `test`;
ALTER TABLE `test`.`t2` ADD CONSTRAINT `relation_tags_ibfk_1` FOREIGN KEY (`ID`, `v`) REFERENCES `t1` (`ID` `v`);               #### <--- Missing comma
SET TIME_ZONE=@OLD_TIME_ZONE;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- Dump end time: Sun Oct 25 03:44:33 2015
Dump completed in 363 milliseconds
[26 Oct 2015 1:25] Erlend Dahl
As far as I can see, the fix was pushed to 5.7.10 and not to 5.7.9 as it was claimed in the bug report. I have sent the bug report back to the docs team to get this corrected.
[26 Oct 2015 12:38] Paul DuBois
Correction: This was pushed to 5.7.10, not 5.7.9.
[18 Jun 2016 21:28] Omer Barnir
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0