CODEWITHSUNDEEP
mysqlsphinx
Tonny Jørgensen
Tonny Jørgensen

Reputation: 79

sphinxsearch gives error with missing attribute

Im trying to make sphinxsearch index using a query where i joint two tables.

The index has been working fine until now, but now i need more data in the index hence the joining of the other table.

The query looks as follows

  sql_query = SELECT IFNULL(`pbd`.`main_page`, 1) as `main_page`, \
                        `act`.`id`,  \
                                UNIX_TIMESTAMP(`act`.`date`) as date, \
                                `act`.`post_title`, \
                                `act`.`post_content`,\
                                `act`.`blog_name`, \
                                `act`.`blog_network`,\
                                CASE `act`.`blog_type`\
                                    WHEN 'premium' THEN 0 \
                                    WHEN 'plus' THEN 1 \
                                    WHEN 'basic' THEN 2 \
                                    WHEN '' THEN 2 \
                                ELSE 2 \
                                END as blog_type, \
                                crc32(`act`.`blog_cluster`) as category \
                FROM `wp_bd_activity` act \
                LEFT JOIN `wp_bd_premium_blogs_data` pbd ON act.blog_id = pbd.blog_id \
                where act.blog_id in (select blog_id from wp_blogs where deleted != 1 and public = 1)

when trying to rotate the index using this command:

/usr/bin/indexer activity --rotate

I get this error

WARNING: attribute 'main_page' not found - IGNORING

which is the first row in the query.

Any suggestions ? i cant seem to figure out what the problem is.

mysql is version 5.5.58

sphinxsearch is version 2.0.4

Upvotes: 0

Views: 223

Answers (1)

barryhunter
barryhunter

Reputation: 21091

The first column in the resultset, is always taken as the 'document-id'. The unique id used for the document.

... as main_page is the first column its been assumed as the id - hence no longer available to be used as an attribute (and can't be a field either!)

Guessing your act.id is the real unique id, so put that first

sql_query = SELECT `act`.`id`,  \
   IFNULL(`pbd`.`main_page`, 1) as `main_page`, \
   UNIX_TIMESTAMP(`act`.`date`) as date, \

The order of other columns don't really matter (other than effecting order stored in index) - its the combination sql_attr* settings etc, that determine if columns are fields and/or attributes.

Upvotes: 2

Related Questions