Mailing-List: contact issues-help@sentry.apache.org; run by ezmlm
Precedence: bulk
Date: Fri, 8 Dec 2017 16:38:00 +0000 (UTC)
From: =?utf-8?Q?Sergio_Pe=C3=B1a_=28JIRA=29?= <jira@apache.org>
To: issues@sentry.apache.org
Message-ID: <JIRA.13110367.1508349448000.419038.1512751080170@Atlassian.JIRA>
In-Reply-To: <JIRA.13110367.1508349448000@Atlassian.JIRA>
References: <JIRA.13110367.1508349448000@Atlassian.JIRA> <JIRA.13110367.1508349448430@jira-lw-us.apache.org>
Subject: [jira] [Reopened] (SENTRY-2011) Oracle does not allow creating more
 than one index on the same column
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
archived-at: Fri, 08 Dec 2017 16:38:07 -0000


     [ https://issues.apache.org/jira/browse/SENTRY-2011?page=3Dcom.atlassi=
an.jira.plugin.system.issuetabpanels:all-tabpanel ]

Sergio Pe=C3=B1a 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 exce=
ptions 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 trie=
d to create an index on a set of columns in a table, but you've already ind=
exed this set of columns." based on https://www.techonthenet.com/oracle/err=
ors/ora01408.php. However, MySql allows this
> 2) Sentry defined the unique index for column "USER_NAME" in table "SENTR=
Y_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 d=
ataNucleus configuration package.jdo. Therefore datanucleus tries to add an=
other unique index to column "USER_NAME" in table "SENTRY_USER" when dataNu=
cleus 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 data=
Nucleus 4, and dataNucleus 4 verifies the data and creates indexes, and the=
refore 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 =3D CREATE UNIQUE INDEX SENTRY_USER_NAME=
 ON SENTRY_USER (USER_NAME), OriginalSql =3D CREATE UNIQUE INDEX SENTRY_USE=
R_NAME ON SENTRY_USER (USER_NAME), Error Msg =3D ORA-01408: such column lis=
t already indexed
> http://shardy-qainfra-3730-c6-hs2-sentry-1.vpc.cloudera.com:7180/cmf/proc=
ess/all/logs/context?path=3D%2Fvar%2Flog%2Fsentry%2Fhadoop-cmf-SENTRY-1-SEN=
TRY_SERVER-shardy-qainfra-3730-c6-hs2-sentry-1.vpc.cloudera.com.log.out&rol=
eId=3D13&host=3Dshardy-qainfra-3730-c6-hs2-sentry-1.vpc.cloudera.com&port=
=3D9000&timestamp=3D1508342110946
> 8:52:07.632 AM=09ERROR=09Datastore=09
> An exception was thrown while adding/validating class(es) : ORA-01408: su=
ch 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(OracleStatemen=
t.java:1119)
> at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.jav=
a:1737)
> at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1692)
> at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapp=
er.java:300)
> at com.jolbox.bonecp.StatementHandle.execute(StatementHandle.java:300)
> at org.datanucleus.store.rdbms.table.AbstractTable.executeDdlStatement(Ab=
stractTable.java:879)
> at org.datanucleus.store.rdbms.table.TableImpl.createIndices(TableImpl.ja=
va:640)
> at org.datanucleus.store.rdbms.table.TableImpl.validateIndices(TableImpl.=
java:587)
> at org.datanucleus.store.rdbms.table.TableImpl.validateConstraints(TableI=
mpl.java:395)
> at org.datanucleus.store.rdbms.table.ClassTable.validateConstraints(Class=
Table.java:3514)
> at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.performTables=
Validation(RDBMSStoreManager.java:3449)
> at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.run(RDBMSStor=
eManager.java:2877)
> at org.datanucleus.store.rdbms.AbstractSchemaTransaction.execute(Abstract=
SchemaTransaction.java:119)
> at org.datanucleus.store.rdbms.RDBMSStoreManager.manageClasses(RDBMSStore=
Manager.java:1608)
> at org.datanucleus.store.rdbms.RDBMSStoreManager.getDatastoreClass(RDBMSS=
toreManager.java:671)
> at org.datanucleus.store.rdbms.query.RDBMSQueryUtils.getStatementForCandi=
dates(RDBMSQueryUtils.java:425)
> at org.datanucleus.store.rdbms.query.JDOQLQuery.compileQueryFull(JDOQLQue=
ry.java:864)
> at org.datanucleus.store.rdbms.query.JDOQLQuery.compileInternal(JDOQLQuer=
y.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.getAllRol=
es(SentryStore.java:319)
> at org.apache.sentry.provider.db.service.persistent.SentryStore.access$16=
00(SentryStore.java:121)
> at org.apache.sentry.provider.db.service.persistent.SentryStore$33.execut=
e(SentryStore.java:1761)
> at org.apache.sentry.provider.db.service.persistent.SentryStore$33.execut=
e(SentryStore.java:1755)
> {code}
> Many of the index names in package.jdo for dataNucleus are different from=
 the corresponding index names in sql script.=20
> If dataNucleus is configured to add schema info into DB, it will result i=
n creating additional index for the same purpose in some DB (like MySql), o=
r cause exception for other DB (like Oracle). It is better to keep the inde=
x name in package.jdo the same as what's in sql script. So only one index i=
s created in DB.=20


--
This message was sent by Atlassian JIRA
(v6.4.14#64029)