MySQL Bugs: #115951: ALTER TABLE ... IMPORT TABLESPACE may cause unsafe reads

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:	Documenting	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

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

Hello!

Thank you for the report and test case.

regards,
Umesh

8.0.39 test results

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

After applying the above patch:

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

Hello Huahua Xu! 
Thank you for offering a patch.
Can you please sign the OCA (see Contributions tab, https://bugs.mysql.com/bug.php?id=115951&contribs=1 ) so we can use it?

Posted by developer: Bug status updated to 'Patch pending'

Posted by developer: Bug status updated to 'Patch approved'

Hello - turned out the patch had to be substantially larger (to include permanent changes to DD, partitioned tables, and MTR tests) and different than the contribution.

Also AFAICT the OCA was not yet signed.

For these two reasons this commit will not be explicitly marked as a contribution.

Still, I'd like to thank you for bringing this issue to our attention, and proposing a sketch!

Posted by developer: Bug status updated to 'Documenting'