Collation Conflict During Page Creation When Using MS SQL Server Database
- After an upgrade or database migration, when creating a page and clicking the Save button, the page is lost.
- Another symptom might be that you're able to create a new page, but cannot edit and save existing pages.
- The database in use is Microsoft SQL Server.
The following appears in the
2012-05-01 19:53:49,934 ERROR [TP-Processor5] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP850_BIN" in the equal to operation. -- referer: http://localhost:8080/pages/docreatepage.action | url: /pages/docreatepage.action | userName: admin | action: docreatepage
Run the following query against your old database. If it returns any results, you must adjust the collation before upgrading to newer versions of Confluence.
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%'
This issue is caused by a conflict between the collation of some table columns.
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.