Resend missing records in dbremote replication?

[Former Member]

As a followup question for this problem:

http://sqlanywhere-forum.sap.com/questions/18293/dbremote-on-windows-linux-character-encoding-proble...

Is there a way to resend the missing/failed rows to the remote database(s)?

I. 2013-09-03 09:03:00. INSERT INTO DBA.CardEntries(CardEntries,ClientCard,CreationDate,Amount,
                              REMOTENAME,Salesperson,EntryProcessed,BatchID)
                        VALUES ('O000CB','J001UW','12:26:15.446918 2013/08/30',169,'SiteTest1','Sil Sch.r',0,NULL)
E. 2013-09-03 09:03:00. SQL-Anweisung fehlgeschlagen: (-131) Syntaxfehler bei 'Sil Sch.,0,NULL)' in Zeile 3
E. 2013-09-03 09:03:00. Wird übersprungen:
E. 2013-09-03 09:03:00. INSERT INTO DBA.CardEntries(CardEntries,ClientCard,CreationDate,Amount,
                              REMOTENAME,Salesperson,EntryProcessed,BatchID)
                        VALUES ('O000CB','J001UW','12:26:15.446918 2013/08/30',169,'SiteTest1','Sil Sch.r',0,NULL)

A remote reset did not clear up things as I have expected, but instead fails with this error on the remote:

I. 2013-09-16 17:16:00. DELETE FROM DBA.Salesperson
I. 2013-09-16 17:16:01. DELETE FROM DBA.StartIDS
E. 2013-09-16 17:16:01. SQL-Anweisung fehlgeschlagen: (-20000) RAISERROR ausgef.hrt: Cannot delete parent StartIDS if children still exist in CardEntri

E. 2013-09-16 17:16:01. Wird übersprungen:
E. 2013-09-16 17:16:01. DELETE FROM DBA.StartIDS
I. 2013-09-16 17:16:01. DELETE FROM DBA.GROUPSPERADDRESS
I. 2013-09-16 17:16:01. DELETE FROM DBA.GlobVars
I. 2013-09-16 17:16:01. DELETE FROM DBA.GD
I. 2013-09-16 17:16:01. DELETE FROM DBA.ClientCard
I. 2013-09-16 17:17:33. DELETE FROM DBA.AddressGroups
I. 2013-09-16 17:17:33. DELETE FROM DBA.ADDRESS
I. 2013-09-16 17:17:39. DELETE FROM DBA.CardEntries
I. 2013-09-16 17:17:39. SELECT Fullname FROM DBA.Salesperson
I. 2013-09-16 17:17:39. 98 Zeilen synchronisiert
I. 2013-09-16 17:17:39. SELECT REMOTENAME, STARTVALUE, Stammfiliale FROM DBA.StartIDS
E. 2013-09-16 17:17:39. SQL-Anweisung fehlgeschlagen: (-193) Prim.rschl.ssel f.r Tabelle 'StartIDS' ist nicht eindeutig
E. 2013-09-16 17:18:34. Empfang von Nachrichten fehlgeschlagen
I. 2013-09-16 17:18:34. Ausführung abgeschlossen

[VolkerBarth]

As to the REMOTE RESET: That won't work here as skipped statements like this one lead to inconsistent data but they don't prevent the general application of messages, so log offsets will be advanced as usual, and a REMOTE RESET won't set these offsets back to an older state..

AFAIK, this behaviour is different for FK violations and other errors.

[Former Member]

Thanks, probably a reextract will be the only safe way then.. 😞

[VolkerBarth]

Well, if "fixing the data" is too complex and/or possibly too error-prone, and re-extracting is not too uncomfortable for the customer, I'd choose the "normal route" (aka re-extracting), too...

FWIW, we have "fixed" such missing data in our SR setup now and then "manually", but only when the effect was understood, and when the errors comprised just a few rows. So that can be a save way, too - as usually with SQL Remote, it's important to understand the results of comreplication:)

[VolkerBarth]

In case the missing rows don't have according child records, you might be fine to delete them in the cons and insert them back again.

The delete will be an no-op in the remotes where the insert have been skipped.

If the RI relationship is more difficult, and there are dependent rows that are also missing, it's harder to give advice.

FWIW, you can also try to use PASSTHROUGH ONLY mode to sent statements to remotes outside of the usual replication logic.

---

I surely have to note that I would always test "data fixes" like these thorougly in a test environment...