ER CDR SYNC: cdr sync does not start due to some locks in the to be replicated table: any way to set some dirty read sync ?
Answered: amber (hollow confidence) — Andreas explains cdr sync runs with a hard-coded lock wait of 30 seconds, so no dirty-read option exists; the asker concludes the source table must be free of transactions briefly. No workaround is found.
Advisory only.
Posted in 2021
Eric Vercelletto could not run cdr sync on 12.10.FC14 because locks on the source table made its initial select count(*) fail. Paul Watson suggested cdr check repair, since sync is deprecated. Andreas Legner said cdr sync uses a hardcoded lock wait of 30 seconds. Ashok Sharma and Andreas suggested truncating targets first, but Eric had done so. The sync needs a moment without transactions; no workaround.
Topics: High Availability & Replication, Transactions, Locking & Isolation
Hi all I am despererately trying to cdr sync a few tables from one instance to another. Most of replicates work fine, but a few of them have some locks in the table, that prevent the initial "select count(*) from table" statement fired by cdr sync to run successfully, therefore crashing the cdr sync command . this select count(*) is execute in the scanRepl1NWayMatchMerge function ... this is 12.10 FC14 Is there any undocumented option of cdr sync that can pass this select count(*) in dirty read, on any tricky way ? Or just way for locks to be gone (maybe some day ? ) Thanks for your lights Eric ------------------------------ [eric] [Vercelletto] [] [Founder] [kandooerp.org] [Pont l'Abbé] [France] [+33 626 52 50 68] Disclaimer: My own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. ------------------------------ #Informix
Have you tried cdr check repair - cdr sync has been deprecated AFAIK FYI export MyTtrace=1 will/should dump all the sql to stdio Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC
Thanks Paul. The problem is locks in the replicated table that crash the select count stmt at thebeginning ------------------------------ [eric] [Vercelletto] [] [Founder] [kandooerp.org] [Pont l'Abbé] [France] [+33 626 52 50 68] Disclaimer: My own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. ------------------------------
Hi Eric,
whole 'cdr sync' runs with a (hardcoded) "set lock mode to wait 30" after which e.g. said "select count(*) from <table_to_sync>" would fail with
Error returned from scanReplNWayMatchMerge: Execute statement: select count(*) from "owner".tabname
SQLCODE -252 ISAM -154
Error Details:
252: Cannot open file 'csql.iem'.
154: Cannot open file 'cisam.iem'.
Current Connection:conn4, Database:db@er_node
command failed -- SQL failure (195)
... for which "crashing", imho, would sound a tad too ugly ;-)
HTH,Andreas
------------------------------
Andreas Legner
------------------------------
Crashing is maybe too hard terminology The background issue is that, due to that, it is impossible to sync a replicate if there is 1 lock in the source table ... This would imply a production stop ( even for one minute or less ) so that the select count(*) can be executed. Are there any addition queries on the source table after that select count(*) or does it go ALL thru logical logs processing ? Customer is not happy ...
Hello Eric, FYI. Where we used Primary-Target ER model, we had the luxury to truncate the target table first before sync . We never ran into an issue. Regards, Ashok ------------------------------ Ashok Sharma ------------------------------
True, empty target tables would greatly simplify a sync ... and, among other savings, allow bypassing the logical logs. ------------------------------ Andreas Legner ------------------------------
Ashok and Andreas: Target tables have been truncated previously ... It seems that it is necessary to obtain a lock at the beginning of the operation, so starting it with no current transactions on the source table seems to be a must, at least for a few seconds And thanks for your input Have a great week end Eric Vercelletto Data Management Architect and Owner / Begooden IT Consulting KandooERP Founder and CTO IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020 Tel: +33(0) 298 51 3210 Mob : +33(0)626 52 50 68 skype: begooden-it Google Hangout: eric.vercelletto@begooden-it.com Email: eric.vercelletto@begooden-it.com www : http://www.vercelletto.com www https://kandooerp.org