MySQL Bugs: #30287: tmpdir not respected on GROUP BY

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	CPU Architecture:	Any
Tags:	bfsm_2007_08_16, filesort, regression, temp tables, tmpdir

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.

Uploaded the test table as test_table.sql.gz to ftp://ftp.mysql.com/pub/mysql/upload/

Thank you for the report.

Please provide your configuration file.

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.

Thank you for the feedback.

Verified as described.

Bug #30429 was marked as duplicate of this one.

Bug has been introduced in version 5.0.44

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!

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)

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.

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.

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.

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.

Pushed into 5.1.22-beta

Pushed into 5.0.48

Thanks a lot for fixing this, patch works well here so far. :)

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.

Pushed into 5.1.23-beta

Bug: http://bugs.mysql.com/bug.php?id=31458 marked as duplicate of this one.