[SENTRY-2011] 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:57
• Updated: 08/Dec/17 16:37
• Resolved: 08/Dec/17 16:37

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)

Many of the index names in package.jdo for dataNucleus are different from the corresponding index names in sql script.

If dataNucleus is configured to add schema info into DB, it will result in creating additional index for the same purpose in some DB (like MySql), or cause exception for other DB (like Oracle). It is better to keep the index name in package.jdo the same as what's in sql script. So only one index is created in DB.

Issue Links

• duplicates: SENTRY-2010 Oracle does not allow creating more than one index on the same column
• is duplicated by: SENTRY-2010 Oracle does not allow creating more than one index on the same column
• is related to: SENTRY-2041 Change Index name in Package.jdo to match index name in SQL
• links to: Review Link for this case