Teamcity and SQL server - Collation conflict
I have an issue with Teamcity and SQL server.
When I installed the SQL server I used the default collation type which is SQL_Latin1_General_CP1_CI_AS And for TeamCity DB it has to be Latin1_General_CS_AS according to their document, so for that, I created a DB called "TeamCity" and set the collation to Latin1_General_CS_AS. and while installing TeamCity and setting up the external database I connected it to that "TeamCity" DB that I created.
For the Teamcity project, I added only one Build Step (Just a simple step to get me started with TC): Runner type: Visual Studio (sln) & Targets: Clean, Build
And now when I try to run the project with Teamcity I get this error:
Unexpected exception: SQL error when doing 'Executing with parameters: [1585662168832] caused SQL error: ' while performing SQL query: select r.test_name_id, r.success_count, r.failure_count, r.last_failure_time, r.build_type_id from test_failure_rate r join #TT{build_type$} tmp on r.build_type_id = tmp.build_type_id where (r.last_failure_time is null or r.last_failure_time > ?) order by r.test_name_id: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CS_AS" in the equal to operation.
Does the collation type for the server effect TeamCity DB regardless of what collation the DB is set to? and how can I fix this?
Please sign in to leave a comment.
It appears that TEMPDB on your MS SQL Server is using server default collation which results in a conflict. You could check that with the following query:
SELECT name, COLLATION_NAME FROM sys.databases
TeamCity does not use COLLATE keyword to specify a collation for the temporary table, instead relying on the server configuration. In order for us to better reflect when there could be a collation conflict, I have created this feature request: https://youtrack.jetbrains.com/issue/TW-65308
If you would like to amend default collation for the system databases, including TEMPDB, you could follow this MSDN instruction:
https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-server-collation?redirectedfrom=MSDN&view=sql-server-ver15
Yes this is exactly the issue ' TEMPDB on your MS SQL Server is using server default collation' I solved it for now by installing a new server with Collation: Latin1_General_CS_AS and changed the server/database settings for Teamcity by setting the new server name and database in <TeamCity Data Directory>/config/database.properties and restoring the old DB into the new one that belongs to the new server.
Hmm, same problem here.
I noticed that you already left a comment in the issue. Please vote for it for visibility. Thank you!
Best regards,
Anton
Done, thanks.
By the way, if the Collation for the TeamCity database does not match the one the tempdb has in MS SQL then any of TeamCity functionalities will not work?
So far after the migration everything seems to be working. The only error and SQL query that gets logged is:
ERROR - $Lambda$438/0x0000000100190840 - Failed to execute Custom Data Storage Manager SyncTask. jetbrains.buildServer.serverSide.db.UnexpectedDBException: Unexpected exception: SQL error when doing 'Querying tuples' while performing SQL query: SQL SELECT: select dd.data_domain, dd.data_key from custom_data dd join "#custom_data_domain" tmpd on tmpd.data_domain = dd.data_domain: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "Polish_CS_AS" and "Polish_CI_AS" in the equal to operation.I would like to recommend changing the default MS SQL collation in the production environment, as it may lead to unexpected consequences, as the TeamCity was tested to work only with the case-sensitive collation (with the collation name ending with _CS_AS) and not others. While it may seem that it is working now, there may be some cases where collation conflict will have a negative effect.
However, I would also like to point out that while we have a small number of issues reported by users related to the collation conflict, they are for the very old versions of TeamCity (and as the default collation for MS SQL is _CI_AS, we would see much more of them in case something would not be working due to the different collations). Also, the requirement is for the TeamCity database to have a _CS_AS collation, not an MS SQL server.
At the very least, if you can't for any reason change the MS SQL server collation now, please keep the collation conflict in mind in case you encounter any issues in the future.
Best regards,
Anton