MySQL Bugs: #47679: Temporary table data in temp directory gets deleted on startup

Bug #47679	Temporary table data in temp directory gets deleted on startup
Submitted:	28 Sep 2009 15:43	Modified:	9 Oct 2009 11:13
Reporter:	Udo Schwedt	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.30SP1	OS:	Windows (Windows Server 2003)
Assigned to:		CPU Architecture:	Any
Tags:	multiple installations, startup, tempdir, temporary tables

Description:
On startup, MySQL deletes all files matching the name pattern "#sql*" from the temporary directory. For parallel MySQL instances on one server, this causes errors if one instance is already running and performs an SQL statement which causes temporary tables to be created. The files containing the temporary table descriptions are deleted by the second instance on startup, subsequently causing an error for the first instance (when trying to delete the temporary files). 

How to repeat:
Configure two parallel instances of MySQL on one server without explicitly giving a tmpdir variable. Startup first MySQL instance. Issue a long running statement which causes a temporary table to be created (e.g. an expensive JOIN clause). Startup second MySQL instance while the statement is still executed. The statement on the first MySQL instance will end with an error 
"ERROR 6 (HY000): Error on delete of 'C:\WINDOWS\TEMP\#sql_4a2c_1.MYI' (Errcode: 2)"

Suggested fix:
Do not delete all #sql* files in the data directory, but delete only the files relevant to the respective instance (e.g. by using an additional discriminator part in the temporary file names - the server id being a good candidate).

Many thanks for writing a bug report.

Verified as described.

I think a simpler solution would be to set default /tmpdir not to system TEMP but to /datadir/temp folder for instance.

At least the installer could easily do this (but another concern is that it should it avoided that install as service with "mysqld --install .." and using installer behaves differently in this respect).

Against this speaks that too much 'temp-activity' to /datadir-tree should be avoided in cases where /datadir is specified to a drive system different from system drive. However users having such scenario would probably also be able to manage this.

Using the datadir for this would possible cause other problems. Directories in there usually show up as databases. There was a maybe related problem some time ago in the 4.0/4.1 timeframe (http://web.nvd.nist.gov/view/vuln/detail?vulnId=CVE-2005-0711) which dealt with temporary files/directories, too.
I just wanted to hint at this to avoid some change made to temp file handling back then be reverted to something that was deemed problematic in the past.

if you use server id as the discriminator then mysqld will delete files of the other instance if they don't specify server id explicitly.

The point is - whatever discriminator you will use, it has to be specified explicitly on instances. So, just use tmpdir as a discriminator, specify it explicitly and avoid the problem completely.

As long as this is undocumented behaviour, I still consider this a serious problem. Parallel installations will happily work even when configured to use the same tempdir, causing spurious errors only once in a while, basically leaving the operator without a proper chance of identifying the error.
At least this behaviour has to be clearly stated in the documentation, and the tempdir parameter should be marked as highly critical for parallel installations.

Moreover, the approach of silently deleting files from a temporary directory on startup is at least questionable. One should consider constructing an identifier which makes it at least less likely to delete files not belonging to the respective MySQL installation. A combination of server id, port, and socket would be sufficient IMHO. Parallel installations with all three parameters set to identical values would not make sense, anyway.

internal FR filed:
Bug 16049106 - PREVENT TMPDIR INTERFERENCE BETWEEN MYSQLD INSTANCES