MVA attributes in Sphinx

Question

Can anybody help me understand the expected format of data for creating MVA (multi-value) attributes in Sphinx?

I have a MySQL function which returns a row of comma-separated integers, collated with GROUP_CONCAT, as a blob. I have two further MVA attributes which collate the results of a JOIN statement, with GROUP_CONCAT, as a blob (as generated by ThinkingSphinx). These are all included in my sql_query in my sphinx.conf.

I've tried running the SQL on a small result set in the console, and it works: for all the MVA columns, the results are a blob containing data such as:

2432,35345,342347,8975,453645

and so on. The two MVA attributes generated with the JOIN/GROUP_CONCAT combination index correctly. However, the MVA attribute generated with the MySQL function causes the indexing to fail silently (seemingly little or no data is indexed). This is despite the query working absolutely fine in the console..

So the data format seems to be identical, but Sphinx is rejecting one of the columns. Does anybody know of any gotchas with defining MVA attributes which might help me debug this?

Answer 1

I've never used thinking-sphinx (being a PHP shop here), but I don't think you should be group_concat'ing your results. From a working example in one of my sphinx.conf files:

sql_attr_multi = uint categories from query; SELECT entry_id, cat_id FROM exp_category_posts

Answer 2

I solved this problem eventually. It was happening because of something which seemed unrelated: a 'sql_attr_str2ordinal' attribute which seemed to be affected (or effect) the SQL query/indexing in ways I don't fully understand.

See: http://www.sphx.org/forum/view.html?id=2867

Fortunately, in my case I was able to remove it entirely, and indexing now seems to work.