[rt-users] Upgrade problem.

[J.P. Racine]

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