MySQL Bugs: #88499: mysqldump incorrectly dumps tables with self-reference foreign keys

Bug #88499	mysqldump incorrectly dumps tables with self-reference foreign keys
Submitted:	15 Nov 2017 10:49	Modified:	16 Nov 2017 8:30
Reporter:	Pavel Pushkarev	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.7.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysqldump when dumping a table with a self-reference foreign key adds indexes after the data when it should add them before.

How to repeat:
mysql -e 'create table test (a int, b int, index(a), index(b), foreign key (a) references test(b));'
mysqldump test test > test.sql
mysql -e 'drop table test' test
mysql test < test.sql

Suggested fix:
Move right hand side reference indices to the create statement

The workaround is to add --skip-innodb-optimize-keys to mysqldump when dumping.

Hello Pavel,

Thank you for the report and test case.
I'm not seeing any issues here while using official builds, are you using non-oracle builds? Please confirm.

==
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table test (a int, b int, index(a), index(b), foreign key (a) references test(b));
Query OK, 0 rows affected (0.01 sec)

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql>  insert into test values(1,1);
Query OK, 1 row affected (0.01 sec)

mysql>  insert into test values(2,2);
Query OK, 1 row affected (0.00 sec)

mysql> set foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: bin/mysqldump -uroot -S /tmp/mysql_ushastry.sock --databases test > t1.test
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: cat t1.test
-- MySQL dump 10.13  Distrib 5.7.20, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.7.20

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `test`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `test`;

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`),
  CONSTRAINT `test_ibfk_1` FOREIGN KEY (`a`) REFERENCES `test` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test`
--

LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test` VALUES (1,1),(2,2);
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-11-16  9:19:48
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test < t1.test
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20:

Also, the suggestion you mentioned doesn't exists in official binaries as well:

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.20: bin/mysqldump --help --verbose | grep optimize
                      default tables are dumped in a format optimized for

Thanks,
Umesh

Yes, it is a Percona build. Must be the Percona bug then. Sorry.

Thank you for confirming.

Thanks,
Umesh