Can't start or upgrade Confluence due to an error with MS SQL Server database collation
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.
- Follow the steps on the "How to fix the collation of a MSSQL 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 this article. 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.