Description:
Can't restore by loadDump with --deferTableIndexes=all option when the table is under these conditions.
- Has AUTO_INCREMENT column
- Has secondary index associates AUTO_INCREMENT column
- Has secondary index not-associates AUTO_INCREMENT column
loadDump fails "MySQL Error 1061 (42000): Duplicate key name 'xxx'", because trying to create same index twice on the non-associates AUTO_INCREMENT column.
General logs are,
```
2022-12-08T16:17:43.405632+09:00 220 Query -- MySQLShell dump 2.0.1 Distrib Ver 8.0.31 for Linux on x86_64 - for MySQL 8.0.31 (MySQL Community Server (GPL)), for Linux (x86_64)
2022-12-08T16:17:43.405769+09:00 220 Query --
2022-12-08T16:17:43.405875+09:00 220 Query -- Host: localhost Database: test Table: t1
2022-12-08T16:17:43.405977+09:00 220 Query -- ------------------------------------------------------
2022-12-08T16:17:43.406111+09:00 220 Query -- Server version 8.0.31
2022-12-08T16:17:43.406240+09:00 220 Query --
2022-12-08T16:17:43.406341+09:00 220 Query -- Table structure for table `t1`
2022-12-08T16:17:43.406440+09:00 220 Query --
2022-12-08T16:17:43.406592+09:00 220 Query /*!40101 SET @saved_cs_client = @@character_set_client */
2022-12-08T16:17:43.406765+09:00 220 Query /*!50503 SET character_set_client = utf8mb4 */
2022-12-08T16:17:43.407034+09:00 220 Query CREATE TABLE IF NOT EXISTS `t1` (
`num` int unsigned NOT NULL AUTO_INCREMENT,
`val` varchar(32) COLLATE utf8mb4_ja_0900_as_cs DEFAULT NULL,
PRIMARY KEY (`num`),
KEY `val` (`val`),
KEY `val_2` (`val`,`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
2022-12-08T16:17:43.432469+09:00 220 Query /*!40101 SET character_set_client = @saved_cs_client */
2022-12-08T16:17:43.433771+09:00 221 Query USE `test`
2022-12-08T16:17:43.433974+09:00 221 Query SET SQL_MODE = 'no_auto_value_on_zero'
2022-12-08T16:17:43.434106+09:00 221 Query SET NAMES 'utf8mb4'
2022-12-08T16:17:43.434270+09:00 221 Query SET autocommit = 1
2022-12-08T16:17:43.434400+09:00 221 Query SET unique_checks = 0
2022-12-08T16:17:43.434513+09:00 221 Query SET foreign_key_checks = 0
2022-12-08T16:17:43.434674+09:00 221 Query SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
2022-12-08T16:17:43.434961+09:00 221 Query /* mysqlsh loadDump(), thread 0, table `test`.`t1`, chunk ID: 0 */ LOAD DATA LOCAL INFILE '/tmp/bugtest/test@t1@@0.tsv.zst' REPLACE INTO TABLE `test`.`t1` CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY ' ' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' (`num`, `val`)
2022-12-08T16:17:43.441459+09:00 221 Query ALTER TABLE `test`.`t1` ADD KEY `val` (`val`)
2022-12-08T16:17:43.442918+09:00 221 Quit
```
loadDump try to create KEY `val` (`val`) which is already in CREATE TABLE statement.
How to repeat:
### Generate table
$ mysqlsh -S /usr/mysql/8.0.31/data/mysql.sock -uroot --sql
MySQL localhost SQL > CREATE DATABASE test;
MySQL localhost SQL > CREATE TABLE test.t1 (num INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, val varchar(32), KEY(val), KEY(val, num));
### Get dump
$ mysqlsh -S /usr/mysql/8.0.31/data/mysql.sock -uroot --js -- util dumpSchemas test --output-url=/tmp/bugtest
### Remove current test schema.
$ mysqlsh -S /usr/mysql/8.0.31/data/mysql.sock -uroot --sql -e "DROP DATABASE test"
### loadDump with deferTableIndexes=all
$ mysqlsh -S /usr/mysql/8.0.31/data/mysql.sock -uroot --js -- util loadDump /tmp/bugtest --deferTableIndexes=all
Loading DDL and Data from '/tmp/bugtest' using 4 threads.
Opening dump...
Target is MySQL 8.0.31. Dump was produced from MySQL 8.0.31
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
?% (0 bytes / ?), 0.00 B/s, 0 / 1 tables done
ERROR: While recreating indexes for table `test`.`t1`: MySQL Error 1061 (42000): Duplicate key name 'val': ALTER TABLE `test`.`t1` ADD KEY `val` (`val`)
ERROR: [Worker000] While recreating indexes for table `test`.`t1`: Duplicate key name 'val'
ERROR: Aborting load...
Recreating indexes - done
No data loaded.
1 errors and 0 warnings messages were reported during the load.
ERROR: Error loading dump
Suggested fix:
Do not include the "secondary index not-associates AUTO_INCREMENT column" in CREATEA TABLE statement.
