Bug #113285 ODBC error reading DECIMAL values : Multiple-step OLE DB operation ...
Submitted: 29 Nov 2023 16:22 Modified: 15 Dec 2023 6:36
Reporter: Emmanuel KARTMANN Email Updates:
Status: Analyzing Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:8.0.33 OS:Windows
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: 5.1, 8.0.33, 8.2.0

[29 Nov 2023 16:22] Emmanuel KARTMANN
Description:
When using ODBC Connector (version 5.1 or 8.0), values of DECIMAL types cannot be read : reading the recordset's field values causes error "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

Sample SQL code :

SET @testDECIMAL := CAST(1.5 AS DECIMAL(15,2));
SELECT @testDECIMAL;

I wrote a simple JSCRIPT to reproduce the bug : run it with CSCRIPT.EXE on a Windows host:

SET MYSQL_PASSWORD=[ROOT_PASSWORD]
CSCRIPT.EXE mariadb_decimal_odbc_bug.js

It outputs the error when trying to read the @testDECIMAL variable...

Regards,

E.

How to repeat:
Execute SQL request "SET @testDECIMAL := CAST(1.5 AS DECIMAL(15,2)); SELECT @testDECIMAL;" with ODBC.

E.

Suggested fix:
Handle DECIMAL() type properly in SQL variables.
[30 Nov 2023 13:26] MySQL Verification Team
Hello Emmanuel KARTMANN,

Thank you for the bug report.
Imho this is duplicate of Bug #72541, please see Bug #72541.

Regards,
Ashwini Patil
[30 Nov 2023 13:30] Emmanuel KARTMANN
Agree - it's a duplicate of a bug of 2014 (almost 10 years ago).

E.
[13 Dec 2023 9:46] Bogdan Degtyariov
Hi Emmanuel,

Which 8.0 version of MySQL ODBC Driver did you try?

I checked with 8.0.33, 8.1.0, 8.2.0 and it worked without any errors.
Here is the output:

D:\Projects\bugs\113285>cscript mariadb_decimal_odbc_bug.js
Microsoft (R) Windows Script Host Version 5.812
Copyright (C) Microsoft Corporation. All rights reserved.

Connecting to Database...
SQL#1 Execute : "SELECT CAST(1.5 AS DECIMAL(15,2));"...
SQL#1 : FIELD#1 : "CAST(1.5 AS DECIMAL(15,2))" = 1.5
SQL#2 Execute : "SET @testDECIMAL := CAST(1.5 AS DECIMAL(15,2));"...
SQL#3 Execute : "SELECT @testDECIMAL;"...
SQL#3 : FIELD#1 : "@testDECIMAL" = 1.5
[13 Dec 2023 10:15] Emmanuel KARTMANN
I tested it (as mentioned in the cscript's comment) with "MySQL ODBC 8.0 ANSI Driver". Do you mean it has been fixed since?

E.
[13 Dec 2023 12:29] Bogdan Degtyariov
We released many releases of ODBC Driver 8.0 starting from 8.0.1 up to 8.0.33 (the version 8.0.35 is the last in 8.0 line, but it is only for 32-bits).

Your script is working with 8.0.33. Can you please check on your side?
It can be downloaded from here:

https://downloads.mysql.com/archives/c-odbc/
[13 Dec 2023 14:07] Emmanuel KARTMANN
The bug is still here with the following driver versions, although the error message is different :
* 8.0.33 ANSI driver "MySQL ODBC 8.0 ANSI Driver" : ERROR (0x800a000d) : Type mismatch
* 8.0.33 Unicode driver "MySQL ODBC 8.0 Unicode Driver" : ERROR (0x800a000d) : Type mismatch
* 8.2.0 ANSI driver "MySQL ODBC 8.2 ANSI Driver" : ERROR (0x800a000d) : Type mismatch
* 8.2.0 ANSI driver "MySQL ODBC 8.2 Unicode Driver" : ERROR (0x800a000d) : Type mismatch

Sample output:
  CSCRIPT.EXE //NoLogo mariadb_decimal_odbc_bug.js
  Connecting to Database with driver "MySQL ODBC 8.0 ANSI Driver"...
  SQL#1 Execute : "SELECT CAST(1.5 AS DECIMAL(15,2));"...
  SQL#1 : FIELD#1 : "CAST(1.5 AS DECIMAL(15,2))" = 1,5
  SQL#2 Execute : "SET @testDECIMAL := CAST(1.5 AS DECIMAL(15,2));"...
  SQL#3 Execute : "SELECT @testDECIMAL;"...
  SQL#3 : FIELD#1 : "@testDECIMAL" =
  SQL#3 : FIELD#1 : ERROR (0x800a000d) : Type mismatch

I updated the script so it does output the driver name and version (to make sure we use the proper driver).

I also tested the script with the very old driver version 3.51.30, and I WORKED :

  CSCRIPT.EXE //NoLogo mariadb_decimal_odbc_bug.js
  Connecting to Database with driver "MySQL ODBC 3.51 Driver"...
  SQL#1 Execute : "SELECT CAST(1.5 AS DECIMAL(15,2));"...
  SQL#1 : FIELD#1 : "CAST(1.5 AS DECIMAL(15,2))" = 1,5
  SQL#2 Execute : "SET @testDECIMAL := CAST(1.5 AS DECIMAL(15,2));"...
  SQL#3 Execute : "SELECT @testDECIMAL;"...
  SQL#3 : FIELD#1 : "@testDECIMAL" = 1,5

Hope this helps,

Regards,

E.
[14 Dec 2023 0:41] Bogdan Degtyariov
I am still not able to repeat the problem.
The output prints the correct result without error as I already posted in one of my previous messages.

Maybe the problem is observable with a certain MySQL server version.
Which version of MySQL Server are you testing with?
[14 Dec 2023 6:44] Emmanuel KARTMANN
I'm using MariaDB Server version 10.11.4

Regards,

E.
[14 Dec 2023 7:38] Bogdan Degtyariov
Thanks for the reply.

Please note that although MariaDB network protocol is very similar to MySQL Server, these two protocols are not identical.

We do not guarantee that MySQL ODBC Driver will support MariaDB server. Most likely you hit one of the differences between protocols when MySQL ODBC driver is not able to properly interpret the reply from MariaDB server.

There are only two ways of fixing the issue:

 1. Use MariaDB ODBC Driver instead of MySQL ODBC Driver

 or

 2. Use MySQL Server instead of MariaDB Server

At this point I can see that the original bug is not affecting the ODBC Driver 8.0 anymore, so it is going to be closed:

https://bugs.mysql.com/bug.php?id=72541

Unless there is evidence of the problem with MySQL Server and MySQL ODBC Driver, this bug will be closed as well.
[14 Dec 2023 8:41] Emmanuel KARTMANN
I downloaded MySQL 8.2.0 Innovation and still got the same issue (I adapted the script so it displays driver and server versions):

CSCRIPT //NoLogo C:\Replay\Sources\lutecia\devpt\batch\tests\mariadb_decimal_odbc_bug.js
Connecting on server localhost with driver "MySQL ODBC 8.2 ANSI Driver"...
Connected to server localhost (version 8.2.0)
SQL#1 Execute : "SELECT CAST(1.5 AS DECIMAL(15,2));"...
SQL#1 : FIELD#1 : "CAST(1.5 AS DECIMAL(15,2))" = 1,5
SQL#2 Execute : "SET @testDECIMAL := CAST(1.5 AS DECIMAL(15,2));"...
SQL#3 Execute : "SELECT @testDECIMAL;"...
SQL#3 : FIELD#1 : "@testDECIMAL" =
SQL#3 : FIELD#1 : ERROR (0x800a000d) : Type incompatible

Which version of MySQL Server are you using?

Is there a configuration for ODBC Drivers (system-wide) that could explain the difference?

Regards,

E.
[14 Dec 2023 11:15] Bogdan Degtyariov
Your script is using the connection string containing DRIVER={...} option. This means that all options are specified in the connection string and can be visible there (unlike DSN where options are specified independently). Therefore, I do not see how any extra settings might affect the connection.

I was testing the ODBC Driver against MySQL Server 8.2.0 and 8.3.0. They all worked for me.

In order to get to the bottom of this we need to inspect the ODBC trace generated during the js script execution. The ODBC trace can be enabled from ODBC Data Source Administrator - > Tracing. Make sure that the Log File Path is valid and the directory has the write permissions. Also, it is recommended to tick the box with "Machine-Wide tracing for all user identities".

The tracing has to be started before executing of the script and stopped immediately after it exited, so the trace would not contain records from other processes that might use ODBC.

Please attach the generated file to this ticket.
Thanks.
[15 Dec 2023 6:36] Emmanuel KARTMANN
I activated the ODBC Trace - log file is attached.

Thank you for your help,

Best regards,

E.