MySQL Bugs: #87814: unreliable where clause when editing

Bug #87814	unreliable where clause when editing
Submitted:	20 Sep 2017 9:27	Modified:	18 Dec 2018 5:34
Reporter:	Hugues-Yanis Amanieu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL for Windows: MySQL for Excel	Severity:	S2 (Serious)
Version:	1.3.7	OS:	Microsoft Windows
Assigned to:		CPU Architecture:	Any
Tags:	clause, Excel, where

Description:
Editing a MySQL table using MySQL for Excel cannot work. When editing a field, then committing the change, the "where" clause of the sent query makes no sense. It contains the old entered value and checks whether it is equal to NULL. Of course it raises a warning and doesn't commit the changes if applied.

What is expected is that the where clause use the primary key 

Another user reported this error on stackoverflow: https://stackoverflow.com/questions/45748599/cannot-edit-mysql-table-from-excel

How to repeat:
Try to edit a mysql table with mysql for excel.

Suggested fix:
Change the automatically generated commit where the clause field only contains the primary key field(s).

Hello Hugues,

Thank you for the bug report.
I tried to reproduce the issue at my end using MS Excel 2013,  MySQL for Excel 1.3.7 but not seeing any issues in edit and commit data.
Could you please provide repeatable test case (create table, table data, screenshot, etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

Sorry for yet another comment, I think I found the root of the problem: it's linked to floating numbers. It's looking for a value exactly equal to the inserted floating number (in excel with only 1 digit). However in the DB, there is more than 1 digit after the coma, so it doesn't find the value and it results in an error saying the row could not be found.
So you need to use floating numbers to reproduce the error.

All in all, the where clause must be better automated and only with primary key fields.

Hello Hugues,

Thank you for the feedback and testcase.
Verified this behavior on MySQL for Excel 1.3.7 version.

Thanks,
Chiranjeevi.

Posted by developer:
 
What is being reported is not a bug.
The "weird" where clauses are being assembled for optimistic updates (you can google that up).
This can be easily turned off in the add-in options, in the "SQL Queries" tab.

For more information to understand how optimistic updates work in a multi-user environment you can read the following blog post:
https://blogs.oracle.com/mysql/optimistic-updates-for-edit-data-operations-in-mysql-for-ex...