Summary:

JIRA Service Desk unable to process the emails due to the error below:

2016-03-15 23:05:36,158 ERROR [] Caesium-1-2 ServiceRunner     MailJobRunner Failed
com.mysema.query.QueryException: Caught SQLSyntaxErrorException for delete from "AO_2C4E5C_MAILITEMAUDIT" where "AO_2C4E5C_MAILITEMAUDIT"."MAIL_ITEM_ID" in (?, ?, ?, ?, ?,
....
Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440) [ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) [ojdbc6-11.2.0.2.0.jar:11.2.0.2.0]
	....

Cause:

• There is more than 1000 values on the IN query when DELETING rows to "AO_2C4E5C_MAILITEMAUDIT"

Expected Result:

• JIRA Service Desk should be able to run the query or at least adapt to it if the value is exceeding 1000.

Notes:

• JIRA Service Desk have a Cleaner Job to remove Rows that is old enough. It seems that due to Oracle have a limit for the IN value on one query, it reject the sql query if it exceed the limit, making JIRA Service Desk unable to remove the old Rows.
• So far, this makes JIRA Service Desk unable to process the emails from the mail server.

Workaround:

The workaround would need to do a direct modification towards the database. Please create the necessary backup before proceed on the steps below

1. The cleaner job will try to remove any entries that are 6 months older.
2. Generate the Epoch time for the date using http://www.epochconverter.com/
3. Run the following query to count how many rows that is older than 6 months:

   select count(*) from "AO_2C4E5C_MAILITEMAUDIT" WHERE "CREATED_TIMESTAMP" < <generated epoch time above>;
   

4. If the result is more than 1000, run the following query to remove the entries.

   DELETE FROM "AO_2C4E5C_MAILITEMAUDIT" WHERE "CREATED_TIMESTAMP" < <generated epoch time above>;
   DELETE FROM "AO_2C4E5C_MAILITEMCHUNK" WHERE "AO_2C4E5C_MAILITEMCHUNK"."MAIL_ITEM_ID" IN (SELECT "ID" from "AO_2C4E5C_MAILITEM" WHERE "UPDATED_TIMESTAMP" < <generated epoch time above>);
   DELETE FROM "AO_2C4E5C_MAILITEM" WHERE "UPDATED_TIMESTAMP" < <generated epoch time above>;
   

5. Restart JIRA