Bug #80916 innodb_tmpdir doesn't have any effect

• Submitted: 31 Mar 2016 3:09
• Modified: 3 Apr 2016 13:33
• Reporter: Jesper wisborg Krogh
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.6.29
• OS: Any
• CPU Architecture: Any

[31 Mar 2016 3:09] Jesper wisborg Krogh

Description:
The innodb_tmpdir option was introduced in 5.6.29 and 5.7.11 to allow InnoDB online ALTER to use a non-default directory for the temporary files. However even when set the files are created in @@datadir/<schema_name>/.

Verified both in 5.6.29, 5.7.11, and 5.7.12.

How to repeat:
mysql> SET SESSION innodb_tmpdir = '/mysql/';
Query OK, 0 rows affected (0.00 sec)

mysql> OPTIMIZE TABLE employees.salaries;
...

While the OPTIMIZE TABLE executes:

shell$ $ ls -lh /mysql/\#* data/employees/\#*
ls: cannot access /mysql/#*: No such file or directory
-rw-r-----. 1 mysql mysql 8.5K Mar 30 08:53 data/employees/#sql-259e_3.frm
-rw-r-----. 1 mysql mysql  92M Mar 30 08:53 data/employees/#sql-ib60-1305668149.ibd

Doesn't help setting innodb_tmpdir in the configuration file either or using other online ALTERs (see also http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html )

[3 Apr 2016 13:33] Daniel Price

Posted by developer:
 
The following sections were revised:

http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_tmpdir
http://dev.mysql.com/doc/refman/5.7/en/temporary-files.html

Thank you for the bug report.