MySQL Bugs: #114872: ODBC--Call Failed error using this version of ODBC. No error in older versions.

Bug #114872	ODBC--Call Failed error using this version of ODBC. No error in older versions.
Submitted:	3 May 2024 19:34	Modified:	16 Oct 2024 6:11
Reporter:	Toni Elias	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	9.1.0	OS:	Windows (Windows 11 Version 10.0.22631.3527)
Assigned to:		CPU Architecture:	x86 (Intel Core i5-10210U)
Tags:	Microsoft Access, microsoft access linked table odbc connector

Description:
I'm getting "ODBC--Call Failed" error when I try to open a linked table in Microsoft Access 365 only when I use version 8.4.0.

The same file works with version 8.3.0 and older version.
The error is showing only after updating to version 8.4.0.

I tested both versions multiple times and got the same results.

Table has more than 12,000 records.

How to repeat:
Install ODBC connector version 8.4.0.
Add a system DNS.
Use MySQL ODBC 8.4 Unicode Driver.
Test it (testing the MySQL database is successful).

Open Microsoft Access 365.
External Data.
New Data Source.
From Other Sources.
ODBC Database.
Link to the data source by creating a linked table.
Machine Data Source.
Select the created data source.
Open the linked table and you'll get an error.

Note: Table has more than 12,000 records.

Table works on version 8.3.0 and older.

Attachment: ODBC.png (image/png, text), 130.43 KiB.

Test is successful in all versions even the problematic version 8.4.0

Attachment: ODBC Test.png (image/png, text), 1.20 MiB.

Hello.

Were you able to recreate the error?

Kindest regards.

Exact same problem for me when connecting an MySQL linked table into Access 2021 with v8.4 ODBC connector.

With v8.3, the same table shows all the values; with v8.4, I get "#Name ?" instead of the values...

Hello Toni Elias,

Thank you for the bug report.
Verified as described.

Regards,
Ashwini Patil

Hello.

Issue is not resolved in version 9.0.0.

Regards.

I tested version 9.0.0 and the got the same error as version 8.4.0.

Please help.

Many thanks.

Posted by developer:
 
Unfortunately we did not manage to fix this issue in 9.0.0. We are working on it and it should be fixed in the upcoming release. Sorry for the inconvenience.

Thank you.

Posted by developer:
 
I tried reproducing this bug locally using MySQL ODBC Driver version 9.0.0 (64-bit) and MS Access 365.
Followed all suggested steps and the issue is not repeatable in my local environment.

I created a table with 15000 rows with the following DDL:

CREATE TABLE `bug_36652441` (
  `id` int NOT NULL AUTO_INCREMENT,
  `time` int DEFAULT NULL,
  `task_id` int DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `notes` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Attaching a screenshot showing the test data at the end of 15K recordset in MS Access.

At this point we cannot continue unless the issue can be reproduced at out end.
I suspect that the table structure and column types play a role in this problem.

Can you send us a CREATE TABLE statement and a few rows of sample data that allow seeing the issue in MS Access?

P.S.: The verification comment with the screenshot is about the architecture mismatch, which does not have bearing upon the present issue.

Screenshot from MS Access showing the position at 15K rows

Attachment: ms-access-15k-rows.jpg (image/jpeg, text), 87.76 KiB.

Hello.
Thank you for your help.

MySQL table:

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
SET NAMES utf8mb4;

DROP TABLE IF EXISTS `time_entries`;
CREATE TABLE `time_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` int(11) NOT NULL,
  `task_id` int(11) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `start_date` int(11) NOT NULL,
  `notes` mediumtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `time_entries_max` CHECK (`time` <= 43200)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Sample data:

6	7200	82	1	1606653555	NULL
9	3600	71	1	1606660156	NULL
10	3600	73	1	1606660707	NULL
13	3600	88	9	1606667960	NULL
14	7200	88	9	1606668012	NULL
15	25200	82	1	1606675654	NULL
41	7200	NULL	8	1607279052	Communication and searching
42	7200	NULL	9	1607279052	تجربة

Hi Tony,

Thanks for providing the CREATE TABLE and sample data.
Unfortunately, I still could not repeat the problem.

I tried ODBC Driver version 8.4.0 and 9.0.0.

Attaching a screenshot with the sample data you gave us and an extra record I added from MS Access.

Does MS Access fail when you have just these records in the table?
Please advise.

Screenshot from MS Access showing the sample data.

Attachment: bug_114872_ms_access.jpg (image/jpeg, text), 39.70 KiB.

Hello.
I'm just trying to read the table's data from Access and getting:

#Name ?

In all fields, columns and rows.
There are Arabic language characters in the table.

As shown on my last screenshot the Arabic characters are displayed correctly.
Do you have any special options set for the DSN? This could make difference.

It's not a DSN error.
I shared a screenshot of the successful test earlier.

Install 8.3.0 and earlier = no issue.
Update to 8.4.0 and later = error.

The issue is definitely caused by the ODBC connector.
Another user is facing the exact same problem.

Hi Mr. Elias,

We can only fix those bugs that we can repeat.

We are not able to repeat what you are reporting, for the moment.

But, we shall try again .....

DSN is Data Source Name that your program (MS Access) use to get a connection to an ODBC Data Source. Knowing what options are set for DSN would help us to repeat the issue, which at this moment we are not able to do.

We have the exact same problem.
A simple MySql DB using MS Access as frontend using 9.0 driver.
It fails exactly as described.

The same DB setup works with Excel (all tables can be fetched) and old MS ACCESS 32 bit/8.0.37.

The problem can be isolated to the combination of MS Access / ODBC driver >8.3

As you can see this report could not progress any further because we do not know the connection options being used. We tried different combinations of options, but they all worked for us.

Alternatively, you can try producing ODBC trace following the steps described here:

https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-trace.html

Once the trace is obtained please attach it to this bug report for our analysis.

For all.

There seems to be a known problem when using MS Access as frontend for MySQL using ODBC driver version 9.0

Workaround: Use ODBC driver version 8.3 (also 64 bit) as the problem seems to be introduced with version 8.4

https://downloads.mysql.com/archives/c-odbc/

Hello.
Here's the SQL for a table that's causing the error:
----------------------------------------------------

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET NAMES utf8mb4;

CREATE TABLE `time_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` int(11) NOT NULL,
  `task_id` int(11) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `start_date` int(11) NOT NULL,
  `notes` mediumtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `time_entries_max` CHECK (`time` <= 43200)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Dear everyone,

please note that MariaDB Server and MySQL Server are not always compatible on a protocol level. For that reason you cannot use MySQL ODBC driver with MariaDB.

I see this error in the ODBC trace file:

"[MySQL][ODBC 9.0(w) Driver][mysqld-5.5.5-10.11.6-MariaDB-0+deb12u1]No data supplied for parameters in prepared statement"

As already mentioned, due to protocol incompatibility (which by the way was introduced around version 8.4) it is expected that MySQL ODBC Driver 8.4/9.0 fails to do certain operations on MariaDB server.

The issue is around server-side prepared statement (SSPS) being executed. MySQL network protocol and C API for Prepared Statements are using a new function mysql_stmt_bind_named_param() instead of mysql_stmt_bind_param().

A workaround is to disable SSPS via the connection option NO_SSPS=1 or checking the appropriate box in the GUI (Details >> Misc >> [X] Prepare Statements on the client)

We will only accept this as a bug if there is a proven issue between GA versions of MySQL Server and MySQL ODBC driver.

Just to make it clear: we do not recommend using the NO_SSPS workaround as a means of work with the data on production servers. Just a quick hack to prove the concept, which is neither reliable nor documented.

For MariaDB Server the appropriate ODBC Driver from MariaDB should be used. There could be more incompatibilities that we are not aware. Also, we do not guarantee and do not test that MySQL ODBC driver works against Maria DB Server.

Fair enough with the difference between MariaDB and MySQL server. 
And thanks for the reference.

We have a workaround for now and hopefully a solution for tomorrow.

Another thing, which should be mentioned.
The incompatible change in the network protocol makes it impossible for MySQL ODBC Driver 8.4/9.X to talk both old and new protocol. For this reason the issues are expected with non-GA versions of MySQL Servers as well: 8.1.0, 8.2.0 and 8.3.0.

However, since 8.0.X is still GA + Long Term Supported, it received the protocol updates and MySQL ODBC Driver 9.X works fine with it. This scenario is always tested, so we are confident that ODBC 9.X driver has the same level of compatibility with MySQL Server 8.0.X as with 9.X.

Hello.

Issue is resolved in version 9.1.0.

Regards.