Bug 785022

Summary: Server upgrade fails if db-uprade.xml task is unable to created index when index already exists
Product: [Other] RHQ Project Reporter: Larry O'Leary <loleary>
Component: Database, InstallerAssignee: Jay Shaughnessy <jshaughn>
Status: CLOSED CURRENTRELEASE QA Contact: Mike Foley <mfoley>
Severity: high Docs Contact:
Priority: high    
Version: 3.0.1CC: hrupp, jshaughn, skondkar, spinder
Target Milestone: ---   
Target Release: JON 3.0.1   
Hardware: All   
OS: All   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
: 786252 (view as bug list) Environment:
JON 2.4.1 to JON 3.0.0 upgrade Oracle 11g as JON database
Last Closed: 2013-09-03 15:10:20 UTC Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Bug Depends On:    
Bug Blocks: 782579, 785899, 786252, 786297, 806590    
Attachments:
Description Flags
Proposed patch
none
rhq-installer-dbupgrade.log none

Description Larry O'Leary 2012-01-26 23:20:29 UTC
Description of problem:
Server upgrade is fails due to database upgrade (db-upgrade.xml) failure to create an index:

[upgrade] [dbupgrade] Upgrading: [2.101] -> [2.102]
[schemaSpec] Executing the task [class org.rhq.core.db.ant.dbupgrade.SST_AddColumn] in schema spec version [2.102]
[upgrade] [schema-addColumn] Adding new column: table=[RHQ_OPERATION_SCHEDULE], column=[ID], columnType=[INTEGER], precision=[null]
[schemaSpec] Executing the task [class org.rhq.core.db.ant.dbupgrade.SST_JavaTask] in schema spec version [2.102]
[upgrade] [schema-javaTask] Executing Java Task: class=[org.rhq.core.db.upgrade.OperationScheduleIdSetterUpgradeTask]
[schemaSpec] Executing the task [class org.rhq.core.db.ant.dbupgrade.SST_AlterColumn] in schema spec version [2.102]
[schemaSpec] Executing the task [class org.rhq.core.db.ant.dbupgrade.SST_DirectSQL] in schema spec version [2.102]
[statement] Executing direct SQL. Description=[Changing primary key of RHQ_OPERATION_SCHEDULE to ID. Step1/3...] : SQL=[
                        ALTER TABLE RHQ_OPERATION_SCHEDULE DROP CONSTRAINT RHQ_OPERATION_SCHEDULE_KEY
                    ]
[statement] Executing direct SQL. Description=[Changing primary key of RHQ_OPERATION_SCHEDULE to ID. Step2/3...] : SQL=[
                        ALTER TABLE RHQ_OPERATION_SCHEDULE ADD PRIMARY KEY (ID)
                    ]
[statement] Executing direct SQL. Description=[Changing primary key of RHQ_OPERATION_SCHEDULE to ID. Step3/3...] : SQL=[
                        CREATE UNIQUE INDEX RHQ_OPERATION_SCHEDULE_KEY_IDX ON RHQ_OPERATION_SCHEDULE (JOB_NAME, JOB_GROUP)
                    ]
[upgrade] [dbupgrade] 
/opt/jboss/jon/server/jon-server-3.0.0.GA/logs/db-upgrade.xml:27: Failed to upgrade - error in spec version [2.102]. Cause: Error executing the task [org.rhq.core.db.ant.dbupgrade.SST_DirectSQL] in schema spec version [2.102]. Cause: The schema spec task [DirectSQL] has encountered an error. Cause: java.sql.SQLException: ORA-01408: such column list already indexed

	at org.rhq.core.db.ant.dbupgrade.DBUpgrader.execute(DBUpgrader.java:314)
	at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
	at org.apache.tools.ant.Task.perform(Task.java:364)
	at org.apache.tools.ant.Target.execute(Target.java:341)
	at org.apache.tools.ant.Target.performTasks(Target.java:369)
	at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1216)
	at org.apache.tools.ant.Project.executeTarget(Project.java:1185)

Version-Release number of selected component (if applicable):
JON 3.0

How reproducible:
Always

Steps to Reproduce:
1. Install JON 2.4.1 using an Oracle 11g database
2. Start JON 2.4.1
3. Using http://localhost:7080/admin/test/sql.jsp remove the constraint RHQ_OPERATION_SCHEDULE_KEY by executing the following SQL:
   ALTER TABLE RHQ_OPERATION_SCHEDULE DROP CONSTRAINT RHQ_OPERATION_SCHEDULE_KEY;
4. Create an index on the RHQ_OPERATION_SCHEDULE columns that were in the constraint:
  CREATE UNIQUE INDEX test_01_IDX ON RHQ_OPERATION_SCHEDULE (JOB_NAME, JOB_GROUP);
5. Add the unique key constraint back:
   ALTER TABLE RHQ_OPERATION_SCHEDULE ADD CONSTRAINT RHQ_OPERATION_SCHEDULE_KEY PRIMARY KEY (JOB_NAME, JOB_GROUP);
6. Perform upgrade to JON 3.0.0

Actual results:
Upgrade fails and database is left in a corrupt state at spec version [2.101]

Expected results:
Upgrade should succeed

Additional info:
Some RDBMS automatically create an index when a constraint is defined or enabled on a table for a primary or unique key. Although the reproduction steps were done manually, they simulate the problem. Specifically, the constraint resulted in the index being created prior to the upgrade. If the data stored in the database is restored from a backup or migrated to a new Oracle instance (such as upgrading from Oracle 10g to 11g) the index is explicitly created by Oracle. Meaning that the constraint will no longer be auto-creating the index but instead using the index. What this means is that when the constraint is removed (as we do in the db-upgrade tasks) the index remains intact resulting in the upgrade task throwing an exception.

The solution is place index creation in a schema-directSQL task and specify the ignoreError="true" parameter. This should be done for all index creations and should be safe. Basically, if an index creation fails, it means less optimal performance but consider:
  - fails in the event the columns are already indexed
    o This is okay because it simply means the index is already there and we need not create it
  - fails because the user doesn't have adequate permissions to create the index
    o Means the database will be doing a whole lot of table-scans but perhaps after time, this won't be an issue if the RDBMS is self-tuning or the DBA is maintaining the database based on log hints/suggestions
  - fails because there is insufficient space
    o Again, nothing we can do about this and chances are, this is the least of the user's issues

Comment 1 Larry O'Leary 2012-01-26 23:23:16 UTC
Created attachment 557764 [details]
Proposed patch

Proposed patch which adds ignoreError="true" to all schema updates which deal with INDEX manipulation. This is done for all INDEX operations because we can not guarantee an index name and just because the index is not there or named something different shouldn't mean a complete failure.

Comment 2 Larry O'Leary 2012-01-30 16:31:00 UTC
Info on auto-indexes: http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm

Comment 3 Mike Foley 2012-01-30 16:41:43 UTC
per https://bugzilla.redhat.com/show_bug.cgi?id=785802  ... setting priority to HIGH and targetting for JON 3.01

Comment 4 Larry O'Leary 2012-01-30 16:48:36 UTC
Additional forum discussion regarding auto generated indexes when dealing with keys: https://forums.oracle.com/forums/thread.jspa?threadID=2175562

Comment 5 Jay Shaughnessy 2012-01-31 20:12:41 UTC
I see no reason not to apply the suggested patch from Larry...

master:
commit 3ae6a9600f01eb0fad0f52b61277d9cbb31e5479

Applying suggested fix from loleary.  Could not apply patch successfully
so manually reapplied the fix, which is to protect create/drop index
stmts with ignoreErrors="true".


release/jon3.0.x:
cherry pick commit db02533c122d00f11bcfdda3531db5567558a144

Comment 6 Simeon Pinder 2012-02-03 03:23:33 UTC
Moving to ON_QA as there is a new binary 3.0.1.GA RC2 available here to test with:
https://brewweb.devel.redhat.com//buildinfo?buildID=197202

Comment 7 Sunil Kondkar 2012-02-28 10:14:31 UTC
Installed JON 2.4.1 using an Oracle 11g database and Using http://localhost:7080/admin/test/sql.jsp , followed the steps as below:

ALTER TABLE RHQ_OPERATION_SCHEDULE DROP CONSTRAINT RHQ_OPERATION_SCHEDULE_KEY;

CREATE UNIQUE INDEX test_01_IDX ON RHQ_OPERATION_SCHEDULE (JOB_NAME,JOB_GROUP);

ALTER TABLE RHQ_OPERATION_SCHEDULE ADD CONSTRAINT RHQ_OPERATION_SCHEDULE_KEY PRIMARY KEY (JOB_NAME, JOB_GROUP);

Performed upgrade from JON 2.4.1 to JON 3.0.1.GA RC5 build (Build Number: dd8a001:fbca611).

The 'rhq-installer-dbupgrade.log' file displays below error but it proceeds further for upgrade and the upgrade went successful.

[schemaSpec] Error executing the task [org.rhq.core.db.ant.dbupgrade.SST_DirectSQL] in schema spec version [2.102]. Cause: The schema spec task [DirectSQL] has encountered an error. Cause: java.sql.SQLException: ORA-01408: such column list already indexed
[upgrade] [dbupgrade] Finished upgrade: [2.101] -> [2.102] OK
[upgrade] [dbupgrade] Upgrading: [2.102] -> [2.103]
--

Please refer the attached rhq-installer-dbupgrade.log.

Comment 8 Sunil Kondkar 2012-02-28 10:16:06 UTC
Created attachment 566279 [details]
rhq-installer-dbupgrade.log

Comment 9 Heiko W. Rupp 2013-09-03 15:10:20 UTC
Bulk closing of old issues in VERIFIED state.