Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-57923

After Confluence upgrade, The duplicate index and incorrect data type caused MSSQL Database CPU spike

    XMLWordPrintable

Details

    • Bug
    • Resolution: Unresolved
    • Low
    • None
    • 6.2.3
    • None

    Description

      Issue Summary

      Upgrading to 6.2.3 causes a duplicate index on the BANDANA table named `band_cont_key_idx` with column "BANDANACONTEXT", "BANDANAKEY". This currently affects SQL Server database.

      This upgrade also causes the BANDANA table column "BANDANACONTEXT", "BANDANAKEY" to have the `varchar` type instead of the intended type `nvarchar`.

      Steps to Reproduce

      Upgarding Confluence to the version 6.2.3 with MSSQL database.

      Expected Results

      The query against the bandana table should neither long-running query or high CPU contributor.

      Actual Results

      A simple select from bandana table is contributing around 20% database server CPU or become long-running query.

      Workaround

      Manually change the column type to nvarchar with the following query:

      ALTER TABLE BANDANA DROP CONSTRAINT bandana_unique_key;
      DROP INDEX BANDANA.band_cont_key_idx;
      DROP INDEX BANDANA.band_context_idx;
      ALTER TABLE BANDANA ALTER COLUMN BANDANACONTEXT NVARCHAR(255) NOT NULL;
      ALTER TABLE BANDANA ALTER COLUMN BANDANAKEY NVARCHAR(100) NOT NULL;
      CREATE NONCLUSTERED INDEX band_cont_key_idx ON BANDANA(BANDANACONTEXT, BANDANAKEY);
      CREATE NONCLUSTERED INDEX band_context_idx ON BANDANA(BANDANACONTEXT);
      ALTER TABLE BANDANA ADD CONSTRAINT bandana_unique_key UNIQUE (BANDANACONTEXT, BANDANAKEY);
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            yilinmo Yilin (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated: