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
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