MySQL Bugs: #111709: Error missing tablespace if innodb-validate-tablespace-paths=OFF

Bug #111709	Error missing tablespace if innodb-validate-tablespace-paths=OFF
Submitted:	10 Jul 2023 14:52	Modified:	3 Aug 2023 1:58
Reporter:	Huaxiong Song (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.33	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When mysql-server was normal shutdown and startu up with --innodb-validate-tablespace-paths=OFF, teh error of missing tablespaces will happen sometimes. 

How to repeat:
#=============== step-1 ===============
CREATE DATABASE test;
USE test;
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE=InnoDB;
SHUTDOWN;

#=============== step-2 ===============
#start mysqld with --innodb-validate-tablespace-paths=OFF
CREATE TABLE test.t1(a int) TABLESPACE=ts1 ENGINE = InnoDB;

error happens like:
ERROR 1812 (HY000): InnoDB: A general tablespace named `ts1` cannot be found.

Suggested fix:
Analysis:
When the above happens, the function "fil_ibd_open" will not be calledm which results in struct fil_space_t not being constructed. In some places such as "create_table_info_t::create_option_tablespace_is_valid", "innobase_get_tablespace_statistics"(called by information_schema.files) and so on , fil_space_t is required. If the above happens fil_space_t has not been created(which is nullptr), missing tablespace error happens.

So what's the problem with this?
case 1: create table with tablespace will be failed.
case 2: query from information_schema.files will be wrong.
case 3: every case about visit a nullptr fil_space_t, which should not actually be nullptr.

There are many test cases will failed if --innodb-validate-tablespace-paths=OFF for this reason. In my opinion, this parameter is a startup-related parameter, and all tests or most cases should pass when it is OFF. Perhaps the test cases related to startup should be more complete.

Fix:
Create fil_space_t when visit nullptr, which should not actually be nullptr.

Hello Huaxiong Song,

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

regards,
Umesh

Thanks for reporting this!

May I ask, what's your use case for disabling validation?

I'm doing some research on startup acceleration, which has similar characteristics to this parameter, so I found it. ^-^

After setting innodb_validate_tablespace_paths to OFF and running the test case, the failed cases can be found. Most of these problems are related to fil_space_t not being built in time. At the same time, there is also the problem that the old partition table(version < 8.0.18) move operation is not executed, which will also cause some problems. I think setting this parameter to OFF is very helpful to improve the startup speed (especially in the case of a large number of tables). A simple way to deal with it is: setting this parameter to OFF does not skip fil_ibd_open, but just skips validate_to_dd. Because inside fil_ibd_open, validate_to_dd is an IO operation, which takes most of the time compared to other memory operations.