Bug 786252

Cloned to include fix/QA in master

+++ This bug was initially created as a clone of Bug #785022 +++

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

--- Additional comment from loleary on 2012-01-26 18:23:16 EST ---

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.

--- Additional comment from loleary on 2012-01-30 11:31:00 EST ---

Info on auto-indexes: http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm

--- Additional comment from mfoley on 2012-01-30 11:41:43 EST ---

per https://bugzilla.redhat.com/show_bug.cgi?id=785802  ... setting priority to HIGH and targetting for JON 3.01

--- Additional comment from loleary on 2012-01-30 11:48:36 EST ---

Additional forum discussion regarding auto generated indexes when dealing with keys: https://forums.oracle.com/forums/thread.jspa?threadID=2175562

--- Additional comment from jshaughn on 2012-01-31 15:12:41 EST ---

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