MySQL Bugs: #64940: Memory table no longer falls back to MyISAM when full

Bug #64940	Memory table no longer falls back to MyISAM when full
Submitted:	11 Apr 2012 9:25	Modified:	11 Apr 2012 12:25
Reporter:	ken sands	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S2 (Serious)
Version:	5.5.19	OS:	Windows (2003 server (32bit))
Assigned to:		CPU Architecture:	Any
Tags:	Memory Table Full

Description:
I've had 5.1.26(rc) running for a long time with the max heap table size about 800meg and the fix full table box ticked (the one that says it'll write to a MyISAM when the memory gets full) This has worked fine allowing me to create tables as Memory storage engine, pulling in data with the speed that gives and if ever it's a bigger data set then it falls back to writing it as a MyISAM on disk.

I installed 5.5.19 to find this part broken. I now get a table full error, which if not picked up on means I actually just get a partial import of the data.

I'm using "load data" to import the data.

I've checked all the settings of the 2 servers, and played around with the settings mentioned above, and in all cases what will work correctly on 5.1.26 fails on 5.5.19

How to repeat:
Install 5.5.19
set the heap table size to 800
set the fix full table box (fall back to MyISAM)
create a big structured memory table
import a big enough data set to hit the limit using load data

try the above using 5.1.26(rc) to see it work ok.

Suggested fix:
Ensure Memory tables fall back to on disk MyISAM as stated above a certain size or if table full would otherwise happen.

Our manual, http://dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html, clearly says:

"MEMORY table contents are stored in memory, which is a property that MEMORY tables share with internal temporary tables that the server creates on the fly while processing queries. However, the two types of tables differ in that MEMORY tables are not subject to storage conversion, whereas internal temporary tables are:

    If an internal temporary table becomes too large, the server automatically converts it to on-disk storage, as described in Section 8.4.3.3, “How MySQL Uses Internal Temporary Tables”.

    User-created MEMORY tables are never converted to disk tables."

So, whatever the behavior was in 5.1.26 in your case, current behavior is explicitly documented and thus is not a bug.

So it does, I guess that's changed since the version I was running. It's a shame as it was a really nice feature. I guess I'll have to change all the code to create MyISAM / InnoDB tables from the start or create myself a custom patched version of mysql. Thanks.