Bug 486946 - spacewalk-schema-upgrade is unable to upgrade from schema ver. < 5.2.0
spacewalk-schema-upgrade is unable to upgrade from schema ver. < 5.2.0
Status: CLOSED CURRENTRELEASE
Product: Red Hat Satellite 5
Classification: Red Hat
Component: Upgrades (Show other bugs)
530
All Linux
low Severity medium
: ---
: ---
Assigned To: Milan Zazrivec
Jeff Browning
:
Depends On:
Blocks: 456986
  Show dependency treegraph
 
Reported: 2009-02-23 08:15 EST by Milan Zazrivec
Modified: 2009-08-27 13:33 EDT (History)
1 user (show)

See Also:
Fixed In Version: sat530
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2009-08-27 13:33:08 EDT
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 Milan Zazrivec 2009-02-23 08:15:35 EST
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 Zazrivec 2009-02-23 09:25:27 EST
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 Zazrivec 2009-02-24 08:21:52 EST
satellite.git SATELLITE-5.3:
6f2567df6218ba55451111e8e9786c6f94fac577
6c39e6c788b2c511089939f22c1d94e04a613e43
Comment 3 Jeff Browning 2009-06-17 04:44:20 EDT
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 Zazrivec 2009-06-17 05:00:32 EDT
(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 Zazrivec 2009-06-20 04:57:53 EDT
spacewalk-schema-0.5.20-17
Comment 7 Jeff Browning 2009-06-26 16:41:12 EDT
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.