[#MDEV-18288] Transportable Tablespaces leave AUTO_INCREMENT in mismatched state, causing INSERT errors in newly imported tables when .cfg is not used.

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.

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.

[MDEV-18288] Transportable Tablespaces leave AUTO_INCREMENT in mismatched state, causing INSERT errors in newly imported tables when .cfg is not used. Created: 2019-01-17 Updated: 2024-02-06 Resolved: 2024-02-06
Status:	Closed
Project:	MariaDB Server
Component/s:	Data Definition - Alter Table
Affects Version/s:	10.2.4, 10.3.0, 10.4.0, 10.5.0
Fix Version/s:	10.5.25, 10.6.18, 10.11.8, 11.0.6, 11.1.5, 11.2.4, 11.3.3, 11.4.2

[MDEV-18288] Transportable Tablespaces leave AUTO_INCREMENT in mismatched state, causing INSERT errors in newly imported tables when .cfg is not used. Created: 2019-01-17 Updated: 2024-02-06 Resolved: 2024-02-06