Migrating a jira server returns a 500 error on Jira Service Desk projects

[Marta Carbone]

Hi,

I've migrated a Jira installation from a server to a new one. The old server was a Windows instance running SQL Server, the new one is a Linux based server running Postgres. During the migration I maintained the same Jira version (Jcore 8.7.1) and I created an xml backup, as recommended by the jira migration instruction,.

The Jira migration goes fine, everything is almost working and the instance is up and running but opening a Service Desk Project (Jira Service Desk 4.7.1) I get an HTTP 500 error with the following stack trace:

java.lang.RuntimeException: com.atlassian.cache.CacheException: com.querydsl.core.QueryException: Caught PSQLException for select "AO_54307E_SERVICEDESK"."ID", [...] K" inner join "PROJECT" "PROJECT"
[...]
Caused by: org.postgresql.util.PSQLException: ERROR: relation "PROJECT" does not existI can find the table on the database, I suppose the problem is with the case sensitivity due to the double quotes around the table name.

Is this a known issue? There is something I missed during the migration process?

Thanks for any help,

Marta Carbone

[Daniel Ebers · Accepted solution]

Hi Marta,

this has been mentioned a few times in the past by several users. They pointed out it was because of a case-sensitivity. This was reported to be related to a wrong collation why this link applies:
https://confluence.atlassian.com/jirakb/unsupported-collation-error-thrown-in-jira-server-using-postgresql-776657961.html

Are you getting currently any warnings in health check for collation issues?

Cheers,
Daniel

[Marta Carbone]

Hi Daniel,

thank you for your reply.
I checked the logs and the health check, but (unfortunately) there is nothing related to collation problems or case sensitivity.
I double checked the db creation and the collation is the right one.

The Jira projects are working fine and the error arise with the Jira Service Desk projects, so I suppose the problem is the query, since it uses the double quote around the Project table name, forcing Postgres to case sensitivity.

I've found the same stack trace here:
https://community.atlassian.com/t5/Jira-Software-questions/Upgrading-Jira-service-desk-results-in-error-500/qaq-p/852153
but it's related to an upgrade, and is not my case.

I have ho idea on how to solve this, I can try to:
- upgrade Jira and attempt the migration process again (but I prefer to don't touch the old installation, and I don't know if this can solve)
- or migrate to a SQL server based solution instead of a Postgres one.

Do you have any thoughts, ideas? Thanks for your time,

Marta Carbone

[Daniel Ebers]

Could you please paste in the content of your dbconfig.xml?
Please make sure you xxx-out the password of the database. Also the username is not of interest so feel free to xxx-out it if you wish to.

If you do not know where to look for the file, the default locations are - but this might vary on your server (in last instance - please search for dbconfig.xml using file browser/find tools):

• C:\Program Files\Atlassian\Application Data\JIRA (on Windows) or
• /var/atlassian/application-data/jira (on Linux)

Why I am asking: of interest is if there is a line:

<schema-name>name_of_schema</schema-name>

where "name_of_schema" is the name of the schema - mostly it should be "public".

This should be placed directly above a line that reads:

<jdbc-datasource>

Please compary this to your installation - or, as offered above, to paste in dbconfig.xml here for inspection by Community :)
It could be that this line is missing. If so, it needs to be added.

[Marta Carbone]

Hi Daniel,

the problem was the missing schema-name line in the dbconfig.xml file.

I fixed the configuration file and I started the migration from scratch (just to be sure the migration runs with the right configuration). Once finished the Service Desk Project works fine.

Thank you very much for your help!

Marta Carbone

[Gonchik Tsymzhitov]

To be honest adding schema name helped to me :)

<schema-name>name_of_schema</schema-name>

i.e. in my situation

<schema-name>public</schema-name>