"Your Posts" search fails with Sphinx Backend

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
grega
Registered User
Posts: 3
Joined: Tue May 21, 2019 8:02 am

"Your Posts" search fails with Sphinx Backend

Post by grega » Tue May 21, 2019 8:10 am

What version of phpBB are you using? phpBB 3.2.7
What is your board's URL? https://www.raspberrypi.org/forums/
Who do you host your board with? https://www.mythic-beasts.com
How did you install your board? I used the download package from phpBB.com
What is the most recent action performed on your board? Switched search to utilise Sphinx backend, updated from a previous version of phpBB3 (3.2.2)
Is registration required to reproduce this issue? Yes
Do you have any MODs installed? No
Do you have any extensions installed? Yes
What extensions do you have installed? phpbbmodders/adduser and senky/akismet
What styles do you currently have installed? Prosilver, RPi (custom theme based on Prosilver)
What language(s) is your board currently using? English
Which database type/version are you using? MariaDB
What is your level of experience? Comfortable with PHP and phpBB
What username can be used to view this issue? No answer given
What password can be used to view this issue? No answer given
What actions did you take (updating your board; installing a MOD, style or extension; etc.) prior to this problem becoming noticeable? Switched to Sphinx as the search index backend.
Please describe your problem.

Hello,

We recently switched over to Sphinx and discovered that the "Your Posts" option from Quick Links (search.php?search_id=egosearch) results in the error:

Search failed: index index_phpbb_b6ef3502b2b4eb1d_delta,index_phpbb_b6ef3502b2b4eb1d_main: group-by attribute 'topic_id' not found

Sphinx has been configured using the generated config under adm/index.php?i=acp_search&mode=settings

All other "X Posts" searches under Quick Links (eg. "New Posts", "Unread Posts") work as expected, as does the regular forum search / advanced search functionality (and the Sphinx index appears to have been fully populated).
Generated by SRT Generator

User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 3160
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: "Your Posts" search fails with Sphinx Backend

Post by thecoalman » Tue May 21, 2019 10:18 am

Is that on page error or from sphinx error log? The error log may provide some more information. You can enable the sphinx search error log by adding a path for it in the config file. If I recall correctly it's blank on the generated config file. You may also want to post the config file.

grega
Registered User
Posts: 3
Joined: Tue May 21, 2019 8:02 am

Re: "Your Posts" search fails with Sphinx Backend

Post by grega » Wed May 22, 2019 8:49 am

Thanks! The error is presented in the UI:
Screenshot 2019-05-22 at 09.46.27.png
I'll enable Sphinx logging and see if that provides any more info.

Sphinx config:

Code: Select all

source source_phpbb_b6ef3502b2b4eb1d_main 
{
	type = mysql # mysql or pgsql 
	sql_host = [::1] # SQL server host sphinx connects to 
	sql_user = [dbuser] 
	sql_pass = [dbpassword] 
	sql_db = phpbb 
	sql_port =  # optional, default is 3306 for mysql and 5432 for pgsql 
	sql_query_pre = SET NAMES 'utf8' 
	sql_query_pre = UPDATE phpbb_sphinx SET max_doc_id = (SELECT MAX(post_id) FROM phpbb_posts) WHERE counter_id = 1 
	sql_query_range = SELECT MIN(post_id), MAX(post_id) FROM phpbb_posts 
	sql_range_step = 5000 
	sql_query = SELECT \
						p.post_id AS id, \
						p.forum_id, \
						p.topic_id, \
						p.poster_id, \
						p.post_visibility, \
						CASE WHEN p.post_id = t.topic_first_post_id THEN 1 ELSE 0 END as topic_first_post, \
						p.post_time, \
						p.post_subject, \
						p.post_subject as title, \
						p.post_text as data, \
						t.topic_last_post_time, \
						0 as deleted \
					FROM phpbb_posts p, phpbb_topics t \
					WHERE \
						p.topic_id = t.topic_id \
						AND p.post_id >= $start AND p.post_id <= $end 
	sql_query_post =  
	sql_query_post_index = UPDATE phpbb_sphinx SET max_doc_id = $maxid WHERE counter_id = 1 
	sql_query_info = SELECT * FROM phpbb_posts WHERE post_id = $id 
	sql_attr_uint = forum_id 
	sql_attr_uint = topic_id 
	sql_attr_uint = poster_id 
	sql_attr_uint = post_visibility 
	sql_attr_bool = topic_first_post 
	sql_attr_bool = deleted 
	sql_attr_timestamp = post_time 
	sql_attr_timestamp = topic_last_post_time 
	sql_attr_string = post_subject 
}
source source_phpbb_b6ef3502b2b4eb1d_delta : source_phpbb_b6ef3502b2b4eb1d_main 
{
	sql_query_pre = SET NAMES 'utf8' 
	sql_query_range =  
	sql_range_step =  
	sql_query = SELECT \
						p.post_id AS id, \
						p.forum_id, \
						p.topic_id, \
						p.poster_id, \
						p.post_visibility, \
						CASE WHEN p.post_id = t.topic_first_post_id THEN 1 ELSE 0 END as topic_first_post, \
						p.post_time, \
						p.post_subject, \
						p.post_subject as title, \
						p.post_text as data, \
						t.topic_last_post_time, \
						0 as deleted \
					FROM phpbb_posts p, phpbb_topics t \
					WHERE \
						p.topic_id = t.topic_id \
						AND p.post_id >=  ( SELECT max_doc_id FROM phpbb_sphinx WHERE counter_id=1 ) 
	sql_query_post_index =  
}
index index_phpbb_b6ef3502b2b4eb1d_main 
{
	path = /sphinxindex_phpbb_b6ef3502b2b4eb1d_main 
	source = source_phpbb_b6ef3502b2b4eb1d_main 
	docinfo = extern 
	morphology = none 
	stopwords =  
	min_word_len = 2 
	charset_type = utf-8 
	charset_table = U+FF10..U+FF19->0..9, 0..9, U+FF41..U+FF5A->a..z, U+FF21..U+FF3A->a..z, A..Z->a..z, a..z, U+0149, U+017F, U+0138, U+00DF, U+00FF, U+00C0..U+00D6->U+00E0..U+00F6, U+00E0..U+00F6, U+00D8..U+00DE->U+00F8..U+00FE, U+00F8..U+00FE, U+0100->U+0101, U+0101, U+0102->U+0103, U+0103, U+0104->U+0105, U+0105, U+0106->U+0107, U+0107, U+0108->U+0109, U+0109, U+010A->U+010B, U+010B, U+010C->U+010D, U+010D, U+010E->U+010F, U+010F, U+0110->U+0111, U+0111, U+0112->U+0113, U+0113, U+0114->U+0115, U+0115, U+0116->U+0117, U+0117, U+0118->U+0119, U+0119, U+011A->U+011B, U+011B, U+011C->U+011D, U+011D, U+011E->U+011F, U+011F, U+0130->U+0131, U+0131, U+0132->U+0133, U+0133, U+0134->U+0135, U+0135, U+0136->U+0137, U+0137, U+0139->U+013A, U+013A, U+013B->U+013C, U+013C, U+013D->U+013E, U+013E, U+013F->U+0140, U+0140, U+0141->U+0142, U+0142, U+0143->U+0144, U+0144, U+0145->U+0146, U+0146, U+0147->U+0148, U+0148, U+014A->U+014B, U+014B, U+014C->U+014D, U+014D, U+014E->U+014F, U+014F, U+0150->U+0151, U+0151, U+0152->U+0153, U+0153, U+0154->U+0155, U+0155, U+0156->U+0157, U+0157, U+0158->U+0159, U+0159, U+015A->U+015B, U+015B, U+015C->U+015D, U+015D, U+015E->U+015F, U+015F, U+0160->U+0161, U+0161, U+0162->U+0163, U+0163, U+0164->U+0165, U+0165, U+0166->U+0167, U+0167, U+0168->U+0169, U+0169, U+016A->U+016B, U+016B, U+016C->U+016D, U+016D, U+016E->U+016F, U+016F, U+0170->U+0171, U+0171, U+0172->U+0173, U+0173, U+0174->U+0175, U+0175, U+0176->U+0177, U+0177, U+0178->U+00FF, U+00FF, U+0179->U+017A, U+017A, U+017B->U+017C, U+017C, U+017D->U+017E, U+017E, U+0410..U+042F->U+0430..U+044F, U+0430..U+044F, U+4E00..U+9FFF 
	min_prefix_len = 0 
	min_infix_len = 0 
}
index index_phpbb_b6ef3502b2b4eb1d_delta : index_phpbb_b6ef3502b2b4eb1d_main 
{
	path = /sphinxindex_phpbb_b6ef3502b2b4eb1d_delta 
	source = source_phpbb_b6ef3502b2b4eb1d_delta 
}
indexer 
{
	mem_limit = 512M 
}
searchd 
{
	compat_sphinxql_magics = 0 
	listen = sphinx:9313 
	log = /sphinxlog/searchd.log 
	query_log = /sphinxlog/sphinx-query.log 
	read_timeout = 5 
	max_children = 30 
	pid_file = /sphinxsearchd.pid 
	max_matches = 20000 
	binlog_path = /sphinx 
}

User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 3160
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: "Your Posts" search fails with Sphinx Backend

Post by thecoalman » Wed May 22, 2019 3:42 pm

I'm using Sphinx version 2.2.11 Looking at my config file compared to yours I have line 32 commented out. Not sure why since it's a been while.

Code: Select all

#sql_query_info = SELECT * FROM phpbb_posts WHERE
Line 65 mine has a value set and yours does not, this is what I have:

Code: Select all

sql_query_post_index =  SET NAMES 'utf8'
The paths you have are pointing to root directory? You installed in root? Looks like you have missing slash. Mine on line 69:

Code: Select all

path = /var/lib/sphinx/index_phpbb_XXXXXXXXXXXXXXXX_main

Yours on line 69, I believe there is supposed to be slash after /sphinx

Code: Select all

path = /sphinxindex_phpbb_b6ef3502b2b4eb1d_main 
Line 75 on mine is commented out, once again not sure why but it was done for reason.

Code: Select all

#charset_type = utf-8
Yours on line 82 appears to be missing a slash after sphinx

Code: Select all

path = /sphinxindex_phpbb_b6ef3502b2b4eb1d_delta
Line 91 I have commented out:

Code: Select all

#compat_sphinxql_magics = 0


On line 97 you appear to missing a slash again. Mine:

Code: Select all

pid_file = /var/run/sphinx/searchd.pid
Yours:

Code: Select all

pid_file = /sphinxsearchd.pid
Lastly line 98 I have commented out:

Code: Select all

#max_matches = 20000
Not sure if that is going to help but I would try the paths first. To reiterate the lines I have commented out were done for a reason. The version of Sphinx I'm using is slightly higher than the version that was originally implemented with phpBB and I believe those lines were removed because they were deprecated.

grega
Registered User
Posts: 3
Joined: Tue May 21, 2019 8:02 am

Re: "Your Posts" search fails with Sphinx Backend

Post by grega » Fri May 24, 2019 1:54 pm

Thanks for all of the help here. Have got to the bottom of the issue:

I hadn't configured the Sphinx backend but had assumed that it was a copy/paste job from the phpbb generated config, but having been finally granted access to the Sphinx config this afternoon I confirmed that this wasn't the case. Two things:
  • The config I could see generated by phpbb had borked the `/` in some of the paths, this had been fixed in the config Sphinx was actually using (not a contributor to this particular issue, but worth noting!)
  • The Sphinx config had some different `sql_attr_uint` values, in particular `sql_attr_uint = topic_id post_text` which looked to be an invalid declaration. Removing `post_text` so that the line then read `sql_attr_uint = topic_id` and then rebuilding the indexes resulted in all working as expected.

Post Reply

Return to “[3.2.x] Support Forum”