JIRA Agile upgrade to 6.4.0.6 fails with SQLException ORA-24335

Environment

Database: Oracle 10g

Problem

When attempting to upgrade JIRA Agile to 6.4.0.6, JIRA Agile fails to load with error: JIRA Agile is Currently Unavailable.

Logs contain these errors:

2014-06-04 07:12:15,766 localhost-startStop-1 ERROR      [sal.core.upgrade.PluginUpgrader] Upgrade failed: Failed to bulk insert LexoRank rows. SQL[INSERT ALL
INTO "AO_60DB71_LEXORANK" ("FIELD_ID","ISSUE_ID","TYPE","RANK") VALUES(10800,10000,1,'0|1003r4:')
INTO "AO_60DB71_LEXORANK" ("FIELD_ID","ISSUE_ID","TYPE","RANK") VALUES(10800,10001,1,'0|1003qw:')
...
SELECT * FROM DUAL]
	at com.atlassian.greenhopper.manager.lexorank.LexoRankDaoImpl.createBulk(LexoRankDaoImpl.java:130)
	at com.atlassian.greenhopper.upgrade.lexorank.LexoRankIssueIdsCreator.apply(LexoRankIssueIdsCreator.java:38)
	at com.atlassian.greenhopper.upgrade.lexorank.RealLexoRankDatabaseMigratorImpl.migrate(RealLexoRankDatabaseMigratorImpl.java:42)
	at com.atlassian.greenhopper.upgrade.lexorank.LexoRankMigrator.migrate(LexoRankMigrator.java:39)
	at com.atlassian.greenhopper.upgrade.GhUpgradeTask044.performUpgrade(GhUpgradeTask044.java:81)
	at com.atlassian.greenhopper.upgrade.AbstractGhUpgradeTask.doUpgrade(AbstractGhUpgradeTask.java:66)
	...
Caused by: java.sql.SQLException: ORA-24335: cannot support more than 1000 columns
        ...

• The result is JIRA Agile's AO tables in a half upgraded state.

Cause

JIRA Agile 6.4.0.6 introduces the new Lexoranking system. During the migration, the SQL queries use multi-row values ( INSERT ALL ), the upgrade fails if the number of rows are exceeding the 1000 columns limit.

Workaround

IF YOU HAVE A BACKUP OF YOUR JIRA DATABASE PRIOR TO UPGRADING JIRA AGILE

1. Uninstall JIRA Agile 6.4.0.6
2. Restore your JIRA database to a backup from before the JIRA Agile upgrade
3. Install the previous version of JIRA Agile (6.3.13.1)

IF YOU DO NOT HAVE A BACKUP OF YOUR JIRA DATABASE PRIOR TO UPGRADING JIRA AGILE

1. PLEASE BACK UP YOUR JIRA DATABASE BEFORE PROCEEDING
2. Uninstall JIRA Agile 6.4.0.6
3. You will need to manually modify your JIRA database to 'roll back' any upgrade tasks which ran. Please run this SQL:

   UPDATE propertynumber SET propertyvalue = 42 WHERE id = (SELECT id FROM propertyentry WHERE property_key = 'GreenHopper.Upgrade.Latest.Upgraded.Version');
   
   UPDATE propertystring SET propertyvalue = '42' WHERE id = (SELECT id FROM propertyentry WHERE property_key = 'com.pyxis.greenhopper.jira:build');

4. Install the previous version of JIRA Agile (6.3.13.1)