SQL Remote Rollback

[JimDiaz]

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.

[regdomaratzki]

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

[JimDiaz]

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

[regdomaratzki]

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.

[VolkerBarth]

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...

[VolkerBarth]

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

[JimDiaz]

Reg,

Let me know where to send or post the additional information

[regdomaratzki]

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

[JimDiaz]

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'

[regdomaratzki]

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.

[regdomaratzki]

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

[VolkerBarth]

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...