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

XMLWordPrintable

    • 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
          Customers_Errors.png
          126 kB

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

            SET Analytics Bot made changes -
            Support reference count Original: 8 New: 9
            Marc Dacanay made changes -
            Labels Original: cqt New: cqt ril
            Marc Dacanay made changes -
            Remote Link New: This issue links to "Internal ticket (Web Link)" [ 955056 ]
            SET Analytics Bot made changes -
            UIS Original: 1 New: 0
            Patrick Turbett made changes -
            Remote Link Original: This issue links to "JSDS-4661 (Bulldog)" [ 437474 ] New: This issue links to "JSMDC-4661 (JIRA Server (Bulldog))" [ 437474 ]
            SET Analytics Bot made changes -
            UIS Original: 0 New: 1
            SET Analytics Bot made changes -
            UIS Original: 1 New: 0
            Marko Filipan made changes -
            Remote Link New: This issue links to "Page (Confluence)" [ 685013 ]
            SET Analytics Bot made changes -
            UIS Original: 6 New: 1
            Bugfix Automation Bot made changes -
            Support reference count Original: 7 New: 8

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

                Created:
                Updated: