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

Query authorization check uses IN condition that performs poorly on MySQL



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


      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_
                    <if test="authCheck.isHistoricInstancePermissionsEnabled">
                        AUTH.RESOURCE_ID_ = RES.ID_
                    AUTH.RESOURCE_ID_ = '*')
                <if test="DB_TYPE != 'mysql">
                  AUTH.RESOURCE_ID_ in (
                  <if test="authCheck.isHistoricInstancePermissionsEnabled">
                  RES.PROC_DEF_KEY_, '*'))

      Hints (optional):

      The mapping needs to be adjusted for CE and EE.


        This is the controller panel for Smart Panels app


          1. explain-in-strategy.html
            4 kB
            Denis Giovan Marques
          2. explain-multiples-joins-strategy.html
            6 kB
            Denis Giovan Marques
          3. explain-or-strategy.html
            4 kB
            Denis Giovan Marques
          4. image-2021-11-08-15-51-28-304.png
            86 kB
            Thomas Winklmüller
          5. image-2021-11-08-15-52-50-167.png
            53 kB
            Thomas Winklmüller
          6. image-2021-11-08-15-53-30-699.png
            53 kB
            Thomas Winklmüller
          7. image-2021-11-08-15-54-26-191.png
            78 kB
            Thomas Winklmüller



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