[rt-users] Problematic Search/Display Query

[Curtis Bruneau]

Just an update, I have reinstalled the machine in attempt to solve this 
problem, I am using etch packaged apache2+mod_perl2 and mysql5 and the 
problem still occurs,  here is a trimmed sql output from a 
Ticket/Display.html?id=x, attached is the full log for that one page view.

233 Query       SELECT  * FROM Tickets WHERE id = '3' <- The query which 
gets the ticket details.
233 Query       SELECT main.* FROM Tickets main  WHERE (main.Status != 
'deleted') AND ( ( main.Subject LIKE '%e%' ) ) AND (main.Effecti
veId = main.id) AND (main.Type = 'ticket')  ORDER BY main.id ASC <- the 
problem query derived from search criteria with no limit.

Anyone know what it's used for? It's a dangerous query with potentially 
large results. *Can anyone confirm if this happens to them?*

I tried to use as many packages as possible that reasonably met the 
version requirements, the others were done from source. make testdeps 
comes back fine, I attempted to upgrade to the latest versions for 
modules that could be related to the db/query. The module versions are 
also attached.

Thanks

Curtis Bruneau wrote:
> Greetings everyone,
>
> We're having an issue with several versions of RT that are all fully 
> functional aside from one aspect of operation.   The brief description 
> of the problem is that when you go to /Ticket/Display.html?id=<id> 
> from a search page (/Search/Build.html or /Search/Simple.html  ) what 
> appears to be happening is the search is saved in the session (normal) 
> but it's getting queried on Display along with the other queries.  
> This  appears to be an extra query of no use with a limitless result 
> set. This is problematic because however many records your search 
> returns the display will try to load all the records into the web 
> server potentially causing the process to run out of memory.
>
> Packaged OS Details:
> -Debian Etch - kernel 2.6.18-6-amd64 x86_64 GNU/Linux
> -apache2 2.2.3-4+etch4
> -libapache2-mod-perl2 2.0.2-2.4
> -mysql-server-5.0 5.0.51a-3~bpo40+1)
> -perl v5.8.8
>
> From source:
> -libdbix-searchbuilder-perl_1.53-1
> -libmodule-versions-report-perl_1.03-2
> -libtext-quoted-perl-2.05-2
>
> RT Versions Tested and Affected: 3.6.6, 3.7.85, 3.7.86, 3.8.0rc1
> 'make testdeps' in each RT version: PASSED
>
> Steps to reproduce this problem:
>
> 1) Start with a clean installation and clean database.
> 2) Create a few tickets in the default "General" queue as root.
> 3) Enable mysql query logging
>
> log             = /var/log/mysql/mysql.log
>
> Note: Without query logging on the database server you may not notice 
> it unless your search returned thousands of records causing major 
> slowness.
>
> 4)  Next, load the search page in your browser by adding the search 
> criteria and then selecting "Update format and Search" ( 
> Search/Build.html with a listing of the result set of your search). 
> For this example I created 2 test tickets with similar subjects and 
> made my search the subject for the text 'e' which will match both of 
> my tickets. There idea here is to get a common result with multiple 
> records to observe the general query on the db side.
>
> 5) Select one of the result tickets (/Ticket/Display.html?id=...)
>
> 6) **BUG LOADED HERE** -- the search clause, which appears to be 
> stored in the session are used on the following query. The actual 
> query used to display the data is separate and is limited by the 
> Ticket ID.
>
> Watch the query log for:
>
> Query       SELECT main.* FROM Tickets main  WHERE (main.Status != 
> 'deleted') AND (main.Subject LIKE '%e%') AND (main.EffectiveId = 
> main.id) AND (main.Type = 'ticket')  ORDER BY main.id ASC
>
> As you can see the query will return as many records as it matches, 
> potentially thousands or more. The same issue occurs regardless of the 
> search criteria and will always have no limit - it's basically the 
> same query as select count(*) when building the search results paging 
> list, the displayed results also have the same but with a 
> limit/offset. I can not seem to figure out why this is happening, the 
> only reason I can think of as to why this query is needed is possibly 
> some sort of count but the count is missing.
>
> This problem does not occur when you go to the link directly with no 
> referring URL or a non search page, it only seems to happen when a 
> search is saved in the session. Could someone please test this to see 
> if this has been overlooked as most installations wouldn't show any 
> symptom of the problem until there were a large enough result set to 
> cause system memory exhaustion. If you aren't observing this problem I 
> wouldn't mind knowing as well.
>
> Thank you in advance,
>
>
>

-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: display_mysql.log
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20080718/8c7931c4/attachment.ksh>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: mod_versions.txt
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20080718/8c7931c4/attachment.txt>