How to solve OPENQUERY MySQL Update Issue Using ODBC DSN

This blog explains an issue you're likely to face while executing a remote UPDATE query against a MySQL server over a linked server connection using ODBC DSN based connectionstring

Scenario

Recently I was working in a project where I required data to be transferred from a remote server and do some manipulations with it. The status column in the remote server table have to be updated later. The remote server in this case was MySQL server and we were using ODBC DSNs based connectionstring.
When we try to execute an update statement against the remote server as below

UPDATE t
SET col1 = value1,
col2 = value2
FROM OPENQUERY(LINKEDSERVERNAME,'select col1,col2,col3 from mysqlserver.tablename') t
WHERE col3 =  value3

We got an error message as given below

OLE DB provider "MSDASQL" for linked server "MYLINKEDSERVER" returned message "Row cannot be located for updating. Some values may have been changed since it was last read.".
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "MSDASQL" for linked server "MYLINKEDSERVER" could not UPDATE table "[MSDASQL]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

Solution

We tried tweaking the update query but still was not able to overcome the error
I searched regarding the error and finally managed to get the solution from a forum.
The solution required tweaking a small setting in the ODBC DSN property as shown below

Once we set the Return matched rows instead of affected rows option the UPDATE query executed successfully.
This setting will allow the updating of values in MySQL to the fields which has a value already.

Conclusion

From the above illustration we can see that this setting will prevent the error happening in remote query updates due to optimistic concurrency setting. 

Hopefully this should help someone who is facing the same issue to get around the issue.