Synchronization creates new row rather than update old row

  • 70Views
  • Last Post 02 November 2021
0
votes
John Coolidge posted this 21 October 2021

I have set up synchronization to move data from a website (mysql) to sql server and vice versa. When data is updated on the website, instead of syncing that data to the same row in sql server, the sync process creates a new row where the only difference between that row and the row already in the table that it should be updating is that updated column. What I end up with on both mysql and sql server are tables with nearly dupicate data. Is there an option to overwrite data in the sync process that I'm missing here? I assumed that Skyvia would automatically map data from one table to another, updating any data changes rather than creating new rows.

 

The screenshot provided shows (sql server) rows 2 and 8 as nearly identical. What I should see is one row with the most recent data present.

Attached Files

Order By: Standard | Newest | Votes
0
votes
Nataliia Nikulina posted this 22 October 2021

 

Hello John,

 

Thank you for contacting Skyvia Support team. 

 

When synchronization is performed for the first time, it does not check whether or not the records in sources are identical — it simply copies data from one side to another and vice versa and “maps” the original records to their copies in another source. During subsequent synchronizations, if a record is modified or deleted in one source, Skyvia modifies or deletes the data mapped to this record in another source, and vice versa.

Thus, when the synchronization was performed for the first time, it just copied all the records from MySQL to SQL Server and all the records from MySQL to SQL Server.

If there were records with the same names in both objects, then they may have been duplicated.

For more details please view the documentation on synchronization https://docs.skyvia.com/data-integration/synchronization/ 

 

Let us know if we may help you more. 

 

Best regards,

Nataliia,

Customer Support Engineer  

 

0
votes
John Coolidge posted this 25 October 2021

That's exactly what it's not doing. I'm attaching another screenshot where I made some minor changes to one record in the MySQL database and the data it sent over to the SQL Server duplicated record rows instead of overwriting the original record. I can't seem to find a solution for this. You can see the original line item in line 6 and the duplicated records in lines 14 and 15.

Duplicate rows

0
votes
John Coolidge posted this 25 October 2021

I didn't hit reply to your message, Nataliia, for my response to yours, so I'll just say that if you look at the most recent post, I've submitted a reply that was meant for you. After trying to implement a change, my results are the same: Skyvia adds a new row rather than updating the old row. see the post with attachment for more details.

0
votes
Nataliia Nikulina posted this 28 October 2021

Hello John,

 

Thank you for the update.

 

Please send us the package link and the run Id where the records are inserted instead of updated. 

 

We look forward to hearing from you.

 

Best regards,

Nataliia,

Customer Support Engineer

0
votes
John Coolidge posted this 28 October 2021

The RunID = 71067595

Package Link  https://app.skyvia.com/#/85833/packages/137901

Is the above what you are requesting?

 

The results from every run always say the same thing: 0 Success Rows and 0 Error Rows. I assume that 'success rows' means how many rows were updated, which would make sense because no matter how many rows get added to the source table to target, none of them are updated, only new rows are added.

0
votes
Nataliia Nikulina posted this 01 November 2021

Hello John,

 

Thank you for the update. 

 

History details window of the run shows not only updates but also new and deleted records as well as the error records.

Find a screenshot for your reference below:

  

Please clarify if the tables have an autogenerated primary key, whether the corresponding tracking_store tables store any data after changes in the tables and before the launch of the package.

 

As an option, you can start synchronization from scratch, but keep in mind that most likely the target needs to be cleared to avoid even more duplicates.

 

We look forward to hearing from you.

 

Best regards,

Nataliia,

Customer Support Engineer

0
votes
John Coolidge posted this 01 November 2021

Yes, the tables have an autogenerated primary key and yes, the tracking_store stores changes before the package is launched. I will try to resync from scratch and see if that works.

0
votes
Nataliia Nikulina posted this 02 November 2021

Hello John,

 

Thank you for the update.

 

We look forward to hearing from you.

Feel free to contact us in case of any questions or issues.

 

Best regards,

Nataliia,

Customer Support Engineer

 

Close