500 error or JQL query exception while trying to access Jira search filter

Summary

Specific filter(s) cannot be accessed, edited, or removed. 500 error may be received while trying to complete these actions.

Diagnosis

❌ If the filter can be accessed or manipulated at all through the interface or the API, then this article does not apply.

✔️ If accessing the filter in the interface or via the Jira API causes a 500 error, then correcting the problem via the database, as below, will be required.

Cause

Retrieval and parsing of the filter's JQL is failing because of some unexpected syntax. Jira's filter loading is not designed to parse certain syntaxes that shouldn't be in use in the database. If such a string is in the database, it will need to be removed via the database; the API and interface will throw 500 errors. Such data could be put in the database a variety of ways, but determining how this happened after the fact is generally not possible.

Solution

⚠️ Resolving this problem involves editing the database live. A backup of the Jira database should be made before attempting this process.

⚠️ These queries were written for postgres and may need to be edited to work in other database systems.

1. Find the ID of the filter that is causing the problem:

   1. If the error occurs when loading a specific filter, the ID will be in the URL.

      1. In this URL https://jira.atlassian.com/browse/JSWSERVER-2785?filter=98707 the filter's id is 98707.

   2. If a page with multiple filters throws this error, or there are too many affected filters, the id(s) of the problematic filter(s) can be found in the atlassian-jira.log file, in lines similar to this:

      1. 1 A JQL query exception was thrown parsing, error loading search request with id '98707' and name 'My Broken Filter' owned by 'USER1000' query 'project = JSWSERVER AND customfield_1000 = "broken syntax, maybe with "quotes", maybe (parens) or semicolon; maybe something else" ORDER BY status ASC, priority DESC'

2. Confirm the contents of the database using the id:

   1. 1 SELECT * FROM searchrequest WHERE id = 98707;

3. Update the reqcontent field, which contains the JQL query:

   1. 1 UPDATE searchrequest SET reqcontent = 'project = abc' WHERE id = 98707;

      ℹ️ If a specific syntactical problem is known, the reqcontent string can be corrected, but it may be safer to insert the simplest possible valid query, and perform other corrections or edits through supported methods afterwards.

      ⚠️DO NOT REMOVE RECORDS from the searchrequest table. This can cause orphaned references elsewhere. Instead, correct the syntax, and then use the interface or API to remove records.

ℹ️ Unlike most edits to the Jira database, this edit will take effect immediately and no restart is required.