Using a different Database scheme in PSQL will result in "Caused by: org.postgresql.util.PSQLException: ERROR: relation "AO_B9A0F0_APPLIED_TEMPLATE" does not exist" error when creating a project.

Summary

Using a different Database scheme in PSQL will result in "Caused by: org.postgresql.util.PSQLException: ERROR: relation "AO_B9A0F0_APPLIED_TEMPLATE" does not exist" error when creating a project.

Environment

• PSQL
• JIRA 7.3

Steps to Reproduce

1. Setup JIRA via UI
2. Select a different schema (not public)
3. Try to create a project

Expected Results

The project should be created within JIRA

Actual Results

The below exception is thrown in the atlassian-jira.log file:
{noformat}
2017-03-21 10:01:35,928 http-nio-8080-exec-16 ERROR jira 601x384x1 1yue775 0:0:0:0:0:0:0:1 /rest/project-templates/1.0/templates [c.a.jira.project.ProjectCreateRegistrarImpl] The handler with id com.atlassian.jira.project-templates-plugin:apply-project-template-handler threw an exception while handling a notification about a project being created
com.querydsl.core.QueryException: Caught PSQLException for insert into "AO_B9A0F0_APPLIED_TEMPLATE" ("PROJECT_ID", "PROJECT_TEMPLATE_MODULE_KEY", "PROJECT_TEMPLATE_WEB_ITEM_KEY") values (?, ?, ?)
at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
at com.querydsl.sql.Configuration.translate(Configuration.java:451)
at com.querydsl.sql.dml.SQLInsertClause.executeWithKeys(SQLInsertClause.java:364)
at com.querydsl.sql.dml.SQLInsertClause.executeWithKey(SQLInsertClause.java:172)
at com.querydsl.sql.dml.SQLInsertClause.executeWithKey(SQLInsertClause.java:153)
at com.atlassian.jira.projecttemplates.service.AppliedTemplateStorageImpl.lambda$add$16(AppliedTemplateStorageImpl.java:26)
at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.lambda$execute$18(DatabaseAccessorImpl.java:55)
at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.executeInternal(DefaultTransactionalExecutor.java:91)
at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor$1.execute(DefaultTransactionalExecutor.java:45)
at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.lambda$borrowConnectionAndExecute$0(JiraHostConnectionAccessor.java:62)
at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:67)
... 2 filtered
at java.lang.reflect.Method.invoke(Method.java:497)
at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26)
at com.sun.proxy.$Proxy568.executeQuery(Unknown Source)
... 2 filtered
at java.lang.reflect.Method.invoke(Method.java:497)
at com.atlassian.plugin.osgi.bridge.external.HostComponentFactoryBean$DynamicServiceInvocationHandler.invoke(HostComponentFactoryBean.java:136)
at com.sun.proxy.$Proxy568.executeQuery(Unknown Source)
at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.borrowConnectionAndExecute(JiraHostConnectionAccessor.java:50)
at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.execute(JiraHostConnectionAccessor.java:40)
at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.execute(DefaultTransactionalExecutor.java:42)
at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.execute(DatabaseAccessorImpl.java:53)
at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:37)
at com.atlassian.jira.projecttemplates.service.AppliedTemplateStorageImpl.add(AppliedTemplateStorageImpl.java:20)
at com.atlassian.jira.projecttemplates.service.AppliedTemplateStorageCache.add(AppliedTemplateStorageCache.java:20)
at com.atlassian.jira.projecttemplates.service.ApplyProjectTemplateHandler.applyProjectTemplate(ApplyProjectTemplateHandler.java:61)
at com.atlassian.jira.projecttemplates.service.ApplyProjectTemplateHandler.onProjectCreated(ApplyProjectTemplateHandler.java:53)
at com.atlassian.jira.project.ProjectCreateRegistrarImpl.notifyAllHandlers(ProjectCreateRegistrarImpl.java:36)
at com.atlassian.jira.bc.project.DefaultProjectService.notifyHandlersOfProjectCreated(DefaultProjectService.java:394)
at com.atlassian.jira.bc.project.DefaultProjectService.createProjectInternal(DefaultProjectService.java:379)
at com.atlassian.jira.bc.project.DefaultProjectService.createProject(DefaultProjectService.java:349)
... 3 filtered
at java.lang.reflect.Method.invoke(Method.java:497)
at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26)
at com.sun.proxy.$Proxy267.createProject(Unknown Source)
... 3 filtered
at java.lang.reflect.Method.invoke(Method.java:497)
at com.atlassian.plugin.osgi.bridge.external.HostComponentFactoryBean$DynamicServiceInvocationHandler.invoke(HostComponentFactoryBean.java:136)
at com.sun.proxy.$Proxy267.createProject(Unknown Source)
at com.atlassian.jira.projecttemplates.service.ProjectTemplateServiceImpl.applyProjectTemplate(ProjectTemplateServiceImpl.java:173)
at com.atlassian.jira.projecttemplates.rest.ProjectTemplatesResource.createProjectFromFormRequest(ProjectTemplatesResource.java:170)
... 3 filtered
at java.lang.reflect.Method.invoke(Method.java:497)
at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
... 12 filtered
at com.atlassian.plugins.rest.module.RestDelegatingServletFilter$JerseyOsgiServletContainer.doFilter(RestDelegatingServletFilter.java:154)
... 1 filtered
at com.atlassian.plugins.rest.module.RestDelegatingServletFilter.doFilter(RestDelegatingServletFilter.java:68)
... 32 filtered
at com.atlassian.servicedesk.internal.web.ExternalCustomerLockoutFilter.doFilter(ExternalCustomerLockoutFilter.java:56)
... 8 filtered
at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
... 53 filtered
at com.atlassian.jira.security.JiraSecurityFilter.lambda$doFilter$0(JiraSecurityFilter.java:80)
... 1 filtered
at com.atlassian.jira.security.JiraSecurityFilter.doFilter(JiraSecurityFilter.java:78)
... 16 filtered
at com.atlassian.plugins.rest.module.servlet.RestSeraphFilter.doFilter(RestSeraphFilter.java:37)
... 20 filtered
at com.atlassian.jira.servermetrics.CorrelationIdPopulatorFilter.doFilter(CorrelationIdPopulatorFilter.java:30)
... 5 filtered
at com.atlassian.servicedesk.internal.web.CustomerContextSettingFilter.lambda$invokeFilterChain$0(CustomerContextSettingFilter.java:181)
at com.atlassian.servicedesk.internal.utils.context.ReentrantThreadLocalBasedCodeContext.rteInvoke(ReentrantThreadLocalBasedCodeContext.java:139)
at com.atlassian.servicedesk.internal.utils.context.ReentrantThreadLocalBasedCodeContext.runOutOfContext(ReentrantThreadLocalBasedCodeContext.java:92)
at com.atlassian.servicedesk.internal.utils.context.CustomerContextServiceImpl.runOutOfCustomerContext(CustomerContextServiceImpl.java:64)
at com.atlassian.servicedesk.internal.web.CustomerContextSettingFilter.outOfCustomerContext(CustomerContextSettingFilter.java:174)
at com.atlassian.servicedesk.internal.web.CustomerContextSettingFilter.doFilterImpl(CustomerContextSettingFilter.java:130)
at com.atlassian.servicedesk.internal.web.CustomerContextSettingFilter.doFilter(CustomerContextSettingFilter.java:121)
... 4 filtered
at com.atlassian.jwt.internal.servlet.JwtAuthFilter.doFilter(JwtAuthFilter.java:32)
... 8 filtered
at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
... 4 filtered
at com.atlassian.web.servlet.plugin.LocationCleanerFilter.doFilter(LocationCleanerFilter.java:36)
... 29 filtered
at com.atlassian.jira.servermetrics.MetricsCollectorFilter.doFilter(MetricsCollectorFilter.java:25)
... 28 filtered
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: relation "AO_B9A0F0_APPLIED_TEMPLATE" does not exist
Position: 13
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1834)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:332)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
at com.querydsl.sql.dml.SQLInsertClause.executeWithKeys(SQLInsertClause.java:335)
... 294 more
{noformat}

Notes

JIRA Seems to be default check for the public."AO_B9A0F0_APPLIED_TEMPLATE" table, it will not find this.

Workaround

You can set the default search_path at the database level:

ALTER DATABASE <database_name> SET search_path TO schema1,schema2;

Or at the user or role level:

ALTER ROLE <role_name> SET search_path TO schema1,schema2;