Na Li created SENTRY-2011:
-----------------------------
Summary: Oracle does not allow creating more than one index on the
same column
Key: SENTRY-2011
URL: https://issues.apache.org/jira/browse/SENTRY-2011
Project: Sentry
Issue Type: Bug
Components: Sentry
Affects Versions: 2.0.0
Reporter: Na Li
Assignee: Na Li
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×tamp=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)
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
