Uploaded image for project: 'Jira Service Management Data Center'
  1. Jira Service Management Data Center
  2. JSDSERVER-3584

ORA-01795: maximum number of expressions in a list is 1000

XMLWordPrintable

      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

          Form Name

            [JSDSERVER-3584] ORA-01795: maximum number of expressions in a list is 1000

            Owen made changes -
            Workflow Original: JSD Bug Workflow v5 - TEMP [ 2304681 ] New: JAC Bug Workflow v3 [ 3126266 ]
            Status Original: Done [ 10044 ] New: Closed [ 6 ]
            Owen made changes -
            Symptom Severity Original: Minor [ 14432 ] New: Severity 3 - Minor [ 15832 ]
            Katherine Yabut made changes -
            Workflow Original: JSD Bug Workflow v5 [ 2058589 ] New: JSD Bug Workflow v5 - TEMP [ 2304681 ]
            Katherine Yabut made changes -
            Workflow Original: JSD Bug Workflow v5 - TEMP [ 2056024 ] New: JSD Bug Workflow v5 [ 2058589 ]
            Katherine Yabut made changes -
            Workflow Original: JSD Bug Workflow v5 [ 1955495 ] New: JSD Bug Workflow v5 - TEMP [ 2056024 ]
            Katherine Yabut made changes -
            Workflow Original: JSD Bug Workflow v4 [ 1615382 ] New: JSD Bug Workflow v5 [ 1955495 ]
            Confluence Escalation Bot (Inactive) made changes -
            Labels New: affects-server
            Katherine Yabut made changes -
            Workflow Original: JSD Bug Workflow v2 [ 1601962 ] New: JSD Bug Workflow v4 [ 1615382 ]
            Owen made changes -
            Workflow Original: JSD Bug Workflow [ 1399085 ] New: JSD Bug Workflow v2 [ 1601962 ]
            Michelle Chin made changes -
            Description Original: h3. Summary:
            JIRA Service Desk unable to process the emails due to the error below:
            {noformat}
            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]
            ....
            {noformat}

            h3. Cause:
            * There is more than 1000 values on the IN query when DELETING rows to "AO_2C4E5C_MAILITEMAUDIT"

            h3. Expected Result:
            * JIRA Service Desk should be able to run the query or at least adapt to it if the value is exceeding 1000.

            h3. 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.

            h3. Workaround:
            (!) {color:red} The workaround would need to do a direct modification towards the database. Please create the necessary backup before proceed on the steps below{color}
            # The cleaner job will try to remove any entries that are 6 months older.
            # Generate the Epoch time for the date using http://www.epochconverter.com/
            # Run the following query to count how many rows that is older than 6 months:
            {code}
            select count(*) from "AO_2C4E5C_MAILITEMAUDIT" WHERE "CREATED_TIMESTAMP" < <generated epoch time above>;
            {code}
            # If the result is more than 1000, run the following query to remove the entries.
            {code}
            DELETE FROM "AO_2C4E5C_MAILITEMAUDIT" WHERE "CREATED_TIMESTAMP" < <generated epoch time above>;
            {code}
            # Restart JIRA             		New: h3. Summary:
            JIRA Service Desk unable to process the emails due to the error below:
            {noformat}
            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]
            ....
            {noformat}

            h3. Cause:
            * There is more than 1000 values on the IN query when DELETING rows to "AO_2C4E5C_MAILITEMAUDIT"

            h3. Expected Result:
            * JIRA Service Desk should be able to run the query or at least adapt to it if the value is exceeding 1000.

            h3. 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.

            h3. Workaround:
            (!) {color:red} The workaround would need to do a direct modification towards the database. Please create the necessary backup before proceed on the steps below{color}
            # The cleaner job will try to remove any entries that are 6 months older.
            # Generate the Epoch time for the date using http://www.epochconverter.com/
            # Run the following query to count how many rows that is older than 6 months:
            {code}
            select count(*) from "AO_2C4E5C_MAILITEMAUDIT" WHERE "CREATED_TIMESTAMP" < <generated epoch time above>;
            {code}
            # If the result is more than 1000, run the following query to remove the entries.
            {code}
            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>;
            {code}
            # Restart JIRA

              mmcmahon Matthew McMahon (Inactive)
              jrahmadiputra Julian (Inactive)
              Affected customers:
              1 This affects my team
              Watchers:
              4 Start watching this issue

                Created:
                Updated:
                Resolved: