MySQL Error 1114 "table is full" Using MyISAM Engine

Question

When executing the following query, we run into the error The table '/var/lib/mysql/temp/#sql_2d22_1' is full (actual table name varies). I've done a fair amount of research on this; most of the answers seem to hinge on there not being enough drive space or details related to the InnoDB engine. However, I've verified that we have (what seems like) enough free space and are using the MyISAM storage engine.

select a.* from some_table a,
 (select Account, Date, Amount, count(*) as Dupes from some_table
 GROUP BY Account, Date, Amount HAVING Dupes > 1) b
WHERE a.Account = b.Account and a.Date = b.Date and a.Amount = b.Amount;

Here are some details:

• We're using MySQL version 5.5.36 on a CentOS 6 server with 6GB of RAM
• The drive hosting MySQL's temp directory is 120GB in size, about 35 of which are in use
• The table has about 70 million records and is about 3.2GB in size
• big-tables is set to 1
• max_heap_table_size is set to 4096M
• tmp_table_size is also 4096M
• myisam_data_pointer_size is 5

What could cause this error? I'd very much appreciate any suggestions, as I don't know that much about database configuration and dealing with large tables.

Update: I noticed yesterday that the temp directory does not reach capacity; as the query runs, the directory's size increases slowly by about 200MB and then I get the error.

• user2864740: The temp "table is full".
• RolandoMySQLDBA: Please run this: SELECT variable_value FROM information_schema.global_variables WHERE variable_name='default_tmp_storage_engine';. What's the output ?
• William Grootonk: Hmm, it returns an empty result. Looking at the documentation, default_tmp_storage_engine isn't listed as a variable for MySQL 5.5, only 5.6.

Answer 1

A lot of helpfull information and hints can be found here:

https://dev.mysql.com/doc/refman/5.5/en/table-size-limit.html

• William Grootonk: I came across this link in my earlier searches but ruled out many of the possibilities it suggests. The disk doesn't appear to be full; we're not using the InnoDB, NDB, or MEMORY engine; we're using a file system that supports 16TB files (ext4); the pointer size is sufficiently large... This page says that maximum table size is typically determined by file system constraints; what might be a good way to determine if this is the cause of our issue?