Bug #93074 mysqlpump auto_increment and defer-table-indexes
Submitted: 4 Nov 2018 14:11 Modified: 10 May 20:06
Reporter: Bogdan Kecman Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: mysqlpump Command-line Client Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[4 Nov 2018 14:11] Bogdan Kecman
Description:
--defer-table-indexes is default option for mysqlpump

if there is a table with AUTO_INCREMENT field that is UNIQUE key and not PRIMARY key the import of the generated sql will not work.

How to repeat:
mysql [localhost] {root} (test) > CREATE TABLE `test`.`defertableindexesbug` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `f` int(11) DEFAULT NULL,
    -> UNIQUE KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql [localhost] {root} (test) > INSERT INTO `test`.`defertableindexesbug` VALUES (1,1),(2,2),(3,2),(4,2),(5,3);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql [localhost] {root} (test) > \q
Bye
[arhimed@localhost msb_8_0_11]$ ./my sqlpump --defer-table-indexes test defertableindexesbug
-- Dump created by MySQL pump utility, version: 8.0.11, linux-glibc2.12 (x86_64)
-- Dump start time: Sun Nov  4 15:08:24 2018
-- Server version: 8.0.11

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET @@SESSION.SQL_LOG_BIN= 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 utf8mb4 COLLATE utf8mb4_0900_ai_ci */;
CREATE TABLE `test`.`defertableindexesbug` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`f` int(11) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;
INSERT INTO `test`.`defertableindexesbug` VALUES (1,1),(2,2),(3,2),(4,2),(5,3);
Dump progress: 1/1 tables, 0/5 rows
USE `test`;
ALTER TABLE `test`.`defertableindexesbug` ADD UNIQUE KEY `id` (`id`);
mysqlpump: [WARNING] (3719) 'utf8' is currently an alias for the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
mysqlpump: [WARNING] (3719) 'utf8' is currently an alias for the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
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;
SET SQL_MODE=@OLD_SQL_MODE;
-- Dump end time: Sun Nov  4 15:08:25 2018
Dump completed in 808
[arhimed@localhost msb_8_0_11]$
mysql [localhost] {root} (test) > drop table defertableindexesbug;
Query OK, 0 rows affected (0.05 sec)

mysql [localhost] {root} (test) > CREATE TABLE `test`.`defertableindexesbug` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `f` int(11) DEFAULT NULL
    -> ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    -> ;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql [localhost] {root} (test) >

Suggested fix:
do not defer unique keys on auto columns
[23 Feb 17:32] Seth Willits
Duplicate of 84186?
[10 May 20:06] Bogdan Kecman
you are right, it is duplicate of Bug #84186

thanks