MySQL Bugs: #7788: "Table is full" occurs during a multitable update

Bug #7788	"Table is full" occurs during a multitable update
Submitted:	11 Jan 2005 0:07	Modified:	1 Feb 2005 13:43
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.1.8	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

When trying to do a multitable update on one of my table, MySQL fails with a "table is full" error message. 
Since the target table is far from being full, even after the update, I assume it's a bug ;) (moreover if I change the source table by another one, the update works)

Thanks,
  Jocelyn

How to repeat:
get ftp://ftp.mysql.com/pub/mysql/upload/multitablebug.tar.gz

Then : 

UPDATE searchmainhardwarefr0, searchjoinhardwarefr0 SET searchmainhardwarefr0.numeropost=searchjoinhardwarefr0.topic WHERE searchmainhardwarefr0.numreponse=searchjoinhardwarefr0.numreponse;

ERROR 1114 (HY000): The table '#sql_5e8d_0' is full

The error shows that the problem is with a temporary table used to resolve the join. However tthe resulting table of a SELECT doing the same join is quite modest as a structrure and size.
 

mysql> CREATE TEMPORARY TABLE temp 
SELECT searchmainhardwarefr0.*, searchjoinhardwarefr0.topic, searchjoinhardwarefr0.date, searchjoinhardwarefr0.id  
FROM searchmainhardwarefr0, searchjoinhardwarefr0 
WHERE searchmainhardwarefr0.numreponse=searchjoinhardwarefr0.numreponse;
Query OK, 946122 rows affected (7.45 sec)
Records: 946122  Duplicates: 0  Warnings: 0

mysql> show create table temp;

CREATE TEMPORARY TABLE `temp` (
  `mot` char(25) character set latin1 NOT NULL default '',
  `numreponse` int(10) unsigned NOT NULL default '0',
  `numeropost` mediumint(8) unsigned NOT NULL default '0',
  `topic` mediumint(8) unsigned NOT NULL default '0',
  `date` date NOT NULL default '0000-00-00',
  `id` mediumint(8) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Approved by Sergei on IRC.

Approved by Sergei on IRC.

Approved by Sergei on IRC.

Changeset 1.2151

Quick update

The proposed patch doesn't fix all possible error cases. Have now created a better patch for this.
It will be in 4.1.9 and 5.0.3