Refresh a query in a linked MySQL table from SQL Server

Question

I have MS SQL Server with a linked MySQL server. I need to partially synchronize a table between two servers. This is done in three stages and based on the condition:

• Remove all rows from the MySQL table that do not satisfy the condition

• Insert all new rows into the MySQL table that satisfy the condition

• Update all rows on the MySQL server that satisfy this condition and have different data between MySQL and SQL Server

Steps 1 and 2 always work without problems. But step 3 will not start if anything is updated. The query fails with the following exception: the rowset used optimistic concurrency, and the column value was changed after the contained row was last selected or re-synchronized.].

The request is being executed:

update mysqlserver...subscribers set Firstname = Voornaam, Middlename = Tussenvoegsel, Surname = Achternaam, email = e-mail from mysqlserver...subscribers as b, tblkandidaat where (b.kandidaatid = tblkandidaat.kandidaatid) and (tblkandidaat.kandidaatid in ( select subsc.kandidaatid from mysqlserver...subscribers subsc inner join tblKandidaat on (subsc.kandidaatid=tblKandidaat.kandidaatid) where (subsc.list=1) and ((subsc.firstname COLLATE Latin1_General_CI_AI <> Voornaam or (subsc.middlename COLLATE Latin1_General_CI_AI <> Tussenvoegsel) or (subsc.surname COLLATE Latin1_General_CI_AI <> tblKandidaat.Achternaam) or (subsc.email COLLATE Latin1_General_CI_AI <> tblKandidaat.e-mail)) )); 

Anyone have an idea on how to prevent this?

Answer 1

Try this query:

 update b set Firstname = Voornaam, Middlename = Tussenvoegsel, Surname = Achternaam, email = e-mail from mysqlserver...subscribers b inner join tblkandidaat k on b.kandidaatid = k.kandidaatid where b.list=1 and ( b.firstname COLLATE Latin1_General_CI_AI <> k.Voornaam or b.middlename COLLATE Latin1_General_CI_AI <> k.Tussenvoegsel or b.surname COLLATE Latin1_General_CI_AI <> k.Achternaam or b.email COLLATE Latin1_General_CI_AI <> ke-mail ) 

• It is best to use ANSI joins and properly separate JOIN conditions from WHERE conditions.

• It is more readable to use aliases for all of your tables instead of long table names throughout the query.

• It is best to use aliases for all column references instead of leaving them blank. This is not only a good habit and makes everything clearer, but also avoids very unpleasant errors in internal links to external links.

• If performance is also a problem: connected server connections are sometimes transferred line by line in the processor of the database data provider. I found cases where splitting part of a complex connection into a connected server with a regular join followed by a transition significantly reduced the number of unnecessary lines and significantly improved performance. (This essentially did a bookmark search, as well as a non-clustered index scan, followed by a clustered index search using these values). Although this may not be entirely consistent with MySql, it’s worth experimenting. If you can take any footprints to see the actual queries executed on the MySql side, you can get an idea of ​​the other methods used to improve performance.

• Another performance-improving idea is to copy the deleted data locally to the temporary table and add an ActionRequired column. Then update the temporary table so that it looks as it should by placing “U”, “I” or “D” in the ActionRequired, then merge / upsert on the linked server using simple equijoins on the primary key using ActionRequired. Careful attention to possible race conditions in which the remote database can be updated during processing is in order.

• Beware of zeros ... are all the columns you are comparing not null?

Answer 2

You can try to create a second table in mysql by inserting from the sql server into this empty table for all changed rows and doing Step 3 between the two mysql tables.

Answer 3

try not to use the subquery in your where statement. Sub-query may return more than one row, and then you get an error.

Answer 4

try creating a view that has a source, destination, and has_changed column between links and related tables. then you can issue a request

update vi_upd_linkedtable set destination = source where has_changed = 1

Answer 5

This is a shot in the dark, but try adding FOR UPDATE or LOCK IN SHARE MODE at the end of your subquery request. This will tell MySQL that you are trying to select the material to update in your transaction and should create row-level write locks during select and not during update .

From 13.2.8.3. SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE Read lock :

SELECT ... LOCK IN SHARE MODE sets the split mode lock in read lines. General mode locking allows other sessions to read lines, but not to change them. The lines read last are available, therefore, if they belong to another transaction that has not yet completed read blocks until the transaction is completed.

Answer 6

For strings where the names are the same, update is non-op.

You are not saving any work by trying to filter out rows where they are the same, since the data still needs to be compared by reference. Therefore, I do not see any benefit to the subquery.

Therefore, the query can be simplified:

 update mysqlserver...subscribers set Firstname = Voornaam, Middlename = Tussenvoegsel, Surname = Achternaam, email = e-mail from mysqlserver...subscribers as b join tblkandidaat on b.kandidaatid = tblkandidaat.kandidaatid; where b.list = 1; 

Removing the subquery may cause the lock issue to disappear. MySQL has some problems combining select and update in the same table in a given query.

Answer 7

Try it. I wrote a few of them today.

 update b set Firstname = Voornaam, Middlename = Tussenvoegsel, Surname = Achternaam, email = e-mail from mysqlserver...subscribers b inner join tblkandidaat k on b.kandidaatid = k.kandidaatid where b.list=1 and ( ISNULL(b.firstname,'') COLLATE Latin1_General_CI_AI <> ISNULL(k.Voornaam,'') or ISNULL(b.middlename,'') COLLATE Latin1_General_CI_AI <> ISNULL(k.Tussenvoegsel,'') or ISNULL(b.surname,'') COLLATE Latin1_General_CI_AI <> ISNULL(k.Achternaam,'') or ISNULL(b.email,'') COLLATE Latin1_General_CI_AI <> ISNULL(ke-mail,'') ) 

Using ISNULL allows you to zero out columns.