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

[11 Jan 2005 0:07] jocelyn fournier
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
[11 Jan 2005 7:07] Alexander Keremidarski
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
[29 Jan 2005 20:34] MySQL Verification Team
Approved by Sergei on IRC.
[29 Jan 2005 20:35] MySQL Verification Team
Approved by Sergei on IRC.
[29 Jan 2005 20:46] MySQL Verification Team
Approved by Sergei on IRC.

Changeset 1.2151
[1 Feb 2005 13:43] Michael Widenius
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