Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0028834Openbravo ERP03. Procurement managementpublic2015-02-02 10:442015-02-26 18:57
Reporterumartirena 
Assigned Toumartirena 
PriorityhighSeveritymajorReproducibilityalways
StatusclosedResolutionno change required 
PlatformOS5OS Version
Product Version 
Target Version3.0PR15Q2Fixed in Version 
Merge Request Status
Review Assigned Toumartirena
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0028834: M_Reservation_Post errors in oracle
DescriptionDatabase Stored Procedures they work in different way in Oracle and in Postgres. In oracle while calling AD_UPDATE_PINSTANCE the process commits unless an extra parameter is passed to the procedure. In Postgres it never commits. A lot of Stored Procedures the don't have that flag, so they commit automatically in Oracle, and sometimes this is not the expected behavior.
To avoid this, I have added in one of this cases the extra parameter in the call to AD_UPATE_PINSTANCE, but when executing the stored procedure from java using CallProcess.callProcess method it fails in the following piece of code:
OBDal.getInstance().getSession().refresh(pInstance);
with this error:
No row with the given identifier exists: [ADParameter#635B4607588F4D68B28D054795AC7482]
org.hibernate.UnresolvableObjectException: No row with the given identifier exists: [ADParameter#635B4607588F4D68B28D054795AC7482]
    at org.hibernate.UnresolvableObjectException.throwIfNull(UnresolvableObjectException.java:65)
    at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefreshEventListener.java:165)
    at org.hibernate.impl.SessionImpl.fireRefresh(SessionImpl.java:1127)
    at org.hibernate.impl.SessionImpl.refresh(SessionImpl.java:1110)
    at org.hibernate.engine.CascadingAction$3.cascade(CascadingAction.java:203)
    at org.hibernate.engine.Cascade.cascadeToOne(Cascade.java:392)
    at org.hibernate.engine.Cascade.cascadeAssociation(Cascade.java:335)
    at org.hibernate.engine.Cascade.cascadeProperty(Cascade.java:204)
    at org.hibernate.engine.Cascade.cascadeCollectionElements(Cascade.java:425)
    at org.hibernate.engine.Cascade.cascadeCollection(Cascade.java:362)
    at org.hibernate.engine.Cascade.cascadeAssociation(Cascade.java:338)
    at org.hibernate.engine.Cascade.cascadeProperty(Cascade.java:204)
    at org.hibernate.engine.Cascade.cascade(Cascade.java:161)
    at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefreshEventListener.java:127)
    at org.hibernate.event.def.DefaultRefreshEventListener.onRefresh(DefaultRefreshEventListener.java:62)
    at org.hibernate.impl.SessionImpl.fireRefresh(SessionImpl.java:1118)
    at org.hibernate.impl.SessionImpl.refresh(SessionImpl.java:1098)
    at org.openbravo.service.db.CallProcess.callProcess(CallProcess.java:224)
    at org.openbravo.service.db.CallProcess.call(CallProcess.java:117)
    at org.openbravo.service.db.CallProcess.call(CallProcess.java:268)
    at org.openbravo.materialmgmt.ReservationUtils.processReserve(ReservationUtils.java:163)
    at org.openbravo.common.datasource.StockReservationPickAndEditDataSource.getGridData(StockReservationPickAndEditDataSource.java:592)
    at org.openbravo.common.datasource.StockReservationPickAndEditDataSource.getData(StockReservationPickAndEditDataSource.java:131)
    at org.openbravo.common.datasource.StockReservationPickAndEditDataSource.fetchJSONObject(StockReservationPickAndEditDataSource.java:103)
    at org.openbravo.common.datasource.StockReservationPickAndEditDataSource.fetch(StockReservationPickAndEditDataSource.java:83)
    at org.openbravo.service.datasource.DataSourceServlet.doFetch(DataSourceServlet.java:261)
    at org.openbravo.service.datasource.DataSourceServlet.doPost(DataSourceServlet.java:747)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:643)
    at org.openbravo.base.HttpBaseServlet.serviceInitialized(HttpBaseServlet.java:222)
    at org.openbravo.base.secureApp.HttpSecureAppServlet.service(HttpSecureAppServlet.java:449)
    at org.openbravo.client.kernel.BaseKernelServlet.callServiceInSuper(BaseKernelServlet.java:88)
    at org.openbravo.client.kernel.BaseKernelServlet.service(BaseKernelServlet.java:59)
    at org.openbravo.service.datasource.DataSourceServlet.service(DataSourceServlet.java:140)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.utils.SessionExpirationFilter.doFilter(SessionExpirationFilter.java:66)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.utils.CharsetFilter.doFilter(CharsetFilter.java:35)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.client.kernel.KernelFilter$1.doAction(KernelFilter.java:62)
    at org.openbravo.dal.core.ThreadHandler.run(ThreadHandler.java:46)
    at org.openbravo.client.kernel.KernelFilter.doFilter(KernelFilter.java:71)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.openbravo.dal.core.DalRequestFilter$1.doAction(DalRequestFilter.java:81)
    at org.openbravo.dal.core.ThreadHandler.run(ThreadHandler.java:46)
    at org.openbravo.dal.core.DalRequestFilter.doFilter(DalRequestFilter.java:103)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Thread.java:701)
Steps To ReproduceIn oracle environment:
 * Enable Reservations preference.
 * Logout and Login.
 * Create a Sales Order with a line. Click on that line and press Manage Reservations.
 * Click on Cancel.
 * Go to Linked Items of that line and realize that a reservation has been created. This is wrong because we've selected cancel in the P&E. This happens because M_RESERVATION_POST commits.

 * In sqldeveloper, open M_RESERVATION_POST stored procedure. In all places where a call to AD_UPDATE_PINSTANCE is performed, add an extra parameter ('N'). Click again on Manage Reservations in Sales Order Line.
 * Realize that the previous error is raised.

 * The stored procedure it should not commit in Oracle to avoid different behaviors between databases. In general the procedures they should not have to commit, what I think that probably is happening with several stored procedures.
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
related to defect 0029063 closed umartirena M_Reservation_Post fails in Oracle 
related to defect 00285103.0PR15Q2 closed reinaldoguerra Manage Reservation P&E behavior could be confusing in some cases opened from Sales Order Line 
related to design defect 0028875 acknowledged Triage Platform Base Class "CallStoredProcedure" does not support parameters of type out 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2015-02-02 10:44umartirenaNew Issue
2015-02-02 10:44umartirenaAssigned To => AugustoMauch
2015-02-02 10:44umartirenaModules => Core
2015-02-02 10:44umartirenaTriggers an Emergency Pack => No
2015-02-06 07:42alostaleStatusnew => acknowledged
2015-02-10 11:04alostaleRelationship addedrelated to 0028875
2015-02-16 09:43alostaleNote Added: 0074500
2015-02-16 09:44alostaleAssigned ToAugustoMauch => umartirena
2015-02-16 09:44alostaleSummaryStored Procedures have different behavior in Oracle and Postgres => M_Reservation_Post errors in oracle
2015-02-16 09:46alostaleCategoryA. Platform => 03. Procurement management
2015-02-26 18:40umartirenaStatusacknowledged => scheduled
2015-02-26 18:40umartirenafix_in_branch => pi
2015-02-26 18:56umartirenaReview Assigned To => umartirena
2015-02-26 18:56umartirenaNote Added: 0074994
2015-02-26 18:56umartirenaStatusscheduled => closed
2015-02-26 18:56umartirenaResolutionopen => no change required
2015-02-26 18:56umartirenaRelationship addedrelated to 0029063
2015-02-26 18:57umartirenaRelationship addedrelated to 0028510

Notes
(0074500)
alostale   
2015-02-16 09:43   
The actual problem is due to incorrect M_Reservation_Trg trigger implementation which causes "ORA-04091: M_Reservation is mutating" error.

If, as explained in the steps to reproduce, commits are prevented in ad_update_pinstance calls, an exception is raised and this code is executed:

EXCEPTION
WHEN OTHERS THEN
  v_ResultStr:= '@ERROR=' || SQLERRM;
  DBMS_OUTPUT.PUT_LINE(v_ResultStr) ;
  IF (p_pinstance_id IS NOT NULL) THEN
    ROLLBACK;
    AD_UPDATE_PINSTANCE(p_pinstance_id, NULL, 'N', 0, v_ResultStr, 'N') ;
  ELSE
    RAISE;
  END IF;
  RETURN;
END M_RESERVATION_POST

which rollbacks the whole transaction, causing the ad_pinstance row created in the transaction to be rolled back so DAL can't find it afterward. Removing the ROLLBACK line prevents this failure, though the real problem is within the trigger.
(0074994)
umartirena   
2015-02-26 18:56   
Finally not a Platform issue after fixing the following issue:

https://issues.openbravo.com/view.php?id=29063 [^]

The flag to not to commit in M_RESERVATION_PROCESS will be added in the following issue:

https://issues.openbravo.com/view.php?id=28510 [^]