Bug #110429 Incorrect autoinc in imported tablespace after restart server

• Submitted: 20 Mar 2023 5:56
• Modified: 20 Mar 2023 9:54
• Reporter: Rahul Malik
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 8.0.31, 8.0.32
• OS: Any
• CPU Architecture: Any
• Tags: autoinc

[20 Mar 2023 5:56] Rahul Malik

Description:
If server is restarted after an import of tablespace. It does not show correct autoinc value and throws 
ERROR 1062 (23000): Duplicate entry '1' for key 't1.i'

CURRENT_TEST: innodb.export_autoinc
mysqltest: At line 30: Query 'INSERT INTO t1 VALUES(null)' failed.
ERROR 1062 (23000): Duplicate entry '1' for key 't1.i'

 

How to repeat:
--disable_warnings
drop table if exists t1;
--enable_warnings

CREATE TABLE t1( i serial);
INSERT INTO t1 VALUES(null);

--source suite/innodb/include/import.inc

--echo # Flush the table and put IBD/CFG files aside for later import.
FLUSH TABLE t1 FOR EXPORT;

--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/t1.ibd_back
--echo "copy done"
--copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/t1.cfg_back

UNLOCK TABLES;

ALTER TABLE t1 DISCARD TABLESPACE;

--copy_file $MYSQLD_DATADIR/t1.ibd_back $MYSQLD_DATADIR/test/t1.ibd
--copy_file $MYSQLD_DATADIR/t1.cfg_back $MYSQLD_DATADIR/test/t1.cfg

ALTER TABLE t1 IMPORT TABLESPACE;

INSERT INTO t1 VALUES(null);

--source include/restart_mysqld.inc

INSERT INTO t1 VALUES(null);

[20 Mar 2023 5:57] Rahul Malik

test case to reproduce

Attachment: export_autoinc.test (application/octet-stream, text), 738 bytes.

[20 Mar 2023 9:54] MySQL Verification Team

Hello Rahul,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh