Bug #84186 mysqlpump couldn't restore auto_increment column with secondary key
Submitted: 13 Dec 2016 16:47 Modified: 14 Dec 2016 6:50
Reporter: Tsubasa Tanaka (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqlpump Command-line Client Severity:S3 (Non-critical)
Version:5.7.17, 8.0.0 OS:CentOS (7.2)
Assigned to: CPU Architecture:Any

[13 Dec 2016 16:47] Tsubasa Tanaka 
Description:
mysqlpump with --defer-table-indexes creates CREATE TABLE statement which couldn't be restored.
Because --defer-table-indexes strips secondary key which is need by auto_increment column.

How to repeat:
mysql57> CREATE TABLE t1 (c1 int PRIMARY KEY, c2 int auto_increment, KEY(c2));
Query OK, 0 rows affected (0.01 sec)

mysql57> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) NOT NULL,
  `c2` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

$ bin/mysqlpump -S data/mysql.sock d1 t1
..
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `d1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
CREATE TABLE `d1`.`t1` (
`c1` int(11) NOT NULL,
`c2` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
;
USE `d1`;
ALTER TABLE `d1`.`t1` ADD KEY `c2` (`c2`);

CREATE TABLE `d1`.`t1` (
`c1` int(11) NOT NULL,
`c2` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
;

mysql57> CREATE TABLE `d1`.`t1` (
    -> `c1` int(11) NOT NULL,
    -> `c2` int(11) NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (`c1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    -> ;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
[13 Dec 2016 16:48] Tsubasa Tanaka 
Workaround is using --skip-defer-table-indexes.

$ bin/mysqlpump -S data/mysql.sock --skip-defer-table-indexes d1 t1
..
CREATE TABLE `d1`.`t1` (
  `c1` int(11) NOT NULL,
  `c2` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
..
[14 Dec 2016 6:50] MySQL Verification Team 
Hello Tanaka-San,

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

Thanks,
Umesh
[10 May 2019 20:05] MySQL Verification Team 
bug #93074 is set as duplicate of this one
[9 Oct 2019 7:27] Armin Noll 
Dear MySQL team,

are there any plans to fix this?
We would like to use the "defer-table-indexes" option to increase the speed of restoring.

Regards
Armin Noll
[19 Aug 2020 9:59] Jiri Sula 
Dear team, any chance someone focus on this?
Mysqlpump looks nice - but without this feature fixed it's not feasible to use it at all. 
Confirming bug still there, mysqlpump 8.0.19-10, running on Debian Stretch.
[8 Dec 2020 2:46] MySQL Verification Team 
https://bugs.mysql.com/bug.php?id=101898 marked as duplicate of this one.