What to do when we encounter the error “Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "Latin1_General_CS_AI" in the equal to operation.” at the time of startup after performing the upgrade?
In an ideal scenario, the Artifactory upgrade would proceed successfully when following the standard upgrade procedures. However, when there is an issue related to the collation settings on the database. It is crucial for the collation defined on the database to align with the one specified by Artifactory in order to create tables and columns accurately.
The error message indicates that, Artifactory needs to use “Latin1_General_CS_AI” and the database is set with a different collation “Latin1_General_CI_AI”
Reference: https://jfrog.com/help/r/jfrog-installation-setup-documentation/microsoft-sql-server
It's important to note that Artifactory lacks the capability to modify the database collation settings directly on the database. Instead, it utilizes the existing collation definition.
Hence, the specific error would be observed if the collation is altered manually at the database level and after performing the upgrade on Artifactory, it fails to start due to this mismatch and unable to perform alterations on the database.
How to overcome the issue?
It is recommended to take a database backup, prior initiating an upgrade of Artifactory. If you have encountered this error, firstly, confirm the collation set on the database
Query 1:
USE YOURDATABSENAME; SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME <> 'sysdiagrams';
Query 2:
USE YourDatabaseName; SELECT t.name AS 'Table Name', c.name AS 'Column Name', col.collation_name AS 'Collation' FROM YourDatabaseName.sys.tables t INNER JOIN YourDatabaseName.sys.columns c ON t.object_id = c.object_id LEFT JOIN YourDatabaseName.sys.columns col ON col.object_id = c.object_id AND col.column_id = c.column_id WHERE t.type = 'U' ORDER BY t.name, c.column_id;
Note: Please make sure to replace "YourDatabaseName" with the actual name of the Artifactory database
The above queries should help in identifying the exact collations set for the database and the tables. If it is set with a different collation, it is recommended to follow the below steps to overcome this problem.
Perform a restore of the database to the older state before initiating the Artifactory upgrade.
1. Once after the Artifactory is started, please take a full system export from the current instance and make sure to select only the "Exclude Content" option.
2. Install a new Artifactory with a new database and make sure to have the collation set with Latin1_General_CS_AI.
3. Configure the Filestore accordingly on the new Artifactory instance.
4. Perform a full system import on the new instance using the exported content and this will help to update all the existing database records to the newly launched instances' DB.