Incorrect key file for table error in mysql

Question

I have a huge table containing 14 million + rows. All varchar data. I am trying to run this query. I get output quickly. limit is set on 800K records only.

SELECT *
FROM (SELECT unique_user_id, url, count(url) as url_visit_count, categories, count(categories) as count 
FROM ph_usertrack_data GROUP BY unique_user_id LIMIT 800000) a 
ORDER BY a.url_visit_count DESC LIMIT 100;

when I put limit to 1 million records, I get this error

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

My temp folder is having a lot of space around 100 GB, so no space issue. Why this issue appears when I increase number of rows to process?

Community · Accepted Answer · 2017-05-23 12:12:28Z

1

I think this should help-

You can use df -h to check whether \tmp is in its own partition, and how much space is allocated to it.

If it is in its own partition and short of space, you can either:

(a) modify /tmp so that its parition has more space (either by reallocating or moving it to the main partition - e.g. see here) (b) changing MySql config so that it uses a different temp folder on a different partition, e.g. /var/tmp

Refer here and this too.

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Jul 6, 2012 at 10:36

Jacob

2,06114 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shades88 Over a year ago

thanks I did the same. I also increased size of key buffer size from 16M to 256M

Collectives™ on Stack Overflow

Incorrect key file for table error in mysql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related