Problematic Search/Display Query

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= 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.

  1. 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.

  2. Select one of the result tickets (/Ticket/Display.html?id=…)

  3. 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,

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:

display_mysql.log (30.4 KB)

mod_versions.txt (4.39 KB)

To add detail to my issue it seems to only happen when clicking from a
Search/Results.html link, but it doesn’t appear to be referrer based
because if you turn them off it still happens, which makes me think it’s
possibly a session variable that’s triggering it? Refreshes or direct
url doesn’t do it either… for example here is the query output when
clicking from the search results with surrounding queries… They may be
from tickets but the behavior is the same.

233 Query SELECT ACL.id, ACL.ObjectType, ACL.ObjectId FROM ACL,
Principals, CachedGroupMembers WHERE (ACL.RightName = ‘SuperUser’ OR
ACL.RightName = ‘ShowTicketComments’) AND Principals.id =
ACL.PrincipalId AND Principals.PrincipalType = ‘Group’ AND
Principals.Disabled = 0 AND CachedGroupMembers.GroupId =
ACL.PrincipalId AND CachedGroupMembers.GroupId = Principals.id AND
CachedGroupMembers.MemberId = 12 AND CachedGroupMembers.Disabled = 0
AND ((ACL.ObjectType = ‘RT::System’ AND ACL.ObjectId = 1) OR
(ACL.ObjectType = ‘RT::Queue’ AND ACL.ObjectId = 1) OR (ACL.ObjectType =
‘RT::Ticket’ AND ACL.ObjectId = 3)) LIMIT 1
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
33 Query SELECT * FROM Users WHERE id = ‘12’

And this is what happens from anywhere else, the query is missing and
working as intended.

245 Query SELECT ACL.id, ACL.ObjectType, ACL.ObjectId FROM ACL,
Principals, CachedGroupMembers WHERE (ACL.RightName = ‘SuperUser’ OR
ACL.RightName = ‘ShowTicketComments’) AND Principals.id =
ACL.PrincipalId AND Principals.PrincipalType = ‘Group’ AND
Principals.Disabled = 0 AND CachedGroupMembers.GroupId =
ACL.PrincipalId AND CachedGroupMembers.GroupId = Principals.id AND
CachedGroupMembers.MemberId = 12 AND CachedGroupMembers.Disabled = 0
AND ((ACL.ObjectType = ‘RT::System’ AND ACL.ObjectId = 1) OR
(ACL.ObjectType = ‘RT::Queue’ AND ACL.ObjectId = 1) OR (ACL.ObjectType =
‘RT::Ticket’ AND ACL.ObjectId = 2)) LIMIT 1
245 Query SELECT * FROM Users WHERE id = ‘12’

Does anyone have any idea how to fix this? Is this suppose to happen?
Any kind of confirmation would be great.

Curtis

Curtis Bruneau wrote: