Jira 7.8.0 reports error regarding collation

Question

Hi All, just switched from H2 to MS-SQLSRV 2014 After importing my H2 XML Backup i received a message from the System tool that someting is wrong with my database collation setting The message is >> The table collation: 'Latin1_General_CI_AS' is unsupported by JIRA. The Database collation: 'Latin1_General_CI_AI' is supported by JIRA. << Now guess what my jiradb has set as collation - yes - Latin1_General_CI_AI - i searched around a found that there was once a bug <> and that should be fixed in 7.8.0 - so whats wrong ? I must say that the Server wide collation is Latin1_General_CI_AS and my jiradb is the only one thats using Latin1_General_CI_AI Regards Manfred

Solomon Rutzky · Answer

Hi there. Most likely you never needed to change the collation of the Jira DB, and could have kept it using Latin1_General_CI_AS. And in fact, by having a different collations between the instance-level and Jira DB, there is now the potential for a collation error coming from temp table usage. Please see my answer to a related question about Jira and SQL Server collations for details.

Regarding why the "unsupported collation" message did not go away after changing your Jira DB's collation:

Changing a SQL Server database's collation (via ALTER DATABASE [db_name] COLLATE new_collation; , which is also what SSMS submits, if you use the GUI to do this) changes only the default collation at the database-level. And that affects only the collation of string literals, variables (including parameters), and new / altered columns that don't specify the COLLATE clause. Meaning: the collation of all existing columns in all user tables will not be changed. For your situation, yes, it probably made the most sense to drop and recreate the DB with the desired collation. For an in-depth look at changing collations, please see my post: Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

Take care, Solomon...

Manfred_Csikos · Answer

I have to add some new Information

Initially i did not choose one of the supported collations - i changed the collation with the MS SQLSRV Management Studio to Latin1_general_CI_AI

Nevertheless jira always showd that collation message

The only thing that helped was to delete the database and creating it using the supported collation

case solved - closed

Regards

Manfred