Description:
LOAD DATA INFILE doesn't reset LEX::many_values, so the handler::update_auto_increment() uses LEX::many_values.elements counter of the
previous bulk INSERT statement as a gap to reserve.
Thus, if a bulk INSERT inserts 1000 records, then we execute some statements other than bulk INSERT, and then we do a LOAD DATA INFILE to insert only few
records, then after that LOAD statement the autoincrement counter will be incremented by 1000!
How to repeat:
mysql> INSERT INTO t1 VALUES (NULL), (NULL);
Query OK, 2 rows affected (0,00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * INTO OUTFILE 'tmp.txt' FROM t1;
Query OK, 2 rows affected (0,00 sec)
mysql> CREATE TABLE t2 (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0,02 sec)
mysql> INSERT INTO t2 VALUES (), (), (), (), (), (), (), (), (), ();
Query OK, 10 rows affected (0,00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t2;
+----+
| i |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
10 rows in set (0,00 sec)
mysql> LOAD DATA INFILE 'tmp.txt' INTO TABLE t2;
Query OK, 2 rows affected (0,00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM t2;
+----+
| i |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
+----+
12 rows in set (0,00 sec)
mysql> INSERT INTO t2 VALUES (), ();
Query OK, 2 rows affected (0,00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t2;
+----+
| i |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 21 | # 13 is expected!
| 22 | # 14 is expected!
+----+
14 rows in set (0,00 sec)
Suggested fix:
WL#7201 will fix that bug.
