You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@sentry.apache.org by "Sergio Peña (JIRA)" <ji...@apache.org> on 2017/12/08 16:38:00 UTC
[jira] [Resolved] (SENTRY-2011) Oracle does not allow creating more
than one index on the same column
[ https://issues.apache.org/jira/browse/SENTRY-2011?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sergio Peña resolved SENTRY-2011.
---------------------------------
Resolution: Duplicate
> 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
> Fix For: 2.0.0
>
> Attachments: SENTRY-2011.001.patch
>
>
> 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--------------
> {code}
> 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)
> {code}
> 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.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)