Using SQL Anywhere 17.0.11.7058. We have a single consolidated with SQL Remote replication to approximately 200 remote users. At least one of these remotes is experiencing rollback’s of some of the data applied by DBRemote.

Translating the consolidated database log file, I can see the following statement is being sent to 3 publications

--PUBLICATION-1030-04103716670382-0025-SUBSCRIBE
--PUBLICATION-1030-04103716670382-0027-SUBSCRIBE
--PUBLICATION-1030-04103716670382-0030-SUBSCRIBE
--INSERT-1030-04103716670382
INSERT INTO ECO.AddressesEmail(EmailAddressId,DomainName,LocalAddress,
      FullAddress,RecordActive,RecordInserted,RecordUpdated,RecordReceived)
VALUES(450115,'xxx.xxx.xxx','xxxx.x.xxxx.xxx','xxxx.x.xxx.xxx@xxx.xxx.xxx',1,'2022-08-15 10:13:30.469','2022-08-15 10:13:30.469','2022-08-15 10:13:30.469001')
go
--REMOTE-1030-04103716670502-0930-RECEIVED-NA-06943578838
--COMMIT-1030-04103716670518-2022-08-15 12:15:36.993
COMMIT WORK
go

In a remote database where this information is missing I see the following in the translated log file

--PUBLICATION-1007-03057924498607-0025-SUBSCRIBE
--INSERT-1007-03057924498607
INSERT INTO ECO.AddressesEmail(EmailAddressId,DomainName,LocalAddress,
      FullAddress,RecordActive,RecordInserted,RecordUpdated,RecordReceived)
VALUES (450115,'xxx.xxx.xxx','xxxx.x.xxxx.xxx','xxxx.x.xxx.xxx@xxx.xxx.xxx',1,'2022-08-15 10:13:30.469','2022-08-15 10:13:30.469','2022-08-15 10:13:30.469001')
go
--REMOTE-1007-03057924498725-0101-RECEIVED-NA-04103716670529
--ROLLBACK-1007-03057924498779
ROLLBACK WORK
go

Any information on why this might be happening would be appreciated.

asked 04 Jan, 09:21

J%20Diaz's gravatar image

J Diaz
1.2k364363
accept rate: 10%

Do you have the SQL Remote log or the database engine log from the remote database at the time the rollback occurred?

What switches were used when translating the remote's transaction log? When doing a post-mortem on a transaction log I always translate "remote.log" using : "dbtran -y -a -z -d -sr -hd -n remote.sql -o remote.hd remote.log > NUL". This ensures that I always include trigger and rollback operations, SQL Remote information, in chronological order (so the output in the SQL file matches the order things were applied when the database was running) and the secret hidden switch that creates a 2nd file that may show limited information about other operations not output by dbtran.

Reg

(04 Jan, 09:34) Reg Domaratzki

I will dbtran with the switches specified and post, we do have both dbremote and server logs I'll get those as well

(04 Jan, 10:24) J Diaz
Replies hidden

IMHO, the SQL Remote log at the time the SQL was applied will provide the most useful information on why the rollback occurred. That is always the first place I would look before trying different dbtran switches.

(04 Jan, 10:31) Reg Domaratzki
1

Do you have the message log output files available (dbremote -o...) or are you using replication error handling? That should help to identify the causes, such as duplicate PKs or missing FKs...

(04 Jan, 10:38) Volker Barth
Replies hidden
1

Oops, for the first point, apparently Reg had already asked for those...

(04 Jan, 10:40) Volker Barth

Reg,

Let me know where to send or post the additional information

(04 Jan, 12:24) J Diaz
Replies hidden

Can you not post the portion of the SQL Remote log with data redacted (similar to the dbtran output) where the error occurs here?

(04 Jan, 12:51) Reg Domaratzki

Below is a snippet from the time of the rollback

I. 2022-08-15 13:15:16. Applying message from "SAMM" (96-04103715869811-04103716847089-0)
E. 2022-08-15 13:15:17. SQL statement failed: (-194) No primary key value for foreign key 'VesselsMaintenanceSchedule' in table 'MachineryHistoryMaintenanceSchedule'
I. 2022-08-15 13:15:17. Applying message from "SAMM" (96-04103715869811-04103716847089-0)
E. 2022-08-15 13:15:17. SQL statement failed: (-194) No primary key value for foreign key 'VesselsMaintenanceSchedule' in table 'MachineryHistoryMaintenanceSchedule'
E. 2022-08-15 13:15:17. SQL statement failed: (-194) No primary key value for foreign key 'VesselsMaintenanceSchedule' in table 'MachineryHistoryMaintenanceSchedule'
E. 2022-08-15 13:15:18. SQL statement failed: (-194) No primary key value for foreign key 'VesselsMaintenanceSchedule' in table 'MachineryHistoryMaintenanceSchedule'
E. 2022-08-15 13:15:18. SQL statement failed: (-194) No primary key value for foreign key 'VesselsMaintenanceSchedule' in table 'MachineryHistoryMaintenanceSchedule'
E. 2022-08-15 13:15:18. SQL statement failed: (-194) No primary key value for foreign key 'VesselsMaintenanceSchedule' in table 'MachineryHistoryMaintenanceSchedule'
E. 2022-08-15 13:15:18. SQL statement failed: (-194) No primary key value for foreign key 'VesselsMaintenanceSchedule' in table 'MachineryHistoryMaintenanceSchedule'
E. 2022-08-15 13:15:18. SQL statement failed: (-194) No primary key value for foreign key 'VesselsMaintenanceSchedule' in table 'MachineryHistoryMaintenanceSchedule'
E. 2022-08-15 13:15:18. SQL statement failed: (-194) No primary key value for foreign key 'AddressesEmail' in table 'ApplicationFeedback'
E. 2022-08-15 13:15:18. SQL statement failed: (-194) No primary key value for foreign key 'VesselsMaintenanceSchedule' in table 'MachineryHistoryMaintenanceSchedule'
E. 2022-08-15 13:15:18. SQL statement failed: (-194) No primary key value for foreign key 'VesselsMaintenanceSchedule' in table 'MachineryHistoryMaintenanceSchedule'
I. 2022-08-15 13:15:18. Received message from "SAMM" (96-04103716847089-04103718152734-0)
I. 2022-08-15 13:15:18. Applying message from "SAMM" (96-04103716847089-04103718152734-0)
I. 2022-08-15 13:15:20. Received message from "SAMM" (96-04103718152734-04103718330882-0)
I. 2022-08-15 13:15:20. Applying message from "SAMM" (96-04103718152734-04103718330882-0)
E. 2022-08-15 13:15:21. SQL statement failed: (-194) No primary key value for foreign key 'VibrationResult' in table 'VibrationResultArrival'
I. 2022-08-15 13:15:21. Applying message from "SAMM" (96-04103718152734-04103718330882-0)
E. 2022-08-15 13:15:21. SQL statement failed: (-194) No primary key value for foreign key 'VibrationResult' in table 'VibrationResultArrival'
E. 2022-08-15 13:15:21. SQL statement failed: (-194) No primary key value for foreign key 'ServiceRequestsProgress' in table 'ServiceRequestsProgressRead'
(04 Jan, 14:02) J Diaz
Replies hidden

This snippet of log does not contain an error on the ECO.AddressesEmail table, which was the source of your original question, but when I forced a FK violation at a remote database in a test and translated the log, the dbtran output matches with the dbtran output you posted above, so IMHO, it is a foreign key violation.

(05 Jan, 10:40) Reg Domaratzki
showing 5 of 9 show all flat view

The simple and direct answer to your original question appears to be :

When dbremote was trying insert a row into the ECO.AddressesEmail table, there was a foreign key defined on this table that referred to another table, and the parent record in that other table was missing, so the insert failed and was rolled back.

As Volker mentions, you can use PASSTHROUGH ONLY (or a re-extract) to try and address the missing data, but the real question IMHO is why was the parent record not in the remote database?

Reg

permanent link

answered 05 Jan, 10:44

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.4k342113
accept rate: 38%

Well, the errors related to missing PK rows explains the rollback itself. I do not know whether the WAIT_FOR_COMMIT option might make a difference here (in case the missing PK rows would be inserted later, but in the same transaction, and in case the option might be set ON in the cons and OFF in the according remote – and additionally, the DBEMOTE -g option might or might make a difference here when grouping transactions).

If the according rows really are missing in the remote, you might be able to

  • add those via PASSTHROUGH ONLY mode for that remote (something I would recommend to thoroughly test before!),
  • or need to re-extract that particular remote...
permanent link

answered 05 Jan, 03:04

Volker%20Barth's gravatar image

Volker Barth
39.2k353537806
accept rate: 33%

edited 05 Jan, 05:05

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×75
×8

question asked: 04 Jan, 09:21

question was seen: 124 times

last updated: 05 Jan, 10:44