Bug #30287 tmpdir not respected on GROUP BY
Submitted: 7 Aug 2007 21:49 Modified: 25 Aug 2007 15:54
Reporter: Narayan Newton Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.44, 5.1, 5.0 BK OS:Linux (x86,x86_64)
Assigned to: Gleb Shchepa
Tags: bfsm_2007_08_16, filesort, regression, temp tables, tmpdir

[7 Aug 2007 21:49] Narayan Newton
Description:
On mysql 5.0.44, when certain queries run which create temp tables for filesorts, the .frm files are correctly created in tmpdir. However, the actual data files appear in /var/lib/mysql. We run one server that has tmpdir pointed at a shm mount. The upgrade to 5.0.44 brought this server to its knees because of the iowait involved with actually writing these to disk.

How to repeat:
1. Import the attached table.
2. Run 'select * from search_total group by word;'
3. Look in /var/lib/mysql while the query is running.

I have repeated this on innodb and myisam tables.
[7 Aug 2007 21:52] Narayan Newton
Uploaded the test table as test_table.sql.gz to ftp://ftp.mysql.com/pub/mysql/upload/
[8 Aug 2007 7:36] Sveta Smirnova
Thank you for the report.

Please provide your configuration file.
[8 Aug 2007 15:34] Narayan Newton
I was able to reproduce the bug on several config files, but I attached the one that first showed it. Please let me know if I can do anything else.
[10 Aug 2007 8:37] Sveta Smirnova
Thank you for the feedback.

Verified as described.
[16 Aug 2007 5:13] Sveta Smirnova
Bug #30429 was marked as duplicate of this one.

Bug has been introduced in version 5.0.44
[16 Aug 2007 13:00] Oli Sennhauser
Here's the fix for the tmpdir variable not honored for internally created
temporary tables in MySQL 5.0.45:

replace sql/sql_select.cc line 10054:

if ((error=mi_create(table->s->table_name,table->s->keys,&keydef,

with:

if ((error=mi_create(table->s->path,table->s->keys,&keydef,

This fix has no side effects as the mi_create() here is inside a static
function only called when building myisam temp tables on disk.

Thanks to Riccardo Pizzi from Mobango!
[17 Aug 2007 18:43] Narayan Newton
That works 'sort of' :). The tables are created correctly now....it just can't find them.

mysql> SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, GREATEST(n.changed, l.last_comment_timestamp) AS last_updated, l.comment_count FROM node n INNER JOIN users u ON n.uid = u.uid INNER JOIN node_comment_statistics l ON n.nid = l.nid WHERE n.status = 1 ORDER BY last_updated DESC;
ERROR 1017 (HY000): Can't find file: '' (errno: 2)
[18 Aug 2007 17:01] 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:

  http://lists.mysql.com/commits/32716

ChangeSet@1.2496, 2007-08-18 22:31:31+05:00, gshchepa@gleb.loc +1 -0
  Fixed bug #30287.
  The server created temporary tables for filesort in the working directory
  instead of the specified tmpdir directory.
[20 Aug 2007 20:08] 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:

  http://lists.mysql.com/commits/32777

ChangeSet@1.2496, 2007-08-21 01:39:39+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #30287.
  The server created temporary tables for filesort in the working directory
  instead of the specified tmpdir directory.
[23 Aug 2007 18:32] 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:

  http://lists.mysql.com/commits/32990

ChangeSet@1.2572, 2007-08-24 00:06:19+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #30287.
  Recommit to 5.1.22.
  The server created temporary tables for filesort in the working directory
  instead of the specified tmpdir directory.
[23 Aug 2007 20:21] 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:

  http://lists.mysql.com/commits/32993

ChangeSet@1.2569, 2007-08-24 01:54:18+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #30287.
  Recommit to 5.1.22.
  The server created temporary tables for filesort in the working directory
  instead of the specified tmpdir directory.
[24 Aug 2007 7:20] Bugs System
Pushed into 5.1.22-beta
[24 Aug 2007 7:22] Bugs System
Pushed into 5.0.48
[24 Aug 2007 17:44] Narayan Newton
Thanks a lot for fixing this, patch works well here so far. :)
[25 Aug 2007 15:54] Paul Dubois
Noted in 5.0.48, 5.1.22 changelogs.

The server created temporary tables for filesort operations in the
working directory, not in the directory specified by the tmpdir system
variable.
[4 Sep 2007 17:12] Bugs System
Pushed into 5.1.23-beta
[8 Oct 2007 17:46] Miguel Solorzano
Bug: http://bugs.mysql.com/bug.php?id=31458 marked as duplicate of this one.