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:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1, 5.0 OS:Any (all)
Assigned to: Jim Winstead CPU Architecture:Any

[19 Jun 2005 20:27] Guilhem Bichot
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
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.
[21 Jun 2005 17:04] Bugs System
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:

[5 Jul 2005 23:50] Jim Winstead
Fixed in 4.1.13 and 5.0.9.
[10 Jul 2005 15:17] Jon Stephens
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

Additional info:

Documented in change history for 4.1.13 and 5.0.9; closed.