MySQL Bugs: #104555: MySQL ODBC Connector 8.0.26 throws error when doing ADO table update

Bug #104555	MySQL ODBC Connector 8.0.26 throws error when doing ADO table update
Submitted:	6 Aug 2021 8:37	Modified:	20 Nov 2021 13:21
Reporter:	Joachim Bonsmann	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	8.0.26	OS:	Windows (Windows 10 Pro X64)
Assigned to:	MySQL Verification Team	CPU Architecture:	x86

Description:
Good morning,

I have here a quite old ADO Windows application written (by me) under VB6 (Visual Basic) using ADO mysql access via ODBC Connector (32 Bit, although Windows 10 is 64 Bit). Although quite old, everything works fine.

On updating from an elder Connector version (I think 8.0.18) to actual version 8.0.26, the application now always crashes  on updating a table. ODBC debug shows that there may be an error in creating Mysql UPDATE command within the driver regarding the table names: 

The table name is translated as 'database'..'table' with ***DOUBLE DOTS*** in between, instead of single dot. This should be no error in my code, as I only invoke the Recordset.update method, and the table desgination with period seems to be created by the ADO library and/or ODBC driver itself.

The problem simply and completely disappears if I downgrade one version from 8.0.26 to ODBC Connector 8.0.25, so the cause of the problem seems to be created with recent changes.

It is no problem for me to use the downgraded version, but I wanted to report this rather obvious error anyway.

Best regards,
Joachim

How to repeat:
- see above -

Suggested fix:
- unknown -

Hello,
I have the same problem, and for me it is critical. I have found that changing the cursor location form Client to Server solves the problem partially, but not fully. This also re-opens a problem that the ADODB Bookmark is not supported the the cursor location is set to adUseServer. 

What I have found is that the ADODB myRecordset.update command actually creates a MySQL insert string. When the cursor location is set to adUseClient, there is a trappable error that shows all the values to be inserted as ? questionmarks.  Perhaps this is the cause of the failed insert / update command?
For example:
Adodb.Recordset is opened as follows:
  Set rs = SourceCnn.OpenRecordset(strSource, adUseClient, adOpenKeyset, adLockOptimistic)
 When the code attempts to execute, the following error occurs:
Error number:
-2147217900 (from VBA in Access 2016)
Error description: 
[MySQL][ODBC 8.0(w) Driver][mysqld-8.0.26]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`tblInvoice` SET `paymentconditions`=? WHERE `InvoiceID`=? AND `paymentconditio' at line 1

All this worked fine in MySQL 8.0.21, I do not know if version . 8.0.22 to 8.0.25 have this problem
Downgrading back to ---21 is possable, but Please fix this!

I apologize for the typos:
" When the code attempts to execute, the following error occurs:"
should read:

" When the code attempts to execute .Update, the following error occurs:"

And "possable" = "possible"

Hello Joachim Bonsmann,

Thank you for the bug report.
Could you please provide repeatable test case (project bundled with all the required files - please make it as private if you prefer) to reproduce this issue at our end? Thank you.

Regards,
Ashwini Patil

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".