Can't start or upgrade Confluence due to an error with MS SQL Server database collation
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Problem
The following error appears when starting up Confluence:
1
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:
1
2
3
4
5
6
7
8
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.
Solution
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 Database Setup for SQL Server. 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.
Follow the steps on the "How to fix the collation of a Microsoft SQL Server Confluence database"
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 Database Setup for SQL Server. This solution may not work for larger instances.
Use the Collation Changer Tool. Note that this is a third party tool and not supported by Atlassian. Use at your own risk.
Your DB admin may be able to fix your database using a data migration tool.
Contact an Atlassian Expert who can give you hands on assistance.
Was this helpful?