Thinking Sphinx : Error while indexing

indexing index 'users_core'...

ERROR: index 'users_core': sql_range_query: Incorrect key file for tab开发者_如何学Pythonle '/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 ?

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 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 ?