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