[
https://issues.apache.org/jira/browse/SENTRY-2011?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Na Li updated SENTRY-2011:
--------------------------
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--------------
{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.
was:
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)
> 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
> 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)
