MySQL Bugs: #103553: Wrong FILE_NAME of innodb_temporary tablespace in INFORMATION

Bug #103553	Wrong FILE_NAME of innodb_temporary tablespace in INFORMATION_SCHEMA.FILES table
Submitted:	3 May 2021 0:10	Modified:	6 Jul 2021 19:38
Reporter:	Phong Dinh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	8.0.24	OS:	CentOS
Assigned to:		CPU Architecture:	x86
Tags:	regression

Description:
The FILE_NAME OF innodb_temporary tablespace is not synced with the configured value of innodb_temp_data_file_path  

mysql> select @@innodb_temp_data_file_path;
+-----------------------------------------+
| @@innodb_temp_data_file_path            |
+-----------------------------------------+
| ../../../mysqltemp/ibtmp1:1G:autoextend |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE
    ->        AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES
    ->        WHERE TABLESPACE_NAME = 'innodb_temporary'\G
*************************** 1. row ***************************
      FILE_NAME: ./ibtmp1
TABLESPACE_NAME: innodb_temporary
         ENGINE: InnoDB
   INITIAL_SIZE: 1073741824
 TotalSizeBytes: 1073741824
      DATA_FREE: 1053818880
   MAXIMUM_SIZE: NULL
1 row in set (0.00 sec)

The ibtmp1 file is still created in the desired folder. 

# ls -ltrh /mysqltemp/
total 1.1G
-rw-r-----. 1 mysql mysql 1.0G May  2 23:51 ibtmp1

How to repeat:
Setting the value of innodb_temp_data_file_path to a relative path with innodb_data_home_dir, for example

[mysqld]
innodb_temp_data_file_path=../../../mysqltemp/ibtmp1:1G:autoextend 

Suggested fix:
Change the value of FILE_NAME of innodb_temporary tablespace in INFORMATION_SCHEMA.FILES table to match with the value of innodb_temp_data_file_path

Hello Phong Dinh,

Thank you for the report and feedback.

regards,
Umesh

Posted by developer:
 
Fixed as of the upcoming 8.0.27 release, and here's the proposed changelog entry from the documentation team:

The INFORMATION_SCHEMA.FILES view did not show the current path of the
temporary tablespace file, and the file name shown was different from the
one defined by the innodb_temp_data_file_path variable.