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

Service Desk returns an error when searching for Customers within a project that has 65536+ organizations

    • Icon: Bug Bug
    • Resolution: Unresolved
    • Icon: Low Low
    • None
    • 3.10.0
    • Customer Organisation

      Issue description

      In a Service Desk instance connected to a Postgres database, if a project contains more 65536 than organizations, when accessing the project customer page, the following error is shown in the UI:

      And the following error is seen in the logs:

      2018-02-12 21:15:28,621 http-nio-8080-exec-2 ERROR admin 1275x412x3 iwikaq 127.0.0.1 /rest/servicedesk/1/customer/participants/SDD-42/search [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: Caught PSQLException for select "AO_54307E_ORGANIZATION_MEMBER"."USER_KEY" from "public"."AO_54307E_ORGANIZATION_MEMBER" "AO_54307E_ORGANIZATION_MEMBER" inner join "public"."AO_54307E_ORGANIZATION" "AO_54307E_ORGANIZATION" on "AO_54307E_ORGANIZATION"."ID" = "AO_54307E_ORGANIZATION_MEMBER"."ORGANIZATION_ID" where "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?)
      com.querydsl.core.QueryException: Caught PSQLException for select "AO_54307E_ORGANIZATION_MEMBER"."USER_KEY" from "public"."AO_54307E_ORGANIZATION_MEMBER" "AO_54307E_ORGANIZATION_MEMBER" inner join "public"."AO_54307E_ORGANIZATION" "AO_54307E_ORGANIZATION" on "AO_54307E_ORGANIZATION"."ID" = "AO_54307E_ORGANIZATION_MEMBER"."ORGANIZATION_ID" where "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?) or "AO_54307E_ORGANIZATION"."ID" in (?, ..., ?)
      	at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
      	at com.querydsl.sql.Configuration.translate(Configuration.java:459)
      	at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:502)
      [...]
      Caused by: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
      	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:315)
      	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
      	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
      	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
      	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:116)
      	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
      	at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:446)
      	... 356 more
      Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 80472
      	at org.postgresql.core.PGStream.sendInteger2(PGStream.java:211)
      	at org.postgresql.core.v3.QueryExecutorImpl.sendParse(QueryExecutorImpl.java:1409)
      	at org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:1729)
      	at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1294)
      	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:280)
      

      Environment

      • 65k organizations
      • PostgreSQL database

      Steps to reproduce

      • Configure JIRA to use PosgreSQL database
      • Create a service desk project
      • Create more 65536 than organizations
      • Set customer permissions to raise requests to "Customers who are added to the project".
      • Go to the Customer page and wait for a while

      Expected Result

      The list of customers should be displayed

      Actual Result

      The search eventually times out and the following error is shown in the UI:

      Workaround

      Enable the sd.use.search.by.permissions.disabled dark feature, by following the steps below:

      • Navigate to following address: http(s)://<jira_base_url>/secure/SiteDarkFeatures!default.jspa
      • Add the following flag and Enable:
        sd.use.search.by.permissions.disabled
        

        1. Customers_Errors.png
          126 kB
          Julien Rey

            [JSDSERVER-5666] Service Desk returns an error when searching for Customers within a project that has 65536+ organizations

            Konrad Garus added a comment - - edited

            (I reported this via support portal)

            From the outside, it sounds like something that could be solved programmatically, replacing the single statement with a loop if there is more than 65k values.

            Actually, it looks like a possible performance issue anyway, when loading a paginated search showing up to 50 results involves loading all the entities. Perhaps the query should not even be happening in the first place?

            Konrad Garus added a comment - - edited (I reported this via support portal) From the outside, it sounds like something that could be solved programmatically, replacing the single statement with a loop if there is more than 65k values. Actually, it looks like a possible performance issue anyway, when loading a paginated search showing up to 50 results involves loading all the entities. Perhaps the query should not even be happening in the first place?

              Unassigned Unassigned
              jrey Julien Rey
              Affected customers:
              5 This affects my team
              Watchers:
              5 Start watching this issue

                Created:
                Updated: