Description:
In both 5.7 and 8.0 documentation:
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_temp_data_fil...
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_temp_data_fil...
we can read:
"The full directory path for temporary tablespace data files is formed by concatenating the paths defined by innodb_data_home_dir and innodb_temp_data_file_path."
&
"The path must be relative to the data directory."
However, since MySQL 8.0, this changed to use an absolute path rather than a relative.
How to repeat:
mysql [localhost:5752] {msandbox} ((none)) > select @@version,@@innodb_tmpdir,@@innodb_data_home_dir,@@innodb_temp_data_file_path;
+-----------+-----------------+------------------------+-------------------------------------------+
| @@version | @@innodb_tmpdir | @@innodb_data_home_dir | @@innodb_temp_data_file_path |
+-----------+-----------------+------------------------+-------------------------------------------+
| 5.7.43 | /mysql/tmp/ | NULL | /mysql/tmp/ibtmp1:50M:autoextend:max:500M |
+-----------+-----------------+------------------------+-------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost:5752] {msandbox} ((none)) > select FILE_NAME,FILE_TYPE,INITIAL_SIZE from information_schema.files where file_name like '%ibtmp1%';
+---------------------+-----------+--------------+
| FILE_NAME | FILE_TYPE | INITIAL_SIZE |
+---------------------+-----------+--------------+
| .//mysql/tmp/ibtmp1 | TEMPORARY | 52428800 |
+---------------------+-----------+--------------+
1 row in set (0.00 sec)
And indeed, ibtmp1 can be found in $datadir/mysql/tmp (actually, tmp dir must be created to avoid OS err 2).
Using the same option in 8.0.34 results in:
mysql [localhost:8050] {msandbox} ((none)) > select @@version,@@innodb_tmpdir,@@innodb_data_home_dir,@@innodb_temp_data_file_path;
+-----------+-----------------+------------------------+-------------------------------------------+
| @@version | @@innodb_tmpdir | @@innodb_data_home_dir | @@innodb_temp_data_file_path |
+-----------+-----------------+------------------------+-------------------------------------------+
| 8.0.34 | /mysql/tmp/ | NULL | /mysql/tmp/ibtmp1:50M:autoextend:max:500G |
+-----------+-----------------+------------------------+-------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost:8050] {msandbox} ((none)) > select FILE_NAME,FILE_TYPE,INITIAL_SIZE from information_schema.files where file_name like '%ibtmp1%';
+-------------------+-----------+--------------+
| FILE_NAME | FILE_TYPE | INITIAL_SIZE |
+-------------------+-----------+--------------+
| /mysql/tmp/ibtmp1 | TEMPORARY | 52428800 |
+-------------------+-----------+--------------+
1 row in set (0.01 sec)
and the global temp table space can be found in /mysql/tmp/ folder now.
This undocumented change can cause problems during upgrades.
Suggested fix:
Update the documentation accordingly.
