[Spacewalk-list] Database Schema Update Issue - 1.2 to 1.3 Upgrade

[Speagle, Andy]

> > > > I'm having an issue with the upgrade from v1.2 to v1.3 ... when I
> > > > run the spacewalk-schema-upgrade script... it just simply returns:
> > > >
> > > > No existing schema version info found in rhnVersionInfo.
> > > >
> > > > I can make a simple connection via sqlplus.. so I know the
> > > > database is running and available.  Looking at the schema upgrade
> > > > script... I find the query that it runs to check the schema version:
> > > >
> > > > select rhnPackageName.name || '-' || (PE.evr).version || '-' ||
> > > > (PE.evr).release from rhnVersionInfo, rhnPackageName,
> > > > rhnPackageEVR PE
> > > >
> > > >         where rhnVersionInfo.label = 'schema'
> > > >         and rhnVersionInfo.name_id = rhnPackageName.id
> > > >         and rhnVersionInfo.evr_id = PE.id;
> > > >
> > > > And when I run that manually via sqlplus .. I get no rows returned.
> > > >
> > > > Has anyone seen this issue?
> > >
> > > Yes. It means your previous schema upgrade (to Spacewalk 1.2) failed.
> > > Check log files in /var/log/spacewalk/schema-upgrade for reasons why.
> >
> > Right... so, this is something that came up in an earlier thread I
> think.
> > During my 1.2 upgrade I had this issue with the schema:
> >
> > SQL> set echo on
> > SQL> spool
> > /var/log/spacewalk/schema-upgrade/20101129-165813-to-spacewalk-schema-
> 1.2.
> > log append SQL> whenever sqlerror exit sql.sqlcode
> > SQL> select
> > 'spacewalk-schema-1.1-to-spacewalk-schema-1.2/210-rhnPackage-
> indexes.sql'
> > from dual;
> >
> > 'SPACEWALK-SCHEMA-1.1-TO-SPACEWALK-SCHEMA-1.2/210-RHNPACKAGE-INDEXES.S
> > Q
> > ----------------------------------------------------------------------
> > -
> > spacewalk-schema-1.1-to-spacewalk-schema-1.2/210-rhnPackage-indexes.sq
> > l
> >
> > SQL>
> > SQL> drop index rhn_package_id_nid_paid_idx;
> > drop index rhn_package_id_nid_paid_idx
> >            *
> > ERROR at line 1:
> > ORA-02429: cannot drop index used for enforcement of unique/primary
> > key
> >
> > However, there was a post that talked about this.. and how to fix it.
> > Did the fix fail to add something in the database to mark it as
> > "successfully upgraded?"
> 
> I'm not quite sure what steps you did to fix the failed schema upgrade,
> nonetheless ordinarily, spacewalk-schema-upgrade would insert one line
> into rhnVersionInfo table at the very end of schema upgrade to indicate
> version of current schema.
> 
> Find the 1.1 -> 1.2 .sql script in /var/log/spacewalk/schema-upgrade and
> at the very bottom of the file, find a line:
> 
> exec dbms_utility.compile_schema(user);
> 
> You need to manually apply this line and everything after it using
> sqlplus.

Very good.  That was exactly what I needed.  

Thanks!

Andy