Bug 506516

Summary:	Oracle db error when navigating to event history
Product:	Red Hat Satellite 5	Reporter:	wes hayutin <whayutin>
Component:	Server	Assignee:	Justin Sherrill <jsherril>
Status:	CLOSED CURRENTRELEASE	QA Contact:	wes hayutin <whayutin>
Severity:	medium	Docs Contact:
Priority:	low
Version:	530	CC:	bperkins, jsherril, mmraka, msuchy
Target Milestone:	---
Target Release:	---
Hardware:	All
OS:	Linux
URL:	https://riverraid.rhndev.redhat.com/network/systems/details/history/history.pxt?sid=1000010023
Whiteboard:
Fixed In Version:	sat530	Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2009-09-10 18:16:22 UTC	Type:	---
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:
Bug Blocks:	456985, 486216

Description wes hayutin 2009-06-17 15:46:13 UTC

Description of problem:

6/16 rhel 5 selinux enforcing

[root@riverraid search]# su - oracle
-bash-3.2$ db-control report
Tablespace                  Size    Used   Avail   Use%
DATA_TBS                    3.9G    3.3G    531M    87%
SYSAUX                      500M     60M    440M    12%
SYSTEM                      400M  240.2M  159.7M    60%
UNDO_TBS                    1.9G  311.5M    1.6G    16%
USERS                       128M     64K  127.9M     0%
-bash-3.2$ 


The following exception occurred while executing this request:
 GET /network/systems/details/history/event.pxt?sid=1000010023&hid=37 HTTP/1.1 (from browser)
 /network/systems/details/history/event.pxt (from Apache)

Date:
  Wed Jun 17 11:07:36 2009

Headers:
  Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
  Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7
  Accept-Encoding: gzip,deflate
  Accept-Language: en-us,en;q=0.5
  Connection: keep-alive
  Cookie: pxt-session-cookie=76x7213965c18a55dc23ee64518d08b3dc6
  Host: riverraid.rhndev.redhat.com
  Keep-Alive: 300
  Referer: https://riverraid.rhndev.redhat.com/network/systems/details/history/history.pxt?sid=1000010023
  User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.0.10) Gecko/2009042315 Firefox/3.0.10

Form variables:
  hid => 37
  sid => 1000010023

User Information:
  User admin (id 1, org_id 1)

Error notes:
  (none)

Initial Request:
  Yes

Error message:
  RHN::Exception: DBD::Oracle::st execute failed: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_TBS (DBD ERROR: OCIStmtExecute) [for Statement "
SELECT PN.name || '-' || PE.evr.as_vre_simple() AS PACKAGE,
       PC.name || DECODE(PC.version, NULL, '', '-' || PC.version) AS NEEDED_CAPABILITY,
       (SELECT name FROM rhnPackageName WHERE id = APRF.suggested) AS SUGGESTED_PACKAGE,
       APRF.flags AS FLAGS,
       APRF.sense AS SENSE
  FROM rhnPackageName PN,
       rhnPackageEVR PE,
       rhnPackageCapability PC,
       rhnActionPackageRemovalFailure APRF
 WHERE APRF.action_id = :action_id
   AND APRF.server_id = :sid
   AND APRF.name_id = PN.id
   AND APRF.evr_id = PE.id
   AND APRF.capability_id = PC.id
ORDER BY UPPER(PACKAGE)
  " with ParamValues: :sid='1000010023', :action_id='37']
  RHN::DB /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB.pm 233 RHN::Exception::DB::throw
  RHN::DB::st /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB.pm 561 RHN::DB::handle_error
  RHN::DB::DataSource /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB/DataSource.pm 75 RHN::DB::st::execute_h
  RHN::DataSource /usr/lib/perl5/vendor_perl/5.8.8/RHN/DataSource.pm 133 RHN::DB::DataSource::run_query
  RHN::DB::Server /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB/Server.pm 1361 RHN::DataSource::execute_query
  RHN::DB::Server /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB/Server.pm 1415 RHN::DB::Server::server_event_package_dep_errors
  RHN::DB::Server /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB/Server.pm 1574 RHN::DB::Server::server_event_package_action
  RHN::Server /usr/lib/perl5/vendor_perl/5.8.8/RHN/Server.pm 53 RHN::DB::Server::server_event_details
  Sniglets::Servers /usr/lib/perl5/vendor_perl/5.8.8/Sniglets/Servers.pm 847 RHN::Server::lookup_server_event
  PXT::Parser /usr/lib/perl5/vendor_perl/5.8.8/PXT/Parser.pm 171 Sniglets::Servers::server_history_event_details
  PXT::Parser /usr/lib/perl5/vendor_perl/5.8.8/PXT/Parser.pm 83 PXT::Parser::expand_tag
  PXT::ApacheHandler /usr/lib/perl5/vendor_perl/5.8.8/PXT/ApacheHandler.pm 632 PXT::Parser::expand_tags
  PXT::ApacheHandler /usr/lib/perl5/vendor_perl/5.8.8/PXT/ApacheHandler.pm 124 PXT::ApacheHandler::pxt_parse_data
  PXT::ApacheHandler /usr/lib/perl5/vendor_perl/5.8.8/PXT/ApacheHandler.pm 124 (eval)
  main -e 0 PXT::ApacheHandler::handler
  main -e 0 (eval)

Offending Query: 
SELECT PN.name || '-' || PE.evr.as_vre_simple() AS PACKAGE,
       PC.name || DECODE(PC.version, NULL, '', '-' || PC.version) AS NEEDED_CAPABILITY,
       (SELECT name FROM rhnPackageName WHERE id = APRF.suggested) AS SUGGESTED_PACKAGE,
       APRF.flags AS FLAGS,
       APRF.sense AS SENSE
  FROM rhnPackageName PN,
       rhnPackageEVR PE,
       rhnPackageCapability PC,
       rhnActionPackageRemovalFailure APRF
 WHERE APRF.action_id = :action_id
   AND APRF.server_id = :sid
   AND APRF.name_id = PN.id
   AND APRF.evr_id = PE.id
   AND APRF.capability_id = PC.id
ORDER BY UPPER(PACKAGE)
  


I can consistently recreate this issue by running the rhn satellite automation, ActivationKey for sat530 class


1. schedule a install of zsh
2. rhn_check
3. tail httpd error log
4. go to event status pending / history


see 

[Wed Jun 17 11:45:05 2009] [error] Execution of /var/www/html/network/systems/details/history/event.pxt failed at Wed Jun 17 11:45:05 2009: RHN::Exception: DBD::Oracle::st execute failed: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_TBS (DBD ERROR: OCIStmtExecute) [for Statement "\nSELECT PN.name || '-' || PE.evr.as_vre_simple() AS PACKAGE,\n       PC.name || DECODE(PC.version, NULL, '', '-' || PC.version) AS NEEDED_CAPABILITY,\n       (SELECT name FROM rhnPackageName WHERE id = APRF.suggested) AS SUGGESTED_PACKAGE,\n       APRF.flags AS FLAGS,\n       APRF.sense AS SENSE\n  FROM rhnPackageName PN,\n       rhnPackageEVR PE,\n       rhnPackageCapability PC,\n       rhnActionPackageRemovalFailure APRF\n WHERE APRF.action_id = :action_id\n   AND APRF.server_id = :sid\n   AND APRF.name_id = PN.id\n   AND APRF.evr_id = PE.id\n   AND APRF.capability_id = PC.id\nORDER BY UPPER(PACKAGE)\n  " with ParamValues: :sid='1000010024', :action_id='40']\n  RHN::DB /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB.pm 233 RHN::Exception::DB::throw\n  RHN::DB::st /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB.pm 561 RHN::DB::handle_error\n  RHN::DB::DataSource /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB/DataSource.pm 75 RHN::DB::st::execute_h\n  RHN::DataSource /usr/lib/perl5/vendor_perl/5.8.8/RHN/DataSource.pm 133 RHN::DB::DataSource::run_query\n  RHN::DB::Server /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB/Server.pm 1361 RHN::DataSource::execute_query\n  RHN::DB::Server /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB/Server.pm 1415 RHN::DB::Server::server_event_package_dep_errors\n  RHN::DB::Server /usr/lib/perl5/vendor_perl/5.8.8/RHN/DB/Server.pm 1574 RHN::DB::Server::server_event_package_action\n  RHN::Server /usr/lib/perl5/vendor_perl/5.8.8/RHN/Server.pm 53 RHN::DB::Server::server_event_details\n  Sniglets::Servers /usr/lib/perl5/vendor_perl/5.8.8/Sniglets/Servers.pm 847 RHN::Server::lookup_server_event\n  PXT::Parser /usr/lib/perl5/vendor_perl/5.8.8/PXT/Parser.pm 171 Sniglets::Servers::server_history_event_details\n  PXT::Parser /usr/lib/perl5/vendor_perl/5.8.8/PXT/Parser.pm 83 PXT::Parser::expand_tag\n  PXT::ApacheHandler /usr/lib/perl5/vendor_perl/5.8.8/PXT/ApacheHandler.pm 632 PXT::Parser::expand_tags\n  PXT::ApacheHandler /usr/lib/perl5/vendor_perl/5.8.8/PXT/ApacheHandler.pm 124 PXT::ApacheHandler::pxt_parse_data\n  PXT::ApacheHandler /usr/lib/perl5/vendor_perl/5.8.8/PXT/ApacheHandler.pm 124 (eval)\n  main -e 0 PXT::ApacheHandler::handler\n  main -e 0 (eval)\n\nOffending Query: \nSELECT PN.name || '-' || PE.evr.as_vre_simple() AS PACKAGE,\n       PC.name || DECODE(PC.version, NULL, '', '-' || PC.version) AS NEEDED_CAPABILITY,\n       (SELECT name FROM rhnPackageName WHERE id = APRF.suggested) AS SUGGESTED_PACKAGE,\n       APRF.flags AS FLAGS,\n       APRF.sense AS SENSE\n  FROM rhnPackageName PN,\n       rhnPackageEVR PE,\n       rhnPackageCapability PC,\n       rhnActionPackageRemovalFailure APRF\n WHERE APRF.action_id = :action_id\n   AND APRF.server_id = :sid\n   AND APRF.name_id = PN.id\n   AND APRF.evr_id = PE.id\n   AND APRF.capability_id = PC.id\nORDER BY UPPER(PACKAGE)\n  
[Wed Jun 17 11:45:05 2009] [error] Traceback sent to whayutin at /usr/lib/perl5/vendor_perl/5.8.8/PXT/ApacheHandler.pm line 729.

Comment 1 Justin Sherrill 2009-06-17 21:03:02 UTC

So I ran:

db-control gather-stats

on riverraid and the problem went away.  I think the DB just needed to do some analysing to run that query effectively.

Comment 2 Justin Sherrill 2009-06-17 21:37:51 UTC

to run gather stats:


# su - oracle
# db-control gather-stats

Comment 3 wes hayutin 2009-06-19 14:15:48 UTC

gather-stats resolved the issue.
in addition I installed a fresh sat, let it sit for a day, and did not recreate the problem.

resolved

Comment 4 Michael Mráka 2009-06-25 16:55:05 UTC

Just FYI:
Original database error was ORA-01652: unable to extend
temp segment by 128 in tablespace TEMP_TBS. Which means there is no space left in temporary tablespace. I can't imagine how gathering stats may help to free TEMP_TBS. The other way round - gather-stats consume temp space itself so it can make things worse.

In this case I assume a session which held temp space ended so database released it and gathering stats was just a placebo.

Comment 5 Justin Sherrill 2009-06-25 18:08:18 UTC

  Michael,

Is it not possible that the query that the DB was running before gather stats had been run was so unoptimized that it was using more temp space ?  While after running gather stats, oracle was able to more efficiently run the query thereby using less tempspace.  

-Justin

Comment 6 Michael Mráka 2009-06-25 19:35:18 UTC

You're right, I can imagine that.

E.g. unoptimized query does full scan of two huge tables and then merges them so it needs a lot of space for merge sort and then filter nearly all rows out while optimized one uses indexes so it merges only several rows together. But it's so artificial example...

I've never seen such behaviour even on very large and heavy loaded databases.

--mm

Comment 7 Miroslav Suchý 2009-08-20 13:51:51 UTC

I could not reproduce it. I even think that it was never been bug per se.
Anyway moving to verified in stage.

Comment 8 Brandon Perkins 2009-09-10 18:16:22 UTC

An advisory has been issued which should help the problem
described in this bug report. This report is therefore being
closed with a resolution of ERRATA. For more information
on therefore solution and/or where to find the updated files,
please follow the link below. You may reopen this bug report
if the solution does not work for you.

http://rhn.redhat.com/errata/RHEA-2009-1434.html