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-tablesis set to 1
max_heap_table_sizeis set to 4096M
tmp_table_sizeis also 4096M
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.