Ошибки работы с Sphinx

Post Reply
btr
Posts: 197
Joined: Thu Feb 11, 2021 7:04 am

Ошибки работы с Sphinx

Post by btr »

Добрый день,
настроил Sphinx, в админку сыпятся подобные ошибки:

Code: Select all

Mysql error 1064: index 'xxxx_org_smart_index': sort-by attribute 'date' not found (SELECT id FROM `fuckhd_org_smart_index` WHERE match ('@(date,alt,custom_var2,description,sponsor_name,sponsor_site,tags,group_names,model_names) \'Total drama leshawna\'') ORDER BY date DESC LIMIT 0, 40 OPTION max_matches=20000)

Mysql error 1064: index 'xxxx_org_smart_index': sort-by attribute 'date' not found (SELECT id FROM `fuckhd_org_smart_index` WHERE tag_ids IN (253) ORDER BY date DESC LIMIT 0, 40 OPTION max_matches=20000)
Версия sphinx 3.3.1,
Your Build 202106181316 (Current build: 202106181316 ) PHP version 7.3.27-1~deb10u1 Mysql Version: 10.3.27-MariaDB-0+deb10u1

из настроек sphinx:

Code: Select all

    sql_query_pre = SET NAMES utf8
    sql_query_pre = UPDATE rot_settings SET value = (SELECT MAX(gallery_id) FROM rot_gallery_info) WHERE name = 'sphinx_max_gallery_id'

    sql_query   = SELECT gi.gallery_id, UNIX_TIMESTAMP(gi.activation_date) as date, gd.alt, custom_var2, gd.description, gi.duration, gi.sponsor_id, gs.total_ctr, gi.content_type, rot_sponsors.sponsor_name, rot_sponsors.sponsor_site,\
    (SELECT group_concat(tag_id) FROM rot_gal2tag g2t WHERE g2t.gallery_id = gi.gallery_id) as tag_ids, \
    (SELECT group_concat(tag_name) FROM rot_gal2tag g2t LEFT JOIN rot_tags as t on t.tag_id = g2t.tag_id WHERE g2t.gallery_id = gi.gallery_id) as tags, \
    (SELECT group_concat(name) FROM rot_groups WHERE rot_groups.id in (SELECT group_id FROM rot_gallery_stats1 WHERE rot_gallery_stats1.gallery_id = gi.gallery_id AND group_id != 0)) as group_names, \
    (SELECT group_concat(model_name) FROM rot_models WHERE rot_models.model_id in (SELECT model_id FROM rot_gal2model WHERE rot_gal2model.gallery_id = gi.gallery_id)) as model_names, \
    (SELECT group_concat(distinct(gss.group_id)) FROM rot_gallery_stats1 as gss WHERE gss.gallery_id = gi.gallery_id AND group_id != 0) as categories FROM rot_gallery_info AS gi \
    JOIN rot_sponsors on rot_sponsors.sponsor_id = gi.sponsor_id \
    JOIN rot_gallery_data1 AS gd ON gi.gallery_id = gd.gallery_id \
    JOIN rot_gallery_stats1 AS gs ON gs.gallery_id = gi.gallery_id \
    WHERE gallery_status = 'active' and gallery_type = 0 and gs.best_thumb = 'yes' and gs.group_id = 0 AND gs.group_id NOT IN (SELECT group_id FROM rot_groups_data WHERE status != 1)
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: Ошибки работы с Sphinx

Post by admin »

попробовал на локале этот запрос - все ок, тем более и в запросе есть UNIX_TIMESTAMP(gi.activation_date) as date

если точно сфинкс проиндексирован так то не знаю, скидывайте шел , буду разбираться
Don't forget to run script update
btr
Posts: 197
Joined: Thu Feb 11, 2021 7:04 am

Re: Ошибки работы с Sphinx

Post by btr »

удалил индексы, создал заного, ошибки повторяются.
данные отправил в PM
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: Ошибки работы с Sphinx

Post by admin »

думаю нашел

в примере в админке написано

sql_attr_timestamp = date
sql_attr_uint = duration
sql_attr_uint = sponsor_id
sql_attr_float = total_ctr
sql_attr_uint = content_type
sql_attr_multi = uint tag_ids from field;
sql_attr_multi = uint categories from field;


а у вас в конфиге date нет
Don't forget to run script update
btr
Posts: 197
Joined: Thu Feb 11, 2021 7:04 am

Re: Ошибки работы с Sphinx

Post by btr »

Ага, удалил я его, вспомнил почему:

Code: Select all

ERROR: key 'sql_attr_timestamp' was permanently removed (see documentation for details) in /etc/sphinx/sphinx.conf line 59 col 23.
Судя по всему в свежих версиях убрали этот параметр.

PS. В доках нашел это: removed timestamp columns support, use uint type instead (existing indexes are still supported; timestamp should automatically work as uint in those)

но как самому исправить, незнаю.
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: Ошибки работы с Sphinx

Post by admin »

sql_attr_uint = date
Don't forget to run script update
btr
Posts: 197
Joined: Thu Feb 11, 2021 7:04 am

Re: Ошибки работы с Sphinx

Post by btr »

admin wrote: Mon Jun 21, 2021 5:24 am sql_attr_uint = date
Теперь такие сыпит:

Code: Select all

Core error: Mysql error 1064: index 'xxxx_org_smart_index': query error: no field 'date' found in schema (SELECT id FROM `fuckhd_org_smart_index` WHERE match ('@(date,alt,custom_var2,description,sponsor_name,sponsor_site,tags,group_names,model_names) \'porn\'') ORDER BY duration DESC LIMIT 0, 40 OPTION max_matches=20000)

Core error: Mysql error 1064: index 'xxxx_org_smart_index': query error: no field 'date' found in schema (SELECT id FROM `fuckhd_org_smart_index` WHERE match ('@(date,alt,custom_var2,description,sponsor_name,sponsor_site,tags,group_names,model_names) \'korean handsom gay\'') ORDER BY total_ctr DESC LIMIT 0, 40 OPTION max_matches=20000)
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: Ошибки работы с Sphinx

Post by admin »

думаю оно записало ошибки пока вы меняли инедкс, сейчас в поиске ошибков не видно
Don't forget to run script update
btr
Posts: 197
Joined: Thu Feb 11, 2021 7:04 am

Re: Ошибки работы с Sphinx

Post by btr »

да, вы правы, спасибо большое.
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: Ошибки работы с Sphinx

Post by admin »

работает - отлично)
Don't forget to run script update
Post Reply