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

XMLWordPrintable

    • Icon: Bug Report Bug Report
    • Resolution: Fixed
    • Icon: L3 - Default L3 - Default
    • 7.8.0, 7.7.4, 7.8.0-alpha4
    • 7.7.x, 7.8.0-alpha3
    • engine

      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

        This is the controller panel for Smart Panels app

            [CAM-8149] On oracle it is not possible to cascade delete deployment with more than 1000 historic instances

            Roman Smirnov added a comment -

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

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

            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.

            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.

            Christopher Kujawa added a comment -

            You can also do OR IN without introducing a subquery.

            Christopher Kujawa added a comment - You can also do OR IN without introducing a subquery.

              svetlana.dorokhova Svetlana Dorokhova
              christopher.zell Christopher Kujawa
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Created:
                Updated:
                Resolved: