[SENTRY-1066] Sentry oracle upgrade script failed with ORA-0955 duplicate name issue

• Type: Bug
• Status: Resolved
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: 1.5.1, 1.7.0
• Fix Version/s: 1.7.0
• Assignee: Anne Yu
• Reporter: Anne Yu
• Created: 15/Feb/16 06:46
• Updated: 11/Mar/16 07:17
• Resolved: 17/Feb/16 20:44

Description

We've seen ORA-0955 duplicate name issue while running Sentry upgrade scripts.

Running Oracle 11g. We eventually tracked it to /opt/cloudera/parcels/CDH/lib/sentry/scripts/sentrystore/upgrade/004-SENTRY-74.oracle.sql:

ALTER TABLE SENTRY_DB_PRIVILEGE DROP CONSTRAINT "SENTRY_DB_PRIV_PRIV_NAME_UNIQ";
ALTER TABLE SENTRY_DB_PRIVILEGE ADD CONSTRAINT "SENTRY_DB_PRIV_PRIV_NAME_UNIQ" UNIQUE ("SERVER_NAME","DB_NAME","TABLE_NAME","COLUMN_NAME","URI","ACTION","WITH_GRANT_OPTION");

Although constraint was dropped, it fails to re-add, as there was an implicit index added with same name as well (expected behavior according to docs: http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm See: "Unique Constraints").
Dropping the index allowed us to finish upgrading the schema successfully.