| Bug #113285 | ODBC error reading DECIMAL values : Multiple-step OLE DB operation ... | ||
|---|---|---|---|
| Submitted: | 29 Nov 2023 16:22 | Modified: | 30 Jul 5:53 |
| Reporter: | Emmanuel KARTMANN | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 8.0.33 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | 5.1, 8.0.33, 8.2.0 | ||
[29 Nov 2023 16:22]
Emmanuel KARTMANN
[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.
[30 Jul 5:53]
Bogdan Degtyariov
Hi Emmanuel, We were able to track down the problem with the ODBC driver causing the type mismatch errors in your script. It was a wrong type reported by the driver in the result set: mariadb_decimal 32cc-a50 EXIT SQLDescribeColW with return code 0 (SQL_SUCCESS) HSTMT 0x0000023885D53D20 UWORD 1 WCHAR * 0x0000000000000000 <null pointer> SWORD 0 SWORD * 0x0000000000000000 SWORD * 0x0000006B45EFCBB0 (-3) SQLULEN * 0x0000006B45EFCBB8 (65535) SWORD * 0x0000006B45EFCBB4 (0) SWORD * 0x0000006B45EFCBD4 (1) It reported the type (-3), which corresponds to SQL_BINARY. In the version 8.4 of the driver this problem is fixed. It now reports the type as (3) SQL_DECIMAL: cscript mariad 8f48-9eec EXIT SQLDescribeColW with return code 0 (SQL_SUCCESS) HSTMT 0x000001CC7BEDA490 UWORD 1 WCHAR * 0x0000000000000000 <null pointer> SWORD 0 SWORD * 0x0000000000000000 SWORD * 0x0000004A3C15CE60 (3) SQLULEN * 0x0000004A3C15CE70 (65) SWORD * 0x0000004A3C15CE64 (30) SWORD * 0x0000004A3C15CE80 (1) You need to use the ODBC driver 8.4.0 or 9.0.0. If you are still observing the problem please feel free to reopen the bug. Thanks.
