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:Windows
Assigned to: CPU Architecture:Any
Tags: clause, Excel, where

[20 Sep 2017 9:27] Hugues-Yanis Amanieu
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).
[20 Sep 2017 10:53] Chiranjeevi Battula
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.
[20 Sep 2017 11:24] Hugues-Yanis Amanieu
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.
[20 Sep 2017 12:33] Chiranjeevi Battula
Hello Hugues,

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

Thanks,
Chiranjeevi.
[18 Dec 2018 5:34] Javier TreviƱo
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...