Error 500 after installing Service Desk

[Kristjan Vilipõld]

After installing Service Desk, cannot create new SD projects or access the Service desk Configuration menu. Error is 500.

Technical details

Log's referral number: 179ada01-73d0-43e1-9884-983517398c84

Cause

Referer URL: http://142.11.202.6:8080/secure/admin/SDConfiguration.jspa

com.atlassian.cache.CacheException: com.querydsl.core.QueryException: Caught PSQLException for select "AO_54307E_SERVICEDESK"."ID", "AO_54307E_SERVICEDESK"."PROJECT_ID", "PROJECT"."PNAME", "AO_54307E_SERVICEDESK"."PUBLIC_SIGNUP", "AO_54307E_SERVICEDESK"."OPEN_CUSTOMER_ACCESS", "AO_54307E_SERVICEDESK"."CREATED_BY_USER_KEY", "AO_54307E_SERVICEDESK"."CREATED_DATE", "AO_54307E_SERVICEDESK"."CREATED_WITH_EMPTY_PROJECT", "AO_54307E_SERVICEDESK"."VERSION_CREATED_AT", "AO_54307E_SERVICEDESK"."LEGACY_TRANSITION_DISABLED" from "AO_54307E_SERVICEDESK" "AO_54307E_SERVICEDESK" inner join "PROJECT" "PROJECT" on "AO_54307E_SERVICEDESK"."PROJECT_ID" = "PROJECT"."ID" order by "PROJECT"."PNAME" asc

com.atlassian.cache.CacheException: com.querydsl.core.QueryException: Caught PSQLException for select "AO_54307E_SERVICEDESK"."ID", "AO_54307E_SERVICEDESK"."PROJECT_ID", "PROJECT"."PNAME", "AO_54307E_SERVICEDESK"."PUBLIC_SIGNUP", "AO_54307E_SERVICEDESK"."OPEN_CUSTOMER_ACCESS", "AO_54307E_SERVICEDESK"."CREATED_BY_USER_KEY", "AO_54307E_SERVICEDESK"."CREATED_DATE", "AO_54307E_SERVICEDESK"."CREATED_WITH_EMPTY_PROJECT", "AO_54307E_SERVICEDESK"."VERSION_CREATED_AT", "AO_54307E_SERVICEDESK"."LEGACY_TRANSITION_DISABLED" from "AO_54307E_SERVICEDESK" "AO_54307E_SERVICEDESK" inner join "PROJECT" "PROJECT" on "AO_54307E_SERVICEDESK"."PROJECT_ID" = "PROJECT"."ID" order by "PROJECT"."PNAME" asc
	at com.atlassian.cache.memory.DelegatingCachedReference.get(DelegatingCachedReference.java:91) [atlassian-cache-memory-3.2.0.jar:?]
	at com.atlassian.servicedesk.internal.feature.servicedesk.ServiceDeskInternalManagerImpl.getCache(ServiceDeskInternalManagerImpl.java:209) [?:?]

Caused by: com.querydsl.core.QueryException: Caught PSQLException for select "AO_54307E_SERVICEDESK"."ID", "AO_54307E_SERVICEDESK"."PROJECT_ID", "PROJECT"."PNAME", "AO_54307E_SERVICEDESK"."PUBLIC_SIGNUP", "AO_54307E_SERVICEDESK"."OPEN_CUSTOMER_ACCESS", "AO_54307E_SERVICEDESK"."CREATED_BY_USER_KEY", "AO_54307E_SERVICEDESK"."CREATED_DATE", "AO_54307E_SERVICEDESK"."CREATED_WITH_EMPTY_PROJECT", "AO_54307E_SERVICEDESK"."VERSION_CREATED_AT", "AO_54307E_SERVICEDESK"."LEGACY_TRANSITION_DISABLED" from "AO_54307E_SERVICEDESK" "AO_54307E_SERVICEDESK" inner join "PROJECT" "PROJECT" on "AO_54307E_SERVICEDESK"."PROJECT_ID" = "PROJECT"."ID" order by "PROJECT"."PNAME" asc
	at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50) [querydsl-sql-4.1.4.jar:?]
	at com.querydsl.sql.Configuration.translate(Configuration.java:459) [querydsl-sql-4.1.4.jar:?]

Caused by: org.postgresql.util.PSQLException: ERROR: relation "PROJECT" does not exist
      Position: 488
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455) [postgresql-9.4.1212.jar:9.4.1212]
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155) [postgresql-9.4.1212.jar:9.4.1212]

[Uxian]

We've just seen this issue after installing Service Desk 4.2.1 on JIRA 8.2.1 and it seems to be related to the case sensitivity of PostgreSQL.  When JIRA was installed, it created its tables and columns with lower-case names but it appears that Service Desk is running case-sensitive queries on those tables and expecting them to be all in upper-case.

[Uxian]

So the root cause appears to be that the database was created with a case-sensitive collation (it's an RDS database) and the solution is to follow this process for migrating the data to a database with the correct collation:

https://confluence.atlassian.com/jirakb/how-to-fix-the-collation-of-a-postgres-jira-database-776657961.html

[Paul Stahlke]

Hi Uxian, your observation is correct. I confirmed that my PostgreSQL database is case sensitive which is why it's failing when using uppercase to query older lowercase tables. However, I just now set up a new database with the correct collation as mentioned in the DB setup guide, and it's still case sensitive and failing. Any ideas?

CREATE DATABASE jiradb WITH ENCODING 'UNICODE' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;

[Kristjan Vilipõld]

Bump

[Daniel Eads]

Hi Kristjan,

We generally see errors like this after an upgrade attempt that skipped versions. Did you recently upgrade the Jira platform? If so, which version did you start on, which version did you finish on, and did you go to any versions in between?

Did you execute any Service Desk updates during any Jira upgrades?

We may have some suggestions on action but just want to make sure we understand the situation before taking action.

Cheers,
Daniel

[Kristjan Vilipõld]

Hi Daniel,

No, there has not been a single upgrade/update done. JIRA Software 7.12.2 was installed about a month ago and 2 days ago Service Desk 3.15.2 installment.