sentry-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sergio Peña (JIRA) <j...@apache.org>
Subject [jira] [Reopened] (SENTRY-2011) Oracle does not allow creating more than one index on the same column
Date Fri, 08 Dec 2017 16:38:00 GMT

     [ https://issues.apache.org/jira/browse/SENTRY-2011?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Sergio Peña reopened SENTRY-2011:
---------------------------------

> 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&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)
> {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)

Mime
View raw message