[SENTRY-2010] Oracle does not allow creating more than one index on the same column

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 2.0.0
• Fix Version/s: 2.0.0
• Component/s: Sentry
• Assignee: Na Li
• Reporter: Na Li
• Created: 18/Oct/17 17:54
• Updated: 15/Nov/17 21:17
• Resolved: 15/Nov/17 21:17

Description

When running sentry on Oracle DB with dataNucleus 4, we see a lot of exceptions that the column list already indexed when adding unique index.

I suspect the oracle sql command failure is caused by the fact that
1) Oracle does not allow indexing the same column twice. "Cause: You tried to create an index on a set of columns in a table, but you've already indexed this set of columns." based on https://www.techonthenet.com/oracle/errors/ora01408.php. However, MySql allows this
2) Sentry defined the unique index for column "USER_NAME" in table "SENTRY_USER" at sentry-oracle-2.0.0.sql, so a unique index will be created when this sql runs
3) Sentry specifies the field "roleName" as unique in table "MSentryRole" (which corresponds to DB column "USER_NAME" in DB table "SENTRY_USER" in dataNucleus configuration package.jdo. Therefore datanucleus tries to add another unique index to column "USER_NAME" in table "SENTRY_USER" when dataNucleus runs. Oracle throws exception since a unique index is already created by sql script.

The reason it does not show in previous sentry version is because sentry was using datanucleus 3 before. Now, in sentry 2.0, sentry moves up to dataNucleus 4, and dataNucleus 4 verifies the data and creates indexes, and therefore triggers the issue above.

The fix is to remove the unique index in datanucleus.

call stack below--------------
sentry has serious issue with database when creating unique index
Error : 1408, Position : 53, Sql = CREATE UNIQUE INDEX SENTRY_USER_NAME ON SENTRY_USER (USER_NAME), OriginalSql = CREATE UNIQUE INDEX SENTRY_USER_NAME ON SENTRY_USER (USER_NAME), Error Msg = ORA-01408: such column list already indexed
http://shardy-qainfra-3730-c6-hs2-sentry-1.vpc.cloudera.com:7180/cmf/process/all/logs/context?path=%2Fvar%2Flog%2Fsentry%2Fhadoop-cmf-SENTRY-1-SENTRY_SERVER-shardy-qainfra-3730-c6-hs2-sentry-1.vpc.cloudera.com.log.out&roleId=13&host=shardy-qainfra-3730-c6-hs2-sentry-1.vpc.cloudera.com&port=9000&timestamp=1508342110946
8:52:07.632 AM ERROR Datastore
An exception was thrown while adding/validating class(es) : ORA-01408: such column list already indexed
java.sql.SQLException: ORA-01408: such column list already indexed
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:896)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1737)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1692)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:300)
at com.jolbox.bonecp.StatementHandle.execute(StatementHandle.java:300)
at org.datanucleus.store.rdbms.table.AbstractTable.executeDdlStatement(AbstractTable.java:879)
at org.datanucleus.store.rdbms.table.TableImpl.createIndices(TableImpl.java:640)
at org.datanucleus.store.rdbms.table.TableImpl.validateIndices(TableImpl.java:587)
at org.datanucleus.store.rdbms.table.TableImpl.validateConstraints(TableImpl.java:395)
at org.datanucleus.store.rdbms.table.ClassTable.validateConstraints(ClassTable.java:3514)
at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.performTablesValidation(RDBMSStoreManager.java:3449)
at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.run(RDBMSStoreManager.java:2877)
at org.datanucleus.store.rdbms.AbstractSchemaTransaction.execute(AbstractSchemaTransaction.java:119)
at org.datanucleus.store.rdbms.RDBMSStoreManager.manageClasses(RDBMSStoreManager.java:1608)
at org.datanucleus.store.rdbms.RDBMSStoreManager.getDatastoreClass(RDBMSStoreManager.java:671)
at org.datanucleus.store.rdbms.query.RDBMSQueryUtils.getStatementForCandidates(RDBMSQueryUtils.java:425)
at org.datanucleus.store.rdbms.query.JDOQLQuery.compileQueryFull(JDOQLQuery.java:864)
at org.datanucleus.store.rdbms.query.JDOQLQuery.compileInternal(JDOQLQuery.java:346)
at org.datanucleus.store.query.Query.executeQuery(Query.java:1805)
at org.datanucleus.store.query.Query.executeWithArray(Query.java:1733)
at org.datanucleus.store.query.Query.execute(Query.java:1715)
at org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:371)
at org.datanucleus.api.jdo.JDOQuery.execute(JDOQuery.java:213)
at org.apache.sentry.provider.db.service.persistent.SentryStore.getAllRoles(SentryStore.java:319)
at org.apache.sentry.provider.db.service.persistent.SentryStore.access$1600(SentryStore.java:121)
at org.apache.sentry.provider.db.service.persistent.SentryStore$33.execute(SentryStore.java:1761)
at org.apache.sentry.provider.db.service.persistent.SentryStore$33.execute(SentryStore.java:1755)

Issue Links

• duplicates: SENTRY-2011 Oracle does not allow creating more than one index on the same column
• is duplicated by: SENTRY-2011 Oracle does not allow creating more than one index on the same column