Error on Shipping Estimate deletion

[Abdullah Shaikh]

I created a Shipping Estimate and then I clicked on Remove button to delete
the shipping estimate, but I got the below error.

It looks it is also trying to delete the record from QuantityBreak table,
which it shouldn't.


The Following Errors Occurred:

Error calling event: org.ofbiz.webapp.event.EventHandlerException: Service
invocation error (Could not commit transaction for service
[removeShipmentEstimate] call: Roll back error, could not commit
transaction, was rolled back instead because of: Failure in
removeByCondition operation for entity [QuantityBreak]:
org.ofbiz.entity.GenericDataSourceException: Generic Entity Exception
occured in deleteByCondition (SQL Exception while executing the
following:DELETE FROM public.QUANTITY_BREAK WHERE (QUANTITY_BREAK_ID =
'RentalStore') (ERROR: update or delete on table "quantity_break" violates
foreign key constraint "shpmnt_ce_wht_qb" on table
"shipment_cost_estimate"<br/> Detail: Key (quantity_break_id)=(RentalStore)
is still referenced from table "shipment_cost_estimate".)). Rolling back
transaction.org.ofbiz.entity.GenericDataSourceException: Generic Entity
Exception occured in deleteByCondition (SQL Exception while executing the
following:DELETE FROM public.QUANTITY_BREAK WHERE (QUANTITY_BREAK_ID =
'RentalStore') (ERROR: update or delete on table "quantity_break" violates
foreign key constraint "shpmnt_ce_wht_qb" on table
"shipment_cost_estimate"<br/> Detail: Key (quantity_break_id)=(RentalStore)
is still referenced from table "shipment_cost_estimate".)) (Generic Entity
Exception occured in deleteByCondition (SQL Exception while executing the
following:DELETE FROM public.QUANTITY_BREAK WHERE (QUANTITY_BREAK_ID =
'RentalStore') (ERROR: update or delete on table "quantity_break" violates
foreign key constraint "shpmnt_ce_wht_qb" on table
"shipment_cost_estimate"<br/> Detail: Key (quantity_break_id)=(RentalStore)
is still referenced from table "shipment_cost_estimate".))))

- Abdullah

[Abdullah Shaikh]

After looking into ShipmentServices.java's removeShipmentEstimate method
which actually deletes the Shipping Estimate, it also try to delete the
related entity records from WeightQuantityBreak, QuantityQuantityBreak &
PriceQuantityBreak.

The Shipping Estimate which I configured for eCommerce was using the Weight
Break Id  "0 - 0 [RentalStore]" which is also being used by Open Travel
system store and that's why it couldn't delete because of foreign key
constraint.

Should the removeShipmentEstimate also delete the related entries from
QuantityBreak tables ?

- Abdullah

On Thu, Jan 28, 2010 at 1:12 PM, Abdullah Shaikh <
[hidden email]> wrote:

> I created a Shipping Estimate and then I clicked on Remove button to delete
> the shipping estimate, but I got the below error.
>
> It looks it is also trying to delete the record from QuantityBreak table,
> which it shouldn't.
>
>
> The Following Errors Occurred:
>
> Error calling event: org.ofbiz.webapp.event.EventHandlerException: Service
> invocation error (Could not commit transaction for service
> [removeShipmentEstimate] call: Roll back error, could not commit
> transaction, was rolled back instead because of: Failure in
> removeByCondition operation for entity [QuantityBreak]:
> org.ofbiz.entity.GenericDataSourceException: Generic Entity Exception
> occured in deleteByCondition (SQL Exception while executing the
> following:DELETE FROM public.QUANTITY_BREAK WHERE (QUANTITY_BREAK_ID =
> 'RentalStore') (ERROR: update or delete on table "quantity_break" violates
> foreign key constraint "shpmnt_ce_wht_qb" on table
> "shipment_cost_estimate"<br/> Detail: Key (quantity_break_id)=(RentalStore)
> is still referenced from table "shipment_cost_estimate".)). Rolling back
> transaction.org.ofbiz.entity.GenericDataSourceException: Generic Entity
> Exception occured in deleteByCondition (SQL Exception while executing the
> following:DELETE FROM public.QUANTITY_BREAK WHERE (QUANTITY_BREAK_ID =
> 'RentalStore') (ERROR: update or delete on table "quantity_break" violates
> foreign key constraint "shpmnt_ce_wht_qb" on table
> "shipment_cost_estimate"<br/> Detail: Key (quantity_break_id)=(RentalStore)
> is still referenced from table "shipment_cost_estimate".)) (Generic Entity
> Exception occured in deleteByCondition (SQL Exception while executing the
> following:DELETE FROM public.QUANTITY_BREAK WHERE (QUANTITY_BREAK_ID =
> 'RentalStore') (ERROR: update or delete on table "quantity_break" violates
> foreign key constraint "shpmnt_ce_wht_qb" on table
> "shipment_cost_estimate"<br/> Detail: Key (quantity_break_id)=(RentalStore)
> is still referenced from table "shipment_cost_estimate".))))
>
> - Abdullah
>

[Jacopo Cappellato-4]

Hi Abdullah,

On Jan 28, 2010, at 9:03 AM, Abdullah Shaikh wrote:

> After looking into ShipmentServices.java's removeShipmentEstimate method
> which actually deletes the Shipping Estimate, it also try to delete the
> related entity records from WeightQuantityBreak, QuantityQuantityBreak &
> PriceQuantityBreak.
>
> The Shipping Estimate which I configured for eCommerce was using the Weight
> Break Id  "0 - 0 [RentalStore]" which is also being used by Open Travel
> system store and that's why it couldn't delete because of foreign key
> constraint.
>
> Should the removeShipmentEstimate also delete the related entries from
> QuantityBreak tables ?
>

No, in my opinion it shouldn't delete the QuantityBreak records.
However the code seems to be written in order to only *attempt* to delete the records:

        try {
            if (estimate.get("weightBreakId") != null) {
                delegator.removeRelated("WeightQuantityBreak", estimate);
            }
        } catch (GenericEntityException e) {
            Debug.logInfo("Not removing WeightQuantityBreak records related to ShipmentCostEstimate [" + shipmentCostEstimateId + "] because they are used by other entities.", module);
        }
        return ServiceUtil.returnSuccess();

but this doesn't seem to work as expected: my guess is that when the error is thrown the transaction is rolled back.
The quick solution is to comment the above code but it would be nice to verify if there is a better way to avoid the rollback (I am forwarding this mail to the dev list).

Kind regards,

Jacopo

> - Abdullah
>
> On Thu, Jan 28, 2010 at 1:12 PM, Abdullah Shaikh <
> [hidden email]> wrote:
>
>> I created a Shipping Estimate and then I clicked on Remove button to delete
>> the shipping estimate, but I got the below error.
>>
>> It looks it is also trying to delete the record from QuantityBreak table,
>> which it shouldn't.
>>
>>
>> The Following Errors Occurred:
>>
>> Error calling event: org.ofbiz.webapp.event.EventHandlerException: Service
>> invocation error (Could not commit transaction for service
>> [removeShipmentEstimate] call: Roll back error, could not commit
>> transaction, was rolled back instead because of: Failure in
>> removeByCondition operation for entity [QuantityBreak]:
>> org.ofbiz.entity.GenericDataSourceException: Generic Entity Exception
>> occured in deleteByCondition (SQL Exception while executing the
>> following:DELETE FROM public.QUANTITY_BREAK WHERE (QUANTITY_BREAK_ID =
>> 'RentalStore') (ERROR: update or delete on table "quantity_break" violates
>> foreign key constraint "shpmnt_ce_wht_qb" on table
>> "shipment_cost_estimate"<br/> Detail: Key (quantity_break_id)=(RentalStore)
>> is still referenced from table "shipment_cost_estimate".)). Rolling back
>> transaction.org.ofbiz.entity.GenericDataSourceException: Generic Entity
>> Exception occured in deleteByCondition (SQL Exception while executing the
>> following:DELETE FROM public.QUANTITY_BREAK WHERE (QUANTITY_BREAK_ID =
>> 'RentalStore') (ERROR: update or delete on table "quantity_break" violates
>> foreign key constraint "shpmnt_ce_wht_qb" on table
>> "shipment_cost_estimate"<br/> Detail: Key (quantity_break_id)=(RentalStore)
>> is still referenced from table "shipment_cost_estimate".)) (Generic Entity
>> Exception occured in deleteByCondition (SQL Exception while executing the
>> following:DELETE FROM public.QUANTITY_BREAK WHERE (QUANTITY_BREAK_ID =
>> 'RentalStore') (ERROR: update or delete on table "quantity_break" violates
>> foreign key constraint "shpmnt_ce_wht_qb" on table
>> "shipment_cost_estimate"<br/> Detail: Key (quantity_break_id)=(RentalStore)
>> is still referenced from table "shipment_cost_estimate".))))
>>
>> - Abdullah
>>