Create index error while building OFBiz's db in Oracle

[Jacopo Cappellato]

Today I got the following error while running "ant run-install-seed":

CREATE INDEX AFFILIATE_PARTY ON OFBIZSCHEMA.AFFILIATE (PARTY_ID)

Error: An index already exists for this list of columns
The db is Oracle and with trunk OFBiz.

Any ideas?

Jacopo

[David E Jones]

Have you checked to see if this is an auto-index from a foreign key definition or a manually defined one?

My guess is the first and there are just two FKs that are very similar, in which case the auto index name creation needs to be more intelligent or the FKs setup a little different.

-David


Jacopo Cappellato wrote:

> Today I got the following error while running "ant run-install-seed":
>
> CREATE INDEX AFFILIATE_PARTY ON OFBIZSCHEMA.AFFILIATE (PARTY_ID)
>
> Error: An index already exists for this list of columns
> The db is Oracle and with trunk OFBiz.
>
> Any ideas?
>
> Jacopo
>

[Jacopo Cappellato]

This is the keys definition for the Affiliate entity:

<prim-key field="partyId"/>
<relation type="one" fk-name="AFFILIATE_PARTY" rel-entity-name="Party">
   <key-map field-name="partyId"/>
</relation>
<relation type="one" fk-name="AFFILIATE_PGRP" rel-entity-name="PartyGroup">
   <key-map field-name="partyId"/>
</relation>

My guess is that OFBiz tries to create the index for the partyId field
three times:

1) because the field is the primary key
2) because of the fk with the Party entity
3) because of the fk with the PartyGroup entity

Maybe (but I could be wrong) the db is throwing an error because we are
trying to create the same exact index (with different names) three times.

Any suggestions on how we could fix this?

Jacopo


David E Jones wrote:

>
> Have you checked to see if this is an auto-index from a foreign key
> definition or a manually defined one?
>
> My guess is the first and there are just two FKs that are very similar,
> in which case the auto index name creation needs to be more intelligent
> or the FKs setup a little different.
>
> -David
>
>
> Jacopo Cappellato wrote:
>> Today I got the following error while running "ant run-install-seed":
>>
>> CREATE INDEX AFFILIATE_PARTY ON OFBIZSCHEMA.AFFILIATE (PARTY_ID)
>>
>> Error: An index already exists for this list of columns
>> The db is Oracle and with trunk OFBiz.
>>
>> Any ideas?
>>
>> Jacopo
>>

[Jacopo Cappellato]

And this is the complete error log:

      [java] 2007-06-13 11:58:37,062 (main) [
DatabaseUtil.java:2710:ERROR]
  Could not create foreign key indices for entity [Affiliate]: SQL
Exception while executing the following:
      [java] CREATE INDEX AFFILIATE_PARTY ON DBCGCGEST.AFFILIATE (PARTY_ID)
      [java] Error was: java.sql.SQLException: ORA-01408: such column
list already indexed
      [java]
      [java] Exception in thread "main" java.lang.NullPointerException
      [java]     at
org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.removeConnectionEventListener(XAConnectionImpl.java:225)
      [java]     at
org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory$2.closeConnection(XAConnectionFactory.java:101)
      [java]     at
org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory$2.connectionClosed(XAConnectionFactory.java:95)
      [java]     at
org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.clientConnectionClosed(XAConnectionImpl.java:155)
      [java]     at
org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAClientConnection.close(XAClientConnection.java:293)
      [java]     at
org.ofbiz.entity.jdbc.DatabaseUtil.createForeignKeyIndex(DatabaseUtil.java:2763)
      [java]     at
org.ofbiz.entity.jdbc.DatabaseUtil.createForeignKeyIndices(DatabaseUtil.java:2707)
      [java]     at
org.ofbiz.entity.jdbc.DatabaseUtil.checkDb(DatabaseUtil.java:389)
      [java]     at
org.ofbiz.entity.jdbc.DatabaseUtil.checkDb(DatabaseUtil.java:125)
      [java]     at
org.ofbiz.entity.datasource.GenericDAO.checkDb(GenericDAO.java:1087)
      [java]     at
org.ofbiz.entity.datasource.GenericHelperDAO.checkDataSource(GenericHelperDAO.java:198)
      [java]     at
org.ofbiz.entity.GenericDelegator.<init>(GenericDelegator.java:184)
      [java]     at
org.ofbiz.entity.GenericDelegator.getGenericDelegator(GenericDelegator.java:116)
      [java]     at
org.ofbiz.entityext.data.EntityDataLoadContainer.start(EntityDataLoadContainer.java:194)
      [java]     at
org.ofbiz.base.container.ContainerLoader.start(ContainerLoader.java:82)
      [java]     at
org.ofbiz.base.start.Start.startStartLoaders(Start.java:263)
      [java]     at org.ofbiz.base.start.Start.startServer(Start.java:314)
      [java]     at org.ofbiz.base.start.Start.start(Start.java:318)
      [java]     at org.ofbiz.base.start.Start.main(Start.java:404)
      [java] Java Result: 1

Any hints (especially from the ones that are using Oracle with OFBiz)
would be greatly appreciated.

Jacopo

Jacopo Cappellato wrote:

> This is the keys definition for the Affiliate entity:
>
> <prim-key field="partyId"/>
> <relation type="one" fk-name="AFFILIATE_PARTY" rel-entity-name="Party">
>   <key-map field-name="partyId"/>
> </relation>
> <relation type="one" fk-name="AFFILIATE_PGRP" rel-entity-name="PartyGroup">
>   <key-map field-name="partyId"/>
> </relation>
>
> My guess is that OFBiz tries to create the index for the partyId field
> three times:
>
> 1) because the field is the primary key
> 2) because of the fk with the Party entity
> 3) because of the fk with the PartyGroup entity
>
> Maybe (but I could be wrong) the db is throwing an error because we are
> trying to create the same exact index (with different names) three times.
>
> Any suggestions on how we could fix this?
>
> Jacopo
>
>
> David E Jones wrote:
>>
>> Have you checked to see if this is an auto-index from a foreign key
>> definition or a manually defined one?
>>
>> My guess is the first and there are just two FKs that are very
>> similar, in which case the auto index name creation needs to be more
>> intelligent or the FKs setup a little different.
>>
>> -David
>>
>>
>> Jacopo Cappellato wrote:
>>> Today I got the following error while running "ant run-install-seed":
>>>
>>> CREATE INDEX AFFILIATE_PARTY ON OFBIZSCHEMA.AFFILIATE (PARTY_ID)
>>>
>>> Error: An index already exists for this list of columns
>>> The db is Oracle and with trunk OFBiz.
>>>
>>> Any ideas?
>>>
>>> Jacopo
>>>

[Jacopo Cappellato]

And this is what I've found about the error in Oracle forums:

http://www.error-code.org.uk/view.asp?cl=ORACLE&p=ORA&ec=01408

Jacopo


Jacopo Cappellato wrote:

> And this is the complete error log:
>
>      [java] 2007-06-13 11:58:37,062 (main) [ DatabaseUtil.java:2710:ERROR]
>  Could not create foreign key indices for entity [Affiliate]: SQL
> Exception while executing the following:
>      [java] CREATE INDEX AFFILIATE_PARTY ON DBCGCGEST.AFFILIATE (PARTY_ID)
>      [java] Error was: java.sql.SQLException: ORA-01408: such column
> list already indexed
>      [java]
>      [java] Exception in thread "main" java.lang.NullPointerException
>      [java]     at
> org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.removeConnectionEventListener(XAConnectionImpl.java:225)
>
>      [java]     at
> org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory$2.closeConnection(XAConnectionFactory.java:101)
>
>      [java]     at
> org.ofbiz.minerva.pool.jdbc.xa.XAConnectionFactory$2.connectionClosed(XAConnectionFactory.java:95)
>
>      [java]     at
> org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAConnectionImpl.clientConnectionClosed(XAConnectionImpl.java:155)
>
>      [java]     at
> org.ofbiz.minerva.pool.jdbc.xa.wrapper.XAClientConnection.close(XAClientConnection.java:293)
>
>      [java]     at
> org.ofbiz.entity.jdbc.DatabaseUtil.createForeignKeyIndex(DatabaseUtil.java:2763)
>
>      [java]     at
> org.ofbiz.entity.jdbc.DatabaseUtil.createForeignKeyIndices(DatabaseUtil.java:2707)
>
>      [java]     at
> org.ofbiz.entity.jdbc.DatabaseUtil.checkDb(DatabaseUtil.java:389)
>      [java]     at
> org.ofbiz.entity.jdbc.DatabaseUtil.checkDb(DatabaseUtil.java:125)
>      [java]     at
> org.ofbiz.entity.datasource.GenericDAO.checkDb(GenericDAO.java:1087)
>      [java]     at
> org.ofbiz.entity.datasource.GenericHelperDAO.checkDataSource(GenericHelperDAO.java:198)
>
>      [java]     at
> org.ofbiz.entity.GenericDelegator.<init>(GenericDelegator.java:184)
>      [java]     at
> org.ofbiz.entity.GenericDelegator.getGenericDelegator(GenericDelegator.java:116)
>
>      [java]     at
> org.ofbiz.entityext.data.EntityDataLoadContainer.start(EntityDataLoadContainer.java:194)
>
>      [java]     at
> org.ofbiz.base.container.ContainerLoader.start(ContainerLoader.java:82)
>      [java]     at
> org.ofbiz.base.start.Start.startStartLoaders(Start.java:263)
>      [java]     at org.ofbiz.base.start.Start.startServer(Start.java:314)
>      [java]     at org.ofbiz.base.start.Start.start(Start.java:318)
>      [java]     at org.ofbiz.base.start.Start.main(Start.java:404)
>      [java] Java Result: 1
>
> Any hints (especially from the ones that are using Oracle with OFBiz)
> would be greatly appreciated.
>
> Jacopo
>
> Jacopo Cappellato wrote:
>> This is the keys definition for the Affiliate entity:
>>
>> <prim-key field="partyId"/>
>> <relation type="one" fk-name="AFFILIATE_PARTY" rel-entity-name="Party">
>>   <key-map field-name="partyId"/>
>> </relation>
>> <relation type="one" fk-name="AFFILIATE_PGRP"
>> rel-entity-name="PartyGroup">
>>   <key-map field-name="partyId"/>
>> </relation>
>>
>> My guess is that OFBiz tries to create the index for the partyId field
>> three times:
>>
>> 1) because the field is the primary key
>> 2) because of the fk with the Party entity
>> 3) because of the fk with the PartyGroup entity
>>
>> Maybe (but I could be wrong) the db is throwing an error because we
>> are trying to create the same exact index (with different names) three
>> times.
>>
>> Any suggestions on how we could fix this?
>>
>> Jacopo
>>
>>
>> David E Jones wrote:
>>>
>>> Have you checked to see if this is an auto-index from a foreign key
>>> definition or a manually defined one?
>>>
>>> My guess is the first and there are just two FKs that are very
>>> similar, in which case the auto index name creation needs to be more
>>> intelligent or the FKs setup a little different.
>>>
>>> -David
>>>
>>>
>>> Jacopo Cappellato wrote:
>>>> Today I got the following error while running "ant run-install-seed":
>>>>
>>>> CREATE INDEX AFFILIATE_PARTY ON OFBIZSCHEMA.AFFILIATE (PARTY_ID)
>>>>
>>>> Error: An index already exists for this list of columns
>>>> The db is Oracle and with trunk OFBiz.
>>>>
>>>> Any ideas?
>>>>
>>>> Jacopo
>>>>
>

[MRTesloni]

Hello,

After few day of investigation I found solution...

In file ofbiz/framework/entity/config/entityengine.xml in localoracle datasource tag definition you must add key
use-foreign-key-indices="false"
After that intervention database creation were successfull...

example:
<datasource name="localoracle"
helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
schema-name="OFBIZ"
field-type-name="oracle"
check-on-start="true"
add-missing-on-start="true"
alias-view-columns="false"
use-foreign-key-indices="false"
join-style="ansi">
<read-data reader-name="seed"/>
<read-data reader-name="demo"/>
<read-data reader-name="ext"/>
<inline-jdbc
jdbc-driver="oracle.jdbc.driver.OracleDriver"
jdbc-uri="jdbc:oracle:thin:@127.0.0.1:1521:OFBiz"
jdbc-username="User"
jdbc-password="Password"
pool-minsize="2"
pool-maxsize="250"/>
</datasource>


Default setting of use-foreign-key-indices implicate that application must make explicit indexes for every Foreign Key and that could make problems with Oracle. FALSE setting implicate that DBM should automatically generate necessary indexes. This is strongly recommended in Oracle DBM Case...

Tesloni...

Jacopo Cappellato wrote

Today I got the following error while running "ant run-install-seed":

CREATE INDEX AFFILIATE_PARTY ON OFBIZSCHEMA.AFFILIATE (PARTY_ID)

Error: An index already exists for this list of columns
The db is Oracle and with trunk OFBiz.

Any ideas?

Jacopo