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,
080718 11:32:17 233 Query SELECT * FROM Users WHERE Name =
'RT_System'
233 Query SELECT * FROM Users WHERE Name = 'Nobody'
233 Query SELECT
GET_LOCK('Apache-Session-c1a988c47cd483e43670f2854bc0ceae', 3600)
233 Query SELECT a_session FROM sessions WHERE id =
'c1a988c47cd483e43670f2854bc0ceae'
233 Query SELECT *