Red Hat Satellite 5: Getting "javax.servlet.ServletException: com.redhat.rhn.common.db.WrappedSQLException: ERROR: column sop.package_arch_id does not exist" webtrace back emails from satellite server.

Solution Unverified - Updated 2015-06-03T10:34:33+00:00 -

Environment

Built new Red Hat Satellite 5.6
Schema version 5.6.0.19-3.
Installed schema rpm of 5.6.0.21

Issue

There have been symptoms of database corruption lately. For example, a server that was fully up to date yesterday display 203 Errata and 336 packages that need updating -- despite having been updated again this morning (after this occured). In addition, clicking on the packages # in the web UI (which should display the packages requiring updating) presents an Internal Server Error.
Satellite sending traceback emails with following error:

Exception:
javax.servlet.ServletException: com.redhat.rhn.common.db.WrappedSQLException: ERROR: column sop.package_arch_id does not exist
  Position: 910

Resolution

Refer: How do I upgrade the database schema of a Red Hat Satellite server? to update the schema on the Red Hat Satellite.

Root Cause

The schema rpm had been updated but the schema version of the satellite was not updated yet.

Diagnostic Steps

Logs from when the Internal Server Error occured:

==> /var/log/tomcat6/catalina.out <==
2015-01-20 13:10:48,407 [TP-Processor13] ERROR 
com.redhat.rhn.common.db.datasource.CachedStatement - Error while processing 
cached statement sql: SELECT  n.id AS id,
        n.id AS name_id,
        lookup_evr(((latest.evr)).epoch, (latest.evr).version, 
(latest.evr).release) AS evr_id,
        latest.package_arch_id AS arch_id,
        (latest.evr).epoch AS epoch,
        (latest.evr).version AS version,
        (latest.evr).release AS release,
        n.name AS name,
        n.name ||'-'|| evr_t_as_vre_simple(latest.evr) || '.' || 
latest_pa.label AS nvrea,
        n.name ||'-'|| evr_t_as_vre_simple(spe.evr) || '.' || spa.label AS 
installed_package,
        n.id || '|' || lookup_evr((latest.evr).epoch, (latest.evr).version, 
(latest.evr).release)|| '|' || latest.package_arch_id AS id_combo
  FROM
       rhnServerPackage sp
  join rhnPackageName n
    on n.id = sp.name_id
  join rhnPackageArch spa
    on spa.id = sp.package_arch_id
  join rhnPackageEvr spe
    on spe.id = sp.evr_id
  join (
        select sop.package_name_id,
               sop.package_arch_id,
               max(PE.evr) evr
          from rhnServerOutdatedPackages sop
          join rhnPackageEVR pe
            on sop.package_evr_id = pe.id
         where sop.server_id = ?
         group by sop.package_name_id, sop.package_arch_id) latest
    on latest.package_name_id = sp.name_id
  join rhnPackageArch latest_pa
    on latest_pa.id = latest.package_arch_id
  join rhnPackageUpgradeArchCompat puac
    on puac.package_arch_id = sp.package_arch_id
   and puac.package_upgrade_arch_id = latest.package_arch_id
 where sp.server_id = ?
 order by upper(n.name)
com.redhat.rhn.common.db.WrappedSQLException: ERROR: column 
sop.package_arch_id does not exist
  Position: 910
   at 
com.redhat.rhn.common.translation.SqlExceptionTranslator.postgreSqlException(SqlExceptionTranslator.java:54)
   at 
com.redhat.rhn.common.translation.SqlExceptionTranslator.sqlException(SqlExceptionTranslator.java:44)
   at 
com.redhat.rhn.common.db.NamedPreparedStatement.execute(NamedPreparedStatement.java:143)
   at 
com.redhat.rhn.common.db.datasource.CachedStatement.execute(CachedStatement.java:467)
   at 
com.redhat.rhn.common.db.datasource.CachedStatement.execute(CachedStatement.java:443)
   at 
com.redhat.rhn.common.db.datasource.CachedStatement.execute(CachedStatement.java:345)
   at 
com.redhat.rhn.common.db.datasource.CachedStatement.execute(CachedStatement.java:351)
   at 
com.redhat.rhn.common.db.datasource.CachedStatement.execute(CachedStatement.java:287)
   at 
com.redhat.rhn.common.db.datasource.SelectMode.execute(SelectMode.java:110)
   at com.redhat.rhn.manager.BaseManager.makeDataResult(BaseManager.java:57)
   at 
com.redhat.rhn.manager.rhnpackage.PackageManager.upgradable(PackageManager.java:271)
   at 
com.redhat.rhn.frontend.action.rhnpackage.UpgradableListSetupAction.getDataResult(UpgradableListSetupAction.java:28)
   at 
com.redhat.rhn.frontend.action.rhnpackage.BaseSystemPackagesAction.execute(BaseSystemPackagesAction.java:83)
   at 
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425)
   at 
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:228)
   at 
com.redhat.rhn.frontend.struts.RhnRequestProcessor.process(RhnRequestProcessor.java:102)
   at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
   at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
   at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
   at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at 
com.redhat.rhn.frontend.servlets.AuthFilter.doFilter(AuthFilter.java:127)
   at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at 
com.opensymphony.sitemesh.webapp.SiteMeshFilter.obtainContent(SiteMeshFilter.java:129)
   at 
com.opensymphony.sitemesh.webapp.SiteMeshFilter.doFilter(SiteMeshFilter.java:77)
   at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at 
com.redhat.rhn.frontend.servlets.LocalizedEnvironmentFilter.doFilter(LocalizedEnvironmentFilter.java:67)
   at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at 
com.redhat.rhn.frontend.servlets.EnvironmentFilter.doFilter(EnvironmentFilter.java:100)
   at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at 
com.redhat.rhn.frontend.servlets.SessionFilter.doFilter(SessionFilter.java:57)
   at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at 
com.redhat.rhn.frontend.servlets.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:97)
   at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at 
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
   at 
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
   at 
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
   at 
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
   at 
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
   at 
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
   at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:190)
   at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:291)
   at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:769)
   at 
org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:698)
   at 
org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:891)
   at 
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:690)
   at java.lang.Thread.run(Thread.java:761)
Caused by:
org.postgresql.util.PSQLException: ERROR: column sop.package_arch_id does not 
exist
  Position: 910
   at 
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2094)
   at 
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1827)
   at 
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
   at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:508)
   at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:384)
   at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:377)
   at 
com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:651)
   at 
com.redhat.rhn.common.db.NamedPreparedStatement.execute(NamedPreparedStatement.java:140)
   ... 51 more
2015-01-20 13:10:48,409 [TP-Processor13] WARN 
org.apache.struts.action.RequestProcessor - Unhandled Exception thrown: class 
com.redhat.rhn.common.db.WrappedSQLException
2015-01-20 13:10:48,409 [TP-Processor13] ERROR 
com.redhat.rhn.frontend.servlets.SessionFilter - Error during transaction. 
Rolling back
javax.servlet.ServletException: com.redhat.rhn.common.db.WrappedSQLException: 
ERROR: column sop.package_arch_id does not exist
  Position: 910
   at 
org.apache.struts.action.RequestProcessor.processException(RequestProcessor.java:520)
   at 
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:427)
   at 
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:228)
   at 
com.redhat.rhn.frontend.struts.RhnRequestProcessor.process(RhnRequestProcessor.java:102)
   at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
   at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
   at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
   at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at 
com.redhat.rhn.frontend.servlets.AuthFilter.doFilter(AuthFilter.java:127)
   at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at 
com.opensymphony.sitemesh.webapp.SiteMeshFilter.obtainContent(SiteMeshFilter.java:129)
   at 
com.opensymphony.sitemesh.webapp.SiteMeshFilter.doFilter(SiteMeshFilter.java:77)
   at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at 
com.redhat.rhn.frontend.servlets.LocalizedEnvironmentFilter.doFilter(LocalizedEnvironmentFilter.java:67)
   at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at 
com.redhat.rhn.frontend.servlets.EnvironmentFilter.doFilter(EnvironmentFilter.java:100)
   at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at 
com.redhat.rhn.frontend.servlets.SessionFilter.doFilter(SessionFilter.java:57)
   at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at 
com.redhat.rhn.frontend.servlets.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:97)
   at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at 
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
   at 
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
   at 
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
   at 
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
   at 
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
   at 
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
   at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:190)
   at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:291)
   at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:769)
   at 
org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:698)
   at 
org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:891)
   at 
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:690)
   at java.lang.Thread.run(Thread.java:761)
Caused by:
com.redhat.rhn.common.db.WrappedSQLException: ERROR: column 
sop.package_arch_id does not exist
  Position: 910
   at 
com.redhat.rhn.common.translation.SqlExceptionTranslator.postgreSqlException(SqlExceptionTranslator.java:54)
   at 
com.redhat.rhn.common.translation.SqlExceptionTranslator.sqlException(SqlExceptionTranslator.java:44)
   at 
com.redhat.rhn.common.db.NamedPreparedStatement.execute(NamedPreparedStatement.java:143)
   at 
com.redhat.rhn.common.db.datasource.CachedStatement.execute(CachedStatement.java:467)
   at 
com.redhat.rhn.common.db.datasource.CachedStatement.execute(CachedStatement.java:443)
   at 
com.redhat.rhn.common.db.datasource.CachedStatement.execute(CachedStatement.java:345)
   at 
com.redhat.rhn.common.db.datasource.CachedStatement.execute(CachedStatement.java:351)
   at 
com.redhat.rhn.common.db.datasource.CachedStatement.execute(CachedStatement.java:287)
   at 
com.redhat.rhn.common.db.datasource.SelectMode.execute(SelectMode.java:110)
   at com.redhat.rhn.manager.BaseManager.makeDataResult(BaseManager.java:57)
   at 
com.redhat.rhn.manager.rhnpackage.PackageManager.upgradable(PackageManager.java:271)
   at 
com.redhat.rhn.frontend.action.rhnpackage.UpgradableListSetupAction.getDataResult(UpgradableListSetupAction.java:28)
   at 
com.redhat.rhn.frontend.action.rhnpackage.BaseSystemPackagesAction.execute(BaseSystemPackagesAction.java:83)
   at 
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425)
   ... 40 more
Caused by:
org.postgresql.util.PSQLException: ERROR: column sop.package_arch_id does not 
exist
  Position: 910
   at 
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2094)
   at 
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1827)
   at 
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
   at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:508)
   at 
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:384)
   at 
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:377)
   at 
com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:651)
   at 
com.redhat.rhn.common.db.NamedPreparedStatement.execute(NamedPreparedStatement.java:140)
   ... 51 more

==> /var/log/httpd/ssl_access_log <==
XXX.XXX.XXX.XXX - - [20/Jan/2015:13:10:48 -0600] "GET 
/rhn/systems/details/packages/UpgradableList.do?sid=1000010007 HTTP/1.1" 500 
8273

==> /var/log/httpd/ssl_request_log <==
[20/Jan/2015:13:10:48 -0600] XXX.XXX.XXX.XXX TLSv1 ECDHE-RSA-AES256-SHA "GET 
/rhn/systems/details/packages/UpgradableList.do?sid=1000010007 HTTP/1.1" 8273

==> /var/log/httpd/access_log <==
XXX.XXX.XXX.XXX - - [20/Jan/2015:13:11:00 -0600] "POST /cobbler_api HTTP/1.1" 
200 144 "-" "Java/1.7.0_71"
XXX.XXX.XXX.XXX - - [20/Jan/2015:13:11:00 -0600] "POST /cobbler_api HTTP/1.1" 
200 129 "-" "Java/1.7.0_71"

Verify the columns in the table:

# spacewalk-sql -i
psql (8.4.20)
Type "help" for help.

rhnschema=# \d rhnServerOutdatedPackages
       View "public.rhnserveroutdatedpackages"
     Column      |          Type          | Modifiers 
-----------------+------------------------+-----------
 server_id       | numeric                | 
 package_name_id | numeric                | 
 package_evr_id  | numeric                | 
 package_nvre    | text                   | 
 errata_id       | numeric                | 
 errata_advisory | character varying(100) | 
View definition:
 SELECT DISTINCT snpc.server_id, p.name_id AS package_name_id, p.evr_id AS package_evr_id, p.package_arch_id, (pn.name::text || '-'::text) || evr_t_as_vre_simple(pe.evr)::text AS package_nvre, e.id AS errata_id, e.advisory AS errata_advisory
   FROM rhnpackagename pn, rhnpackageevr pe, rhnpackage p, rhnserverneededpackagecache snpc
   LEFT JOIN rhnerrata e ON snpc.errata_id = e.id
  WHERE snpc.package_id = p.id AND p.name_id = pn.id AND p.evr_id = pe.id;

and note that the package_arch_id column is missing.

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.

Select Your Language

Infrastructure and Management

Cloud Computing

Storage

Runtimes

Integration and Automation