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

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)
      

      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.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: