MySQL Bugs: #100965: import table has auto increment, after restart,report Duplicate entry error

Bug #100965	import table has auto increment, after restart,report Duplicate entry error
Submitted:	27 Sep 2020 1:20	Modified:	28 Sep 2020 14:38
Reporter:	aceking king	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Dictionary	Severity:	S3 (Non-critical)
Version:	8.0.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
import table which contain auto_increment column,
then restart the DB server, insert the new row, it will report error :

ERROR 1062 (23000): Duplicate entry '1' for key 't2.PRIMARY'

the mysql-test script as follow.

How to repeat:

-- let $MYSQLD_DATADIR= `select @@datadir`
create table t1(a int primary key auto_increment, b int);
create table t2(a int primary key auto_increment, b int);
insert into t1 values(NULL,1),(NULL,1),(NULL,1),(NULL,1),(NULL,1);
alter table t2 discard tablespace;
flush table t1 for export;
# copy_file t1.ibd t2.ibd
-- let $MYSQLD_DATADIR= `select @@datadir`
-- copy_file $MYSQLD_DATADIR/test/t1.ibd  $MYSQLD_DATADIR/test/t2.ibd
-- copy_file $MYSQLD_DATADIR/test/t1.cfg  $MYSQLD_DATADIR/test/t2.cfg

unlock tables;
alter table t2 import tablespace;

--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
-- send_shutdown
--source include/wait_until_disconnected.inc
# restart
--exec echo "restart:" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--enable_reconnect
--source include/wait_until_connected_again.inc
--disable_reconnect
insert into t2 values(NULL,2);

Hi,

Verified as described. Thanks for the report

Bogdan

Is there a workaround to this issue? Any command I can execute on the table to fix the issue post importing the tablespace? 

Auto increment is the fundamental part of the RDBMS and it is surprising to me that it breaks with the tablespace import after the restart.

I think I figured out the workaround. After reboot, I can set the AUTO_INCREMENT value on the table and then it works fine. 

Get current AUTO_INCREMENT VALUE;

SELECT AUTO_INCREMENT as auto_increment from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '<schema>' AND TABLE_NAME = '<table>'

Set AUTO_INCREMENT on table with imported tablespace

ALTER TABLE t2 AUTO_INCREMENT = <number>;

For those who run into this issue, I hope this helps until the official fix rolls out with future MySQL release. 

This is a crude workaround, but it works.

Even better solution is to reset the AUTO_INCREMENT and let MySQL handle the rest after the restart.

ALTER TABLE <table_name> AUTO_INCREMENT = 0;