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:
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:8.0.13 OS:Any
Assigned to: CPU Architecture:Any
Tags: ACE

[8 Nov 2018 13:47] Ye Jinrong
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.
[8 Nov 2018 17:52] Sunny Bains
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?
[11 Nov 2018 11:16] Ye Jinrong
sorry, the title was wrong, it should be "can not truncate or resize online" but not "can truncate online and resize".
[11 Nov 2018 11:17] Ye Jinrong
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)]>
[18 Nov 2018 5:54] MySQL Verification Team
Hello Ye Jinrong,

Thank you for the feature request!