Bug #93204 Mysql Odbc cannot update an existing row using a server-side keyset cursor
Submitted: 15 Nov 2018 7:55 Modified: 21 May 8:52
Reporter: Ciprian Anton Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:8.0 (a) OS:Microsoft Windows
Assigned to: CPU Architecture:x86

[15 Nov 2018 7:55] Ciprian Anton
Description:
If we are trying to update an existing row, using a server side cursor and a cursor type different than adOpenForwardOnly, we get the following error:

Error:00205540
[MySQL][ODBC 8.0(a) Driver][mysqld-8.0.11]Build WHERE -> insert_fields() failed.

Is this limitation expected?

How to repeat:
Just run the attached visual studio project, you will see that the update in UpdateFirstRecord() will fail
[15 Nov 2018 7:57] Ciprian Anton
The Visual Studio project that will reproduce the problem

Attachment: MySqRecordsetUpdateFails.zip (application/x-zip-compressed, text), 11.33 KiB.

[1 Dec 2018 18:49] Miguel Solorzano
Thank you for the bug report.

D:\code\MySqRecordsetUpdateFails\Debug>MySqRecordsetUpdateFails.exe
Error:000B9790
[MySQL][ODBC 8.0(a) Driver][mysqld-8.0.13]Build WHERE -> insert_fields() failed.
[21 May 6:23] Bogdan Degtyariov
Posted by developer:
 
The UPDATE query fails because the table does not have a primary key. Adding it to the table resolves the issue and makes queries faster.
However, the driver should be able to work without the primary key.
[21 May 8:52] Bogdan Degtyariov
Posted by developer:
 
After further investigation the following facts were discovered:

the test program creates a table without a primary key, but with a FLOAT column, which later is used for updating the record through the recordset->update() method.
If the table has a primary key the ODBC driver will use it to identify the record for updating, otherwise it will attempt to use all columns data to identify the record, which is not always possible.
The FLOAT and DOUBLE types are not exact types because of their internal representation. The value like 20.0 can be represented as 1.9999999999998E+1.
Naturally, the comparison 20.0 == 1.9999999999998E+1 will not match.
It would be possible with "good" or exact types such as INT or CHAR/VARCHAR etc. However, even with good types the uniqueness of a record is not guaranteed. It might happen that several records in the table match the criteria. Consequently, they all will be updated.
If you intend to do updates in the table it is always a good idea to create a primary key in order to maintain the data integrity.

Due to the reasons explained above the issue cannot be resolved by fixing the driver code.
Setting "Not a bug" status.