Error 500 after installing Service Desk

Question

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]

Paul Stahlke_bptp3c · Answer

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;

Uxian · Answer

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

Uxian · Answer

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.