Uploaded image for project: 'SonarQube'
  1. SonarQube
  2. SONAR-7988

Since collation isn't hardcoded on MsSQL anymore, collation inconsistencies can cause runtime errors

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 6.0
    • Fix Version/s: 6.1
    • Component/s: Database
    • Labels:

      Description

      On MsSQL, Collation used to be enforced to Latin1_General_CS_AS in versions 5.3 and 5.4 (see SONAR-6884).

      This has been dropped in favour of only requiring collation to include CS and AS (and enforcing it if need be) (see. SONAR-7549 and SONAR-7798).

      Since this change of strategy (ie. in 5.5), whenever the default collation of the database isn't Latin1_General_CS_AS, the default collation is now used (after potential fix to include CS and AS) resulting in the database having columns with different collations.

      These collations are potentially incompatible causing error at runtime such as :

      Fail to execute database migration: org.sonar.db.version.v60.CleanUsurperRootComponents com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "Latin1_General_CS_AS" in the equal to operation.
      

      The bug occurs only on instances that upgraded from versions 5.3 or 5.4. It does not impact instances that directly upgrade from 5.6 to 6.0 for instance.

      Solution

      • before executing migrations, NVARCHAR columns must be converted to the CS_AS version of db default collation. That fixes the columns badly created in 5.3 and 5.4.
      • new NVARCHAR columns must be created with the CS_AS version of db default collation. That allows to support data migrations on these new columns.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              simon.brandhof Simon Brandhof (Inactive)
              Reporter:
              sebastien.lesaint Sebastien Lesaint
              Votes:
              2 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Due:
                Created:
                Updated:
                Resolved: