How to fix or remove corrupt search filters

Hi,

I've managed to corrupt a number of filters on my production Jira installation. Because of this, users that own these filters can't search for issues and get errors trying to load the dashboard.

Using the Jira gui to search for the corrupt filters generates an exception, trying to load the filters into a groovy script generates exceptions and trying to delete the filters with a groovy script also generates exceptions.

So how do I fix this. Is my only option to start fiddeling with the database directly?

The following errors are displayed in the log:

2012-12-13 10:10:01,327 http-8093-5 ERROR admin 610x7957x2 1z0zuuy xxx.xx.xx.xx,xxx.xx.xx.xx /secure/admin/groovy/GroovyRunner.jspa [jira.issue.search.OfBizSearchRequestStore] A JQL query exception was thrown parsing, error loading search request with id '11564' and name 'All TAS marked CRs and TRs' owned by 'stlul' query '{Labels = "TAS"} AND {issuetype not in ("Sub-task", "Task")}'.
2012-12-13 10:10:01,327 http-8093-5 ERROR admin 610x7957x2 1z0zuuy xxx.xx.xx.xx,xxx.xx.xx.xx /secure/admin/groovy/GroovyRunner.jspa [onresolve.jira.groovy.GroovyRunner] The script failed : javax.script.ScriptException: javax.script.ScriptException: com.atlassian.jira.exception.DataAccessException: com.atlassian.jira.jql.parser.JqlParseException: com.atlassian.jira.jql.parser.antlr.RuntimeRecognitionException: MismatchedSetException(123!=null)
2012-12-13 10:10:01,330 http-8093-5 ERROR admin 610x7957x2 1z0zuuy xxx.xx.xx.xx,xxx.xx.xx.xx /secure/admin/groovy/GroovyRunner.jspa [onresolve.jira.groovy.GroovyRunner] The script failed : javax.script.ScriptException: javax.script.ScriptException: com.atlassian.jira.exception.DataAccessException: com.atlassian.jira.jql.parser.JqlParseException: com.atlassian.jira.jql.parser.antlr.RuntimeRecognitionException: MismatchedSetException(123!=null)
javax.script.ScriptException: javax.script.ScriptException: com.atlassian.jira.exception.DataAccessException: com.atlassian.jira.jql.parser.JqlParseException: com.atlassian.jira.jql.parser.antlr.RuntimeRecognitionException: MismatchedSetException(123!=null)
        at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:117)
        at javax.script.AbstractScriptEngine.eval(Unknown Source)
        at com.onresolve.jira.groovy.GroovyRunner.runFile(GroovyRunner.java:105)
        at com.onresolve.jira.groovy.GroovyRunner.run(GroovyRunner.java:56)
        at com.onresolve.jira.groovy.GroovyRunner.doExecute(GroovyRunner.java:231)
        at com.onresolve.jira.groovy.GroovyRunner.execute(GroovyRunner.java:341)
        at webwork.interceptor.DefaultInterceptorChain.proceed(DefaultInterceptorChain.java:39)
        at webwork.interceptor.NestedInterceptorChain.proceed(NestedInterceptorChain.java:31)
        at webwork.interceptor.ChainedInterceptor.intercept(ChainedInterceptor.java:16)
        at webwork.interceptor.DefaultInterceptorChain.proceed(DefaultInterceptorChain.java:35)
        at webwork.dispatcher.GenericDispatcher.executeAction(GenericDispatcher.java:221)
        at webwork.dispatcher.GenericDispatcher.executeAction(GenericDispatcher.java:150)
        at com.atlassian.jira.web.dispatcher.JiraWebworkActionDispatcher.service(JiraWebworkActionDispatcher.java:153)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.jira.web.filters.steps.ChainedFilterStepRunner.doFilter(ChainedFilterStepRunner.java:78)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.jira.web.filters.XContentTypeOptionsNoSniffFilter.doFilter(XContentTypeOptionsNoSniffFilter.java:22)
        at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.core.filters.HeaderSanitisingFilter.doFilter(HeaderSanitisingFilter.java:44)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
        at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter$1.doFilter(DelegatingPluginFilter.java:66)
        at com.atlassian.labs.botkiller.BotKillerFilter.doFilter(BotKillerFilter.java:36)
        at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter.doFilter(DelegatingPluginFilter.java:74)
        at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:42)
        at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter$1.doFilter(DelegatingPluginFilter.java:66)
        at com.atlassian.jira.tzdetect.IncludeResourcesFilter.doFilter(IncludeResourcesFilter.java:39)
        at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter.doFilter(DelegatingPluginFilter.java:74)
        at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:42)
        at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter$1.doFilter(DelegatingPluginFilter.java:66)
        at com.atlassian.applinks.core.rest.context.ContextFilter.doFilter(ContextFilter.java:25)
        at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter.doFilter(DelegatingPluginFilter.java:74)
        at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:42)
        at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
        at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.jira.web.filters.accesslog.AccessLogFilter.executeRequest(AccessLogFilter.java:103)
        at com.atlassian.jira.web.filters.accesslog.AccessLogFilter.doFilter(AccessLogFilter.java:87)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.jira.security.xsrf.XsrfTokenAdditionRequestFilter.doFilter(XsrfTokenAdditionRequestFilter.java:54)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.opensymphony.sitemesh.webapp.SiteMeshFilter.obtainContent(SiteMeshFilter.java:129)
        at com.opensymphony.sitemesh.webapp.SiteMeshFilter.doFilter(SiteMeshFilter.java:77)
        at com.atlassian.jira.web.filters.SitemeshPageFilter.doFilter(SitemeshPageFilter.java:124)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
        at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter$1.doFilter(DelegatingPluginFilter.java:66)
        at com.almworks.jira.structure.web.AbstractStructureServletFilter.doHttpFilter(AbstractStructureServletFilter.java:45)
        at com.almworks.jira.structure.web.AbstractStructureServletFilter.doFilter(AbstractStructureServletFilter.java:33)
        at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter.doFilter(DelegatingPluginFilter.java:74)
        at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:42)
        at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter$1.doFilter(DelegatingPluginFilter.java:66)
        at com.atlassian.labs.remoteapps.modules.permissions.ApiScopingFilter.doFilter(ApiScopingFilter.java:71)
        at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter.doFilter(DelegatingPluginFilter.java:74)
        at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:42)
        at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
        at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.seraph.filter.SecurityFilter.doFilter(SecurityFilter.java:234)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.security.auth.trustedapps.filter.TrustedApplicationsFilter.doFilter(TrustedApplicationsFilter.java:98)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.seraph.filter.BaseLoginFilter.doFilter(BaseLoginFilter.java:157)
        at com.atlassian.jira.web.filters.JiraLoginFilter.doFilter(JiraLoginFilter.java:70)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
        at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter$1.doFilter(DelegatingPluginFilter.java:66)
        at com.atlassian.oauth.serviceprovider.internal.servlet.OAuthFilter.doFilter(OAuthFilter.java:71)
        at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter.doFilter(DelegatingPluginFilter.java:74)
        at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:42)
        at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
        at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.util.profiling.filters.ProfilingFilter.doFilter(ProfilingFilter.java:99)
        at com.atlassian.jira.web.filters.JIRAProfilingFilter.doFilter(JIRAProfilingFilter.java:19)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.johnson.filters.AbstractJohnsonFilter.doFilter(AbstractJohnsonFilter.java:71)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:738)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.gzipfilter.GzipFilter.doFilterInternal(GzipFilter.java:74)
        at com.atlassian.gzipfilter.GzipFilter.doFilter(GzipFilter.java:51)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
        at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter$1.doFilter(DelegatingPluginFilter.java:66)
        at com.atlassian.labs.remoteapps.modules.oauth.OAuth2LOFilter.doFilter(OAuth2LOFilter.java:70)
        at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter.doFilter(DelegatingPluginFilter.java:74)
        at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:42)
        at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter$1.doFilter(DelegatingPluginFilter.java:66)
        at com.sysbliss.jira.plugins.workflow.servlet.JWDSendRedirectFilter.doFilter(JWDSendRedirectFilter.java:25)
        at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter.doFilter(DelegatingPluginFilter.java:74)
        at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:42)
        at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
        at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.jira.web.filters.steps.ChainedFilterStepRunner.doFilter(ChainedFilterStepRunner.java:78)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.core.filters.cache.AbstractCachingFilter.doFilter(AbstractCachingFilter.java:33)
        at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.core.filters.encoding.AbstractEncodingFilter.doFilter(AbstractEncodingFilter.java:41)
        at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
        at com.atlassian.jira.web.filters.PathMatchingEncodingFilter.doFilter(PathMatchingEncodingFilter.java:49)
        at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.jira.web.monitor.ActiveRequestsFilter$PassToChainFilterFunc.doFilter(ActiveRequestsFilter.java:346)
        at com.atlassian.jira.web.monitor.ActiveRequestsFilter$DebugLogFilterFunc.doFilter(ActiveRequestsFilter.java:463)
        at com.atlassian.jira.web.monitor.ActiveRequestsFilter.doFilter(ActiveRequestsFilter.java:173)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.jira.startup.JiraStartupChecklistFilter.doFilter(JiraStartupChecklistFilter.java:75)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.multitenant.servlet.MultiTenantServletFilter.doFilter(MultiTenantServletFilter.java:91)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at com.atlassian.jira.web.filters.steps.ChainedFilterStepRunner.doFilter(ChainedFilterStepRunner.java:78)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:554)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
        at java.lang.Thread.run(Unknown Source)
Caused by: javax.script.ScriptException: com.atlassian.jira.exception.DataAccessException: com.atlassian.jira.jql.parser.JqlParseException: com.atlassian.jira.jql.parser.antlr.RuntimeRecognitionException: MismatchedSetException(123!=null)
        at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:318)
        at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:111)
        ... 152 more
Caused by: com.atlassian.jira.exception.DataAccessException: com.atlassian.jira.jql.parser.JqlParseException: com.atlassian.jira.jql.parser.antlr.RuntimeRecognitionException: MismatchedSetException(123!=null)
        at com.atlassian.jira.issue.search.OfBizSearchRequestStore.getSearchQueryFromGv(OfBizSearchRequestStore.java:419)
        at com.atlassian.jira.issue.search.OfBizSearchRequestStore.convertGVToRequest(OfBizSearchRequestStore.java:393)
        at com.atlassian.jira.issue.search.OfBizSearchRequestStore.getSearchRequest(OfBizSearchRequestStore.java:206)
        at com.atlassian.jira.issue.search.CachingSearchRequestStore.getSearchRequest(CachingSearchRequestStore.java:186)
        at com.atlassian.jira.issue.search.DefaultSearchRequestManager.getSearchRequestOwnerUserName(DefaultSearchRequestManager.java:142)
        at com.atlassian.jira.bc.filter.DefaultSearchRequestService.validateForDelete(DefaultSearchRequestService.java:297)
        at com.atlassian.jira.bc.filter.DefaultSearchRequestService.deleteFilter(DefaultSearchRequestService.java:125)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoCachedMethodSiteNoUnwrap.invoke(PojoMetaMethodSite.java:210)
        at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:53)
        at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:120)
        at Script5.run(Script5.groovy:29)
        at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:315)
        ... 153 more
Caused by: com.atlassian.jira.jql.parser.JqlParseException: com.atlassian.jira.jql.parser.antlr.RuntimeRecognitionException: MismatchedSetException(123!=null)
        at com.atlassian.jira.jql.parser.DefaultJqlQueryParser.parseClause(DefaultJqlQueryParser.java:132)
        at com.atlassian.jira.jql.parser.DefaultJqlQueryParser.parseQuery(DefaultJqlQueryParser.java:34)
        at com.atlassian.jira.issue.search.OfBizSearchRequestStore.getSearchQueryFromGv(OfBizSearchRequestStore.java:414)
        ... 168 more
Caused by: com.atlassian.jira.jql.parser.antlr.RuntimeRecognitionException: MismatchedSetException(123!=null)
        at com.atlassian.jira.jql.parser.antlr.LexerErrorHelper.handleError(LexerErrorHelper.java:55)
        at com.atlassian.jira.jql.parser.antlr.JqlLexer.recover(JqlLexer.java:129)
        at com.atlassian.jira.jql.parser.antlr.JqlLexer.recover(JqlLexer.java:135)
        at com.atlassian.jira.jql.parser.antlr.JqlLexer.mERROR_RESERVED(JqlLexer.java:1986)
        at com.atlassian.jira.jql.parser.antlr.JqlLexer.mTokens(JqlLexer.java:2738)
        at org.antlr.runtime.Lexer.nextToken(Lexer.java:84)
        at org.antlr.runtime.CommonTokenStream.fillBuffer(CommonTokenStream.java:95)
        at org.antlr.runtime.CommonTokenStream.LT(CommonTokenStream.java:238)
        at com.atlassian.jira.jql.parser.antlr.JqlParser.query(JqlParser.java:202)
        at com.atlassian.jira.jql.parser.DefaultJqlQueryParser.parseClause(DefaultJqlQueryParser.java:121)
        ... 170 more
Caused by: MismatchedSetException(123!=null)
        at com.atlassian.jira.jql.parser.antlr.JqlLexer.recover(JqlLexer.java:134)
        ... 177 more

5 answers

1 accepted

That does help insofar as deleting the corrupt filters does get rid of all the errors. I was hoping to not having to delete the filters though as there's quite a few of them.

So glad to see when someone is giving a link that requires you to login.

Hello Sorin Sbârnea (CITRIX) SORIN Listen to me, I extracted the information from customware.net website, I posted the actual instructions to remove another users filters. Follow those instructions or ask your MySQL database administrator to help you. Its very easy, goto the Database of your Jira instance or Confluence and delete the entries for the filters you want removed. Okay if you dont want to perform the mysql fix then you can purchase a plugin called SU - Switch user plugin, it allows you to login as another user and use there profile. These two solutions should suffice for fixing filters. READ BELOW FOR ACTUAL STEPS Deletion of the offending filters and subscriptions will need to be done at the database level. We cannot specifically endorse any activity at the database level, but simply provide this FAQ if you're desperate to remove the filters. <colgroup><col width="24"><col></colgroup> Before following these instructions, always backup your JIRA instance. Firstly, there are two outstanding feature requests for these features to be added in JIRA - JRA-9997 and JRA-6561. If you are patient, the good folks at Atlassian are likely to introduce this functionality. If you are confident with direct database access though, the relevant tables are searchrequest for the filters, and filtersubscription for the subscriptions. A foreign key (although JIRA's database structure doesn't enforce this) connects the id field in searchrequest with filter_i_d in filtersubscription. If you are using PostgreSQL, you can execute the following SQL to show the details of the filters and subscriptions: select searchrequest.id as filterid, filtername, authorname, filtersubscription.username as subscriptionauthorname from searchrequest left outer join filtersubscription on (searchrequest.id = filtersubscription.filter_i_d) If I wanted to delete Johnny's subscription in this example, I would simply execute the query: delete from filtersubscription where username = 'johnny.tu' and filter_i_d = 10083 If we wanted to take this one step further to remove all of Johnny's subscriptions, we would execute the query: delete from filtersubscription where username = 'johnny.tu' If I need to delete a specific filter created by a user, this is a two step process. I would execute the following queries, where "id" is replaced by the ID of the filter (the "filterid" column if you executed the query shown above). No username is necessary in this case, since each filter has a unique ID. delete from filtersubscription where filter_i_d = 10081 delete from searchrequest where id = 10081 In this case the filter "Sessions delivered" will be deleted, along with all associated subscriptions. We need to execute both queries since JIRA does not enfore foreign key constraints, and to ensure that the subscription does not try to run on a filter that no longer exists. Alternatively, to delete all of the filters created by Johnny, we would remove all subscriptions using the method to delete all subscriptions shown above, then execute the query: delete from searchrequest where authorname = 'johnny.tu' After executing your SQL, no JIRA restart is required. It might be worth opening the JIRA Administration page and running the Integrity Checker though. The check to execute is "Check for invalid filter subscriptions". If the above commands were executed correctly, there should be no errors reported by the Integrity Checker. <input id="p18u9tskaac0tkjc197k1cfe9rf0_html5" style="font-size: 999px; position: absolute; width: 100%; height: 100%;" type="file" accept=""/>

0 vote

You can check for invalid filters and filter subscriptions using Integrity checker. Might help.

Check for Search Requests and invalid filter subscriptions.

Thanks for the suggestion but the integrity checker finds no problems.

Hi,

I did end up deleting all the corrupt filters in the database. I do however feel that not being able to retrieve (or delete) search requests with faulty jql syntax from the database is a bit of a bug.

How to delete filters and subscriptions added by other users


Question

As of JIRA 4.0, the product does not support modification of filters or subscriptions created by other users. This causes a problem when users are no longer active in your JIRA instance (for whatever reason), but their subscriptions are still active.

Answer

Deletion of the offending filters and subscriptions will need to be done at the database level. We cannot specifically endorse any activity at the database level, but simply provide this FAQ if you're desperate to remove the filters.

<colgroup><col width="24"><col></colgroup>

Before following these instructions, always backup your JIRA instance.

Firstly, there are two outstanding feature requests for these features to be added in JIRA - JRA-9997 and JRA-6561. If you are patient, the good folks at Atlassian are likely to introduce this functionality.

If you are confident with direct database access though, the relevant tables are searchrequest for the filters, and filtersubscription for the subscriptions. A foreign key (although JIRA's database structure doesn't enforce this) connects the id field in searchrequest with filter_i_d in filtersubscription.

If you are using PostgreSQL, you can execute the following SQL to show the details of the filters and subscriptions:

select
searchrequest.id as filterid,
filtername,
authorname,
filtersubscription.username as subscriptionauthorname
from
searchrequest
left outer join filtersubscription
on (searchrequest.id = filtersubscription.filter_i_d)

If I wanted to delete Johnny's subscription in this example, I would simply execute the query:

delete from filtersubscription where username = 'johnny.tu' and filter_i_d = 10083

If we wanted to take this one step further to remove all of Johnny's subscriptions, we would execute the query:

delete from filtersubscription where username = 'johnny.tu'

If I need to delete a specific filter created by a user, this is a two step process. I would execute the following queries, where "id" is replaced by the ID of the filter (the "filterid" column if you executed the query shown above). No username is necessary in this case, since each filter has a unique ID.

delete from filtersubscription where filter_i_d = 10081
delete from searchrequest where id = 10081

In this case the filter "Sessions delivered" will be deleted, along with all associated subscriptions. We need to execute both queries since JIRA does not enfore foreign key constraints, and to ensure that the subscription does not try to run on a filter that no longer exists.

Alternatively, to delete all of the filters created by Johnny, we would remove all subscriptions using the method to delete all subscriptions shown above, then execute the query:

delete from searchrequest where authorname = 'johnny.tu'

After executing your SQL, no JIRA restart is required. It might be worth opening the JIRA Administration page and running the Integrity Checker though. The check to execute is "Check for invalid filter subscriptions". If the above commands were executed correctly, there should be no errors reported by the Integrity Checker.

<input id="p18u9tskaac0tkjc197k1cfe9rf0_html5" style="font-size: 999px; position: absolute; width: 100%; height: 100%;" type="file" accept=""/>

How to delete filters and subscriptions added by other users


Question

As of JIRA 4.0, the product does not support modification of filters or subscriptions created by other users. This causes a problem when users are no longer active in your JIRA instance (for whatever reason), but their subscriptions are still active.

Answer

Deletion of the offending filters and subscriptions will need to be done at the database level. We cannot specifically endorse any activity at the database level, but simply provide this FAQ if you're desperate to remove the filters.

<colgroup><col width="24"><col></colgroup>

 

Before following these instructions, always backup your JIRA instance.

 

Firstly, there are two outstanding feature requests for these features to be added in JIRA - JRA-9997 and JRA-6561. If you are patient, the good folks at Atlassian are likely to introduce this functionality.

If you are confident with direct database access though, the relevant tables are searchrequest for the filters, and filtersubscription for the subscriptions. A foreign key (although JIRA's database structure doesn't enforce this) connects the id field in searchrequest with filter_i_d in filtersubscription.

If you are using PostgreSQL, you can execute the following SQL to show the details of the filters and subscriptions:

 

select
searchrequest.id as filterid,
filtername,
authorname,
filtersubscription.username as subscriptionauthorname
from
searchrequest
left outer join filtersubscription
on (searchrequest.id = filtersubscription.filter_i_d)

 


If I wanted to delete Johnny's subscription in this example, I would simply execute the query:

 

delete from filtersubscription where username = 'johnny.tu' and filter_i_d = 10083

 

If we wanted to take this one step further to remove all of Johnny's subscriptions, we would execute the query:

 

delete from filtersubscription where username = 'johnny.tu'

 

If I need to delete a specific filter created by a user, this is a two step process. I would execute the following queries, where "id" is replaced by the ID of the filter (the "filterid" column if you executed the query shown above). No username is necessary in this case, since each filter has a unique ID.

 

delete from filtersubscription where filter_i_d = 10081

 

 

delete from searchrequest where id = 10081

 

In this case the filter "Sessions delivered" will be deleted, along with all associated subscriptions. We need to execute both queries since JIRA does not enfore foreign key constraints, and to ensure that the subscription does not try to run on a filter that no longer exists.

Alternatively, to delete all of the filters created by Johnny, we would remove all subscriptions using the method to delete all subscriptions shown above, then execute the query:

 

delete from searchrequest where authorname = 'johnny.tu'

 

After executing your SQL, no JIRA restart is required. It might be worth opening the JIRA Administration page and running the Integrity Checker though. The check to execute is "Check for invalid filter subscriptions". If the above commands were executed correctly, there should be no errors reported by the Integrity Checker.

<input id="p18u9tskaac0tkjc197k1cfe9rf0_html5" style="font-size: 999px; position: absolute; width: 100%; height: 100%;" type="file" accept=""/>

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Monday in Jira Software

How large do you think Jira Software can grow?

Hi Atlassian Community! My name is Shana, and I’m on the Jira Software team. One of the many reasons this Community exists is to connect you to others on similar product journeys or with comparabl...

709 views 6 13
Read article

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you