This discussion is archived
2 Replies Latest reply: Mar 21, 2012 5:12 AM by lunicon RSS

mgp apply of updatable view error

lunicon Newbie
Currently Being Moderated
I encountered a strange error during MGP Apply:
only sql query from log:
clog: Subscription.prepStatement: ALL: SQL: SELECT DMLTYPE$$,     VERSION$$, "CONTRACTID","STATUSID","VALIDDATE","SIGNDATE","ENDDATE","DEPARTID","SALEOFFICEID","SELLER1ID","SELLER2ID","AUTHORID","INSURERID","INSURERISJUR","INSOBJECTID","BENEFICIARYID","BENEFICIARYISJUR","INSPRODUCTID","COSTTC","STRAHPREMBYTARIF","SPECIALPREMIUM","KKR","KREG","COLOR","CFO","FUNDID","PAYMENTPERIODICITYID","RRIGHTOFPOSSESSION","ISAGGREG","INSPERIODID","ADDPARAMETER","AUTOKASKOCALC","AUTOKASKOPREM","CONTRACTINSPARENT","WEBMADE","DOCNUMBER","DATECREATE","NOTE","STARTDATE","PREMIUM","KT","DEDUCTIBLE","DRIVERSLIMIT","STATEDATE","INSURERADDRESSID","INSURERCERTIFICATEID","GIVEDATE","SPECAILMARKS","AVARCOMCARDNUM","KBM","KN","KM","KVS","KO","BASICRATETARIFF","NUMSTROSAGO","OTHERCOMP","OTHERCOMPNOLOSS" FROM "MOBILEADMIN"."CFM$PI_V_B2B_CONTRACT" WHERE CLID$$CS=? and TRANID$$= ?  and SEQNO$$= ?  ORDER BY DMLTYPE$$     ASC: 
clog: Subscription.prepStatement: ALL: SQL: UPDATE "SOGINS"."V_B2B_CONTRACT" SET "CONTRACTID"=?,"STATUSID"=?,"VALIDDATE"=?,"SIGNDATE"=?,"ENDDATE"=?,"DEPARTID"=?,"SALEOFFICEID"=?,"SELLER1ID"=?,"SELLER2ID"=?,"AUTHORID"=?,"INSURERID"=?,"INSURERISJUR"=?,"INSOBJECTID"=?,"BENEFICIARYID"=?,"BENEFICIARYISJUR"=?,"INSPRODUCTID"=?,"COSTTC"=?,"STRAHPREMBYTARIF"=?,"SPECIALPREMIUM"=?,"KKR"=?,"KREG"=?,"COLOR"=?,"CFO"=?,"FUNDID"=?,"PAYMENTPERIODICITYID"=?,"RRIGHTOFPOSSESSION"=?,"ISAGGREG"=?,"INSPERIODID"=?,"ADDPARAMETER"=?,"AUTOKASKOCALC"=?,"AUTOKASKOPREM"=?,"CONTRACTINSPARENT"=?,"WEBMADE"=?,"DOCNUMBER"=?,"DATECREATE"=?,"NOTE"=?,"STARTDATE"=?,"PREMIUM"=?,"KT"=?,"DEDUCTIBLE"=?,"DRIVERSLIMIT"=?,"STATEDATE"=?,"INSURERADDRESSID"=?,"INSURERCERTIFICATEID"=?,"GIVEDATE"=?,"SPECAILMARKS"=?,"AVARCOMCARDNUM"=?,"KBM"=?,"KN"=?,"KM"=?,"KVS"=?,"KO"=?,"BASICRATETARIFF"=?,"NUMSTROSAGO"=?,"OTHERCOMP"=?,"OTHERCOMPNOLOSS"=? WHERE "CONTRACTID"=? AND ("CONTRACTID") IN (SELECT "CONTRACTID" FROM "SOGINS"."CVR$CONTRACTINS" WHERE VERSION$$=?): 
clog: Subscription.prepStatement: ALL: SQL: UPDATE "SOGINS"."V_B2B_CONTRACT" SET "CONTRACTID"=? WHERE "CONTRACTID"=?: 
clog: Subscription.prepStatement: ALL: SQL: INSERT INTO     "MOBILEADMIN".C$EQ(CLIENTID,TRANID$$,SEQNO$$,PUBLICATION,MESSAGE_TEXT) VALUES(?,?,?,?,?): 
clog: Subscription.prepStatement: ALL: SQL: INSERT INTO     "SOGINS"."CEQ$CONTRACTINS"(DMLTYPE$$, VERSION$$,"CONTRACTID","STATUSID","VALIDDATE","SIGNDATE","ENDDATE","DEPARTID","SALEOFFICEID","SELLER1ID","SELLER2ID","AUTHORID","INSURERID","INSURERISJUR","INSOBJECTID","BENEFICIARYID","BENEFICIARYISJUR","INSPRODUCTID","COSTTC","STRAHPREMBYTARIF","SPECIALPREMIUM","KKR","KREG","COLOR","CFO","FUNDID","PAYMENTPERIODICITYID","RRIGHTOFPOSSESSION","ISAGGREG","INSPERIODID","ADDPARAMETER","AUTOKASKOCALC","AUTOKASKOPREM","CONTRACTINSPARENT","WEBMADE","DOCNUMBER","DATECREATE","NOTE","STARTDATE","PREMIUM","KT","DEDUCTIBLE","DRIVERSLIMIT","STATEDATE","INSURERADDRESSID","INSURERCERTIFICATEID","GIVEDATE","SPECAILMARKS","AVARCOMCARDNUM","KBM","KN","KM","KVS","KO","BASICRATETARIFF","NUMSTROSAGO","OTHERCOMP","OTHERCOMPNOLOSS",CLIENTID$$,TRANID$$,SEQNO$$)     VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?): 
clog: MGP.apply_client_changes(Connection,String,Set): MANDATORY: ALL: Exception     caught in transaction, rolling back...: 
      Exception: java.sql.SQLSyntaxErrorException: ORA-00904: "BASICRATETARIFF": недопустимый идентификатор
full log can be found here MGPAPPLY_RU_AVTOMEN_530.7z

I have updatable view V_B2B_CONTRACT based on multiple tables
set CONTRACTINS as parent table and set CONTRACTID as parent key hint

During the "MGP Apply" after update of view V_B2B_CONTRACT it tries to insert conflict row of view to error list of primary table CEQ$CONTRACTINS directly but it can find BASICRATETARIFF from other table.
Did I miss something?
  • 1. Re: mgp apply of updatable view error
    lunicon Newbie
    Currently Being Moderated
    I did an experiment: described below has done several times
    <ul><li>Synchronize database</li>
    <li>Change one one column in updatable view in client database</li>
    <li>Synchronize database</li>
    <li>Wait untill MGP APPLY complete</li></ul>
    update error appears every second try, unlike in the log starts with the lines:
    clog: MGPClientStatus_impl.addApplyConflict: ALL: FUNCTION: Invoking
    clog: MGP.apply_client_DML: FINEST: GENERAL: UPDATE conflict,     conflict_rule=S
    clog: MGP.bindPKV: ALL: FUNCTION: Entering
    clog: Subscription.getViewPrimaryKey: ALL: FUNCTION: Entering
    It turns, update of view leads to apply conflict with the next update.

    My view INSTEAD OF trigger updates base tables for which I have done dependencyHint.
    It is not clear why MGPAPPLY is trying to updated the primary/base table of view in apply conflict?
    That is the cause of the conflict?

    Probably need to use Queue-Based publication item as do not understand the work of MGP...
  • 2. Re: mgp apply of updatable view error
    lunicon Newbie
    Currently Being Moderated
    I add publication items of view base tables in the same publication where view publication item Re: view snapshot, but noting changed... Same error in apply.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points