[Spacewalk-list] Database Error - PXTSessions
Andy Speagle
andy.speagle at wichita.edu
Wed Dec 2 13:24:24 UTC 2009
On Tue, 2009-12-01 at 12:35 -0600, Jesus M. Rodriguez wrote:
> On Tue, Dec 1, 2009 at 1:21 PM, Andy Speagle <andy.speagle at wichita.edu> wrote:
> > Hi all...
> >
> > I've had a problem rear its ugly head today.. and I'm loathe to explain
> > it. Essentially, nearly anything I do in the WebUI times out. I've
> > tracked it to a database issue. It seems that I'm running into table
> > contention because anything I try fires up two queries... one SELECT and
> > one UPDATE... the offensive one I believe is the UPDATE.. which is:
> >
> > update PXTSessions set value=:1, web_user_id=:2, expires=:3 where id=:4
>
> PXTSessions is the way the perl webui handles logged in sessions. It is
> shared with the java webui as well. If you login with no cookies, we will create
> a new entry, each request updates the table to keep the expires date updated.
> If your login times out, but we get presented with the same cookie value, we simply
> update the record again to re-establish the session (if the row exists).
>
> So the update above is quite normal. Are you seeing an error?
>
> >
> > Can anyone speak to this issue? I don't see anything in the archives
> > regarding this. What I think happened is this:
> >
> > I have some automated jobs that run via cron on the Spacewalk server as
> > the "admin" user... while one of those was running, I logged into the
> > WebUI as the same user... I suspect that somehow these logins were in
> > contention for session info...
>
> Are these cron jobs using the API? or are they screen scraping the webui?
> In theory, you should be able to login as many times as you want with the
> same user from different locations. It shouldn't affect anything.
>
> Are all the jobs presenting the same cookie? That would cause them
> all to use the same sessions which would probably cause some bizarre
> issues.
>
> Is there an error log in either /var/log/tomcat5/catalina.out or /var/log/httpd/error_log?
>
> Sincerely,
> jesus
More information... my Spacewalk server is essentially non-operational
at this point, since any time I try to go into system groups, system set
manager, channels, configuration... et al... it just times out and
returns an error.
I've had one of my DBAs collect the running queries at the time when
it's freezing up... perhaps this data will help:
SID 109:
SELECT
G.ID AS ID, G.NAME AS NAME,
(SELECT COUNT(*) FROM rhnUserManagedServerGroups UMSG WHERE
UMSG.server_group_id = G.id)
AS GROUP_ADMINS,
(SELECT COUNT(*) FROM rhnServerGroupMembers SGM WHERE
SGM.server_group_id = G.id
AND EXISTS ( SELECT 1
FROM rhnServerFeaturesView SFV
WHERE SFV.server_id = SGM.server_id
AND SFV.label = 'ftr_system_grouping')) AS SERVER_COUNT,
(SELECT DECODE(
MAX(DECODE(PS.state, 'OK', 1, 'PENDING', 2, 'UNKNOWN', 3, 'WARNING', 4,
'CRITICAL', 5)),
1, 'OK', 2, 'PENDING', 3, 'UNKNOWN', 4, 'WARNING', 5, 'CRITICAL') ST
FROM rhn_check_probe CP, rhn_probe_state PS, rhnServerGroupMembership
SGM
WHERE PS.probe_id = CP.probe_id
AND SGM.server_id = CP.host_id(+)
AND SGM.group_id = UMSG.server_group_id
GROUP BY SGM.group_id) MONITORING_STATUS,
( SELECT 1 from dual where exists
(select 1 FROM rhnErrata E,
rhnServerNeededPackageCache SNPC,
rhnServerGroupMembers SGM
WHERE E.advisory_type = 'Security Advisory'
and snpc.errata_id = e.id
and snpc.server_id = sgm.server_id
and sgm.server_group_id = G.id)) AS SECURITY_ERRATA,
(SELECT 1 from dual where exists
(select 1 FROM rhnErrata E,
rhnServerNeededPackageCache SNPC,
rhnServerGroupMembers SGM
WHERE E.advisory_type = 'Bug Fix Advisory'
and snpc.errata_id = e.id
and snpc.server_id = sgm.server_id
and sgm.server_group_id = G.id)) AS BUG_ERRATA,
(SELECT 1 from dual where exists
(select 1 FROM rhnErrata E,
rhnServerNeededPackageCache SNPC,
rhnServerGroupMembers SGM
WHERE E.advisory_type = 'Product Enhancement Advisory'
and snpc.errata_id = e.id
and snpc.server_id = sgm.server_id
and sgm.server_group_id = G.id)) AS ENHANCEMENT_ERRATA
FROM rhnServerGroup G, rhnUserManagedServerGroups UMSG
WHERE G.ORG_ID = :1
AND UMSG.user_id = :2
AND G.id IN (:3, :4, :5, :6, :7, :8)
AND G.id = UMSG.server_group_id
SID 142:
SELECT
G.ID AS ID, G.NAME AS NAME,
(SELECT COUNT(*) FROM rhnUserManagedServerGroups UMSG WHERE
UMSG.server_group_id = G.id)
AS GROUP_ADMINS,
(SELECT COUNT(*) FROM rhnServerGroupMembers SGM WHERE
SGM.server_group_id = G.id
AND EXISTS ( SELECT 1
FROM rhnServerFeaturesView SFV
WHERE SFV.server_id = SGM.server_id
AND SFV.label = 'ftr_system_grouping')) AS SERVER_COUNT,
(SELECT DECODE(
MAX(DECODE(PS.state, 'OK', 1, 'PENDING', 2, 'UNKNOWN', 3, 'WARNING', 4,
'CRITICAL', 5)),
1, 'OK', 2, 'PENDING', 3, 'UNKNOWN', 4, 'WARNING', 5, 'CRITICAL') ST
FROM rhn_check_probe CP, rhn_probe_state PS, rhnServerGroupMembership
SGM
WHERE PS.probe_id = CP.probe_id
AND SGM.server_id = CP.host_id(+)
AND SGM.group_id = UMSG.server_group_id
GROUP BY SGM.group_id) MONITORING_STATUS,
( SELECT 1 from dual where exists
(select 1 FROM rhnErrata E,
rhnServerNeededPackageCache SNPC,
rhnServerGroupMembers SGM
WHERE E.advisory_type = 'Security Advisory'
and snpc.errata_id = e.id
and snpc.server_id = sgm.server_id
and sgm.server_group_id = G.id)) AS SECURITY_ERRATA,
(SELECT 1 from dual where exists
(select 1 FROM rhnErrata E,
rhnServerNeededPackageCache SNPC,
rhnServerGroupMembers SGM
WHERE E.advisory_type = 'Bug Fix Advisory'
and snpc.errata_id = e.id
and snpc.server_id = sgm.server_id
and sgm.server_group_id = G.id)) AS BUG_ERRATA,
(SELECT 1 from dual where exists
(select 1 FROM rhnErrata E,
rhnServerNeededPackageCache SNPC,
rhnServerGroupMembers SGM
WHERE E.advisory_type = 'Product Enhancement Advisory'
and snpc.errata_id = e.id
and snpc.server_id = sgm.server_id
and sgm.server_group_id = G.id)) AS ENHANCEMENT_ERRATA
FROM rhnServerGroup G, rhnUserManagedServerGroups UMSG
WHERE G.ORG_ID = :1
AND UMSG.user_id = :2
AND G.id IN (:3, :4, :5, :6, :7)
AND G.id = UMSG.server_group_id
SID 122:
SELECT
G.ID AS ID, G.NAME AS NAME,
(SELECT COUNT(*) FROM rhnUserManagedServerGroups UMSG WHERE
UMSG.server_group_id = G.id)
AS GROUP_ADMINS,
(SELECT COUNT(*) FROM rhnServerGroupMembers SGM WHERE
SGM.server_group_id = G.id
AND EXISTS ( SELECT 1
FROM rhnServerFeaturesView SFV
WHERE SFV.server_id = SGM.server_id
AND SFV.label = 'ftr_system_grouping')) AS SERVER_COUNT,
(SELECT DECODE(
MAX(DECODE(PS.state, 'OK', 1, 'PENDING', 2, 'UNKNOWN', 3, 'WARNING', 4,
'CRITICAL', 5)),
1, 'OK', 2, 'PENDING', 3, 'UNKNOWN', 4, 'WARNING', 5, 'CRITICAL') ST
FROM rhn_check_probe CP, rhn_probe_state PS, rhnServerGroupMembership
SGM
WHERE PS.probe_id = CP.probe_id
AND SGM.server_id = CP.host_id(+)
AND SGM.group_id = UMSG.server_group_id
GROUP BY SGM.group_id) MONITORING_STATUS,
( SELECT 1 from dual where exists
(select 1 FROM rhnErrata E,
rhnServerNeededPackageCache SNPC,
rhnServerGroupMembers SGM
WHERE E.advisory_type = 'Security Advisory'
and snpc.errata_id = e.id
and snpc.server_id = sgm.server_id
and sgm.server_group_id = G.id)) AS SECURITY_ERRATA,
(SELECT 1 from dual where exists
(select 1 FROM rhnErrata E,
rhnServerNeededPackageCache SNPC,
rhnServerGroupMembers SGM
WHERE E.advisory_type = 'Bug Fix Advisory'
and snpc.errata_id = e.id
and snpc.server_id = sgm.server_id
and sgm.server_group_id = G.id)) AS BUG_ERRATA,
(SELECT 1 from dual where exists
(select 1 FROM rhnErrata E,
rhnServerNeededPackageCache SNPC,
rhnServerGroupMembers SGM
WHERE E.advisory_type = 'Product Enhancement Advisory'
and snpc.errata_id = e.id
and snpc.server_id = sgm.server_id
and sgm.server_group_id = G.id)) AS ENHANCEMENT_ERRATA
FROM rhnServerGroup G, rhnUserManagedServerGroups UMSG
WHERE G.ORG_ID = :1
AND UMSG.user_id = :2
AND G.id IN (:3, :4, :5, :6, :7)
AND G.id = UMSG.server_group_id
SID 112:
update PXTSessions set value=:1, web_user_id=:2, expires=:3 where id=:4
I hope someone can make heads or tails of this... is there something I
can do to cleanup old sessions... figure out what's "stuck" ...?
--
Andy Speagle
"THE Student" - UCATS
Wichita State University
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 198 bytes
Desc: This is a digitally signed message part
URL: <http://listman.redhat.com/archives/spacewalk-list/attachments/20091202/291e9052/attachment.sig>
More information about the Spacewalk-list
mailing list