Bug #4291 max_heap_table_size affects creation of disk-based temporary tables
Submitted: 25 Jun 2004 23:30 Modified: 13 Dec 2010 10:47
Reporter: Dean Ellis Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.21 OS:Linux (Linux)
Assigned to:
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[25 Jun 2004 23:30] Dean Ellis
Description:
It seems that both tmp_table_size and max_heap_table_size must be set to prevent creation of disk-based temporary tables.

A query of the form:

SELECT t1.intcol, t2.varcharcol1, t2.varcharcol2, COUNT(*)
FROM t1, t2
WHERE t1.a = t2.a
GROUP BY intcol, varcharcol1, varcharcol2
ORDER BY NULL

can produce a temporary table (#sql_xxxx.MYD, .MYI) in tmpdir.  To prevent this, both tmp_table_size and max_heap_table_size must exceed the size of the temporary table.

Unsure if this is intended behavior or a bug; if intended it should be documented in Server System Variables which implies that tmp_table_size is the only relevant variable.

How to repeat:
Can supply a test case if necessary; data I was using cannot be redistributed but is still available.

Suggested fix:
Change or document behavior...  max_heap_table_size should presumably only affect explicitly created HEAP/MEMORY tables.
[30 Jun 2004 17:46] Dean Ellis
Noting that the varchar columns mentioned above were nullable, so presumably this is caused by a change back in 4.0.5:

"Fixed that GROUP BY on columns that may have a NULL value doesn't always use disk based temporary tables."

which would use HEAP tables if possible, which I assume brings max_heap_table_size into play (and should not, I think).
[16 Nov 2006 1:11] 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/15390

ChangeSet@1.2311, 2006-11-16 04:11:16+03:00, ted@ted.mysql.internal +6 -0
  BUG #4291 fix: new configuration option "disk-tmp-table-size"
    introduced to set maximum expected on-disk temporary table size 
    and avoid mix-up of tmp_table_size and max_heap_table_size
[7 Dec 2006 12:35] 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/16579

ChangeSet@1.2342, 2006-12-07 15:35:18+03:00, ted@ted.mysql.internal +7 -0
  BUG #4291: max_heap_table_size affects creation of disk-based temporary table
[7 Dec 2006 13:39] 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/16582

ChangeSet@1.2342, 2006-12-07 16:38:48+03:00, ted@ted.mysql.internal +7 -0
  BUG #4291: max_heap_table_size affects creation of disk-based temporary table
[3 Jan 2007 11:45] 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/17577

ChangeSet@1.2358, 2007-01-03 14:45:26+03:00, ted@ted.mysql.internal +7 -0
  BUG #4291: max_heap_table_size affects creation of disk-based temporary table
  
  fix: the new system variable memory_tmp_table_size is introduced; 
       it stands now for the exact purpose the Manual says 
       tmp_table_size used to do. 
  
  tmp_table_size retains to (give a hint about a) 
  limit of the on-disk temporary table size. The limit imposed upon 
  the disk-based temporary tables is still quite relative due to MyISAM
  current implementation restrictions.
[21 Jan 2007 7:21] Valerii Kravchuk
I am not sure this is a proper fix. It will change current well-known behaviour in 5.0.x. Please, read PeterZ's oppinion about this in http://www.mysqlperformanceblog.com/2007/01/19/tmp_table_size-and-max_heap_table_size/

So, this fix should better go to 5.1 or even 5.2...
[22 Jan 2007 6:43] James Day
This should be changed so that memory_tmp_table_size (better: tmp_table_max_memory_size) is an alternative name for tmp_table_size and tmp_table_size can go through the deprecation process if necessary.

If there is a desire to add a new limit the disk size, that should be a new variable tmp_table_max_disk_size or similar.

All end users are using the way this actually works at present and has worked since 4.0 or earlier: tmp_table_size is the transition size at which the temporary table goes to disk and there is no disk limit. The code may not have been intended to work this way but this is how it does work and we should not break backwards compatibility without using the deprecation process.
[23 Jan 2007 13:06] Sergei Golubchik
As it happened out, tmp_table_size does not affect on-disk temporary tables currently (even if it was the intention). So the bug fix is to replace min(tmp_table_size, max_heap_table_size) with tmp_table_size, so that temporary tables wouldn't be affected by max_heap_table_size.