Thinking Sphinx : Error while indexing

Question

indexing index 'users_core'...
ERROR: index 'users_core': sql_range_query: Incorrect key file for table '/tmp/#sql_ff2_0.MYI'; try to repair it (DSN=mysql://root:*@localhost:3306/myname)

What does this means ?
I can't find the file '/tmp/#sql_ff2_0.MYI'
How do i repair it ?

not fixed yet . i did some work arround like - removed some fields from indexing and fixed it temporarily . — Krishna Prasad Varma, Apr 06 '11 at 04:16
there was a namespace conflict between the table name and the field alias name — Krishna Prasad Varma, Apr 06 '11 at 04:17
Any news about this? I'm having the same error. Some conflicts i was able to resolve using :as, but others doesn't make any sense to me. — Tiago, May 18 '11 at 17:08
I was running some tests here. If the db is clean, it goes ok. If it have some records with associations, it uses all the space left in the /var/tmp and then return this failure message. — Tiago, May 18 '11 at 17:51

score 1 · Answer 1 · answered Feb 25 '13 at 20:47

This actually has nothing to do with Ruby / Rails, I just ran into this myself and had quite a bit of trouble finding a real answer.

The issue is that the sphinx indexer script is trying to create a temporary MySQL table while building the index. In my case MySQL ran out of disk space for the temporary table (default is /tmp, my partition was only 2GB)

As data is added to this temporary table and the disk is filled the table becomes corrupt, because the last bit of data written to the temp table is truncated.

The solution is to ensure the drive that MySQL is writing temporary tables to has enough space on it. I ended up changing the temp directory in my.cnf to a different larger partition. Default location for the config file on debian is /etc/mysql/my.cnf

Add:

tmpdir = /var/lib/mysql/tmp

Best place to put it is next to the datadir setting in the [mysqld] section.

i had the same problem in a production environment. You need to either free up space on /tmp or increase the size of the drive — dabobert, Jan 28 '16 at 17:26

score 0 · Answer 2 · answered Jan 27 '11 at 09:22

0

I can't find the file '/tmp/#sql_ff2_0.MYI'

This is perhaps some temporary table mysql creates during the query.

Did you try to repair the main table you use in the sql_range_query ?

answered Jan 27 '11 at 09:22

Manticore Search

1,462
9
9

same here am also not able to find that temp file. i tried repairing but no luck – Krishna Prasad Varma Apr 15 '11 at 06:03

Thinking Sphinx : Error while indexing

2 Answers2