Uploaded image for project: 'Sentry (Retired)'
  1. Sentry (Retired)
  2. SENTRY-2010

Oracle does not allow creating more than one index on the same column

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 2.0.0
    • Fix Version/s: 2.0.0
    • Component/s: Sentry
    • Labels:
      None

      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)

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                linaataustin Na Li
                Reporter:
                linaataustin Na Li
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: