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

On oracle it is not possible to cascade delete deployment with more than 1000 historic instances

    Details

    • Type: Bug Report
    • Status: Closed
    • Priority: L3 - Default
    • Resolution: Fixed
    • Affects Version/s: 7.7.x, 7.8.0-alpha3
    • Fix Version/s: 7.8.0, 7.7.4, 7.8.0-alpha4
    • Component/s: engine
    • Labels:

      Description

      On oracle there is a limitation of maximum number of expressions in a list,
      which means it is not possible to have a IN expression with more then 1000 entries.

      Workaround would be to split the IN Expression and combine them with an or OR use a
      https://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-expressions-in-a-list-is

      Or to delete in batches.

      The specific bug on the engine is to delete a deployment that contains for example a decision definition with more than 1000 historic decision instances.
      This bug exist also for normal activity instances, case instances etc.

      Executing:

        <delete id="deleteHistoricDecisionInputInstanceByteArraysByDecisionInstanceIds">
          delete from ${prefix}ACT_GE_BYTEARRAY
          where ID_ in
            (select distinct BYTEARRAY_ID_
            from ${prefix}ACT_HI_DEC_IN
            where DEC_INST_ID_ in
                <foreach item="decisionInstanceId" index="index" collection="list" open="(" separator="," close=")">
                  #{decisionInstanceId}
                </foreach>
              and BYTEARRAY_ID_ is not NULL)
        </delete>
      

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

      Maybe it is possible to avoid the IN expressions in general.

      Note: We have to fix all deleteHistoric*By*Ids statements which contains IN expressions

        Issue Links

          Activity

          Hide
          roman.smirnov Smirnov Roman added a comment -

          We already solved a similar issue related to jobs with CAM-8004. Maybe the same fix can be applied here.

          Show
          roman.smirnov Smirnov Roman added a comment - We already solved a similar issue related to jobs with CAM-8004 . Maybe the same fix can be applied here.
          Hide
          tassilo.weidner Tassilo Weidner added a comment -

          ORA-01795: maximum number of expressions in a list is 1000
          This limitation appears only for explicit listed expressions. A subquery introducing in clause is not affected by this limitation.

          Show
          tassilo.weidner Tassilo Weidner added a comment - ORA-01795: maximum number of expressions in a list is 1000 This limitation appears only for explicit listed expressions. A subquery introducing in clause is not affected by this limitation.
          Hide
          christopher.zell Christopher Zell added a comment -

          You can also do OR IN without introducing a subquery.

          Show
          christopher.zell Christopher Zell added a comment - You can also do OR IN without introducing a subquery.

            People

            • Assignee:
              svetlana.dorokhova Svetlana Dorokhova
              Reporter:
              christopher.zell Christopher Zell
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development