Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,301,285
Community Members
 
Community Events
165
Community Groups

Error 500 after installing Service Desk

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]

 

3 answers

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.

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

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;
0 votes
Daniel Eads Atlassian Team Oct 22, 2018

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

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.

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Service Management

Improving the Create Issue Experience in Jira Service Management Cloud

Hello everyone!  We are very excited to announce some much needed changes to the issue create experience in JSM (the blue "create" button) at the top of the screen.  We have just starte...

301 views 15 10
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you