[rt-users] Upgrade problem.

Fri May 9 15:35:25 EDT 2008

I couldn't reproduce this issue and suspect an extension or local modification.

Find file SearchBuilder/Handle.pm

in function SimpleQuery right after:

my $QueryString = shift;

add:

require Carp;
Carp::cluck( $QueryString );

In RT config turn on logging of warnings. Restart server, reproduce
error, in RT log you should find the query with long stack trace. Send
it.

On Fri, May 9, 2008 at 9:41 PM, J.P. Racine <racinejp at vianet.ca> wrote:
> The bug occurs when you click on /Ticket/Display.html?id=<id> where the
> ticket is Status='resolved'. The link is given anywhere you can list
> tickets, search / at a glance etc.. Without query logging on the
> database server you may not notice it, for our database with 250k
> resolved tickets the query attempts to load the whole record set into
> apache's memory until it hits a 1.5gig limit then the 'out of memory'
> condition kills it (or mysql due to over-commit), we were able to
> observe this while debugging the apache children, but the query log also
> showed the following query.
>
> SELECT main.* FROM Tickets main  WHERE (main.Status != 'deleted') AND
> (main.Status = 'resolved') AND (main.EffectiveId = main.id) AND
> (main.Type = 'ticket')  ORDER BY main.id ASC
>
> mysql> SELECT count(*) FROM Tickets main  WHERE (main.Status !=
> 'deleted') AND (main.Status = 'resolved') AND (main.EffectiveId =
> main.id) AND (main.Type = 'ticket')  ORDER BY main.id ASC;
> +----------+
> | count(*) |
> +----------+
> |   234766 |
> +----------+
>
> As you can see it would cause a problem, oddly while trying other ticket
> Status types I didn't see any similar type queries as they were all
> pretty limited to related data via ID and whatnot.. It would seem that
> the query should be limited to the ID supplied to the Display.html. The
> page does finally display if we give it enough memory to play with, I'm
> guessing it's still using the data supplied by the query but it's
> applying the constraint
>
> Ruslan Zakirov wrote:
>> Sounds like a bug, but I don't understand how to reproduce it. Can you
>> describe step by step how to reproduce the problem, so I can turn on
>> SQL logging and find it myself. I just don't get with which page I
>> should work and where start debugging.
>>
>> On Fri, May 9, 2008 at 7:48 PM, J.P. Racine <racinejp at vianet.ca> wrote:
>>
>>> I don't think that this would be related - we have no saved searches at
>>> all.   The single resolved ticket display tries to return all resolved..
>>> we have ~250K resolved tickets in this db and it looks to be returning
>>> all resolved tickets instead of just 1.  The tickets aren't displayed
>>> but the page is trying to get them.
>>>
>>> mysql> SELECT count(*) FROM Tickets main  WHERE (main.Status !=
>>> 'deleted') AND (main.Status = 'resolved') AND (main.EffectiveId =
>>> main.id) AND (main.Type = 'ticket')  ORDER BY main.id ASC;
>>> +----------+
>>> | count(*) |
>>> +----------+
>>> |   234766 |
>>> +----------+
>>>
>>> mysql> select status, count(*) from Tickets GROUP BY status;
>>> +----------+----------+
>>> | status   | count(*) |
>>> +----------+----------+
>>> | deleted  |     7919 |
>>> | new      |     1785 |
>>> | open     |      624 |
>>> | rejected |    23641 |
>>> | resolved |   237938 |
>>> | stalled  |      367 |
>>> +----------+----------+
>>>
>>> ( a few tickets may or may not have been resolved between those 2 queries ).
>>>
>>> Ruslan Zakirov wrote:
>>>
>>>> I think it's related to different handling of Rows attribute of a
>>>> saved search in 3.4 and 3.6. In 3.6 those are unlimited what me be
>>>> desired in some cases. Check that all saved searches have correct
>>>> number of rows. For searches on the at glance page use Edit link then
>>>> another link to edit it for all users, check value and save search.
>>>>
>>>> On Thu, May 8, 2008 at 11:51 PM, J.P. Racine <racinejp at vianet.ca> wrote:
>>>>
>>>>
>>>>> Greetings,
>>>>>
>>>>> I've just upgraded an RT installation from 3.4.4 to 3.6.6 and found that
>>>>> although the schema hasn't changed that there are some SQL queries that
>>>>> seem to behave strangely.  The database is about 9 gigs in size and is
>>>>> runing on mysql 5 ( mysql-server-5.0 debian package 5.0.51a-3~bpo40+1 ).
>>>>>
>>>>> After the upgrade we noticed some strange behavior when trying to view a
>>>>> ticket that was in a resolved state.  The query below was called when
>>>>> the problem occured and and seem to return *all* resolved tickets and
>>>>> that was a bit to much for the memory of the system to handle.
>>>>>
>>>>> http://hostname.domain.com/Ticket/Display.html?id=123456
>>>>>
>>>>> SELECT main.* FROM Tickets main  WHERE (main.Status != 'deleted') AND
>>>>> (main.Status = 'resolved') AND (main.EffectiveId
>>>>> = main.id) AND (main.Type = 'ticket')  ORDER BY main.id ASC
>>>>>
>>>>> The same database is running fine ( albeit a bit slow ) with a
>>>>> production 3.4.4 install. Any pointers or suggestions to remedy this
>>>>> problem would be appreciated.  I just don't see why there is a query
>>>>> with no limit for no apparent reason returning unlimited results.
>>>>>
>>>>> Thanks,
>>>>>
>>>>> JP
>>>>>
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>>>>>
>>>>> Community help: http://wiki.bestpractical.com
>>>>> Commercial support: sales at bestpractical.com
>>>>>
>>>>>
>>>>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
>>>>> Buy a copy at http://rtbook.bestpractical.com
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>> _______________________________________________
>>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>>>
>>> Community help: http://wiki.bestpractical.com
>>> Commercial support: sales at bestpractical.com
>>>
>>>
>>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
>>> Buy a copy at http://rtbook.bestpractical.com
>>>
>>>
>>
>>
>>
>>
>
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>

-- 
Best regards, Ruslan.