Incorrect key file for table '/tmp/#sql_3c51_0.MYI'; try to repair it [duplicate]

Question

I wrote a query and this runs on my local server correctly it has less data,

but when i run this on production server it gets an error - (this has more data around 6GB)

Incorrect key file for table '/tmp/#sql_3c51_0.MYI'; try to repair it 

Here is my query

SELECT        `j25_virtuemart_products`.`virtuemart_product_id`,     `product_name`,      `product_unit`,     `product_s_desc`,     `file_url_thumb`,     `virtuemart_custom_id`,      `custom_value`        FROM      `j25_virtuemart_product_customfields`,     `j25_virtuemart_products`,     `j25_virtuemart_products_en_gb`,     `j25_virtuemart_product_medias`,     `j25_virtuemart_medias`          WHERE     (     `j25_virtuemart_products`.`virtuemart_product_id`=`j25_virtuemart_products_en_gb`.`virtuemart_product_id`     AND      `j25_virtuemart_products`.`virtuemart_product_id`=`j25_virtuemart_product_customfields`.`virtuemart_product_id`)  AND      `j25_virtuemart_products`.`virtuemart_product_id`=`j25_virtuemart_product_medias`.`virtuemart_product_id`     AND      `j25_virtuemart_product_medias`.`virtuemart_media_id`=`j25_virtuemart_medias`.`virtuemart_media_id`      GROUP BY `j25_virtuemart_products`.`virtuemart_product_id`      LIMIT 0, 1000; 

Anyone know how to recover from that error - something like otimize this query or any other way thank you

Answer 1

The problem is caused by the lack of disk space in /tmp folder. The /tmp volume is used in queries that require to create temporary tables. These temporary tables are in MyISAM format even if the query is using only tables with InnoDB.

Here are some solutions:

• optimize the query so it will not create temporary tables (rewrite the query, split it in multiple queries, or add proper indexes, analyze the execution plan with pt-query-digest and EXPLAIN <query>) See this Percona article about temporary tables.
• optimize MySQL so it will not create temporary tables (sort_buffer_size, join_buffer_size). See: https://dba.stackexchange.com/questions/53201/mysql-creates-temporary-tables-on-disk-how-do-i-stop-it
• make tables smaller. If possible, delete unneeded rows
• use SELECT table1.col1, table2,col1 ... instead of select * to use only the columns that you need in the query, to generate smaller temp tables
• use data types that take less space
• add more disk space on the volume where /tmp folder resides
• change the temp folder user by mysql by setting the TMPDIR environment variable prior to mysqld start-up. Point TMPDIR to a folder on a disk volume that has more free space. You can also use tmpdir option in /etc/my.cnf or --tmpdir in the command line of the mysqld service. See: B.5.3.5 Where MySQL Stores Temporary Files