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; /
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.
satellite.git SATELLITE-5.3: 6f2567df6218ba55451111e8e9786c6f94fac577 6c39e6c788b2c511089939f22c1d94e04a613e43
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 ~]#
(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.
spacewalk-schema-0.5.20-17
Upgraded from 3.7 to 5.3 No error on scheme upgrade. Verified.