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

Upgrade task does not consider schema name when checking for DEFAULT constraints in MSSQL 2008

XMLWordPrintable

      To reproduce:

      1. Install JIRA with GH 5.10.6 to MSSQL
      2. Create a new schema in the same database (other than dbo)
      3. Install JIRA with GH 5.10.6 to the new MSSQL database
      4. Upgrade the second JIRA to GH 6.1.2
      5. Attempt to index

      When upgrading from 5.10.6, GH checked for the DEFAULT constraints on several tables and found them to exist. When trying to delete the constraints that it had found, GH specified the incorrect schema name. Upon further research, it seems the check to find the constraint:

      SELECT 1 FROM SYS.OBJECTS WHERE NAME = 'df_AO_60DB71_ISSUERANKING_CUSTOM_FIELD_ID'

      in a way that will return true for any schema name. In scenarios where there are multiple JIRA databases in the same MSSQL instance, this returns true for a different schema, causing AO to try to remove the constraint, doing so by specifying the working (but incorrect) schema.

      java.sql.SQLException: 'df_AO_60DB71_ISSUERANKING_CUSTOM_FIELD_ID' is not a constraint.

      It's all downhill from there.

      IssueIndexer:thread-9 WARN anonymous 1021x18x1 1e83i9s 10.12.216.81 /secure/SetupImport.jspa [java.ao.db.SQLServerDatabaseProvider] Error in schema creation: 'df_AO_60DB71_COLUMNSTATUS_POS' is not a constraint.; attempting to roll back last partially generated table
      Resolution

      To overcome this issue, use the following steps:

      1. Start with a clean, pre-upgrade backup of your JIRA
      2. Ensure that there is no GreenHopper artifact in <jira-home>/plugins/installed-plugins
      3. Ensure that your working database schema is totally empty (for best results, drop it and create a new schema)
      4. Restore the XML backup using JIRA
      5. Stop JIRA
      6. Remove the constraints: 
        'df_AO_60DB71_COLUMN_POS' 
'df_AO_60DB71_COLUMNSTATUS_POS' 
'df_AO_60DB71_ISSUERANKING_CUSTOM_FIELD_ID' 
'df_AO_60DB71_ISSUERANKING_ISSUE_ID' 
'df_AO_60DB71_QUICKFILTER_POS' 
'df_AO_60DB71_SPRINTMARKER_ID' 
'df_AO_60DB71_SWIMLANE_POS'
      7. Start JIRA
      8. Install GreenHopper 6.1.2 or later

          Form Name

            [JSWSERVER-7384] Upgrade task does not consider schema name when checking for DEFAULT constraints in MSSQL 2008

            Marek Broda (Inactive) added a comment - - edited

            Closing, as the corresponding support ticket has also been closed: https://support.atlassian.com/browse/GHS-7057

            Marek Broda (Inactive) added a comment - - edited Closing, as the corresponding support ticket has also been closed: https://support.atlassian.com/browse/GHS-7057

            Peter Obara added a comment -

            move to AO

            Peter Obara added a comment - move to AO

              Unassigned Unassigned
              jgarcia John Garcia (Inactive)
              Affected customers:
              1 This affects my team
              Watchers:
              4 Start watching this issue

                Created:
                Updated:
                Resolved: