Update query on linked MySQL table from SQL Server

Question

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

1. Delete all rows from the MySQL table that do not satisfy the condition

2. Insert all new rows in the MySQL table that satisfy the condition

3. Update all rows in the MySQL server that satisfy the condition and have different data between MySQL and SQL Server

Steps 1 and 2 always run without a problem. But step 3 won't run if there is anything to update. The query fails with the following exception: The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.].

This is the query that is 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))
  ));

Anybody has an idea about how to prevent this?

Answer 1

You might try creating a second table in mysql, doing an insert from sql-server into that empty table for all changed lines and doing Step 3 between the two mysql tables.

Answer 2

try to not using sub-query in your where statement. Sub-query may return more than one row, and then you got the error.

Answer 3

Hi,

try creating a view which has source, destination and has_changed column between and has linked tables joined. then you can issue query

update vi_upd_linkedtable set destination=source where has_changed=1

Answer 4

This is a shot in the dark, but try adding FOR UPDATE or LOCK IN SHARE MODE to the end of your subselect query. This will tell MySQL that you are trying to select stuff for an update within your transaction and should create a row level write lock during the select rather than during the update.

From 13.2.8.3. SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE Locking Reads:

SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify them. The rows read are the latest available, so if they belong to another transaction that has not yet committed, the read blocks until that transaction ends.

Answer 5

For rows where the names are the same, the update is a no-op.

You don't save any work by trying to filter out the rows where they're the same, because the data still has to be compared across the link. So I can't see any benefit to the subquery.

Therefore the query can be simplified a lot:

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;

Eliminating the subquery might make your the locking issue go away. MySQL does have some issues combining a select and an update on the same table in a given query.

Answer 6

Try this query instead:

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 <> k.e-mail
   )

1. It's best practice to use ANSI joins and properly separate JOIN conditions from WHERE conditions.

2. It's more readable to use aliases for all your tables instead of long table names throughout the query.

3. It's best to use the aliases for all column references instead of leaving them blank. Not only is it a good habit and makes things clearer, it can avoid some very nasty errors in inner-vs-outer table references.

4. If performance is also an issue: linked server joins sometimes devolve to row-by-row processing in the DB data provider engine. I have found cases where breaking out part of a complex join across a linked server into a regular join followed by a cross apply hugely reduced the unneeded rows being fetched and greatly improved performance. (This was essentially doing a bookmark lookup, aka a nonclustered index scan followed by clustered index seek using those values). While this may not perfectly mesh with how MySql works, it's worth experimenting with. If you can do any kind of trace to see the actual queries being performed on the MySql side you might get insight as to other methods to use for increased performance.

5. Another performance-improving idea is to copy the remote data locally to a temp table, and add an ActionRequired column. Then update the temp table so it looks like it should, putting 'U', 'I', or 'D' in ActionRequired, then perform the merge/upsert across the linked server with a simple equijoins on the primary key, using ActionRequired. Careful attention to possible race conditions where the remote database could be updated during processing are in order.

6. Beware of nulls... are all those columns you're comparing non-nullable?

Answer 7

Try this. I wrote several of these 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(k.e-mail,'')
   )

Using the ISNULL allows you to null your columns.