Bug 486946 - spacewalk-schema-upgrade is unable to upgrade from schema ver. < 5.2.0
Summary: spacewalk-schema-upgrade is unable to upgrade from schema ver. < 5.2.0
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: Red Hat Satellite 5
Classification: Red Hat
Component: Upgrades
Version: 530
Hardware: All
OS: Linux
low
medium
Target Milestone: ---
Assignee: Milan Zázrivec
QA Contact: Jeff Browning
URL:
Whiteboard:
Depends On:
Blocks: 456986
TreeView+ depends on / blocked
 
Reported: 2009-02-23 13:15 UTC by Milan Zázrivec
Modified: 2009-08-27 17:33 UTC (History)
1 user (show)

Fixed In Version: sat530
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2009-08-27 17:33:08 UTC
Target Upstream Version:
Embargoed:


Attachments (Terms of Use)

Description Milan Zázrivec 2009-02-23 13:15:35 UTC
Description of problem:
spacewalk-schema-upgrade (from spacewalk-schema package) is unable to
upgrade database schema when upgrading from schema older than 5.2.0.

Version-Release number of selected component (if applicable):
spacewalk-schema-0.5.10-1
satellite-schema-5.3.0.10-1

How reproducible:
Always

Steps to Reproduce:
1. Install a Satellite, older than 5.2.0
2. Upgrade to 5.3.0 (./install.pl --upgrade ...)
3. Proceed with schema upgrade (use spacewalk-schema-upgrade script)
  
Actual results:
Schema upgrade fails with following error present in sql upgrade log:
...
SQL> declare
  2     invalid_schema_version exception;
  3     cursor valid_evrs is
  4        select   1
  5        from  dual
  6        where :evr_id in (
  7           lookup_evr('','5.0.0','26')
  8           );
  9  begin
 10     for vevr in valid_evrs loop
 11        return;
 12     end loop;
 13     raise invalid_schema_version;
 14  end;
 15  /
declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception 
ORA-06512: at line 13 

Expected results:
Schema upgrade proceeds.

Additional info:
* On a Satellite 5.0.0 before upgrade:

# sqlplus ...
SQL> select * from rhnversioninfo;

LABEL                                                               NAME_ID
---------------------------------------------------------------- ----------
    EVR_ID CREATED   MODIFIED
---------- --------- ---------
schema                                                                    6
         5 18-FEB-09 18-FEB-09

* What spacewalk-schema-upgrade does before it actually executes all
sql scripts in the upgrade path:

update rhnVersionInfo
        set label = 'schema-from-' || to_char(created, 'YYYYMMDD-HH24MISS'),
                modified = sysdate
        where label = 'schema';
        commit;

* What we do in at the beginning of every sql upgrade script:

variable evr_id number;
variable epoch varchar2(16);
variable version varchar2(64);
variable release varchar2(64);
variable message varchar2(80);

declare
   cursor evrs is
      select   e.id, e.epoch, e.version, e.release, e.evr
      from  rhnPackageEVR e,
         rhnVersionInfo rvi
      where rvi.label = 'schema'
         and rvi.name_id =
            lookup_package_name('rhn-satellite-schema')
         and rvi.evr_id = e.id;
   cursor valid_evrs is
      select   1
      from  dual
      where :evr_id in (
         lookup_evr('','5.0.0','26')
         );
begin
   :evr_id := null;
   :message := 'XXX Invalid satellite schema version.';
   for evr in evrs loop
      :evr_id := evr.id;
      :epoch := evr.epoch;
      :version := evr.version;
      :release := evr.release;
      :message :=  '*** Schema version is currently ' ||
         evr.evr.as_vre_simple() ||
         ', and will NOT be upgraded';
      for vevr in valid_evrs loop
         :message :=  '*** Schema version is currently ' ||
            evr.evr.as_vre_simple() ||
            ', and will be upgraded';
      end loop;
      return;
   end loop;
end;
/
show errors;

select :message from dual;

declare
   invalid_schema_version exception;
   cursor valid_evrs is
      select   1
      from  dual
      where :evr_id in (
         lookup_evr('','5.0.0','26')
         );
begin
   for vevr in valid_evrs loop
      return;
   end loop;
   raise invalid_schema_version;
end;
/

Comment 1 Milan Zázrivec 2009-02-23 14:25:27 UTC
Another problem we have is that all sql upgrade scripts before 5.3.0
did have exit at the very end. This needs to be removed for 5.3.0 since
we merge all the scripts on the upgrade path to a single file.

Comment 2 Milan Zázrivec 2009-02-24 13:21:52 UTC
satellite.git SATELLITE-5.3:
6f2567df6218ba55451111e8e9786c6f94fac577
6c39e6c788b2c511089939f22c1d94e04a613e43

Comment 3 Jeff Browning 2009-06-17 08:44:20 UTC
Fails QA

Upgrading from 5.0 to 5.3 on an i386 RHEL4u7 box, I get this error when running spacewalk-schema-upgrade:

Commit complete.

SQL> set echo on
SQL> spool /var/log/spacewalk/schema-upgrade/20090617-043338-to-satellite-schema-5.3.log append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select 'rhn-satellite-schema-5.2-to-satellite-schema-5.3/001-rhnServerArch_data-solaris-sun4v.sql' from dual;

'RHN-SATELLITE-SCHEMA-5.2-TO-SATELLITE-SCHEMA-5.3/001-RHNSERVERARCH_DATA-SOLARIS
--------------------------------------------------------------------------------
rhn-satellite-schema-5.2-to-satellite-schema-5.3/001-rhnServerArch_data-solaris-
sun4v.sql


SQL> insert into rhnServerArch (id, label, name, arch_type_id) values
  2  (rhn_server_arch_id_seq.nextval, 'sparc-sun4v-solaris', 'Sparc Solaris', lookup_arch_type('sysv-solaris'));
insert into rhnServerArch (id, label, name, arch_type_id) values
*
ERROR at line 1:
ORA-00001: unique constraint (RHNSAT.RHN_SARCH_LABEL_UQ) violated


Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Upgrade failed, please see log in [/var/log/spacewalk/schema-upgrade/].
[root@fjs-0-01 ~]#

Comment 4 Milan Zázrivec 2009-06-17 09:00:32 UTC
(In reply to comment #3)
> Fails QA
> 
> Upgrading from 5.0 to 5.3 on an i386 RHEL4u7 box, I get this error when running
> spacewalk-schema-upgrade:
[..]
> SQL> insert into rhnServerArch (id, label, name, arch_type_id) values
>   2  (rhn_server_arch_id_seq.nextval, 'sparc-sun4v-solaris', 'Sparc Solaris',
> lookup_arch_type('sysv-solaris'));
> insert into rhnServerArch (id, label, name, arch_type_id) values
> *
> ERROR at line 1:
> ORA-00001: unique constraint (RHNSAT.RHN_SARCH_LABEL_UQ) violated

This actually is a different issue, documented in
https://bugzilla.redhat.com/show_bug.cgi?id=506272

The fact remains, until that bug is resolved, no schema
upgrades are possible.

Comment 6 Milan Zázrivec 2009-06-20 08:57:53 UTC
spacewalk-schema-0.5.20-17

Comment 7 Jeff Browning 2009-06-26 20:41:12 UTC
Upgraded from 3.7 to 5.3

No error on scheme upgrade.

Verified.


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