Bug 965305 - Upgrading Spacewalk from 1.7 to 1.9 using spacewalk-schema-upgrade
Upgrading Spacewalk from 1.7 to 1.9 using spacewalk-schema-upgrade
Status: CLOSED NOTABUG
Product: Spacewalk
Classification: Community
Component: Server (Show other bugs)
1.7
x86_64 Linux
unspecified Severity urgent
: ---
: ---
Assigned To: Milan Zázrivec
Red Hat Satellite QA List
:
Depends On:
Blocks: space27
  Show dependency treegraph
 
Reported: 2013-05-20 16:53 EDT by ajohnson
Modified: 2017-09-28 14:06 EDT (History)
2 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2013-06-04 18:46:09 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
Log file from upgrade with error. (22.04 KB, text/plain)
2013-05-20 16:53 EDT, ajohnson
no flags Details

  None (edit)
Description ajohnson 2013-05-20 16:53:05 EDT
Created attachment 750745 [details]
Log file from upgrade with error.

Description of problem:
While running spacewalk-schema-upgrade.  
In the 

Version-Release number of selected component (if applicable):
1.7 upgrade to 1.8 in sql script for 1.9

How reproducible:
When every running the spacewalk-schema-upgrade

Steps to Reproduce:
1. Restored oracle database from backup
2. Ran spacewalk-schema-upgrade again.   
3.

Actual results:
spacewalk-schema-1.7-to-spacewalk-schema-1.8/127-rhnPackageCapability-version.sql.oracle
drop index rhn_pkg_cap_name_version_uq
          *
ERROR at line 1:
ORA-01418: specified index does not exist

The index on the table is the one below. 
RHN_PKG_CAPABILITY_ID_PK_UQ  this is he index on the table 

Expected results:
To continue with the upgrade and not exit the scripts. 

Additional info:
Comment 1 Jan Pazdziora 2013-05-21 13:38:04 EDT
How was this Spacewalk initially created? Was it a fresh installation of Spacewalk 1.7 or was it upgraded from older release?
Comment 2 ajohnson 2013-05-21 15:54:04 EDT
(In reply to Jan Pazdziora from comment #1)
> How was this Spacewalk initially created? Was it a fresh installation of
> Spacewalk 1.7 or was it upgraded from older release?

Jan,

This was a upgrade.  Spacewalk has been in you environment since 2011,
I start with 1.4 upgrade to 1.5, 1.5 to 1.6 1.6 upgrade to 1.7. 
I have check all the sql script for each upgrade there was not reference to this index added.


Alvin,
Comment 3 Jan Pazdziora 2013-05-22 03:20:43 EDT
(In reply to ajohnson from comment #2)
> 
> This was a upgrade.  Spacewalk has been in you environment since 2011,
> I start with 1.4 upgrade to 1.5, 1.5 to 1.6 1.6 upgrade to 1.7. 

Thank you for confirming this.

> I have check all the sql script for each upgrade there was not reference to
> this index added.

Right. However, checking

   http://yum.spacewalkproject.org/1.4/RHEL/6/x86_64/spacewalk-schema-1.4.18-1.el6.noarch.rpm

the unique index is created during initial schema population in Spacewalk 1.4, see

   /etc/sysconfig/rhn/oracle/main.sql

in that rpm.

So either it was not populated back in 1.4, or it got dropped manually in the mean time.
Comment 4 ajohnson 2013-05-22 12:14:54 EDT
Jan,

I did find the the create statement. in the deploy.sql in 2011 and main.sql
I am going to restore the database back and create the index.
Then rerun the upgrade once again. 
 

Thanks 
Alvin
Comment 5 ajohnson 2013-05-22 16:24:17 EDT
Jan,

Thank for the help once data was import again.   
Found the problem as the index was not there it would have stop duplicate data in the table rhnPackageCapability.   So in the process to clean up and remove the duplicate records then try  to create the index one more time.
These could of been why the drop error was there. 


Thanks 
Alvin
Comment 6 ajohnson 2013-05-30 01:41:47 EDT
Jan,

I have create new database and doing fresh install spacewalk 1.9.
I getting the following error. And can get past the below error about rhnPackageSyncBlacklist 

insert into rhnPackageSyncBlacklist (package_name_id)
*
ERROR at line 1:
ORA-02291: integrity constraint (SPACEWALK.RHN_PACKAGESYNCBL_PNID_FK) violated - parent key not found


This error is before the one above. 


Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY RHN_CHANNEL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/9      PL/SQL: Item ignored
11/18    PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

12/17    PL/SQL: SQL Statement ignored
13/25    PL/SQL: ORA-00942: table or view does not exist

Alvin
Comment 7 ajohnson 2013-06-03 13:14:40 EDT
Jan,

Heard back from oracle about (SPACEWALK.RHN_PACKAGESYNCBL_PNID_FK)

So there is an index rhnPackageName,

How do update main.sql;

Can this process be run from sql script to build from deploy.sql on the server.


Thanks,
Alvin


Here is the output from oracle



Other possibility is something is wrongly coded in ADF and the application tries to insert into the child table BEFORE inserting into the parent table. Maybe you can try to alter the FK constraints to "DEFERRED", then if this is what happens (inserting child row before inserting parent row), the application should "work" as long as the parent is actually added before the "COMMIT". (But in this case the best is probably to review the application logic to create the parent record before its child record(s))

Error:  ORA 2291
Text:   integrity constraint (%s.%s) violated - parent key not found
-------------------------------------------------------------------------------
Cause:  Attempted to insert or update values that do not have corresponding
        Parent key values.
Action: obvious.

*** Important: The notes below are for experienced users - See Note:22080.1

 
Here is the note.

Error:  ORA 2291
Text:   integrity constraint (%s.%s) violated - parent key not found
-------------------------------------------------------------------------------
Cause:  Attempted to insert or update values that do not have corresponding
        Parent key values.
Action: obvious.

*** Important: The notes below are for experienced users - See Note:22080.1

 
Explanation:
	If you have a FOREIGN KEY constraint on a table you cannot INSERT
	or UPDATE rows such that they would not have a matching PARENT
	in the MASTER table.

Diagnosis:
	Check if the PARENT row exists.

	Check the PARENT columns are indexed.

	Check the ORDER of the constraint.
	Eg: alter table d1 add constraint dd1 
		foreign key ( b,a ) references e1( b,a );
	If there is a primary key on E1(A,B) this statement will process
	correctly but at insert time can raise errors. Ensure the ORDER
 	of the columns matches the order of the PRIMARY KEY index.

	Disable the constraint - perform the action and then enable the 
	constraint. Does the enable work ?

Bugs:
	Self Referential Integrity can raise ORA 2291	Bug:294366
	
Articles:
	Referential Integrity and Locking		<Bul:106754.289>
Comment 8 Jan Pazdziora 2013-06-04 02:53:49 EDT
(In reply to ajohnson from comment #7)
>
> Here is the output from oracle
> 
> Other possibility is something is wrongly coded in ADF and the application

Their answer does not apply. I believe you want to ask them specifically about Oracle bug 11930350 and whether it applies to your installation.
Comment 9 ajohnson 2013-06-04 18:46:09 EDT
Jan,

Will close this bug as this does not apply to what doing at this point. 


Alvin
Comment 10 Eric Herget 2017-09-28 14:06:28 EDT
This BZ closed some time during 2.5, 2.6 or 2.7.  Adding to 2.7 tracking bug.

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