| 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: | |
| 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 | |
[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
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;