MySQL Bugs: #4291: max_heap_table_size affects creation of disk-based temporary tables

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:		CPU Architecture:	Any

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.

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).

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

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

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

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.

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...

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.

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.