MySQL Bugs: #286: "The table is full" occurs when using multi-table UPDATE

Bug #286	"The table is full" occurs when using multi-table UPDATE
Submitted:	13 Apr 2003 22:04	Modified:	18 Oct 2004 12:10
Reporter:	shilling lee	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.0.12	OS:	FreeBSD (FreeBSD 4.8)
Assigned to:		CPU Architecture:	Any

Description:
There is a bug in multi-table UPDATE in mysql 4.0.12. If one use a multi-table UPDATE and the query makes a tmp table bigger then @@tmp_table_size, 1114 error is returned.it seems that mysql cannot use the BIG_TABLES option like SELECT does.

How to repeat:

First setup a big table table1, Ex: a table with 1xx,xxx,xxx rows and 1x collums. a big table table2, Ex: a table with 1x,xxx,xxx rows and 1x collums

mysql> UPDATE table1 as t1,table2 as t2 SET t1.id2=t2.id2,t1.value=t2.value,t1.name=t2.name WHERE t1.id=t2.id;
ERROR 1114: The table 't1' is full

Suggested fix:
When a tmp table exceed @@tmp_table_size, donnot use a in-memory table and use a disk table instead, just like SELECT does.

Thank you for the report.

We know about it and it is already fixed in 4.0.13 tree.

Sinisa, I could not find an entry in the 4.0.13 Changelog about this. Has this really been pushed for 
4.0.13? Or is it included in this ChangeSet? 
 
ChangeSet@1.1428, 2003-04-02 17:05:34+03:00, Sinisa@sinisa.nasamreza.org 
  A fix for a bug in multi-table updates which was always caused 
  whenever a temporary MyISAM table had to be created from HEAP table. 
 
If yes, please add an entry to the 4.0.13 NEWS about it and mention this bug ID. Thanks!

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

This bug will be fixed for the upcoming 4.0.13 release.

LenZ,

Here it is , the entry in 4.0.13 Changelog:

   * Fixed bug in `multi-table updates' which occurred whenever a
     temporary table, containing update data, had to be converted from
     `HEAP' to `MyISAM'.

Doesn't looked fixed to me.  I just encountered this error and I am using 4.0.20-0.  I tried to execute this following statement on a table with about 90,000 records:

ALTER TABLE table ADD new_column VARCAHR(255);

and got:

ERROR 1114: The table '#sql-87f_163' is full.

The machine has way more disk space that would be needed to copy the table in question, so perhaps this should be looked at again, either because the problem is happening anew, or the original fix didn't fix it in some circumstances.

Your new report does not have anything to do with this one.

Old one is with multi-table updates and this one is with alter.

Report a new one and upload a table that will always return the error you report
on the ALTER statement you tried.

Try our latest 4.0 binary.