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 1max_heap_table_size
is set to 4096Mtmp_table_size
is also 4096Mmyisam_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.
Best Answer
A lot of helpfull information and hints can be found here:
https://dev.mysql.com/doc/refman/5.5/en/table-size-limit.html