Dev - "Returns" seed data foreign key violations...

[Ray Barlow]

Hi,

It looks like the seed data in applications/order/data/OrderTypeData.xml
is missing some ReturnItemType values. This is the  foreign key
violation error when installing seed data on postgres:
     [java] org.ofbiz.entity.GenericEntityException: Exception while
inserting the following entity:
[GenericEntity:ReturnItemTypeMap][createdStamp,2006-03-29
07:56:44.431(java.sql.Timestamp)][createdTxStamp,2006-03-29
07:56:43.731(java.sql.Timestamp)][lastUpdatedStamp,2006-03-29
07:56:44.431(java.sql.Timestamp)][lastUpdatedTxStamp,2006-03-29
07:56:43.731(java.sql.Timestamp)][returnHeaderTypeId,CUSTOMER_RETURN(java.lang.String)][returnItemMapKey,PROMOTION_ADJUSTMENT(java.lang.String)][returnItemTypeId,RET_PROMOTION_ADJ(java.lang.String)]
(while inserting:
[GenericEntity:ReturnItemTypeMap][createdStamp,2006-03-29
07:56:44.431(java.sql.Timestamp)][createdTxStamp,2006-03-29
07:56:43.731(java.sql.Timestamp)][lastUpdatedStamp,2006-03-29
07:56:44.431(java.sql.Timestamp)][lastUpdatedTxStamp,2006-03-29
07:56:43.731(java.sql.Timestamp)][returnHeaderTypeId,CUSTOMER_RETURN(java.lang.String)][returnItemMapKey,PROMOTION_ADJUSTMENT(java.lang.String)][returnItemTypeId,RET_PROMOTION_ADJ(java.lang.String)]
(SQL Exception while executing the following:INSERT INTO
public.RETURN_ITEM_TYPE_MAP (RETURN_ITEM_MAP_KEY, RETURN_HEADER_TYPE_ID,
RETURN_ITEM_TYPE_ID, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP,
CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?) (ERROR:
insert or update on table "return_item_type_map" violates foreign key
constraint "return_item_map")))

Ray

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[David E. Jones]

Ray,

I just tried a fresh database and a from scratch seed data load and I didn't see any problem with this. Is there a specific circumstance where this happens?

-David


Ray wrote:

> Hi,
>
> It looks like the seed data in applications/order/data/OrderTypeData.xml
> is missing some ReturnItemType values. This is the  foreign key
> violation error when installing seed data on postgres:
>      [java] org.ofbiz.entity.GenericEntityException: Exception while
> inserting the following entity:
> [GenericEntity:ReturnItemTypeMap][createdStamp,2006-03-29
> 07:56:44.431(java.sql.Timestamp)][createdTxStamp,2006-03-29
> 07:56:43.731(java.sql.Timestamp)][lastUpdatedStamp,2006-03-29
> 07:56:44.431(java.sql.Timestamp)][lastUpdatedTxStamp,2006-03-29
> 07:56:43.731(java.sql.Timestamp)][returnHeaderTypeId,CUSTOMER_RETURN(java.lang.String)][returnItemMapKey,PROMOTION_ADJUSTMENT(java.lang.String)][returnItemTypeId,RET_PROMOTION_ADJ(java.lang.String)]
> (while inserting:
> [GenericEntity:ReturnItemTypeMap][createdStamp,2006-03-29
> 07:56:44.431(java.sql.Timestamp)][createdTxStamp,2006-03-29
> 07:56:43.731(java.sql.Timestamp)][lastUpdatedStamp,2006-03-29
> 07:56:44.431(java.sql.Timestamp)][lastUpdatedTxStamp,2006-03-29
> 07:56:43.731(java.sql.Timestamp)][returnHeaderTypeId,CUSTOMER_RETURN(java.lang.String)][returnItemMapKey,PROMOTION_ADJUSTMENT(java.lang.String)][returnItemTypeId,RET_PROMOTION_ADJ(java.lang.String)]
> (SQL Exception while executing the following:INSERT INTO
> public.RETURN_ITEM_TYPE_MAP (RETURN_ITEM_MAP_KEY, RETURN_HEADER_TYPE_ID,
> RETURN_ITEM_TYPE_ID, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP,
> CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?) (ERROR:
> insert or update on table "return_item_type_map" violates foreign key
> constraint "return_item_map")))
>
> Ray
>
>  
> _______________________________________________
> Dev mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/dev

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[Peter Goron]

Hi David, Ray,

I've already seen this message after having ran run-install-seed on an
existing database. This error appears because foreign keys of
ReturnItemTypeMap entity have changed recently.

Peter

> Ray,
>
> I just tried a fresh database and a from scratch seed data load and I didn't see any problem with this. Is there a specific circumstance where this happens?
>
> -David
>
>
> Ray wrote:
> > Hi,
> >
> > It looks like the seed data in applications/order/data/OrderTypeData.xml
> > is missing some ReturnItemType values. This is the  foreign key
> > violation error when installing seed data on postgres:
> >      [java] org.ofbiz.entity.GenericEntityException: Exception while
> > inserting the following entity:
> > [GenericEntity:ReturnItemTypeMap][createdStamp,2006-03-29
> > 07:56:44.431(java.sql.Timestamp)][createdTxStamp,2006-03-29
> > 07:56:43.731(java.sql.Timestamp)][lastUpdatedStamp,2006-03-29
> > 07:56:44.431(java.sql.Timestamp)][lastUpdatedTxStamp,2006-03-29
> > 07:56:43.731(java.sql.Timestamp)][returnHeaderTypeId,CUSTOMER_RETURN(java.lang.String)][returnItemMapKey,PROMOTION_ADJUSTMENT(java.lang.String)][returnItemTypeId,RET_PROMOTION_ADJ(java.lang.String)]
> > (while inserting:
> > [GenericEntity:ReturnItemTypeMap][createdStamp,2006-03-29
> > 07:56:44.431(java.sql.Timestamp)][createdTxStamp,2006-03-29
> > 07:56:43.731(java.sql.Timestamp)][lastUpdatedStamp,2006-03-29
> > 07:56:44.431(java.sql.Timestamp)][lastUpdatedTxStamp,2006-03-29
> > 07:56:43.731(java.sql.Timestamp)][returnHeaderTypeId,CUSTOMER_RETURN(java.lang.String)][returnItemMapKey,PROMOTION_ADJUSTMENT(java.lang.String)][returnItemTypeId,RET_PROMOTION_ADJ(java.lang.String)]
> > (SQL Exception while executing the following:INSERT INTO
> > public.RETURN_ITEM_TYPE_MAP (RETURN_ITEM_MAP_KEY, RETURN_HEADER_TYPE_ID,
> > RETURN_ITEM_TYPE_ID, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP,
> > CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?) (ERROR:
> > insert or update on table "return_item_type_map" violates foreign key
> > constraint "return_item_map")))
> >
> > Ray
> >
> >  
> > _______________________________________________
> > Dev mailing list
> > [hidden email]
> > http://lists.ofbiz.org/mailman/listinfo/dev
>  
> _______________________________________________
> Dev mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/dev
>

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[David E. Jones]

Thanks for the comment Peter, that would explain the problem...  
Updating the foreign key would be necessary then before loading the  
updated seed data.

-David


On Mar 29, 2006, at 6:31 AM, Peter Goron wrote:

> Hi David, Ray,
>
> I've already seen this message after having ran run-install-seed on an
> existing database. This error appears because foreign keys of
> ReturnItemTypeMap entity have changed recently.
>
> Peter
>
>> Ray,
>>
>> I just tried a fresh database and a from scratch seed data load  
>> and I didn't see any problem with this. Is there a specific  
>> circumstance where this happens?
>>
>> -David
>>
>>
>> Ray wrote:
>>> Hi,
>>>
>>> It looks like the seed data in applications/order/data/
>>> OrderTypeData.xml
>>> is missing some ReturnItemType values. This is the  foreign key
>>> violation error when installing seed data on postgres:
>>>      [java] org.ofbiz.entity.GenericEntityException: Exception while
>>> inserting the following entity:
>>> [GenericEntity:ReturnItemTypeMap][createdStamp,2006-03-29
>>> 07:56:44.431(java.sql.Timestamp)][createdTxStamp,2006-03-29
>>> 07:56:43.731(java.sql.Timestamp)][lastUpdatedStamp,2006-03-29
>>> 07:56:44.431(java.sql.Timestamp)][lastUpdatedTxStamp,2006-03-29
>>> 07:56:43.731(java.sql.Timestamp)]
>>> [returnHeaderTypeId,CUSTOMER_RETURN(java.lang.String)]
>>> [returnItemMapKey,PROMOTION_ADJUSTMENT(java.lang.String)]
>>> [returnItemTypeId,RET_PROMOTION_ADJ(java.lang.String)]
>>> (while inserting:
>>> [GenericEntity:ReturnItemTypeMap][createdStamp,2006-03-29
>>> 07:56:44.431(java.sql.Timestamp)][createdTxStamp,2006-03-29
>>> 07:56:43.731(java.sql.Timestamp)][lastUpdatedStamp,2006-03-29
>>> 07:56:44.431(java.sql.Timestamp)][lastUpdatedTxStamp,2006-03-29
>>> 07:56:43.731(java.sql.Timestamp)]
>>> [returnHeaderTypeId,CUSTOMER_RETURN(java.lang.String)]
>>> [returnItemMapKey,PROMOTION_ADJUSTMENT(java.lang.String)]
>>> [returnItemTypeId,RET_PROMOTION_ADJ(java.lang.String)]
>>> (SQL Exception while executing the following:INSERT INTO
>>> public.RETURN_ITEM_TYPE_MAP (RETURN_ITEM_MAP_KEY,  
>>> RETURN_HEADER_TYPE_ID,
>>> RETURN_ITEM_TYPE_ID, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP,
>>> CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?)  
>>> (ERROR:
>>> insert or update on table "return_item_type_map" violates foreign  
>>> key
>>> constraint "return_item_map")))
>>>
>>> Ray
>>>
>>>
>>> _______________________________________________
>>> Dev mailing list
>>> [hidden email]
>>> http://lists.ofbiz.org/mailman/listinfo/dev
>>
>> _______________________________________________
>> Dev mailing list
>> [hidden email]
>> http://lists.ofbiz.org/mailman/listinfo/dev
>>
>
>
> _______________________________________________
> Dev mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/dev

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[Ray Barlow]

Indeed that was a potential problem but I have already been through the
webtools section to remove/create all FKs so they should be inline with
the xml schema. I have also ensured that there are no other
warnings/errors reported during DB start up.

Out of interest David are you using postgre or Derby for your testing?
Certainly in my clean environment testing using Derby I've not had the
error, but I'm wondering if Derby is not enforcing the FK constraint.

In simple data terms ReturnItemTypeMap has an FK to ReturnItemType based
on returnItemTypeId. In the seed OrderDataType.xml line 205:
<ReturnItemTypeMap returnHeaderTypeId="CUSTOMER_RETURN"
returnItemMapKey="PROMOTION_ADJUSTMENT"
returnItemTypeId="RET_PROMOTION_ADJ"/>

I can't see where the data for ReturnItemType with a value of
returnItemTypeId="RET_PROMOTION_ADJ" is being declared!? Hence that is
causing my FK violations. Creating entries for those ReturnItemType's
that are missing does resolve the problem and I can patch if you want,
but it probably ought to go back to the originator to ensure the data is
correct in the other fields such as description and parents.

Ray


David E. Jones wrote:

>Thanks for the comment Peter, that would explain the problem...  
>Updating the foreign key would be necessary then before loading the  
>updated seed data.
>
>-David
>
>
>On Mar 29, 2006, at 6:31 AM, Peter Goron wrote:
>
>  
>
>>Hi David, Ray,
>>
>>I've already seen this message after having ran run-install-seed on an
>>existing database. This error appears because foreign keys of
>>ReturnItemTypeMap entity have changed recently.
>>
>>Peter
>>
>>    
>>
>>>Ray,
>>>
>>>I just tried a fresh database and a from scratch seed data load  
>>>and I didn't see any problem with this. Is there a specific  
>>>circumstance where this happens?
>>>
>>>-David
>>>
>>>
>>>Ray wrote:
>>>      
>>>
>

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev