Bug 465317 - schema upgrade fails on 5.1.1 to 5.2 on external database and 511 i386->5.2 x86-64
schema upgrade fails on 5.1.1 to 5.2 on external database and 511 i386->5.2 x...
Status: CLOSED CURRENTRELEASE
Product: Red Hat Satellite 5
Classification: Red Hat
Component: Upgrades (Show other bugs)
520
All Linux
medium Severity medium
: ---
: ---
Assigned To: Michael Mráka
Preethi Thomas
:
Depends On:
Blocks: 446861
  Show dependency treegraph
 
Reported: 2008-10-02 14:27 EDT by Preethi Thomas
Modified: 2008-11-05 13:42 EST (History)
3 users (show)

See Also:
Fixed In Version: sat520
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2008-11-05 13:42:36 EST
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description Preethi Thomas 2008-10-02 14:27:35 EDT
Description of problem:

schema upgrade fails on 5.1.1 to 5.2 on external database and 511 i386->5.2 x86-64

Version-Release number of selected component (if applicable):

rhn-upgrade-5.2.0-18.el4
How reproducible:


Steps to Reproduce:
1. follow the steps to upgrade a  sat 5.1.1 external database to sat 5.2 external database.
2. run the schema upgrade script.
3.
  
Actual results:
SQL> begin
  2    for i in (select * from user_tables) loop
  3      execute immediate 'alter table "' || i.table_name  || '" enable row movement';
  4    end loop;
  5  end;
  6  /
begin
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at line 3



Expected results:
Successful schema upgrade

Additional info:

This also happens on trying to upgrade from 5.1.1 i386 -> 5.2 x86-64
web traceback
from test01-64 

com.redhat.rhn.common.hibernate.HibernateRuntimeException: couldn't open session
        at com.redhat.rhn.common.hibernate.ConnectionManager.getInternalSession(ConnectionManager.java:260)
        at com.redhat.rhn.common.hibernate.ConnectionManager.getSession(ConnectionManager.java:244)
        at com.redhat.rhn.common.hibernate.HibernateFactory.getSession(HibernateFactory.java:260)
        at com.redhat.rhn.common.db.datasource.CachedStatement.stealConnection(CachedStatement.java:828)
        at com.redhat.rhn.common.db.datasource.CachedStatement.executeCallable(CachedStatement.java:492)
        at com.redhat.rhn.common.db.datasource.CallableMode.execute(CallableMode.java:30)
        at com.redhat.rhn.task.SessionCleanup.run(SessionCleanup.java:89)
        at com.redhat.rhn.task.SingleThreadedTask.execute(SingleThreadedTask.java:51)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:203)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
        at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:426)
        at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
        at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:119)
        at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:57)
        at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1326)
        at com.redhat.rhn.common.hibernate.ConnectionManager.getInternalSession(ConnectionManager.java:257)
        ... 9 more
Caused by: java.sql.SQLException: Connections could not be acquired from the underlying database!
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:104)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:236)
        at com.mchange.v2.c3p0.PoolBackedDataSource.getConnection(PoolBackedDataSource.java:94)
        at org.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:56)
        at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
        ... 14 more
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
        at com.mchange.v2.resourcepool.BasicResourcePool.awaitAcquire(BasicResourcePool.java:970)
        at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:208)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:232)
        ... 17 more
Comment 1 Preethi Thomas 2008-10-02 15:13:11 EDT
More info on what I did with external satellite.

Followed
http://wiki.rhndev.redhat.com/wiki/Database_Migration_-_test-db-X
to upgrade from 9.2 to 10g 

./install.pl --upgrade on satellite

changed cd /etc/tnsnames.ora and /etc/rhn/rhn.conf to point to test-db-3

did the schema upgrade

 5.1.0-27:
      sqlplus pthomas/pthomas@pthomas @satellite-5.1-to-5.2.sql

got the error

Here is the history of what I did on i386 -> x86-64 upgrade

test01-64.rhndev.redhat.com
pass: dog8code

10  ./install.pl --answer-file=/root/answers.txt 
   11  cd
   12  echo $ORACLE_HOME
   13  exit
   14  echo $ORACLE_HOME
   15  ls
   16  up2date rhn-upgrade
   17  vi /etc/sysconfig/rhn/up2date
   18  up2date rhn-upgrade
   19  cd /tmp/
   20  ls
   21  cd dbbackup/
  
   24  runuser oracle -c 'mkdir /opt/apps/oracle/config/9.2.0'
   25  su - oracle
   26  cd /mnt/iso/
   27  ls
   28  runuser oracle -c '/bin/bash install/upgr-rhnsat-db.sh 2>&1' |             tee /var/log/rhn/upgrade_db.log
   29  
   30  /sbin/service rhn-database start
   31  su - oracle
   32  /sbin/service rhn-database start
   33  cd /var/log/
   34  ls
   35  cd rhn
   36  ls
   37  cat rhn_database.log
   38  cd ..
   39  ls
   40  cd rhn
   41  ls
   42  cat install_db.log
   43  /sbin/service rhn-database status
   44  cd
   45  /usr/bin/rhn-schema-version
   46  l
   47  service rhn-satellite status
   48  cd /etc/sysconfig/rhn/satellite-upgrade/
   49  ls
   50  /usr/bin/rhn-schema-version
   51  sqlplus rhnsat/rhnsat@rhnsat @satellite-5.1-to-5.2.sql


after the stellite install

su - oracle

db-control stop
db-control restore /tmp/dbbackup
Comment 2 Steve Salevan 2008-10-02 15:43:14 EDT
To add a short addendum to Preethi's comments, it appears that these issues are not merely limited to external DB-based Satellites.  Both she and I have seen the reported error by following the same steps she reported in Comment #2 on an embedded DB-based Satellite.  In her case, she went from a 511 i386 Satellite to a 520 x86_64 Satellite while I went from a 511 s390x Satellite to a 520 s390x Satellite, and we saw identical behavior to that reported in both instances.
Comment 3 Clifford Perry 2008-10-02 19:12:06 EDT
FYI Prethi - going to ignore the java traceback - because the upgrade log and error above, that the upgrade had not completed. It gave an error 3rd from last task from within the satellite-5.1-to-5.2.sql file:
==================

-- enable row movement on all tables
begin
  for i in (select * from user_tables) loop
    execute immediate 'alter table "' || i.table_name  || '" enable row movement';
  end loop;
end;
/

-- recompile erv_t type to avoid ORA-04061 in the next select
alter type evr_t compile body;

-- End of upgrade body



update rhnVersionInfo set evr_id = lookup_evr(null, '5.2.0', '8')
   where label = 'schema'
   and name_id = lookup_package_name('rhn-satellite-schema');

select   '*** Schema version is now ' || e.evr.as_vre_simple()
from  rhnPackageEVR e, rhnVersionInfo vi
where vi.evr_id = e.id
  and   vi.label = 'schema';

commit;


spool off;
exit;
==============================

Now - I do not have a solution for why this happens and alternative way to do this, but that is all I have so far before I got distracted...
Comment 4 Michael Mráka 2008-10-03 08:02:48 EDT
Error
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at line 3
during 'alter table' means that there is another session connected to database which helds the lock on the table (e.g having uncommited transaction).

=> will fix upgrade documentation like: during schema upgrade all satellite services but rhn-database *must* be stopped.
Comment 5 Michael Mráka 2008-10-03 08:33:27 EDT
Sending        satellite-upgrade/rhn-satellite-5-upgrade-scenario-1a.txt
Sending        satellite-upgrade/rhn-satellite-5-upgrade-scenario-1b.txt
Transmitting file data ..
Committed revision 177541.

Built and tagged rhn-upgrade-5.2.0-22.el[45].
Comment 6 Preethi Thomas 2008-10-07 11:45:04 EDT
verified
Comment 7 Steve Salevan 2008-10-27 11:20:00 EDT
Present in documentation gleaned from Stage, moving to RELEASE_PENDING.
Comment 8 Brandon Perkins 2008-11-05 13:42:36 EST
5.2.0 Satellite is now GA, bugs Closed for Current Release.

Note You need to log in before you can comment on or make changes to this bug.