Bug #115951 ALTER TABLE ... IMPORT TABLESPACE may cause unsafe reads
Submitted: 28 Aug 2024 12:17 Modified: 28 Aug 2024 12:27
Reporter: yuan ting Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:8.0.32, 8.0.39, 8.4.2 OS:Linux
Assigned to: CPU Architecture:Any
Tags: import

[28 Aug 2024 12:17] yuan ting
Description:
During the ALTER TABLE ... IMPORT TABLESPACE operation, the trx_id in dd::index remains unchanged. In contrast, the trx_id of the records in the clustered index of the table is set to the "import" transaction ID, while the rollback pointer (rollptr) is assigned a value of 0.

When a transaction with REPEATABLE READ isolation level is initiated before the import and later attempts to read the imported table, the unchanged trx_id in dd::index misleads the system into believing that the index was created prior to the transaction. Consequently, when the system attempts to read data, it encounters records created by a newer transaction, which are not visible to the current read view established prior to the import. This situation leads the system to attempt to access a previous version of the record using an invalid rollptr valued at 0. This poses a significant risk and can result in server crashes.

How to repeat:
Below is a case provided in MTR format.

--source include/have_debug.inc

connect (con1,localhost,root,,);

connection default;

create table t1 (c1 int, c2 int, primary key(c1));
insert into t1 values (1, 100);
insert into t1 values (2, 200);

create table t2 like t1;
create table t3 like t1;
insert into t2 select * from t1;
insert into t3 select * from t1;

SET SESSION debug='+d,skip_dd_table_access_check';
SELECT tbl.name as table_name, idx.* FROM mysql.tables tbl JOIN mysql.indexes idx ON tbl.id = idx.table_id WHERE tbl.name = 't2';

connection con1;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
start transaction;
select * from t1;

connection default;
ALTER TABLE t2 DISCARD TABLESPACE;

FLUSH TABLES t3 FOR EXPORT;
LET $MYSQLD_DATADIR = `SELECT @@datadir`;
--copy_file $MYSQLD_DATADIR/test/t3.ibd $MYSQLTEST_VARDIR/tmp/t2.ibd
--copy_file $MYSQLD_DATADIR/test/t3.cfg $MYSQLTEST_VARDIR/tmp/t2.cfg
UNLOCK TABLES;
--copy_file $MYSQLTEST_VARDIR/tmp/t2.ibd $MYSQLD_DATADIR/test/t2.ibd
--copy_file $MYSQLTEST_VARDIR/tmp/t2.cfg $MYSQLD_DATADIR/test/t2.cfg

ALTER TABLE t2 IMPORT TABLESPACE;

--remove_file $MYSQLTEST_VARDIR/tmp/t2.ibd
--remove_file $MYSQLTEST_VARDIR/tmp/t2.cfg

SELECT tbl.name as table_name, idx.* FROM mysql.tables tbl JOIN mysql.indexes idx ON tbl.id = idx.table_id WHERE tbl.name = 't2';

connection con1;
select * from t2;

connection default;

disconnect con1;
drop table t1;
drop table t2;
drop table t3;

I encountered an assertion failure when executing the query SELECT * FROM t2;. 
The error message was as follows:
[ERROR] [MY-013183] [InnoDB] Assertion failure: trx0rec.ic:95:len < ((ulint)srv_page_size)

I examined the trx_id of the clustered index for table t2 both before and after the import operation, and I found that they remained unchanged.
SELECT tbl.name as table_name, idx.* FROM mysql.tables tbl JOIN mysql.indexes idx ON tbl.id = idx.table_id WHERE tbl.name = 't2';
table_name	id	table_id	name	type	algorithm	is_algorithm_explicit	is_visible	is_generated	hidden	ordinal_position	comment	options	se_private_data	tablespace_id	engine	engine_attribute	secondary_engine_attribute
t2	290	369	PRIMARY	PRIMARY	BTREE	0	1	0	0	1		flags=0;	id=161;root=4;space_id=7;table_id=1070;trx_id=1563;	12	InnoDB	NULL	NULL
[28 Aug 2024 12:27] MySQL Verification Team
Hello!

Thank you for the report and test case.

regards,
Umesh
[28 Aug 2024 12:29] MySQL Verification Team
8.0.39 test results

Attachment: 115951.results.txt (text/plain), 95.27 KiB.

[14 Sep 2024 11:36] huahua xu
After applying the above patch:

mysql> select * from t2;
ERROR 1412 (HY000): Table definition has changed, please retry transaction