Bug #79992 mysqldump 5.7.10 cannot be used to reload database

• Submitted: 14 Jan 2016 20:53
• Modified: 15 Jan 2016 5:58
• Reporter: Chris Muench
• Status: Duplicate
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S2 (Serious)
• Version: 5.7.10
• OS: MacOS
• CPU Architecture: Any

[14 Jan 2016 20:53] Chris Muench

Description:
I have a database with 59 tables that I backed up using mysqldump. It backs up fine; but I cannot restore it via source DUMPFILE.sql. This only happens in mysq; 5.7 and does not happen in mysql 5.6. The first error I get is:

#1215 - Cannot add foreign key constraint 

Here is an example dump that it happens on (Dump is done in mysql 5.7.10 and attempted to reload onto same machine)

https://www.dropbox.com/s/qf471aq1yijnnhz/database_mysql_5_7_bug.sql?dl=0

How to repeat:
1. Create database or use database that already exists and is running in mysql 5.7
2. mysqldump DATABSE > dump.sql;
3. Create empty database
4. mysql EMPTY_DATABASE < dump.sql;
5. Observe errors

cmuench-air:Dropbox cmuench$ mysql pos < database_mysql_5_7_bug.sql;
ERROR 1215 (HY000) at line 25: Cannot add foreign key constraint
cmuench-air:Dropbox cmuench$

[14 Jan 2016 21:09] Chris Muench

I think it might be related to https://bugs.mysql.com/bug.php?id=78955

[15 Jan 2016 5:58] MySQL Verification Team

Hello Chris Muench,

Thank you for the report.
This is duplicate of Bug #78955, and issue is fixed as of the upcoming 5.7.11, 5.8.0 releases.

I tried restore dump provided in the test case, 5.7.10 has issues as reported but none observed while restoring on 5.7.11.

---
-- 5.7.10 - issues while restoring

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10:  bin/mysql -uroot -S /tmp/mysql_ushastry.sock --verbose test < ../testcases/79992.sql
.
.

--------------
CREATE TABLE `phppos_additional_item_numbers` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `item_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`item_id`,`item_number`),
  UNIQUE KEY `item_number` (`item_number`),
  FULLTEXT KEY `item_number_search` (`item_number`),
  CONSTRAINT `phppos_additional_item_numbers_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
--------------

ERROR 1215 (HY000) at line 25: Cannot add foreign key constraint

-- 5.7.11 - restored without any issues

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.11:  bin/mysql -uroot -S /tmp/mysql_ushastry.sock --verbose test < ../testcases/79992.sql                                        --------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
--------------

.

--------------
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */
--------------

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.11

Thanks,
Umesh