Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-65634

Collation error in Jira when temporary tables are used and the database and server/default collation do not match in SQL Server

    • 7.03
    • 117
    • Severity 2 - Major
    • 5
    • Hide
      Atlassian Update – 24 May 2023

      Hi everyone,

      We’ve investigated the bug and decided to lower its priority.

      Reason:

      After investigating the reported cases we could conclude that the majority of them are actually not about this particular bug but rather about when two or more tables in the Jira database have mismatching collations, which is an accurately reported database configuration error that should be corrected. This bug only applies when temporary tables are used with the Microsoft SQL Server engine and the database default collation is different from the Jira database's own collation.

      Next steps:

      If you come across this error, first check if this bug applies or if there is a configuration error.

      This bug applies if the database engine used is Microsoft SQL Server and the error message contains a reference to a temp table, recognisable by the table name beginning with a hash (#) sign. Like ( select item from #temp1 ) in this example:

      2017-06-20 18:10:46,004 https-jsse-nio-443-exec-12 ERROR User.Name 1090x309x1 15yequj 10.7.35.65 /rest/projecttemplates/1.0/create-templates/item/35 [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT user_name FROM dbo.cwd_user WHERE (lower_user_name IN ( select item from #temp1 ) ) AND (directory_id = ? ) ORDER BY lower_user_name (Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP437_CI_AI" in the equal to operation.)
      

      If you discover that this bug is having any direct functional impact on your Jira, kindly contact support and provide a more detailed description of the issue. Doing so will enable us to investigate and reevaluate its priority.

      If you do not find a reference to a temp table in the error, then we suggest to review that your database collation settings are set up correctly as described for your particular database engine on the page Connecting Jira applications to a database or contact support to identify the root cause in your particular case.

      Best regards
      Magnus Karlsson
      Jira Developer

      Show
      Atlassian Update – 24 May 2023 Hi everyone, We’ve investigated the bug and decided to lower its priority. Reason: After investigating the reported cases we could conclude that the majority of them are actually not about this particular bug but rather about when two or more tables in the Jira database have mismatching collations, which is an accurately reported database configuration error that should be corrected. This bug only applies when temporary tables are used with the Microsoft SQL Server engine and the database default collation is different from the Jira database's own collation. Next steps: If you come across this error, first check if this bug applies or if there is a configuration error. This bug applies if the database engine used is Microsoft SQL Server and the error message contains a reference to a temp table, recognisable by the table name beginning with a hash (#) sign. Like ( select item from #temp1 ) in this example: 2017-06-20 18:10:46,004 https-jsse-nio-443-exec-12 ERROR User.Name 1090x309x1 15yequj 10.7.35.65 /rest/projecttemplates/1.0/create-templates/item/35 [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT user_name FROM dbo.cwd_user WHERE (lower_user_name IN ( select item from #temp1 ) ) AND (directory_id = ? ) ORDER BY lower_user_name (Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP437_CI_AI" in the equal to operation.) If you discover that this bug is having any direct functional impact on your Jira, kindly contact support and provide a more detailed description of the issue. Doing so will enable us to investigate and reevaluate its priority. If you do not find a reference to a temp table in the error, then we suggest to review that your database collation settings are set up correctly as described for your particular database engine on the page Connecting Jira applications to a database or contact support to identify the root cause in your particular case. Best regards Magnus Karlsson Jira Developer

      Summary

      Jira uses temp tables to perform some of its functions. When creating temp tables, SQL server uses the instance-level collation, and Jira currently does not specify collation on table creation. If the instance-level collation is different from the database-level collation, Jira and temp table collation will not match.

      Steps to Reproduce

      1. Install SQL Server with a collation that is not supported (SQL_Latin1_General_CP437_CI_AI and Latin1_General_CI_AI are supported)
      2. Create a database with the correct collation (e.g. SQL_Latin1_General_CP437_CI_AI)
      3. Error shows in logs in reference to temp tables.

      Expected Results

      Should report collation is correct "SQL_Latin1_General_CP437_CI_AI"

      Actual Results

      The below exception is thrown in the atlassian-jira.log file:

      2017-06-20 18:10:46,004 https-jsse-nio-443-exec-12 ERROR Scott.Lepech 1090x309x1 15yequj 10.7.35.65 /rest/projecttemplates/1.0/create-templates/item/35 [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT user_name FROM dbo.cwd_user WHERE (lower_user_name IN ( select item from #temp1 ) ) AND (directory_id = ? ) ORDER BY lower_user_name (Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP437_CI_AI" in the equal to operation.)
      com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT user_name FROM dbo.cwd_user WHERE (lower_user_name IN ( select item from #temp1 ) ) AND (directory_id = ? ) ORDER BY lower_user_name (Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP437_CI_AI" in the equal to operation.)
      

      Workaround

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

            [JRASERVER-65634] Collation error in Jira when temporary tables are used and the database and server/default collation do not match in SQL Server

            Atlassian Update – 24 May 2023

            Hi everyone,

            We’ve investigated the bug and decided to lower its priority.

            Reason:

            After investigating the reported cases we could conclude that the majority of them are actually not about this particular bug but rather about when two or more tables in the Jira database have mismatching collations, which is an accurately reported database configuration error that should be corrected. This bug only applies when temporary tables are used with the Microsoft SQL Server engine and the database default collation is different from the Jira database's own collation.

            Next steps:

            If you come across this error, first check if this bug applies or if there is a configuration error.

            This bug applies if the database engine used is Microsoft SQL Server and the error message contains a reference to a temp table, recognisable by the table name beginning with a hash (#) sign. Like ( select item from #temp1 ) in this example:

            2017-06-20 18:10:46,004 https-jsse-nio-443-exec-12 ERROR User.Name 1090x309x1 15yequj 10.7.35.65 /rest/projecttemplates/1.0/create-templates/item/35 [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT user_name FROM dbo.cwd_user WHERE (lower_user_name IN ( select item from #temp1 ) ) AND (directory_id = ? ) ORDER BY lower_user_name (Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP437_CI_AI" in the equal to operation.)
            

            If you discover that this bug is having any direct functional impact on your Jira, kindly contact support and provide a more detailed description of the issue. Doing so will enable us to investigate and reevaluate its priority.

            If you do not find a reference to a temp table in the error, then we suggest to review that your database collation settings are set up correctly as described for your particular database engine on the page Connecting Jira applications to a database or contact support to identify the root cause in your particular case.

            Best regards
            Magnus Karlsson
            Jira Developer

            Magnus Karlsson (Inactive) added a comment - Atlassian Update – 24 May 2023 Hi everyone, We’ve investigated the bug and decided to lower its priority. Reason: After investigating the reported cases we could conclude that the majority of them are actually not about this particular bug but rather about when two or more tables in the Jira database have mismatching collations, which is an accurately reported database configuration error that should be corrected. This bug only applies when temporary tables are used with the Microsoft SQL Server engine and the database default collation is different from the Jira database's own collation. Next steps: If you come across this error, first check if this bug applies or if there is a configuration error. This bug applies if the database engine used is Microsoft SQL Server and the error message contains a reference to a temp table, recognisable by the table name beginning with a hash (#) sign. Like ( select item from #temp1 ) in this example: 2017-06-20 18:10:46,004 https-jsse-nio-443-exec-12 ERROR User.Name 1090x309x1 15yequj 10.7.35.65 /rest/projecttemplates/1.0/create-templates/item/35 [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT user_name FROM dbo.cwd_user WHERE (lower_user_name IN ( select item from #temp1 ) ) AND (directory_id = ? ) ORDER BY lower_user_name (Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP437_CI_AI" in the equal to operation.) If you discover that this bug is having any direct functional impact on your Jira, kindly contact support and provide a more detailed description of the issue. Doing so will enable us to investigate and reevaluate its priority. If you do not find a reference to a temp table in the error, then we suggest to review that your database collation settings are set up correctly as described for your particular database engine on the page Connecting Jira applications to a database or contact support to identify the root cause in your particular case. Best regards Magnus Karlsson Jira Developer

            Hi all,

            Similar issue that i am also facing from long time.

            Error message is: The table collation: 'Latin1_General_CS_AS' is unsupported by Jira. The Database collation: 'SQL_Latin1_General_CP437_CI_AI' is supported by Jira.

            Ours is Jira server, last time a year back we had done Jira upgration, so this issue could be cuasing after it. Now we need to upgrade Jira again but "Health ceck gives this error".

             

            From SQL team point of view the collation was changed successfully , deleted the table with wrong collation after creating the new database. 

             

            Please folks, help to get rid of this error. 

             

             

            Thanks a ton for fast replies,

            Dev G

             

             

            Devendar Gangapuram added a comment - Hi all, Similar issue that i am also facing from long time. Error message is: The table collation: 'Latin1_General_CS_AS' is unsupported by Jira. The Database collation: 'SQL_Latin1_General_CP437_CI_AI' is supported by Jira. Ours is Jira server, last time a year back we had done Jira upgration, so this issue could be cuasing after it. Now we need to upgrade Jira again but "Health ceck gives this error".   From SQL team point of view the collation was changed successfully , deleted the table with wrong collation after creating the new database.    Please folks, help to get rid of this error.      Thanks a ton for fast replies, Dev G    

            We are facing this issue on Jira 8.7.1 as well. 

            Andreas Morgner | DEMICON added a comment - We are facing this issue on Jira 8.7.1 as well. 

            We experience this every time we upgrade where the collation is reverted back.

            Kevin Dalton added a comment - We experience this every time we upgrade where the collation is reverted back.

            How about to add the information about this issue in related documents?
            Unfortunatelly customers won't awake to this issue before running their JIRA instances and to change the instance level collation after long time will be pretty demanding.

            KURIMOTO Eiichi[Ricksoft] added a comment - How about to add the information about this issue in related documents? Unfortunatelly customers won't awake to this issue before running their JIRA instances and to change the instance level collation after long time will be pretty demanding. https://confluence.atlassian.com/display/ADMINJIRASERVER/Connecting+Jira+applications+to+SQL+Server+2012 https://confluence.atlassian.com/display/ADMINJIRASERVER/Connecting+Jira+applications+to+SQL+Server+2014 https://confluence.atlassian.com/display/ADMINJIRASERVER/Connecting+Jira+applications+to+SQL+Server+2016

            Hello again. Adding support to my suggestion of changing your approach to "supported" collations:

            1. I just noticed that one of the two "supported" collations noted in the description is, in fact, not a supported collation. Latin1_General_CI_AS is not a supported collation. The actual supported collation is: Latin1_General_CI_AI. This is based upon the warning message reported by Jira as well as what people have been trying to use based upon Atlassian Community questions.
            2. I have encountered several cases where people are changing (or attempting to change) their Jira DB collation so that it is one of the two supported collations, yet will be then become different than the instance-level collation, leaving them now open to running into the error that is the subject of this ticket. That's a lot of work to do for possibly no reason, in the first place, but then to find out that all of that work really only served to break, not fix, Jira? Here are the cases:
              1. on DBA.StackExchange:
                1. Process to change collation on a database
              2. on Atlassian Community:
                1. Jira 7.8.0 reports error regarding collation
                2. Using Azure SQL for Jira server 7.13 - Collation issue
                3. Will appreciate how best to fix DB in wrong collation
                4. others...
            3. The warning message about having an unsupported collation contains a technical flaw: one of the two collations it says are supported doesn't exist  (emphasis mine):

              You are using an unsupported mssql collation: SQL_Latin1_General_CP1_CI_AS. This may cause some functionality to not work.Please use SQL_Latin1_General_CP437_CI_AI or SQL_Latin1_General_CI_AI as the collation instead.

              The second supported collation, SQL_Latin1_General_CI_AI, is not a collation. There is an extraneous "SQL_" added to the front of that collation name.

             

             

            Solomon Rutzky added a comment - Hello again. Adding support to my suggestion of changing your approach to "supported" collations: I just noticed that one of the two "supported" collations noted in the description is, in fact, not a supported collation.  Latin1_General_CI_AS is not a supported collation. The actual supported collation is:  Latin1_General_CI_AI . This is based upon the warning message reported by Jira as well as what people have been trying to use based upon Atlassian Community questions. I have encountered several cases where people are changing (or attempting to change) their Jira DB collation so that it is one of the two supported collations, yet will be then become different than the instance-level collation, leaving them now open to running into the error that is the subject of this ticket. That's a lot of work to do for possibly no reason, in the first place, but then to find out that all of that work really only served to break, not fix, Jira? Here are the cases: on DBA.StackExchange: Process to change collation on a database on Atlassian Community: Jira 7.8.0 reports error regarding collation Using Azure SQL for Jira server 7.13 - Collation issue Will appreciate how best to fix DB in wrong collation others... The warning message about having an unsupported collation contains a technical flaw: one of the two collations it says are supported doesn't exist  (emphasis mine): You are using an unsupported mssql collation: SQL_Latin1_General_CP1_CI_AS. This may cause some functionality to not work.Please use SQL_Latin1_General_CP437_CI_AI or SQL_Latin1_General_CI_AI as the collation instead. The second supported collation, SQL_Latin1_General_CI_AI , is not a collation. There is an extraneous " SQL_ " added to the front of that collation name.    

            Solomon Rutzky added a comment - - edited

            Hello @dponzio Thank you very much for making those edits. This definitely looks better .

            Regarding the workaround, the new one (i.e. changing the instance's default collation) is better than setting up a dedicated instance just for Jira. I mean, a dedicated instance is a possible option, but it needs to be cautioned with "check your licensing to ensure that this action does not incur additional costs". Or something to that effect. But, since you are suggesting to change the instance-level collation, I should mention that I have a rather detailed post about an undocumented short-cut approach to that:

            Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

             

            HOWEVER, it did occur to me that there might be a very simple workaround that doesn't require any changes to the instance (which always includes some amount of risk) by the user, nor any code changes to Jira by Atlassian. Given the rather large disparity between the two "supported" collations — SQL_Latin1_General_CP437_CI_AI and Latin1_General_CI_AS — in addition to the fact that this issue even exists in the first place, I am going to assume that the two supported collations were not selected for any specific technical reasons, but instead because they happened to be tested and seemed to work, and worked without the wonkiness that comes with case-sensitive and binary collations. The fact that the SQL Server collation, the one with CP437 in its name, is in this list leads me to believe that there were "issues" with importing data since Code Page 437 is the Windows OS default when the OS language is "US English". I suspect that nobody understood how to set the input file code page for SQLCMD.exe, BCP.exe or `BULK INSERT`, but setting the database-level collation to SQL_Latin1_General_CP437_CI_AI worked. Otherwise most people wouldn't go out of their way to specify a CP437 collation.

            I mention all of that background not as a put-down (collations and encodings, etc seem to be universal pain-points across platforms, RDBMSs, OSs, etc), but as necessary context to support my suggestion, which is:

             

            Get rid of this (more than likely unfounded) requirement of the database having a default collation of  SQL_Latin1_General_CP437_CI_AI or Latin1_General_CI_AS. 

             

            I mean, it is really this notion of "supported" collations that is getting people into trouble in the first place. And for what? There is very little consistency here:

            1. they use different code pages for VARCHAR data
            2. they have different accent-sensitivities for both VARCHAR and NVARCHAR data
            3. even if the accent-sensitivities were the same, they still use very different sorting / comparison rules for VARCHAR data

            So what rationale is behind these two particular collations? And why not variants of them such as SQL_Latin1_General_CP1_CI_AS, Latin1_General_100_CI_AS, or Latin1_General_100_CI_AS_SC (all variants of Latin1_General_CI_AS)? I use Latin1_General_100_CI_AS_SC for my Jira installation. I just ignored the "unsupported collation" warnings. Granted I am not a heavy user of Jira, so there could be some code paths that I haven't tested that might not behave as intended, but I highly doubt that given what the actual differences between the supported Latin1_General_CI_AS and unsupported Latin1_General_100_CI_AS_SC collations are.

             

            What this all boils down to is that the actual requirement here, collation-wise, by Jira should simply be:

            The database in which Jira is installed must have a case-insensitive collation that is the same as the instance-level collation.

             

            Done! Sure, that absolutely requires testing, but the only code change is changing the collation check, both the check itself and the message it displays. But it is easy enough to test for both of those conditions (i.e. database collation is case-insensitive, and database collation is the same as instance-level collation).

             

            I realize that seems like a rather large change, especially when the current situation just works and nobody understands this stuff well enough to not be afraid of making such a change. But unless someone has a specific reason for requiring those two "supported" collations, then this is the correct and best approach for both Jira and its users. And, in the off chance that someone does have a specific reason, I would love to hear what it is because most likely it is based on simply not understanding how this stuff works.

             

            Take care,
            Solomon...

             

            P.S. I am available for consulting  , whether to just answer questions, provide training on working with collations/encoding/etc in SQL Server, do a full system analysis to find other problem areas (e.g. the continued use of the NTEXT datatype that was deprecated in 2005 for good reason), review support tickets related to collations/encodings/working with strings to help identify which issues are code based, which are user-error, etc and how to solve them, etc.

             

            Solomon Rutzky added a comment - - edited Hello @dponzio Thank you very much for making those edits. This definitely looks better . Regarding the workaround, the new one (i.e. changing the instance's default collation) is better than setting up a dedicated instance just for Jira. I mean, a dedicated instance is a possible option, but it needs to be cautioned with "check your licensing to ensure that this action does not incur additional costs". Or something to that effect. But, since you are suggesting to change the instance-level collation, I should mention that I have a rather detailed post about an undocumented short-cut approach to that: Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?   HOWEVER , it did occur to me that there might be a very simple workaround that doesn't require any changes to the instance (which always includes some amount of risk) by the user, nor any code changes to Jira by Atlassian. Given the rather large disparity between the two "supported" collations —  SQL_Latin1_General_CP437_CI_AI  and  Latin1_General_CI_AS  — in addition to the fact that this issue even exists in the first place, I am going to assume that the two supported collations were not selected for any specific technical reasons, but instead because they happened to be tested and seemed to work, and worked without the wonkiness that comes with case-sensitive and binary collations. The fact that the SQL Server collation, the one with CP437 in its name, is in this list leads me to believe that there were "issues" with importing data since Code Page 437 is the Windows OS default when the OS language is "US English". I suspect that nobody understood how to set the input file code page for SQLCMD.exe , BCP.exe or ` BULK INSERT `, but setting the database-level collation to  SQL_Latin1_General_CP437_CI_AI worked. Otherwise most people wouldn't go out of their way to specify a CP437 collation. I mention all of that background not as a put-down (collations and encodings, etc seem to be universal pain-points across platforms, RDBMSs, OSs, etc), but as necessary context to support my suggestion, which is:   Get rid of this (more than likely unfounded) requirement of the database having a default collation of   SQL_Latin1_General_CP437_CI_AI or  Latin1_General_CI_AS .     I mean, it is really this notion of "supported" collations that is getting people into trouble in the first place. And for what? There is very little consistency here: they use different code pages for VARCHAR data they have different accent-sensitivities for both VARCHAR and NVARCHAR data even if the accent-sensitivities were the same, they still use very different sorting / comparison rules for VARCHAR data So what rationale is behind these two particular collations? And why not variants of them such as SQL_Latin1_General_CP1_CI_AS , Latin1_General_100_CI_AS , or Latin1_General_100_CI_AS_SC (all variants of Latin1_General_CI_AS )? I use Latin1_General_100_CI_AS_SC for my Jira installation. I just ignored the "unsupported collation" warnings. Granted I am not a heavy user of Jira, so there could be some code paths that I haven't tested that might not behave as intended, but I highly doubt that given what the actual differences between the supported  Latin1_General_CI_AS and unsupported Latin1_General_100_CI_AS_SC  collations are.   What this all boils down to is that the actual requirement here, collation-wise, by Jira should simply be: The database in which Jira is installed must have a case-insensitive collation that is the same as the instance-level collation.   Done! Sure, that absolutely requires testing, but the only code change is changing the collation check, both the check itself and the message it displays. But it is easy enough to test for both of those conditions (i.e. database collation is case-insensitive, and database collation is the same as instance-level collation).   I realize that seems like a rather large change, especially when the current situation just works and nobody understands this stuff well enough to not be afraid of making such a change. But unless someone has a specific reason for requiring those two "supported" collations, then this is the correct and best approach for both Jira and its users. And, in the off chance that someone does have a specific reason, I would love to hear what it is because most likely it is based on simply not understanding how this stuff works.   Take care, Solomon...   P.S. I am available for consulting  , whether to just answer questions, provide training on working with collations/encoding/etc in SQL Server, do a full system analysis to find other problem areas (e.g. the continued use of the NTEXT datatype that was deprecated in 2005 for good reason), review support tickets related to collations/encodings/working with strings to help identify which issues are code based, which are user-error, etc and how to solve them, etc.  

            srutzky I've confirmed the information you've provided and reworded parts of this ticket so that it reflects the issue properly as you mentioned. The workaround added is not ideal, but it is the only workaround we can currently recommend. In order to fix the issue and specify collation, our development team would need to change the code. I hope this issue looks better now, but let us know if you see any other issues. Thank you so much for your feedback!

            Daniel Ponzio,
            JAC Champion

            Daniel Ponzio added a comment - srutzky I've confirmed the information you've provided and reworded parts of this ticket so that it reflects the issue properly as you mentioned. The workaround added is not ideal, but it is the only workaround we can currently recommend. In order to fix the issue and specify collation, our development team would need to change the code. I hope this issue looks better now, but let us know if you see any other issues. Thank you so much for your feedback! Daniel Ponzio, JAC Champion

            OR,

            if this error is isolated to only 1 or a few queries, then another potential (and easy / low-risk) fix would be to add COLLATE DATABASE_DEFAULT to the expression causing the error. For example, the user reported the following query:
             

            SELECT user_name
            FROM dbo.cwd_user
            WHERE (lower_user_name IN ( select item from #temp1 ) )
            AND (directory_id = ? )
            ORDER BY lower_user_name

            The collation conflict is between the "lower_user_name" column in "dbo.cwd_user" (in the Jira database) and the "item" column in "#temp1" (in tempdb). The fix here is simply to add COLLATE DATABASE_DEFAULT after the column name "item", so the query would now look like:

            SELECT user_name
            FROM dbo.cwd_user
            WHERE (lower_user_name IN ( select item COLLATE DATABASE_DEFAULT from #temp1 ) )
            AND (directory_id = ? )
            ORDER BY lower_user_name

             
            Take care, Solomon...

             

            Solomon Rutzky added a comment - OR, if this error is isolated to only 1 or a few queries, then another potential (and easy / low-risk) fix would be to add COLLATE DATABASE_DEFAULT to the expression causing the error. For example, the user reported the following query:   SELECT user_name FROM dbo.cwd_user WHERE (lower_user_name IN ( select item from #temp1 ) ) AND (directory_id = ? ) ORDER BY lower_user_name The collation conflict is between the " lower_user_name " column in " dbo.cwd_user " (in the Jira database) and the " item " column in " #temp1 " (in tempdb ). The fix here is simply to add COLLATE DATABASE_DEFAULT after the column name "item", so the query would now look like: SELECT user_name FROM dbo.cwd_user WHERE (lower_user_name IN ( select item COLLATE DATABASE_DEFAULT from #temp1 ) ) AND (directory_id = ? ) ORDER BY lower_user_name   Take care, Solomon...  

            Solomon Rutzky added a comment - - edited

            The changes made 3 hours ago to this ticket's Description by Taiwo Akindele are wrong and will not assist in truly resolving this issue. The issues are:

            1. The Title is incorrect: this issue has nothing to do with being on a shared server. Being on a shared server has no bearing on the problem or the solution. That part needs to be removed as it is misleading. The problem is simply that the instance-level collation is not the same as the default collation used in the database that Jira is using. The instance-level collation is what tempdb uses, and by default, the collation used for string columns in temporary tables is the collation used by tempdb.
            2. In the Description:
            3. Under "Summary": this issue has nothing to do with being on a shared server. Being on a shared server has no bearing on the problem or the solution. That part needs to be removed as it is misleading. The problem is simply that the instance-level collation is not the same as the default collation used in the database that Jira is using. The instance-level collation is what tempdb uses, and by default, the collation used for string columns in temporary tables is the collation used by tempdb.
            4. Under "Steps to Reproduce":
              1. The version of SQL Server does not matter. This issue is the same across all versions.
              2. You don't need to use Latin1_General_CI_AS. The error message itself, under "Actual Results", shows that the instance-level (and hence, tempdb's) collation is SQL_Latin1_General_CP1_CI_AS. But practically speaking, any collation that is not the collation of the DB where Jira is installed will have the same effect, with the only difference being the name of the collation that is in conflict, as reported in the error message.
            5. Under "Notes": No, please don't reference that MSSQLTips article. That pertains to a different scenario for collation errors in temp tables. The situation faced by Jira is much less complicated than that.
            6. Under "Workaround": NO, NO, NO. Do not set up a dedicated instance just for Jira. That is absurd, and irresponsible of Atlassian to recommend since it could have costly licensing implications. While SQL Server Express edition is free and wouldn't have licensing implications, a) it has a hard-limit on the max size of any single database (10 GB), and b) it is not supported according to the Jira documentation.
               
              This is an easy problem for Atlassian to solve: simply add the following clause to string column in all CREATE TABLE #tempTable statements:
              COLLATE DATABASE_DEFAULT

              This is a very low-risk change to make. The only problem you could run into is a syntax error by either misspelling COLLATE or DATABASE_DEFAULT, or not having a space between them, or placing them after the comma that separates the column's definition from the next column, etc. AND, you can get away with doing this in two phases:

              1. Add COLLATE DATABASE_DEFAULT to only those temp table columns that are used to JOIN with other string columns, or are involved in a UNION, or string concatenation (because these are the scenarios that produce that collation conflict error)
              2. Come back later to add COLLATE DATABASE_DEFAULT to the remaining string columns in the temp tables.

             

            Take care, Solomon...

             

            P.S. for more info on working with collations in Microsoft SQL Server, please visit: https://collations.info/

             

            P.P.S. Please contact me if you still have questions regarding the cause of this problem or the solution.

             

            Solomon Rutzky added a comment - - edited The changes made 3 hours ago to this ticket's Description  by  Taiwo Akindele are wrong and will not assist in truly resolving this issue. The issues are: The Title is incorrect: this issue has nothing to do with being on a shared server. Being on a shared server has no bearing on the problem or the solution. That part needs to be removed as it is misleading. The problem is simply that the instance-level collation is not the same as the default collation used in the database that Jira is using. The instance-level collation is what tempdb uses, and by default, the collation used for string columns in temporary tables is the collation used by tempdb . In the Description : Under " Summary ": this issue has nothing to do with being on a shared server. Being on a shared server has no bearing on the problem or the solution. That part needs to be removed as it is misleading. The problem is simply that the instance-level collation is not the same as the default collation used in the database that Jira is using. The instance-level collation is what tempdb uses, and by default, the collation used for string columns in temporary tables is the collation used by tempdb . Under " Steps to Reproduce ": The version of SQL Server does not matter. This issue is the same across all versions. You don't need to use  Latin1_General_CI_AS . The error message itself, under " Actual Results ", shows that the instance-level (and hence, tempdb 's) collation is  SQL_Latin1_General_CP1_CI_AS . But practically speaking, any collation that is not the collation of the DB where Jira is installed will have the same effect, with the only difference being the name of the collation that is in conflict, as reported in the error message. Under " Notes ": No, please don't reference that MSSQLTips article. That pertains to a different scenario for collation errors in temp tables. The situation faced by Jira is much less complicated than that. Under " Workaround ": NO, NO, NO . Do not set up a dedicated instance just for Jira. That is absurd, and irresponsible of Atlassian to recommend since it could have costly licensing implications. While SQL Server Express edition is free and wouldn't have licensing implications, a) it has a hard-limit on the max size of any single database (10 GB), and b) it is not supported according to the Jira documentation.   This is an easy problem for Atlassian to solve: simply add the following clause to string column in all CREATE TABLE #tempTable statements: COLLATE DATABASE_DEFAULT This is a very low-risk change to make. The only problem you could run into is a syntax error by either misspelling COLLATE or DATABASE_DEFAULT, or not having a space between them, or placing them after the comma that separates the column's definition from the next column, etc. AND, you can get away with doing this in two phases: Add COLLATE DATABASE_DEFAULT to only those temp table columns that are used to JOIN with other string columns, or are involved in a UNION, or string concatenation (because these are the scenarios that produce that collation conflict error) Come back later to add COLLATE DATABASE_DEFAULT to the remaining string columns in the temp tables.   Take care, Solomon...   P.S. for more info on working with collations in Microsoft SQL Server, please visit: https://collations.info/   P.P.S. Please contact me if you still have questions regarding the cause of this problem or the solution.  

            Solomon Rutzky added a comment - - edited

            Hi there. There is no need to implement the solution shown in the linked MSSQLTIPS article. That would just over-complicate things. You would fix any Collation conflicts (due to comparing string columns between the JIRA database and string columns in temp tables) simply by adding the following to any string columns in the CREATE TABLE #.... statements:

            COLLATE DATABASE_DEFAULT

            For example:

            CREATE TABLE #MyTempTable
            (
              [ID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
              [Description] NVARCHAR(MAX) NOT NULL COLLATE DATABASE_DEFAULT
            );

            The solution shown in that MSSQLTIPS article helps when one or more columns in any user-tables have a Collation that is not the same as the database's default Collation. However, for JIRA (if not all Altassian products), all string columns in all user-tables are indeed using the database's default Collation. You can see this by executing the following query in the JIRA database:

            SELECT obj.[name], typ.[name], col.*
            FROM sys.objects obj
            INNER JOIN sys.columns col
            	ON col.[object_id] = obj.[object_id]
            INNER JOIN sys.types typ
            	ON typ.[user_type_id] = col.[user_type_id]
            WHERE obj.[is_ms_shipped] = 0
            AND col.[collation_name] IS NOT NULL
            ORDER BY obj.[name], col.[name];
            

             


            P.S. The title of this ticket should be changed to replace "Since they are using a shared database server it's" with "The server's". The issue has nothing to do with being on a shared server. I am using JIRA on a private server and am using the Collation Latin1_General_100_CI_AI while the instance itself has a Collation of SQL_Latin1_General_CP1_CI_AS (and hence so does tempdb).

            We will need to discuss separately the two "accepted" Collations as well as the continued use of the long-deprecated NTEXT datatype .

            Solomon Rutzky added a comment - - edited Hi there. There is no need to implement the solution shown in the linked MSSQLTIPS article. That would just over-complicate things. You would fix any Collation conflicts (due to comparing string columns between the JIRA database and string columns in temp tables) simply by adding the following to any string columns in the CREATE TABLE #.... statements: COLLATE DATABASE_DEFAULT For example: CREATE TABLE #MyTempTable ( [ID] INT NOT NULL IDENTITY (1, 1) PRIMARY KEY , [Description] NVARCHAR( MAX ) NOT NULL COLLATE DATABASE_DEFAULT ); The solution shown in that MSSQLTIPS article helps when one or more columns in any user-tables have a Collation that is not the same as the database's default Collation. However, for JIRA (if not all Altassian products), all string columns in all user-tables are indeed using the database's default Collation. You can see this by executing the following query in the JIRA database: SELECT obj.[ name ], typ.[ name ], col.* FROM sys.objects obj INNER JOIN sys. columns col ON col.[object_id] = obj.[object_id] INNER JOIN sys.types typ ON typ.[user_type_id] = col.[user_type_id] WHERE obj.[is_ms_shipped] = 0 AND col.[ collation_name ] IS NOT NULL ORDER BY obj.[ name ], col.[ name ];   P.S. The title of this ticket should be changed to replace " Since they are using a shared database server it's " with " The server's ". The issue has nothing to do with being on a shared server. I am using JIRA on a private server and am using the Collation  Latin1_General_100_CI_AI while the instance itself has a Collation of SQL_Latin1_General_CP1_CI_AS (and hence so does tempdb ). We will need to discuss separately the two "accepted" Collations as well as the continued use of the long-deprecated NTEXT datatype .

            Please see GHS-86064 for more details.

            Quoting from https://www.mssqltips.com/sqlservertip/2440/create-sql-server-temporary-tables-with-the-correct-collation/ :

            > Problem
            > When we write code, we sometimes use temporary tables. Using a temporary table is a convenient way to store intermediate results, and then use them at a later phase in our application logic. When using temporary tables without specifying a collation (for the column used) SQL Server will inherit the collation for our newly created temporary table from ***the SQL Server instance default***. In case our SQL Server database has a different collation setting than the instance's default (there might be various reasons for that), we might fall into a trap of having two tables containing similar information, but with different collation settings.

            (Emphasis mine)

            Justin Spratt added a comment - Please see  GHS-86064 for more details. Quoting from  https://www.mssqltips.com/sqlservertip/2440/create-sql-server-temporary-tables-with-the-correct-collation/  : > Problem > When we write code, we sometimes use temporary tables. Using a temporary table is a convenient way to store intermediate results, and then use them at a later phase in our application logic. When using temporary tables without specifying a collation (for the column used) SQL Server will inherit the collation for our newly created temporary table from *** the SQL Server instance default ***. In case our SQL Server database has a different collation setting than the instance's default (there might be various reasons for that), we might fall into a trap of having two tables containing similar information, but with different collation settings. (Emphasis mine)

              Unassigned Unassigned
              estorch Eric Storch (Inactive)
              Affected customers:
              33 This affects my team
              Watchers:
              59 Start watching this issue

                Created:
                Updated: