Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-14092

Query authorization check uses IN condition that performs poorly on MySQL

    XMLWordPrintable

Details

    • Bug Report
    • Resolution: Fixed
    • L3 - Default
    • 7.17.0, 7.16.5, 7.17.0-alpha4
    • 7.15.6
    • engine
    • None

    Description

      Environment (Required on creation):  MySQL

      Description (Required on creation; please attach any relevant screenshots, stacktraces, log files, etc. to the ticket):

      MySQL chooses a complete range scan when using the IN operator when joining the authorization table on specific values. This can lead to very slow historic process instance queries.

      Steps to reproduce (Required on creation): 

      Reproduction is not easy. See the linked support case for more information.

      Observed Behavior (Required on creation):

      Joining the authorization table on specific values can be slow due to using the IN operator in the ON clause. This slows down Cockpit.

      Expected behavior (Required on creation):

      We use the OR operator instead of IN for joining the authorization table on specific values in historic process instance queries on MySQL systems. This should increase performance in some MySQL environments since a join buffer is used.

      Root Cause (Required on prioritization):

      Solution Ideas (Optional):

      Change the mapping files in CE and EE to use OR operators instead of IN on MySQL only.

      Since the slow part is not part of a top level statement we can not override it with DB-specific statements. We need to find out what DB system is currently used. This could be done like

      • https://mybatis.org/mybatis-3/dynamic-sql.html#Multi-db_vendor_support
      • Create MySQL-specific statements for each statement that includes the slow query. Set a property (DB_TYPE) in each of the statements so we know if we are using MySQL or not.
                AUTH ON (
                
                <if test="DB_TYPE == 'mysql'">
                  AUTH.RESOURCE_ID_ = RES.PROC_DEF_KEY_
                    OR
                    <if test="authCheck.isHistoricInstancePermissionsEnabled">
                        AUTH.RESOURCE_ID_ = RES.ID_
                        OR
                    </if>
                    AUTH.RESOURCE_ID_ = '*')
                </if>
                <if test="DB_TYPE != 'mysql">
                  AUTH.RESOURCE_ID_ in (
                  <if test="authCheck.isHistoricInstancePermissionsEnabled">
                    RES.ID_,
                  </if>
                  RES.PROC_DEF_KEY_, '*'))
                </if>
        

      Hints (optional):

      The mapping needs to be adjusted for CE and EE.

      mgm-controller-panel

        This is the controller panel for Smart Panels app

        Attachments

          Activity

            People

              Unassigned Unassigned
              scott.ghidiu Scott Ghidiu
              Tobias Metzke-Bernstein Tobias Metzke-Bernstein
              Tassilo Weidner Tassilo Weidner
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Salesforce