sql server - sql replication error - record exists on Publisher but it's trying to delete/insert that on Subscriber, so Foreign Key/Primary Key violation -

-

we have transactional one-way replication running, , today started getting error :

the delete statement conflicted reference constraint "fk_branchdetail_branch".   conflict occurred in database "locationdb", table "dbo.branchdetail", column 'branchnumber'.  message: row not found @ subscriber when applying replicated command.  violation of primary key constraint 'pk_branch'. cannot insert duplicate key in object   'dbo.branch'. duplicate key value (23456)  disconnecting subscriber 'sqldb03'

publisher - sqldb02.locationdb
subscriber - sqldb03.locationdb

tables on both servers:
branch (branchnumber primarykey)
branchdetail (branchnumber foreignkey references previous table)

select * sqldb02.locationdb.branch -- contains : 23456,'texas',... select * sqldb03.locationdb.branch -- contains : 23456,'null',...

the problem - branchnumber in question '23456' exists in 4 tables (publisher..branch, publisher..branchdetail, subscriber..branch, subscriber..branchdetail).
yet, when ran trace on subscriber, see repeated commands like:

exec [sp_msdel_dbobranch] 23456 -- throws fk violation exec [sp_msins_dbobranch] 23456,'null',... -- throws pk violation

i'm guessing it's trying update record on subscriber doing delete + insert. it's unable to..

users not have access modify subscriber table. can modify publisher table through ui, , have been doing long time without issue. there job updates publisher table once every night. started getting error around noon today.

our last resort reinitialize subscription off-hours.
ideas have caused , how fix it?

for transactional replication, updating primary key column replicated delete + insert (deferred update). because pk column has fk constraint, delete fail @ subscriber. have couple workarounds prevent happening moving forward:

  • disable replicating of fk constraints, not needed one-way replication. why? users not entering data @ subscriber, there no need maintain referential integrity, , transactional replication replicates log txns, order of txns pretty guaranteed, no need worry 1 txn showing before another.
  • enable trace flag 8207 on publisher. if single row updated, replicated single update statement. if update affects multiple rows, replicated deferred update.
  • somehow block users updating pks

imo, best bet first option.

how fix this? reinit 1 way. if can manaully disable or drop fk constraint on subscriber, easiest solution.


Comments

Post a Comment

Popular posts from this blog

yii2 - Yii 2 Running a Cron in the basic template -

-
i have seen instructions on running cron in advanced template, cant figure out how on basic template. have following controller in basic controllers folder. <?php namespace app\controllers; use yii\console\controller; /** * cron controller */ class croncontroller extends controller { public function actionindex() { echo "cron service runnning"; } public function actionmail($to) { echo "sending mail " . $to; } } i have navigated root of application , tried these comands yii cron php yii cron im getting unknown comand "cron" i know old question failed find actual answer this. if @ code yii exec file you'll notice requires /common/config/aliases.php file console/config/main.php. can naturally change these or copy on these advanced template. hope solves problem wanting same thing.
Read more

asp.net - 'System.Web.HttpContext' does not contain a definition for 'GetOwinContext' Mystery -

-
i realize question might seem trivial some, it's these types of things find myself fighting quite bit , want make sense of despite seeming losing battle in .net (for me anyway). so, if following: using system.web; ... applicationuser user = system.web.httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); that produces error in title , red getowincontext() , error cannot resolve symbol 'getowincontext()' however, if following (remove system.web in front of httpcontext ), works expected (or @ least no errors): using system.web; ... applicationuser user = httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); however, if (same line that's working using system.web commented out): //using system.web; ... applicationuser user = httpcontext.getowincontext().getusermana...
Read more

wso2esb - How to concatenate JSON array values in WSO2 ESB? -

-
we have json object below, expected result in: "(001),(011),(089),(120)". can suggest how iterate json array , concat values mention . "(001),(011),(089),(120)" thanks in advance. { "element": { "values": { "agentid": "aaaaa", "transactiondata": [ { "no": "001" }, { "no": "011" }, { "no": "089" }, { "no": "120" } ] } } } you can using iterate mediator, filter mediator , properties operation scope. try solution. @ end have (001),(011),(089),(120) value in concat-data property. have added complete proxy reference. <?xml version="1.0" encoding=...
Read more