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.