Can't start or upgrade Confluence due to an error with MS SQL Server database collation

Still need help?

The Atlassian Community is here for you.

Ask the community

Problem

The following error appears when starting up Confluence:

Database collation is incorrect, it must be set to 'SQL_Latin1_General_CP1_CS_AS'.

Diagnosis

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%'
AND object_name(object_id) NOT LIKE 'spt%'
AND object_name(object_id) NOT LIKE 'MSrep%'

If the above returns any of the Confluence tables it means your database is using the wrong collation.

Cause

As of version 5.5.3, Confluence requires that collation be set to SQL_Latin1_General_CP1_CS_AS

Resolution

If you are performing a new Confluence install:

If you are installing Confluence on a fresh database then the solution is to simply drop the database and recreate it with the correct collation setting: SQL_Latin1_General_CP1_CS_AS. Instructions on how to setup a MS SQL database for Confluence can be found here. Details about how to set the correct collation are described at Step 3, item 4. Once this is done, you can re-perform the installation, which should now be successful.

If you are upgrading Confluence:

There are several solutions:

Make sure to backup your Confluence database before trying any of those methods.

  1. Follow the steps on the "How to fix the collation of a MSSQL Confluence Database"
  2. Export an xml backup and import it into a new instance of the same version of Confluence, which has the correct collation settings as described in this article. This solution may not work for larger instances.
  3. Use the Collation Changer Tool. Note that this is a third party tool and not supported by Atlassian. Use at your own risk.
  4. Your DB admin may be able to fix your database using a data migration tool.
  5. Contact an Atlassian Expert who can give you hands on assistance.

 

 

Last modified on Nov 1, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.