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 |
[27 Sep 2015 19:58]
Giuseppe Maxia
[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