Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.2.4, 10.3.0, 10.4.0, 10.5.0
-
None
Description
When a tablespace is imported by first creating the table structure and then discarding the empty tablespace and importing a matching tablespace with a different number of rows, the value of AUTO_INCREMENT is undefined in the new table structure, so even though SHOW TABLE STATUS shows the correct number of rows, INSERTs relying on the AUTO_INCREMENT property of a primary key id column fail with
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
|
This attempted INSERT increments the table's AUTO_INCREMENT counter to '2' despite the error, and the error will continue to happen until the actual max value of the id column has been reached.
For example:
create table t1(
|
id int(10) unsigned not null auto_increment,
|
primary key (id)
|
) engine=InnoDB;
|
|
|
insert into t1() values();
|
insert into t1() values();
|
insert into t1() values();
|
|
|
flush tables test.t1 for export;
|
at this point t1 will contain id's 1, 2, and 3.
In the shell, copy t1.ibd to a safe location preserving ownership attributes.
Then back in the MariaDB monitor (console):
unlock tables;
|
drop table t1;
|
create table t1(
|
id int(10) unsigned not null auto_increment,
|
primary key (id)
|
) engine=InnoDB;
|
alter table t1 discard tablespace;
|
and back in the shell, copy t1.ibd back into the datadir while still preserving ownership attributes, and return to the MariaDB monitor.
alter table t1 import tablespace;
|
MariaDB [test]> show table status like 't1';
|
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+------------------+-----------+
|
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Max_index_length | Temporary |
|
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+------------------+-----------+
|
| t1 | InnoDB | 10 | Dynamic | 3 | 5461 | 16384 | 0 | 0 | 0 | 4 | 2019-01-17 16:09:40 | 2019-01-17 16:09:52 | NULL | latin1_swedish_ci | NULL | | | 0 | N |
|
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+------------------+-----------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> select count(*) from t1;
|
+----------+
|
| count(*) |
|
+----------+
|
| 3 |
|
+----------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> insert into t1() values();
|
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
|
|
|
MariaDB [test]> insert into t1() values();
|
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
|
|
|
MariaDB [test]> insert into t1() values();
|
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
|
|
|
MariaDB [test]> insert into t1() values();
|
Query OK, 1 row affected (0.006 sec)
|
Whenever the max value of id in the target table declaration is lower than the actual number of imported rows, this error happens.
Attachments
Issue Links
- relates to
-
MDEV-6076 Persistent AUTO_INCREMENT for InnoDB
-
- Closed
-
-
-
- Closed
-