Uploaded image for project: 'SonarQube'
  1. SonarQube
  2. SONAR-12464

Index drop should not fail when index does not exist

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Database
    • Labels:
      None
    • Edition:
      Community
    • Production Notes:
      None

      Description

      WHY

      Some DB migrations are dropping an index.

      Eg. the index is now useless or it should be modified or one or more of its underlying column need to be changed.

      On some occasions (see links community reports), a DB migration may be dropping an index which does not exist or does not exist anymore (a DB migration could have been partially executed or the DB been tempered with).

      Moreover, a missing index will prevent the DB migration to pass and the only workaround is to recreate the index. This is unfortunate as the DB is already in the expected state.

      WHAT

      The Drop index statement generated by SQ DB migration framework is reentrant on H2 and Postgres thanks to the IF EXISTS  clause. No similar clause is used on MsSQL nor Oracle.

      On MsSQL, IF EXIST clause was added with SQLServer 2016. We currently still support SQLServer 2014.

      Oracle doesn't provide the IF EXIST clause.

      HOW

      Options:

      1. capture the specific exception raised when index does not exist and ignore it
      2. use DB specific scripts (eg. on [Oracle|https://stackoverflow.com/questions/2722630/oracle-drop-index-if-exists], ...)

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              sebastien.lesaint Sebastien Lesaint
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: