MySQL Bugs: #93126: innodb temp data file can not truncate or resize online

Bug #93126	innodb temp data file can not truncate or resize online
Submitted:	8 Nov 2018 13:47	Modified:	18 Nov 2018 5:54
Reporter:	Ye Jinrong	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	8.0.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ACE

Description:
if there are many temporary table, they can increase innodb temp data file larger and larger,
in extreme, it can cause disk be filled up full, but it can not truncate or reclaim online,
even when temporary tables are deleted or truncate, disk space cannot be reclaimed also.

How to repeat:
create many large temporary table

Suggested fix:
innodb temp data file can design like undo log file, they can be truncated online and reclaim disk space.
thanks.

8.0.13 should have fixed this problem, did you test with 8.0.13 or a previous version?
Do you have a test case?

sorry, the title was wrong, it should be "can not truncate or resize online" but not "can truncate online and resize".

in mysql 8.0.13, i want to change innodb_temp_data_file_path setting like:
[yejr]@[imysql.com] [(none)]>\s
--------------
mysql  Ver 8.0.11 for macos10.13 on x86_64 (MySQL Community Server - GPL)

Connection id:		11
Current database:
Current user:		root@localhost
SSL:			Not in use
Current pager:		less
Using outfile:		''
Using delimiter:	;
Server version:		8.0.13 MySQL Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/tmp/mysql.sock
Uptime:			10 min 22 sec

Threads: 1  Questions: 36  Slow queries: 0  Opens: 148  Flush tables: 2  Open tables: 112  Queries per second avg: 0.057
--------------

[yejr]@[imysql.com] [(none)]>show global variables like 'innodb_temp_data_file_path';
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.00 sec)

[yejr]@[imysql.com] [(none)]>set global innodb_temp_data_file_path="ibtmp1:14M:autoextend";
ERROR 1238 (HY000): Variable 'innodb_temp_data_file_path' is a read only variable
[yejr]@[imysql.com] [(none)]>

Hello Ye Jinrong,

Thank you for the feature request!

regards,
Umesh