Uploaded image for project: 'Jira Software Data Center'
  1. Jira Software Data Center
  2. JSWSERVER-10778

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)

            [JSWSERVER-10778] JIRA Agile upgrade to 6.4.0.6 fails with SQLException ORA-24335

            This also corrupted our JIRA installation - we are now having to restore a database backup from yesterday (lost an entire day's worth of work) to try to allow us to get the system back up and running. The 4 step procedure above did not work for us. It is shameful that a simple plugin could bring the entire system down.

            Tom Vercellotti added a comment - This also corrupted our JIRA installation - we are now having to restore a database backup from yesterday (lost an entire day's worth of work) to try to allow us to get the system back up and running. The 4 step procedure above did not work for us. It is shameful that a simple plugin could bring the entire system down.

            vkharisma added a comment -

            Hi ankur.dhawan, the upgrade task will keep running when 6.4.0.6 is installed. Please follow the downgrade procedure above to stabilize your instance and continue using Agile. Should you have any question, please feel free to raise a support ticket (support.atlassian.com) so our team can assist you further.

            vkharisma added a comment - Hi ankur.dhawan , the upgrade task will keep running when 6.4.0.6 is installed. Please follow the downgrade procedure above to stabilize your instance and continue using Agile. Should you have any question, please feel free to raise a support ticket (support.atlassian.com) so our team can assist you further.

            after upgrading JIRA Agile, some migration script started which failed after running for few hours and JIRA became inaccessible.. We bounced the system and that migration job started again which ran for more than 6 hours and then failed again.. during this 6 hours of window, we were not able to access JIRA but once this job failed, we were able to access JIRA instance not not Agile plugin..

            We were seeing following messages in the logs
            15279 2014-06-04 23:25:59,315 localhost-startStop-1 WARN [jira.issue.managers.DefaultCustomFieldManager] Warning: returning 1 of 2 custom fields named ' Rank'
            15280 2014-06-04 23:25:59,319 localhost-startStop-1 WARN [jira.issue.managers.DefaultCustomFieldManager] Warning: returning 1 of 2 custom fields named ' Rank'
            .
            .
            2014-06-04 23:33:13,101 localhost-startStop-1 INFO [greenhopper.upgrade.lexorank.RealLexoRankDatabaseMigratorImpl] [LexoRank] Migrated 5000 out of xxxxx issues on customfield_10012 field
            .
            .
            we uninstalled JIRA, executed the statements mentioned above and installed previous version of Jira Agile.. it started working again

            Let me know if you need any more iformation

            Ankur Dhawan added a comment - after upgrading JIRA Agile, some migration script started which failed after running for few hours and JIRA became inaccessible.. We bounced the system and that migration job started again which ran for more than 6 hours and then failed again.. during this 6 hours of window, we were not able to access JIRA but once this job failed, we were able to access JIRA instance not not Agile plugin.. We were seeing following messages in the logs 15279 2014-06-04 23:25:59,315 localhost-startStop-1 WARN [jira.issue.managers.DefaultCustomFieldManager] Warning: returning 1 of 2 custom fields named ' Rank' 15280 2014-06-04 23:25:59,319 localhost-startStop-1 WARN [jira.issue.managers.DefaultCustomFieldManager] Warning: returning 1 of 2 custom fields named ' Rank' . . 2014-06-04 23:33:13,101 localhost-startStop-1 INFO [greenhopper.upgrade.lexorank.RealLexoRankDatabaseMigratorImpl] [LexoRank] Migrated 5000 out of xxxxx issues on customfield_10012 field . . we uninstalled JIRA, executed the statements mentioned above and installed previous version of Jira Agile.. it started working again Let me know if you need any more iformation

            vkharisma added a comment - - edited

            Hi ankur.dhawan, what do you mean by instance got corrupted? In my understanding of the problem, this shouldn't caused any data point corruption once you rolled back to 6.3.13.1 as instructed. It may corrupts the index but this should be fix once you perform a full re-indexing. If this is not true, could you please raise a support request together with the support zip so we can have a deeper look on the situation.

            Thanks

            vkharisma added a comment - - edited Hi ankur.dhawan , what do you mean by instance got corrupted? In my understanding of the problem, this shouldn't caused any data point corruption once you rolled back to 6.3.13.1 as instructed. It may corrupts the index but this should be fix once you perform a full re-indexing. If this is not true, could you please raise a support request together with the support zip so we can have a deeper look on the situation. Thanks

            Ankur Dhawan added a comment - - edited

            why do you even release a version that is not compatible with base components.. our test instance got corrupted because some user clicked on upgrade button in manage addons

            Ankur Dhawan added a comment - - edited why do you even release a version that is not compatible with base components.. our test instance got corrupted because some user clicked on upgrade button in manage addons

            There is support request made JSP-194799 about errors on upgrade case. I can add downgrade logs to that ticket.

            Jouni Ylärakkola added a comment - There is support request made JSP-194799 about errors on upgrade case. I can add downgrade logs to that ticket.

            vkharisma added a comment -

            Hi jouni.ylarakkola, would you be able to contact support and provide us with the upgrade/downgrade log? Our support team should be able to assist you to mitigate the problem.

            vkharisma added a comment - Hi jouni.ylarakkola , would you be able to contact support and provide us with the upgrade/downgrade log? Our support team should be able to assist you to mitigate the problem.

            Instruction of uninstalling 6.4.0.6 and "roll back" changes manually on DB with SQL didn´t help.

            ERROR [net.java.ao.sql] Exception executing SQL update <ALTER TABLE "AO_60DB71_AUDITENTRY" MODIFY ("USER" NOT NULL)>
            java.sql.SQLIntegrityConstraintViolationException: ORA-02296: cannot enable (database name) - null values found

            Jouni Ylärakkola added a comment - Instruction of uninstalling 6.4.0.6 and "roll back" changes manually on DB with SQL didn´t help. ERROR [net.java.ao.sql] Exception executing SQL update <ALTER TABLE "AO_60DB71_AUDITENTRY" MODIFY ("USER" NOT NULL)> java.sql.SQLIntegrityConstraintViolationException: ORA-02296: cannot enable (database name) - null values found

            This defect causes customers to end up with corrupted databases - this should therefore be upgraded to a blocker and/or 6.4.0.6 should be removed from public availability.

            David Grierson added a comment - This defect causes customers to end up with corrupted databases - this should therefore be upgraded to a blocker and/or 6.4.0.6 should be removed from public availability.

              Unassigned Unassigned
              vkharisma vkharisma
              Affected customers:
              11 This affects my team
              Watchers:
              35 Start watching this issue

                Created:
                Updated:
                Resolved: