Bug #1755 UPDATE on MyISAM table with many rows fails with "Table is full" error
Submitted: 5 Nov 2003 2:43 Modified: 5 Nov 2003 5:43
Reporter: David Harper Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.1.0-alpha and 4.0.12 OS:i386-linux and HP Alpha OSF1
Assigned to: CPU Architecture:Any

[5 Nov 2003 2:43] David Harper
Description:
An multi-table UPDATE on a mediumint column of a table with more than a
million rows fails with the error "Table <TABLENAME> is full".

The table is nowhere near its data size limit. The filesystem holding
the data files has plenty of space. The host machine has plenty of RAM.
The key_buffer_size is very large.

Here's the table creation statement:

CREATE TABLE reads (
  read_id mediumint(8) unsigned NOT NULL default '0',
  readname char(32) NOT NULL default '',
  template char(24) NOT NULL default '',
  PRIMARY KEY  (read_id),
  KEY readname (readname),
  KEY template (template)
) TYPE=MyISAM

I want to create a separate dictionary table to remove the
char(24) column named 'template' and replace it with a mediumint
value. Several rows in the 'reads' table can share the same
'template' value, so the table isn't normalised. Also,
duplicating char(24) values wastes space.

I create a new table named 'template', populated from the
distinct values from reads.template. Then I add a mediumint
column to table 'template' as an auto_increment primary
key, to create unique ID numbers for each template name.

Then I add a similar mediumint column to the 'reads' table
and issue an UPDATE query on the 'reads' and 'template'
table to put the correct template ID value into each row
of the 'reads' table. This is when I get a "Table 'reads'
is full" error.

How to repeat:
Download the table from

  http://www.sanger.ac.uk/Users/adh/reads.sql.gz

and unpack to obtain reads.sql.

Create a new database and read the reads.sql file to create a table
named "reads". Then issue the commands

create table template select distinct template from reads;

alter table template add column (template_id mediumint unsigned not null
       auto_increment, primary key(template_id));

alter table reads add column (template_id mediumint unsigned);

update reads,template set reads.template_id=template.template_id 
       where reads.template=template.template;

The last command will fail with the error

    ERROR 1114: The table 'reads' is full

Here is my mysqld configuration file:

[mysqld]
socket     = /raid0/mysql/test/etc/mysql.test.sock
port       = 24651
pid-file   = /raid0/mysql/test/etc/mysql.test.pid
datadir    = /raid0/mysql/test/data
big-tables
safe-show-database
safe-user-create
server-id  = 1
set-variable = key_buffer=128M
set-variable = table_cache=256
set-variable = sort_buffer=4M
set-variable = record_buffer=1M
set-variable = slave_net_timeout=3600
set-variable = net_read_timeout=3600
myisam-recover=BACKUP,FORCE

The server is running on a Linux PC with 2Gb of RAM.
The filesystem /raid0 is a local 100Gb RAID system.

I have also duplicated this problem on a version 4.0.12
mysqld server running on an HP Alpha OSF1 platform,
with several gigabytes of RAM and several gigabytes of
free disk space on the local filesystem which contains
the data directory, and the server configured using
the same buffer sizes as above.

Suggested fix:
The error does *not* occur on a similar table with only 90,000
rows. I can also avoid it by executing several UPDATE statements
which include a WHERE clause to limit the number of rows
affected.

However, this makes my code more complicated and it should
be unnecessary.
[5 Nov 2003 5:43] MySQL Verification Team
Duplicate of the already fixed bug #286