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

Upgrading Confluence with a wrong collation on Microsoft SQL causes watches to be lost in all pages.

      If your Confluence is being upgraded while using a Microsoft SQL Server database configured with the wrong collation, all the page watchers will no longer appear in the UI.

      Steps to reproduce:

      1. Setup a Confluence under version 5.2.3.
      2. Make sure to use the "SQL_Latin1_General_CP1_CI_AS" collation instead of "SQL_Latin1_General_CP1_CS_AS".
      3. Once this Confluence is up and running, add a few pages and create a few users.
      4. Watch the pages with those new users.
        At this point everything will work fine.
      5. Upgrade Confluence to Confluence 5.4.3 (or latest).
      6. Log on with one of the created users in step 3.
      7. If you go to a page you watched you will see that your user is no longer watching that page.

      Diagnostic

      Run the below query against your Confluence MS SQL server database:

      SELECT object_name(object_id) as TableName, name as ColumnName, collation_name
      FROM sys.columns
      WHERE collation_name <> 'SQL_Latin1_General_CP1_CS_AS'
      AND object_name(object_id) NOT LIKE 'sys%' 
      AND object_name(object_id) NOT LIKE 'queue%'
      AND object_name(object_id) NOT LIKE 'file%'
      

      If the above return any results means your database is using the wrong collation.

      Notes

      If the users re add themselves as watchers the function will work normally.
      In the UI the user is no longer watching a page after the upgrade, however we are still able to see data in the notifications table:
      Before Upgrading:

      After Upgrading:

      Workaround

      1. Restore your pre-upgrade backups
      2. Fix the collation in the database by following the steps here
      3. While you're there, ensure everything else in this document is set correctly
      4. Try the upgrade again

      NB: Once the upgrade is done the watches will be lost and there is no way to retrieve them. You must restore your backups from before the upgrade and start the process again.

        1. afterUp.PNG
          13 kB
          Deividi Luvison
        2. afterUpUI.PNG
          321 kB
          Deividi Luvison
        3. atlassian-confluence-upgrade.log
          1.52 MB
          Deividi Luvison
        4. beforeUp.PNG
          10 kB
          Deividi Luvison
        5. beforeUpUI.PNG
          177 kB
          Deividi Luvison
        6. Change SQL Server Collation failed.png
          84 kB
          Hoang Nguyen

            [CONFSERVER-32992] Upgrading Confluence with a wrong collation on Microsoft SQL causes watches to be lost in all pages.

            Katherine Yabut made changes -
            Workflow Original: JAC Bug Workflow v3 [ 2896840 ] New: CONFSERVER Bug Workflow v4 [ 2990048 ]
            Owen made changes -
            Workflow Original: JAC Bug Workflow v2 [ 2788333 ] New: JAC Bug Workflow v3 [ 2896840 ]
            Status Original: Resolved [ 5 ] New: Closed [ 6 ]
            Owen made changes -
            Workflow Original: JAC Bug Workflow [ 2736029 ] New: JAC Bug Workflow v2 [ 2788333 ]
            Owen made changes -
            Workflow Original: Confluence Workflow - Public Facing - Restricted v5 - TEMP [ 2395079 ] New: JAC Bug Workflow [ 2736029 ]
            Alex Yakovlev (Inactive) made changes -
            Labels Original: affects-server bugfix database/hibernate loyalty New: affects-server database/hibernate loyalty
            Alex Yakovlev (Inactive) made changes -
            Labels Original: affects-server bugfix database/hibernate New: affects-server bugfix database/hibernate loyalty
            Katherine Yabut made changes -
            Workflow Original: Confluence Workflow - Public Facing - Restricted v5 [ 2290822 ] New: Confluence Workflow - Public Facing - Restricted v5 - TEMP [ 2395079 ]
            Katherine Yabut made changes -
            Workflow Original: Confluence Workflow - Public Facing - Restricted v5.1 - TEMP [ 2228779 ] New: Confluence Workflow - Public Facing - Restricted v5 [ 2290822 ]
            Katherine Yabut made changes -
            Workflow Original: Confluence Workflow - Public Facing - Restricted v5 - TEMP [ 2185162 ] New: Confluence Workflow - Public Facing - Restricted v5.1 - TEMP [ 2228779 ]
            Katherine Yabut made changes -
            Workflow Original: Confluence Workflow - Public Facing - Restricted v5 [ 1918122 ] New: Confluence Workflow - Public Facing - Restricted v5 - TEMP [ 2185162 ]

              honguyen Hoang Nguyen (Inactive)
              dluvison Deividi Luvison (Inactive)
              Affected customers:
              7 This affects my team
              Watchers:
              9 Start watching this issue

                Created:
                Updated:
                Resolved: