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:
How was this Spacewalk initially created? Was it a fresh installation of Spacewalk 1.7 or was it upgraded from older release?
(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,
(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.
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
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
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
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>
(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.
Jan, Will close this bug as this does not apply to what doing at this point. Alvin
This BZ closed some time during 2.5, 2.6 or 2.7. Adding to 2.7 tracking bug.