MySQL Bugs: #11440: temp tables now get created in database directory instead of tmpdir

Bug #11440	temp tables now get created in database directory instead of tmpdir
Submitted:	19 Jun 2005 20:27	Modified:	10 Jul 2005 15:17
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1, 5.0	OS:	Any (all)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
I observed that CREATE TEMPORARY TABLE now creates the tmp files (#sql*) in the database's directory (in "datadir/test" if the database is "test"), instead of in tmpdir like it used to.
Problems of this new approach:
- [minor] some liked to put their temp tables on a tmpfs (in-RAM, volatile storage but faster than disk), and putting in the database's directory prevents this, it also keeps the user from using multiple locations for tmp tables (--tmpdir has a round-robin multi-directory feature)
- [major] the rest of code is not ready for this change: DROP DATABASE fails to understand that those #sql* files it finds in the database's directory are temp tables and so fails to map them to tables and so deletes those files without closing them. On Windows the DROP DATABASE will fail. On NFS it failed for me (because NFS does not delete open files but instead renames to .nfs*) because the MYD and MYI were left open before deletion (frm was closed).
This was introduced on May 25th. I'll write an email to our developers.

How to repeat:
won't see it on Unix unless datadir is on NFS; if on NFS, run this .test:
# you can add DROP DATABASE IF EXISTS mysqltest1; here if you want
CREATE DATABASE mysqltest1;
CREATE TEMPORARY TABLE mysqltest1.t22 (f1 BIGINT);
DROP DATABASE mysqltest1;
it fails with:
query 'DROP DATABASE IF EXISTS mysqltest1' failed: 1010: Error dropping database (can't rmdir './mysqltest1', errno: 39)
(39: "directory not empty")

Suggested fix:
Will reply to the email where the patch was submitted.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26255

Fixed in 4.1.13 and 5.0.9.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in change history for 4.1.13 and 5.0.9; closed.