Re: [rt-users] ticket content not displaying via RT's web 'Search' but shows up with sphinx's 'search' binary
the second query isn't run when the first returns 0. This hint helped narrow down things. I figured out the pattern/situation under which search failure occurs, which is: If I create/update a ticket and immediately perform fulltext: search of the same content before reindexing is run(on delta), it will fail to show up on fulltext searches even after the content is later reindexed. In this situation if I run a 'search' from CLI(on delta index specifically) it returns successful hits. Running SELECT COUNT by hand also returns 0 integer at this point. If I do not search for newly created ticket-content before its reindexed, this won't occur. Things are normal in that case. Yes, it is bit awkward but that is the pattern I observe after 8-9 hit and trials ! I'll try clarify things in steps for you: 1. Created a new ticket. Subject - uniqsupport1 , Content - uniqsupport1tic. So I'll be searching for this 1 word content from web end. 2. Searched fulltext:uniqsupport1tic from RT's web end. Yes obviously it didn't return a result. 3. Executed SELECT COUNT query that I got from the logs for this search and it returned 0. All as expected. 4. Indexed delta index (rt_delta) with --rotate. From the command line, I did search -i rt_delta uniqsupport1tic. Got a matching result ticket as expected. I executed the same SELECT COUNT query from Step 2 by hand and it returned 1. 5. I issued fulltext:uniqsupport1tic for 2nd time from RT's web end. Surprisingly, 0 matching result. Observing rt.log I find that only the SELECT COUNT was being executed in response to this 2nd time fulltext search. This was happening when the same SELECT COUNT returned 1 for the search of the same ticket content 'uniqsupport1tic'. After Step 5, I reindexed delta again but proved no use. In this situation I created a new ticket with some random content. I ran the previous failed search again (fulltext:uniqsupport1tic) for 3rd time and this time, it returned the exact matching result. On checking the logs, the 2 SQL queries were also executed consecutively. This also worked if I replied to ANY existing ticket instead of creating a new one! The interesting fact here is that had I NOT done 'Step 2' after updating or creating a ticket, this whole issue don't exist and I get exact matching results from fulltext search without creating or updating a ticket. Yes. I redid Steps 1-5 with a new ticket skipping only Step 2 and in the 5th step I got the exact matching result. Thanks, -- Help improve RT by taking our user survey: https://www.surveymonkey.com/s/N23JW9T
Re: [rt-users] ticket content not displaying via RT's web 'Search' but shows up with sphinx's 'search' binary
On Friday 08 February 2013 12:20 AM, Thomas Sibley wrote: On 02/07/2013 03:13 AM, Subin wrote: the second query isn't run when the first returns 0. This hint helped narrow down things. I figured out the pattern/situation under which search failure occurs, which is: If I create/update a ticket and immediately perform fulltext: search of the same content before reindexing is run(on delta), it will fail to show up on fulltext searches even after the content is later reindexed. In this situation if I run a 'search' from CLI(on delta index specifically) it returns successful hits. Running SELECT COUNT by hand also returns 0 integer at this point. If I do not search for newly created ticket-content before its reindexed, this won't occur. Things are normal in that case. Thanks for your detailed investigation and description (which I've trimmed from below). It sounds very much like you're hitting a cache on the SELECT COUNT query that isn't expired until you create a new ticket (or presumably after some longer amount of time). RT doesn't cache database queries this way, so I suspect it's MySQL's standard query cache or, perhaps more likely, some cache in the Sphinx engine for MySQL (not the sphinxd server itself). Modifying one of the tables involved in the query, as you do by creating a new ticket or replying to an existing ticket, then triggers the cache expiry. If you're interested in further tracking this down, I'd investigate caching at the MySQL and Sphinx level. However, it may be moot in a production environment where the caching is beneficial and there's enough database activity to keep it from being noticeably stale. Spot on! It was the mysql query cache that was responsible. I disabled it and search just works fine without need to update tables. All along I thought RT did some caching in this situation. Well I guess this query caching can be left ON anyway since on a busy production environment the likeliness of the cache to remain state is very less. Thank you for your effort and continued support. -- Help improve RT by taking our user survey: https://www.surveymonkey.com/s/N23JW9T
Re: [rt-users] ticket content not displaying via RT's web 'Search' but shows up with sphinx's 'search' binary
I've SQL query logging onto a file rt.log. While running searches I'm suffixing grep AttachmentsIndex since there is no much of other log data. I've noticed, for every successful string search(a search that returns expected matching results) from RT webUI there are 2 sets of SQL queries performed on 'AttachmentsIndex'. One begins with 'SELECT COUNT(DISTINCT main.id)...' and other 'SELECT DISTINCT main.*...'. Please see the output below from the logs for the search query 'fulltext:uniqops2reply1' where 'uniqops2reply1'//was the only content of a particular ticket that was indexed. / //SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Transactions Transactions_1 ON ( Transactions_1.ObjectType = 'RT::Ticket' ) AND ( Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2 ON ( Attachments_2.TransactionId = Transactions_1.id ) JOIN AttachmentsIndex AttachmentsIndex_3 ON ( AttachmentsIndex_3.id = Attachments_2.id ) WHERE (main.Status != 'deleted') AND ( ( ( AttachmentsIndex_3.query = 'uniqops2reply1;limit=1;maxmatches=1' ) ) AND ( main.Status = 'new' OR main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) ; (/opt/rt4/sbin/../lib/RT/Interface/Web.pm:1183)/ * */SELECT DISTINCT main.* FROM Tickets main JOIN Transactions Transactions_1 ON ( Transactions_1.ObjectType = 'RT::Ticket' ) AND ( Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2 ON ( Attachments_2.TransactionId = Transactions_1.id ) JOIN AttachmentsIndex AttachmentsIndex_3 ON ( AttachmentsIndex_3.id = Attachments_2.id ) WHERE (main.Status != 'deleted') AND ( ( ( AttachmentsIndex_3.query = 'uniqops2reply1;limit=1;maxmatches=1' ) ) AND ( main.Status = 'new' OR main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) ORDER BY main.id ASC LIMIT 50; (/opt/rt4/sbin/../lib/RT/Interface/Web.pm:1183)// / Next, I made a 2nd reply to the same ticket with the content as 'uniqops2reply2'. I ran 'indexer' with --rotate and was successful. For clarity, I issued CLI search of sphinx and it exactly matched the string 'uniqops2reply2' in the same ticket. But on querying 'fulltext:uniqops2reply2' on web-UI it failed with 0 results! I checked the logs to see that only 1 out of the 2 SQL queries were executed i.e. only the below was seen for the failed web-UI search: /SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Transactions Transactions_1 ON ( Transactions_1.ObjectType = 'RT::Ticket' ) AND ( Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2 ON ( Attachments_2.TransactionId = Transactions_1.id ) JOIN AttachmentsIndex AttachmentsIndex_3 ON ( AttachmentsIndex_3.id = Attachments_2.id ) WHERE (main.Status != 'deleted') AND ( ( ( AttachmentsIndex_3.query = 'uniqops2reply2;limit=1;maxmatches=1' ) ) AND ( main.Status = 'new' OR main.Status = 'open' OR main.Status = 'stalled' ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) ; (/opt/rt4/sbin/../lib/RT/Interface/Web.pm:1183) /The /'SELECT DISTINCT...' /query//was missing. I re-ran 'indexer' again but the same story repeated. I executed this missing query by hand on the MySQL server and it returned the matching result! Testing further, another 3rd reply was made to the same ticket with content 'uniqops2reply3'. I *did not *run 'indexer'. Then I ran the previous failed query 'fulltext:uniqops2reply2'. Surprisingly it returned the results! Also on checking rt.log both the SQL queries /'SELECT COUNT...' /and '/SELECT DISTINCT...' /were executed! I ran 'indexer' to index the 3rd reply. Same story; 'fulltext:uniqops2reply3' failed with 0 results and with only /'SELECT COUNT...'/ being executed. I created a new ticket instead of replying to the same ticket. I issued the previously failed 'fulltext:uniqops2reply3' again, but this time it returned the expected result with both SQL queries executed. So it seems that after an update is made and is indexed, it requires another update for the previously updated content to be available from RT's webUI search OR putting it in more specific terms: it requires another update so that '/SELECT DISTINCT...' /is also executed on AttachmentsIndex while searching for previously indexed content. What could possibly be the problem here? Thanks, -- Help improve RT by taking our user survey: https://www.surveymonkey.com/s/N23JW9T
[rt-users] ticket content not displaying via RT's web 'Search' but shows up with sphinx's 'search' binary
After successfully setting up RT with sphinx I'm trying to search for a string in a ticket's 'content'. When I enter the string into 'Search' box of RT it returns no result even when I'm using fulltext:search-string format of search. When the same is searched with Sphinx's 'search' binary, the results are coming up fine. Is RT not communicating with Sphinx over here? A section of my RT_SiteConfig.pm: Set( %FullTextSearch, Enable = 1, Indexed= 1, Table = 'AttachmentsIndex', MaxMatches = '1', ); mysql show create table AttachmentsIndex \G Table: AttachmentsIndex Create Table: CREATE TABLE `AttachmentsIndex` ( `id` int(10) unsigned NOT NULL, `weight` int(11) NOT NULL, `query` varchar(3072) NOT NULL, KEY `query` (`query`(1024)) ) ENGINE=SPHINX DEFAULT CHARSET=utf8 CONNECTION='sphinx://localhost:3312/rt,rt_delta' NB: 1. I have stopped,started Apache after all the sphinx integration was done. Also did it again when search resultswere not returned from web-interface search. 2. 'searchd' is listening at 127.0.0.1:3312 -- Help improve RT by taking our user survey: https://www.surveymonkey.com/s/N23JW9T
Re: [rt-users] ticket content not displaying via RT's web 'Search' but shows up with sphinx's 'search' binary
This issue now seems to be with only 'updated' and 'newly' tickets. The custom string which I create by replying to tickets are not being returned via RT's web search. But are being returned when searched from command line using sphinx's 'search' binary. Thanks, After successfully setting up RT with sphinx I'm trying to search for a string in a ticket's 'content'. When I enter the string into 'Search' box of RT it returns no result even when I'm using fulltext:search-string format of search. When the same is searched with Sphinx's 'search' binary, the results are coming up fine. Is RT not communicating with Sphinx over here? -- Help improve RT by taking our user survey: https://www.surveymonkey.com/s/N23JW9T
[rt-users] how to make updates to already existing tickets or records reflected into indexes quickly
Hello all, Thanks to Arkadiusz Miskiewicz for sharing his knowledge here: http://lists.fsck.com/pipermail/rt-users/2011-August/072464.html My question is based exactly on this config which I find is good. As per my understanding the 'delta' index makes newly created rows/records available by using 'SphinxCounters' table. That is the delta index which will be run every 15 minutes will check if any row which has 'id' greater than the 'id' stored previously in 'max_doc_id' column of 'SphinxCounters' table. If greater then it will read the source and then add the new record to the 'delta' index. If an edit/update happens to an *existing* ticket/document/row in the source database, how will this change get updated into indexes quickly? So according to this config, we will need to wait until the next time the 'main' is reindexed again, right? If true how can we have a workaround for this problem? Please excuse and let me know if I made any wrong assumptions here. Thanks,
Re: [rt-users] how to make updates to already existing tickets or records reflected into indexes quickly
On Monday 04 February 2013 11:53 PM, Thomas Sibley wrote: On 02/04/2013 08:58 AM, Subin wrote: If an edit/update happens to an *existing* ticket/document/row in the source database, how will this change get updated into indexes quickly? So according to this config, we will need to wait until the next time the 'main' is reindexed again, right? If true how can we have a workaround for this problem? Please excuse and let me know if I made any wrong assumptions here. I'm not very familiar with the specifics of Arkadiusz Miskiewicz's delta+main Sphinx setup, but understand the intent. I think you're imagining the RT database incorrectly. Any updates to existing tickets are stored as new rows in Transactions and Attachments tables the database, and those new rows should be picked up by the delta index. Once an individual Transaction/Attachment row is created, it's never changed by RT. This is why it doesn't matter that data in the main index doesn't get updated more frequently. Hello Thomas, I guess that was exactly the information I was lacking. Things are clear now. Thanks a lot.
Re: [rt-users] Both 'subject' and 'body' of an email ticket searchable via RT's web interface?
On Tuesday 05 February 2013 05:21 AM, Thomas Sibley wrote: On 02/04/2013 03:36 PM, Subin wrote: I'd like to fulltext search for patterns/words both in the 'subject' and 'body'of an email ticket from the RT's web interface 'Search' box. Is it possible with the above configs of sql_query*? If not please let me know what more should I need to add? Most of the time true full-text indexing isn't needed for the Subject since it's a normally indexed string with a max length. I'd suggest first approaching this by extending RT::Search::Googleish with an overlay that instead of defaulting unknown and quoted words to Subject searches, defaults them to Subject OR Content searches. This will do what you mean, and should be fairly simple. I know we've done it for support clients. RT::Search::Googleish was written to be extensible with minimal effort. Thomas -- Help improve RT by taking our user survey: https://www.surveymonkey.com/s/N23JW9T I just searched the documentation on how to do this. I'm not able to find any other than a brief description about RT::Search::Googleish here: http://bestpractical.com/rt/docs/4.0/RT/Search/Googleish.html Thanks, -- Help improve RT by taking our user survey: https://www.surveymonkey.com/s/N23JW9T
[rt-users] is default sphinx.conf 'sql_query' enough?
Hello all, Isetup RT v4 with fulltext with indexing.I used the default sphinx.conf generated where the 'sql_query' to fetch data for indexing is like: /*sql_query = \ SELECT a.id, a.content FROM Attachments a \ JOIN Transactions txn ON a.TransactionId = txn.id AND txn.ObjectType = 'RT::Ticket' \ JOIN Tickets t ON txn.ObjectId = t.id \ WHERE a.ContentType = 'text/plain' AND t.Status != 'deleted' sql_query_info = SELECT * FROM Attachments WHERE id=$id */My questionis: I'd like to fulltext search for patterns/words both in the 'subject' and 'body'of an email ticket from the RT's web interface 'Search' box.Is it possible with the above configs of sql_query*?If not please let me know what more should Ineed to add? Thanks,
Re: [rt-users] Taking RT offline required for enabling sphinx fulltext 'only'?
Noted it. Thanks Kevin. Re, On Sunday 27 January 2013 10:51 PM, Kevin Falcone wrote: On Sun, Jan 27, 2013 at 07:04:11PM +0530, Subin wrote: 1. Took the previous RT 3 offline and configured mail redirect. 2. Re-compiled mysql-server 5.1 with 'sphinx' storage engine(sphinxse) for enabling fulltext support for RT 4. 3. Installed RT 4 and restored the previous database backup. It was successful and the new RT interface was working fine after restoring mail flow. All good. However I didn't enable fulltext although I compiled mysql with sphinxse support. I chose it to do another time since I wasn't sure of what all the RT tables to index and was a bit unsure of Sphinx stuff. So my question is: Q) If I intended to enable fulltext with RT4 later on like as mentioned here: [1]http://www.bestpractical.com/rt/docs/4.0/full_text_indexing.html#Creating-and-configuring-the-index1 starting from the step sbin/rt-setup-fulltext-index --dba root --dba-password secret would I be need to take RT offline like I did for the upgrade? I would recommend taking the webserver down. That script creates a new table in your database. You'll need to make the table, add in the configuration, restart apache, run an initial index, etc. These are all things best tested in a development environment and then deployed during a scheduled downtime. -kevin
[rt-users] Taking RT offline required for enabling sphinx fulltext 'only'?
Hello all, Iupgraded my RT from 3.8.7 to 4.0.9 recently. These were the steps I followed: 1. Took the previous RT 3 offline and configured mail redirect. 2. Re-compiled mysql-server 5.1 with 'sphinx' storage engine(sphinxse) for enabling fulltext support for RT 4. 3.Installed RT 4 and restored the previous database backup. It was successful and the new RT interface was working fine after restoring mail flow. All good. However I didn't enable fulltext although I compiled mysql with sphinxse support. I chose it to do another time since I wasn't sure of what all the RT tables to index and was a bit unsure of Sphinx stuff. So my question is: Q) If I intended to enable fulltext with RT4 later onlike as mentioned here: http://www.bestpractical.com/rt/docs/4.0/full_text_indexing.html#Creating-and-configuring-the-index1 starting from the step |sbin/rt-setup-fulltext-index --dba root --dba-password secret would I be need to take RT offline like I did for the upgrade?|
