Suneth Kalhara Suneth Kalhara - 1 month ago 7
MySQL Question

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

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

Do these steps

Stop mysql service

rename the .myi file to x.old

Start mysql

REPAIR all the tables in query ,MySQL will rebuild key file