Updating a row through Linked Server to MySQL DB

Question

I'm trying to update a single row using linked server from MS SQL 2012 to MySQL DB.

I run this query:

UPDATE b2b...product set [index]=`test_value` where id=12345;

Error Message:

The OLE DB provider "MSDASQL" for linked server "b2b" could not UPDATE table "[b2b]...[product]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

I have googled this error and everyone recommends to check option "Return matched rows instead of affected rows" in MySQL ODBC Data Source Configuration, so I tried to do that, but it was already checked. So I tried to uncheck this, it didn't help, so I re-checked this setting.

If checking this option doesn't help, what else could I do?

By the way I tried the same update using MySQL WorkBench and it works fine.

Edit: this is very weird. When I update a particular row using MySQL WorkBench then I'm able to update it using SQL Server Management Studio. I tried it on several rows. BTW, I had to set safe mode in MySQL WorkBench in order to be able to update rows.

Answer 1

You can use

EXEC('UPDATE product set [index]=''test_value'' where id=12345;') AT b2b

as an alternative syntax, which executes the query in pass-through mode.

The reason why you're getting the optimistic locking error is probably because the query can't be run in pass-through, so SQL Server downloads the whole table, locates the row to update and then pushes the row back to the linked server. Between the moment the row is read and the moment the row is written, the value has changed in the linked server, so you have an optimistic locking violation.