Jira server throws column name SQLException on upgrade or installation
Symptoms
- Database Server used is Microsoft SQL Server
- The following appears in the
atlassian-jira.log
:
java.sql.SQLException: Invalid column name '<column_name>'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:723)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1166)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1119)
Diagnosis and Cause
Identify the collation used for the JIRA database in MS SQL Server by executing the following SQL query and substituting JIRADB with the database used in your installation. The name of the database could be identified from the database configuration file at $JIRA_HOME/dbconfig.xml
.
SELECT name, collation_name FROM sys.databases WHERE name='JIRADB';
The recommended collation for the JIRA database in MS SQL Server is SQL_Latin1_General_CP437_CI_AI
. The SQL Exception occurs due to collation problems
- When an case sensitive collation is used. The text "_CS_" in the collation name indicates case sensitive collation instead of "_CI" for a case insensitive one.
- The exception is also observed with accent sensitive – indicated by "AS" – collations like
Turkish_CI_AS
.
Resolution
Option 1
Change the database collation to the recommended SQL_Latin1_General_CP437_CI_AI
using the following Microsoft documentation:
- Microsoft SQL Server 2016: Set or change the database collation
- Microsoft SQL Server 2012: Set or change the database collation
- Microsoft SQL Server 2008 R2: Set or change the database collation
- Microsoft SQL Server 2008: Set or change the database collation
- Microsoft SQL Server 2005: Set or change the database collation
Option 2
Create a new database for JIRA using the recommended collation SQL_Latin1_General_CP437_CI_AI
using the following documentation:
- Connecting JIRA to SQL Server 2016
Connecting JIRA to SQL Server 2012 - Connecting JIRA to SQL Server 2008
- Connecting JIRA to SQL Server 2005
Configure JIRA to use this database and re-try the installation or upgrade.