SkyVia Replication - LastModifiedDate vs SystemModStamp - LastModifiedDate Unreliable Mechanism

  • 115Views
  • Last Post 03 May 2018
0
votes
API User Reporting posted this 30 March 2018

Greetings:

I could be wrong, but I was under the impression that SkyVia Replication had been changed to not use the LastModifiedDate, which is not a reliable mechanism of change detection because there are changes made to Salesforce objects through code which do not update the value of LastModifiedDate.

I was under the impression that you are now using SystemModStamp wherever it is available.

However, from examining activity against our target database it seems clear that LastModifiedDate is being used.

Please advise.

Respectfully,

Darryll Petrancuri

Order By: Standard | Newest | Votes
0
votes
Simon Bubnov posted this 02 April 2018

There are no updates in Skyvia by your requests that I sent to our developers at the moment.

When there are any updates by any of your requests, we will immediately notify you, and you will be the first to know about the corresponding Skyvia updates.

0
votes
API User Reporting posted this 02 April 2018

Will you please answer my specific question?

 

This issue was raised months ago and I was under the impression that there had been a change made to use SystemModStamp.

 

 

0
votes
Simon Bubnov posted this 02 April 2018

You suggested to use SystemModStamp instead of LastModifiedDate in the following post: https://support.skyvia.com/thread/skyvia-replication-breaks-when-column-is-removed-from-salesforce-object-being-replicated/?order=all#comment-40cca6f5-ee44-402f-983e-a8a9011fbcfd

 

This post is created about two weeks ago, not a month ago. Unfortunately, we haven't implemented this suggestion yet. We will notify you as soon as these changes will be uploaded to our test server, this is sooner than when you can see the changes on our production server.

0
votes
API User Reporting posted this 03 April 2018

I first raised this issue in November / December. If you'd look back through the history of all tickets I created, it's in there. This was when we first discovered problems with deleted records in Salesforce objects not being properly replicated.

0
votes
Simon Bubnov posted this 06 April 2018

I first raised this issue in November / December.

 

Thank you for pointing that. We have found this post and the corresponding request.

 

We would like to inform you that we decided to implement the possibility to use SystemModStamp instead of LastModifiedDate. We will add an option, where you can select which field to use. We will post here when this feature is supported in Skyvia.

0
votes
API User Reporting posted this 06 April 2018

Simon:

 

You'll forgive me, but I don't understand how you don't consider this a BUG in SkyVia and how it's not getting handled with an extreme sense of urgency? Replication products come with an expectation of delivering data to target systems with correctness, completeness and resilience. This issue falls into the category of completeness. By not using SystemModStamp you are not replicating data which has changed by programmatic action as compared to user action. I believe I speak for every SkyVia user who uses Salesforce as a data source that this behavior is a bug and should be treated as a critical one requring the utmost urgency in resolution.

Respectfully,

Darryll Petrancuri

 

0
votes
Simon Bubnov posted this 10 April 2018

Hello Darryll,

 

As you mentioned, we don't consider this case a bug. Only one user except you offered us to use SystemModStamp instead of the LastModifiedDate. This was a feature request, not a case when records were actually lost because of using LastModifiedDate instead of SystemModStamp, that's why this request had low priority. Now this is also rather your feature request than a real case of data loss. However, we have raised the request priority, and our developer will start working on it as soon as they finish their urgent tasks.

We will notify you when the possibility to select which column to use: SystemModStamp or LastModifiedDate to determine whether record is changed or not is implemented.

 

Best regards,

Simon

0
votes
API User Reporting posted this 10 April 2018

Simon:

I don't care whether only one other user reported it. And frankly, I find the assertion you don't consider it a bug mind numbing. 

Perhaps you don't see it as a bug because not enough users are building enterprise applications in SFDC and reporting environments in RDBMS' such as SQL Server / Azure SQL Database are using SkyVia.

If it can be clearly established a record will not be replicated when it is modified programmatically versus by user activity, then this clearly is a bug. Whether the action that modifies data is exclusively programmatic vs that of uniser interaction is NEVER a valid factor for determining whether to replicate a record. 

It DOES create data loss. The SkyVia user should not be given the choice of the use of SystemModStamp or LastModifiedDate. If SystemModStamp is present in the object being replicated (which I believe it is in every SFDC object), then it MUST be used for replication if you care about data consistency.

Darryll Petrancuri

0
votes
Simon Bubnov posted this 12 April 2018

Hello Darryll,

 

Thank you for the detailed explanation why it should be considered as a bug. I have forwarded your information to our developers. They will analyze the information and decide whether to use SystemModStamp only instead of a choice which column to use: SystemModStamp or LastModifiedDate, in order to determine whether record is changed or not. I will post here when we get any results.

 

Best regards,

Simon

0
votes
API User Reporting posted this 18 April 2018

Simon et al:

Can we please have an update on this? We've been experiencing data loss because of the reliance on LastModifiedDate.

Respectfully.

Darryll Petrancuri

0
votes
Simon Bubnov posted this 20 April 2018

Hello Darryll,

 

Our project manager has studied the question of replacing SystemModStamp with LastModifiedDate. We have taken into account your arguments and reasons. We agree that SystemModStamp should be used instead of LastModifiedDate. However, simple replacing of SystemModStamp with LastModifiedDate for all users can break old replication packages where the SystemModStamp field was not replicated (where this field was not selected in a replication task). Because of this, we decided to add an option to the Salesforce connection. This option will determine the field used - SystemModStamp or LastModifiedDate. This allows switching to SystemModStamp without modifying a replication package and resetting LastSyncTime and won't break existing replication packages.

We plan to implement this option next week. We will post here when this option is implemented.

 

Best regards,

Simon

0
votes
Simon Bubnov posted this 27 April 2018

Hello Darryll,

 

As we told you before, we planned to implement this option this week. This option is not implemented yet. We will post here when it is implemented.

Taking into account your posts and wishes, we have made changes to the work of your replication packages as a temporary solution. They now use SystemModStamp instead of LastModifiedDate.

 

Best regards,

Simon

0
votes
API User Reporting posted this 30 April 2018

Simon:

You've broken our replication packages with whatever change you have made. Please undo this change immediately.

We were not advised you were making any such change before hand and the change you have made is causing our replication packages to fail.

This is URGENT!

Please advise.

Darryll Petrancuri

0
votes
API User Reporting posted this 30 April 2018

Simon:

As Darryll stated above, it appears the SystemModStamp change was released into our production packages some time Friday and caused many of our packages to fail due to invalid column errors.  This has serious impact and we need this resolved as soon as possible.  Please advise on status of reverting code. 

Please also avoid changes to production packages which cause breakage.  In this case, I am unclear why you included the SystemModStamp in the projection that gets replicated vs. just in  the predicate to select which rows require replication.  As I'm sure you guys know, you can't add a column in the projection to replicate if they are NOT in the target schema.  

We have broken production systems and need clear resolution from you immediately.

 

Thanks,

Mort

0
votes
Simon Bubnov posted this 02 May 2018

Sorry for the late response. April 30 and May 1 are days off in Ukraine, and we were unable to answer you in these days.

 

Our implementation of a replication package with incremental updates requires LastModifiedDate and CreatedDate fields to be replicated. And after changes that we made for your packages, they require target to have the SystemModStamp column. We have already written to you that this is the reason we don't want to just switch from LastModifiedDate to SystemModStamp, and we decided to make it optional.

 

This case has two possible solutions:

1. We can rollback changes to your packages, and they will use LastModifiedDate again.

2. You can reset LastSyncTime for each package. Then select SystemModStamp for each object in each package. Then select the "Drop Tables" option, to recreate target tables and run your packages. As the result, the tables will be re-created with the SystemModStamp column. These packages will check whether a record changed, using the SystemModStamp column. All your packages will be restarted, and process all the records. We will reset your record counter for your account after this, so you won't need to pay for additional records in this case.

 

Please tell us, which option you select.

 

Best regards,

Simon

0
votes
API User Reporting posted this 02 May 2018

Simon:

Specifically related to this problem is the glaring IMMEDIATE need for a notification mechanism to advise a person immediately upon the failure of a SkyVia integration package.

We cannot continue to have to rely exclusively on visual inspection of package execution history to determine when a package has failed and continues to fail.

Had such a mechanism existed, we would have been alerted and been able to immediately advise you of the failure, if necessary.

Continual failures of replication packages in a production environment are CRITICAL and cannot be stumbled upon in a happenstance manner as is the case with visual inspection.

We previously raised this issue and nothing has been done to deliver the requested functionality. This is not a nicety, but rather a fundamental requirement of an enterprise replication solution.

Please advise.

Respectfully,

Darryll Petrancuri

0
votes
Simon Bubnov posted this 03 May 2018

Hello Darryll,

 

We have answered you on your last post in another topic of Skyvia support portal: https://support.skyvia.com/thread/immediate-requirement-for-package-execution-failure-notification-mechanism/

 

As for the issue that most of your replication packages continue to fail. You still haven't answered which of the two solutions you prefer:

1. Either rollback changes to your packages, and they will use LastModifiedDate again. Please note that if you want to use SystemModStamp later, when we add an option to use SystemModStamp or LastModifiedDate, you will still need to perform everything for the second solution in order to have SystemModStamp columns with the corresponding data in target tables.

 

2. Reset LastSyncTime in your packages, add the SystemModStamp field to each object in each task, select the "Drop Tables" check box, and rerun your packages. All data will be transferred from source to target and we will reset your subscription counter after this.

Please select the option you prefer.

 

Best regards,

Simon

Close