Personal Access Token cannot be revoked on MS SQL Server with bundled version of pats-plugin

Issue Summary

Personal Access Tokens cannot be revoked in Jira running with MS SQL database when using the bundled version (1.1.2 on Jira 8.18.1).

Steps to Reproduce

1. Install Jira on MS SQL Server database
2. Create a personal access token at <base-url>/secure/ViewProfile.jspa
3. Attempt to revoke the personal access token and receive an error message
   

Expected Results

Personal access tokens can be revoked with no changes to the default installation files.

Actual Results

1. Add logging for com.atlassian.pats
2. The below exceptions are thrown in the atlassian-jira.log file:

3. 2021-06-29 /rest/pat/latest/tokens/1 [c.a.pats.rest.PersonalTokenExceptionMapper] Got exception: [class com.querydsl.core.QueryException] with message: [Caught SQLServerException for select top 2 "AO_81F455_PERSONAL_TOKEN"."CREATED_AT", "AO_81F455_PERSONAL_TOKEN"."NAME", "AO_81F455_PERSONAL_TOKEN"."EXPIRING_AT", "AO_81F455_PERSONAL_TOKEN"."ID", "AO_81F455_PERSONAL_TOKEN"."LAST_ACCESSED_AT", "AO_81F455_PERSONAL_TOKEN"."HASHED_TOKEN", "AO_81F455_PERSONAL_TOKEN"."TOKEN_ID", "AO_81F455_PERSONAL_TOKEN"."USER_KEY", "AO_81F455_PERSONAL_TOKEN"."NOTIFICATION_STATE" from "AO_81F455_PERSONAL_TOKEN" where "AO_81F455_PERSONAL_TOKEN"."ID" = ?]
   
   2021-06-29 /rest/pat/latest/tokens/1 [c.a.pats.rest.PersonalTokenExceptionMapper] Caught unknown exception: 
   com.querydsl.core.QueryException: Caught SQLServerException for select top 2 "AO_81F455_PERSONAL_TOKEN"."CREATED_AT", "AO_81F455_PERSONAL_TOKEN"."NAME", "AO_81F455_PERSONAL_TOKEN"."EXPIRING_AT", "AO_81F455_PERSONAL_TOKEN"."ID", "AO_81F455_PERSONAL_TOKEN"."LAST_ACCESSED_AT", "AO_81F455_PERSONAL_TOKEN"."HASHED_TOKEN", "AO_81F455_PERSONAL_TOKEN"."TOKEN_ID", "AO_81F455_PERSONAL_TOKEN"."USER_KEY", "AO_81F455_PERSONAL_TOKEN"."NOTIFICATION_STATE" from "AO_81F455_PERSONAL_TOKEN" where "AO_81F455_PERSONAL_TOKEN"."ID" = ?
       at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
       at com.querydsl.sql.Configuration.translate(Configuration.java:459)
       at com.querydsl.sql.AbstractSQLQuery.iterateSingle(AbstractSQLQuery.java:410)
       at com.querydsl.sql.AbstractSQLQuery.iterate(AbstractSQLQuery.java:342)
       at com.querydsl.sql.ProjectableSQLQuery.fetchOne(ProjectableSQLQuery.java:397)
       at com.atlassian.data.activeobjects.repository.support.QuerydslPocketKnifeReadOnlyPredicateExecutor.lambda$findOne$3(QuerydslPocketKnifeReadOnlyPredicateExecutor.java:63)
       at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.lambda$execute$0(DatabaseAccessorImpl.java:69)
       at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.executeInternal(DefaultTransactionalExecutor.java:87)
       at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.lambda$execute$0(DefaultTransactionalExecutor.java:43)
       at com.atlassian.jira.database.DatabaseAccessorImpl.runInManagedTransaction(DatabaseAccessorImpl.java:131)
       ... 2 filtered
       at java.lang.reflect.Method.invoke(Method.java:498)
       at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26)
       at com.sun.proxy.$Proxy664.runInManagedTransaction(Unknown Source)
       ... 2 filtered
       at java.lang.reflect.Method.invoke(Method.java:498)
       at com.atlassian.plugin.osgi.bridge.external.HostComponentFactoryBean$DynamicServiceInvocationHandler.invoke(HostComponentFactoryBean.java:130)
       at com.sun.proxy.$Proxy664.runInManagedTransaction(Unknown Source)
       at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.runInStartedOrExistingTransaction(JiraHostConnectionAccessor.java:130)
       at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.execute(JiraHostConnectionAccessor.java:60)
       at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.execute(DefaultTransactionalExecutor.java:40)
       at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.execute(DatabaseAccessorImpl.java:67)
       at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:43)
       at com.atlassian.data.activeobjects.repository.support.QuerydslPocketKnifeReadOnlyPredicateExecutor.executeQuery(QuerydslPocketKnifeReadOnlyPredicateExecutor.java:79)
       at com.atlassian.data.activeobjects.repository.support.QuerydslPocketKnifeReadOnlyPredicateExecutor.executeQuery(QuerydslPocketKnifeReadOnlyPredicateExecutor.java:71)
       at com.atlassian.data.activeobjects.repository.support.QuerydslPocketKnifeReadOnlyPredicateExecutor.findOne(QuerydslPocketKnifeReadOnlyPredicateExecutor.java:63)
       at com.atlassian.data.activeobjects.repository.support.QuerydslPocketKnifeCrudPredicateExecutor.findById(QuerydslPocketKnifeCrudPredicateExecutor.java:95)
       ... 3 filtered
       at java.lang.reflect.Method.invoke(Method.java:498)
       at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:359)
       at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:200)
       at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:644)
       at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
       at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:608)
       at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$invoke$3(RepositoryFactorySupport.java:595)
       at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:595)
       at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
       at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
       at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
       at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
       at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
       at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
       at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
       at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
       at com.sun.proxy.$Proxy8171.findById(Unknown Source)
       at com.atlassian.pats.rest.TokensResource.getAccessibleToken(TokensResource.java:132)
       at com.atlassian.pats.rest.TokensResource.deleteToken(TokensResource.java:161)
       ... 3 filtered
       at java.lang.reflect.Method.invoke(Method.java:498)
       ... 19 filtered
       at com.atlassian.plugins.rest.module.RestDelegatingServletFilter$JerseyOsgiServletContainer.doFilter(RestDelegatingServletFilter.java:160)
       ... 1 filtered
       at com.atlassian.plugins.rest.module.RestDelegatingServletFilter.doFilter(RestDelegatingServletFilter.java:70)
       ... 36 filtered
       at com.atlassian.jira.plugin.mobile.web.filter.MobileAppRequestFilter.doFilter(MobileAppRequestFilter.java:59)
       ... 4 filtered
       at com.atlassian.jira.plugin.mobile.login.MobileLoginSuccessFilter.doFilter(MobileLoginSuccessFilter.java:54)
       ... 3 filtered
       at com.atlassian.diagnostics.internal.platform.monitor.http.HttpRequestMonitoringFilter.doFilter(HttpRequestMonitoringFilter.java:55)
       ... 8 filtered
       at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
       ... 43 filtered
       at com.atlassian.ratelimiting.internal.filter.RateLimitFilter.doFilter(RateLimitFilter.java:73)
       ... 17 filtered
       at com.atlassian.jira.security.JiraSecurityFilter.lambda$doFilter$0(JiraSecurityFilter.java:66)
       ... 1 filtered
       at com.atlassian.jira.security.JiraSecurityFilter.doFilter(JiraSecurityFilter.java:64)
       ... 16 filtered
       at com.atlassian.plugins.rest.module.servlet.RestSeraphFilter.doFilter(RestSeraphFilter.java:38)
       ... 3 filtered
       at com.atlassian.pats.web.filter.TokenBasedAuthenticationFilter.doFilter(TokenBasedAuthenticationFilter.java:82)
       ... 19 filtered
       at com.atlassian.jira.servermetrics.CorrelationIdPopulatorFilter.doFilter(CorrelationIdPopulatorFilter.java:30)
       ... 10 filtered
       at com.atlassian.ratelimiting.internal.filter.RateLimitPreAuthFilter.doFilter(RateLimitPreAuthFilter.java:71)
       ... 3 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)
       ... 26 filtered
       at com.atlassian.jira.servermetrics.MetricsCollectorFilter.doFilter(MetricsCollectorFilter.java:25)
       ... 25 filtered
       at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
       at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
       at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
       at java.lang.Thread.run(Thread.java:748)
   Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'AO_81F455_PERSONAL_TOKEN'.
   

Workaround

1. Upload the pats-plugin-1.3.0.jar in ⚙️ → Manage apps → Manage apps
2. Personal access token can now be revoked