Database Connection in SQL Server 2017 (Database Collation)

[Cormant Systems Administrator]

JIRA Old Version 7.11.2  - SQL Server 2012

Upgrade JIRA version to 8.8.1  SQL Server 2017

Database Collation.

The table collation: 'Latin1_General_CI_AS' is unsupported by Jira. The Database collation: 'Latin1_General_CI_AI' is supported by Jira

We do some action base on the solution below. 

How to Verify Table Collations

SELECT DISTINCT C.collation_name 
FROM   sys.tables AS T 
       INNER JOIN sys.columns C 
               ON T.object_id = C.object_id 
WHERE  collation_name IS NOT NULL; 

How to Verify Database Collations

SELECT DISTINCT C.collation_name 
FROM   sys.tables AS T 
       INNER JOIN sys.columns C 
               ON T.object_id = C.object_id 
WHERE  collation_name IS NOT NULL; 

[Earl McCutcheon]

Hello @Cormant Systems Administrator ,

Check out the following Bug that is most likely the cause here:

• https://jira.atlassian.com/browse/JRASERVER-65634

The main takeaway is that Microsoft SQL Server will use the Servers instance-level collation rather than the Database level collation when creating temporary tables, and in doing so will set the incorrect collation on some of the tables, when the server and the database have mismatched collations.

to get around this the workaround is:

Workaround

Change the instance-level collation to match the Jira database collation toSQL_Latin1_General_CP437_CI_AIorLatin1_General_CI_AS. You can use this guide to do so:Set or Change the Server Collation.

Regards,
Earl

[Cormant Systems Administrator]

Dear Earl,

I already try this method.

ALTER DATABASE [database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 

GO 

ALTER DATABASE [database] COLLATE Latin1_General_CI_AS; 

GO 

ALTER DATABASE [database] SET MULTI_USER; 

GO 

I also perform ALTER DATABASE to SQL_Latin1_General_CI_AI but result are the same.

[Earl McCutcheon]

Hello @Cormant Systems Administrator ,

The commands you posted are for altering the Jira database level collation as covered in:

• https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql?view=sql-server-ver15

The workaround is noting changing the Instance level Collation via the steps in:

• Set or Change the Server Collation.

Changing the Server Collation

Changing the default collation for an instance of SQL Server can be a complex operation and involves the following steps:

• Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.

• Export all your data using a tool such as the bcp Utility. For more information, see Bulk Import and Export of Data (SQL Server).

• Drop all the user databases.

• Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:

  Copy

  Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName   
  /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]   
  /SQLCOLLATION=CollationName  
  

  For more information, see Rebuild System Databases.

• Create all the databases and all the objects in them.

• Import all your data.

Regards,
Earl

[Serge Mbitom]

Why can't JIRA specify the collation when creating the table ? 

Change the collation at the instance level is simply not an option. Not everyone has the JIRA database living alone on a dedicated server ...

[Earl McCutcheon]

Hello @Serge Mbitom ,

It's a limitation in how MSSQL is implemented via the reference objects via the transitional SQL being designed for interaction with other Microsoft products but disregards applications designed around multi platform support (i.e. Jira is designed to also work on PSQL and MySQL).  If the specified collation or the collation used by the referenced object uses a code page that is not supported.  and in some cases that can be an entirely incorrect/bad way to do things for compatibility for multiplatform apps, so Jira is working around this MSSQL limitation using the most common collation standard.

The requirement is that either:

1. the collation is the same for string columns being joined, or used in concatenation / UNION, etc, or
2. you specify the COLLATE clause in the predicate or expression to override one or more of the columns.

So, if you know that your column has a collation of Z and your database has a default collation of Y, then create the temp table specifying COLLATE Z for that column. Then table variables use the database's default collation, not the instance's collation. And, contained databases are yet another set of rules.

Hope this helps clarify thing a bit more.

Regards,
Earl