MySQL Bugs: #80791: innodb_tmpdir behaviour and variable scope looks problematic

Bug #80791	innodb_tmpdir behaviour and variable scope looks problematic
Submitted:	18 Mar 2016 14:36	Modified:	4 Apr 2016 14:53
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	5.6.29.,5.7.11	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, innodb, innodb_tmpdir

Description:
There is a new variable in 5.6.29 and 5.7.11.

The idea is to be able to redirect the location of temporary "storage" space needed when running an ALTER TABLE and to avoid tmpdir being used.

The setting on a single server seems fine and useful but it seems to fail to take into account people who use replication and may want to "indicate" the location so that downstream slaves behave the same.

So making this a session only variable which is not replicated means that if I use a specific value (maybe I need to due to space reasons etc) then this will not be replicated identically to downstream slaves.

I thus consider this behaviour buggy or only useful on single server installations.

How to repeat:
Setup replication and try doing some ALTER TABLEs.
The slaves will not respect the session variable used on the master.

Suggested fix:
I would personally expect this to be a normal variable with the same behaviour as indicated currently.
This allows all servers to be configured the same via /etc/my.cnf or the use of SET GLOBAL innodb_tmpdir = 'XXXXX'.

However, it may be convenient, if you know what you're doing, to push down the hint to the downstream slaves to use this setting too. If the variable can not be "transported downstream", perhaps an option would allow a SQL hint of the form /* SET SESSION innodb_tmpdir= 'xxxx'; */ (or similar) to achieve the same effect?

This might be safer to use existing "hinting infrastructure" and extending it to replication so that where needed and where the downstream slave understands the intent can be signalled.

Adding these hints manually might be tedious so a new variable which would allow "automatic hinting" of certain scenarios (like this) [ perhaps on a per variable basis ] would be good.

This idea could be extended to another variable such as explicit_defaults_for_timestamp which is also not "replication safe" (or dynamic) and thus can not be enabled on systems which never stop.

Hello Simon,

Thank you for the feedback and reasonable feature request!
Imho innodb_tmpdir was implemented after Bug #73250.

Thanks,
Umesh

innodb_tmpdir is both global | session variable. we updated the documentation.

That is true, replicating innodb_tmpdir will get some convenient if all servers have same OS environment. But there are also many slaves don't have same environment as master. For those cases, users still need to configure the variable per server, otherwise it will still break replication. 

So replicating innodb_tmpdir  isn't a perfect solution. And it will make the situation a little bit complex to users. They need to decide when they should 
configure it per server and when they shouldn't.

Since the variable depends on OS environment, It is better not to replicate it and I suggest to warn users in the reference manual that it will probably break replication if ALTER TABLE online uses innodb_tmpdir on master but its slave doesn't configure it.

Posted by developer:
 
The following documentation pages have been updated. Changes should appear online with 24 hours.

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#innodb-temporary-files

Thank you for the bug report.