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
-
Activity
During import, if cfg file is not specified, we don't update the autoinc field in innodb dictionary object dict_table_t. The next insert tries to
insert from the starting position of auto increment and fails.
It can be observed that the issue is resolved once server is restarted as the persistent value is read correctly from PAGE_ROOT_AUTO_INC from index root page. The patch fixes the issue by reading the the autoincrement value directly from PAGE_ROOT_AUTO_INC during import if cfg file is not specified.
Added required test changes to fix 2 test failures.
1. import_bugs.test: Embedded mode warning has absolute path. Regular
expression replacement in test.
2. full_crc32_import.test: Table level auto increment mismatch after
import. It was using the auto increment data from the table prior to
discard and import which is not right. This value has cached auto
increment value higher than the actual inserted value and value stored
in PAGE_ROOT_AUTO_INC. Updated the result file and added validation for
checking the maximum value of auto increment column.
pushed to 10.5
commit 66bb229e910bfeff9fd41ee9d8743344e0738025 (HEAD > 10.5, origin/10.5MDEV-18288, origin/10.5, 10.5-MDEV-18288)
Author: mariadb-DebarunBanerjee <debarun.banerjee@mariadb.com>
Date: Thu Feb 1 12:32:02 2024 +0530
If I understood the test case correctly (I did not try executing it), IMPORT TABLESPACE would fail to read the PAGE_ROOT_AUTO_INC of the imported table (
MDEV-6076), and also fail to fall back to reading the maximum value of the AUTO_INCREMENT column.Could this bug be worked around by the following?
This should reset the AUTO_INCREMENT attribute to the lowest possible value.