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
Are you current on DBIX::SearchBuilder?------- Original message -------
From: J.P. Racine racinejp@vianet.ca
Sent: 5-8-'08, 15:51
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
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com
Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
I’m using 1.53-1 from sources found at.
http://packages.debian.org/lenny/libdbix-searchbuilder-perl
Jesse Vincent 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@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
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@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.
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:
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@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@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
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com
Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@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.
The bug occurs when you click on /Ticket/Display.html?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:
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@vianet.ca wrote:
The bug occurs when you click on /Ticket/Display.html?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@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@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
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com
Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com
Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
The rt-users Archives
Community help: http://wiki.bestpractical.com
Commercial support: sales@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.