MySQL Bugs: #98060: ODBC v8.0.11 works, v0.8.12+ no rows, v8.0.16+ zero values

Bug #98060	ODBC v8.0.11 works, v0.8.12+ no rows, v8.0.16+ zero values
Submitted:	23 Dec 2019 20:12	Modified:	9 Jan 2020 8:49
Reporter:	Sander Bouwhuis	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	v8.0.12+, 8.0.18	OS:	Microsoft Windows (x64)
Assigned to:		CPU Architecture:	x86

Description:
Table:
CREATE TABLE `user_settings_profiles` (
  `User_settings_profile_id` int(11) NOT NULL,
  `Description` varchar(64) DEFAULT NULL,
  `User_settings` mediumblob,
  `Point_of_sale_permissions` mediumblob
) ENGINE=InnoDB DEFAULT CHARSET=utf8"

Values:
 ------------------------------------------------------------------------------------
| User_settings_profile_id | Description | User_settings | Point_of_sale_permissions |
|--------------------------|-------------|---------------|---------------------------|
|                        1 | Everything  | ( 5824 bytes) | (                0 bytes) |
|                        2 | Burpjes     | ( 5824 bytes) | (                0 bytes) |
 ------------------------------------------------------------------------------------

I try to execute this query through ODBC:
SELECT
  "User_settings_profile_id", "Description", "User_settings", "Point_of_sale_permissions"
FROM
  User_settings_profiles
WHERE
  "Description" = ?

Only ODBC v8.0.11 gives me a valid value for the User_settings_profile_id. Some versions even return 0 rows instead of 1.

v0.8.11 : works -> returns 1 row with User_settings_profile_id = 2
v0.8.12 : fails -> returns 0 rows
v0.8.13 : fails -> returns 0 rows
v0.8.14 : fails -> returns 0 rows
v0.8.15 : fails -> returns 0 rows
v0.8.16 : fails -> returns 1 row with User_settings_profile_id = 0
v0.8.17 : fails -> returns 1 row with User_settings_profile_id = 0
v0.8.18 : fails -> returns 1 row with User_settings_profile_id = 0

If I replace the parameter with "Description" = 'Everything' I get the correct answer for all drivers.

How to repeat:
I think it has to do with the situation where you have 1 parameter in the where condition. Not sure why the integer column fails while the varchar and mediumblob columns succeed.

Extra info:
1. I fetch 32 rows per fetch by using SQLSetStmtAttr with the SQL_ATTR_ROW_ARRAY_SIZE parameter on 32.
2. I have to do a rebind with SQLBindCol because the User_settings column's value exceeds my default size of 514 bytes.

Suggested fix:
I don't know, but it's a really BIG problem currently for my customers.

It doesn't matter whether I set the parameter to 'Everything' or to 'Burpjes'. Both give the wrong results as mentioned above.

Hello Sander Bouwhuis,

Thank you for the report and feedback.

regards,
Umesh

I did some more tests with the v5.3.x series:

v5.3.8  : works -> returns 1 row with User_settings_profile_id = 2
v5.3.9  : works -> returns 1 row with User_settings_profile_id = 2
v5.3.10 : works -> returns 1 row with User_settings_profile_id = 2
v5.3.11 : fails -> returns 0 rows
v5.3.12 : fails -> returns 0 rows
v5.3.13 : fails -> returns 1 row with User_settings_profile_id = 0
v5.3.14 : fails -> returns 1 row with User_settings_profile_id = 0

So, clearly you people had a regression in v5.3.11 and then a bugged fix in v5.3.13.
Seeing both the v8.x branch and the v5.3.x branch go from working, to regression, to bugged fix I'd say this is a BIG problem.

A database that gives WRONG data is DEADLY. It's nearly impossible to check which queries return the correct result and which don't. YOU ARE SCARING ME!

!!!HELP HELP HELP!!!

Dear Sander,

we are sorry to hear about the issues you are having with ODBC.
There is a temporary workaround to this problem, which is to use the client-side prepared statements, which means not using the server-side prepared statements (SSPC).

This can be done by adding NO_SSPS=0; to the connection string or by setting the following option from the GUI dialog:

Detalis >> Misc >> [x] Prepare statements on the client.

Meanwhile we are working on fixing this with SSPS enabled.
Thanks for your patience.

Posted by developer:
 
This bug is a combination of two problems already reported in the following two bugs:

Bug #29467224 STORED PROCEDURE CALL VIA ADO DB AND ODBC CONNECTOR RETURNS RESULTSET OF 1 ROW
Bug #30428851 Prepared Statements when using ODBC / ClassicASP always return 0 for INT values

The problem is confirmed in the version MySQL ODBC 8.0.18. It happened for prepared statements putting the wrong value of numerical parameters in  parametrized queries.

The fix was pushed to the source tree for the above mentioned bugs.

Thanks for the response!

Will this fix be in the upcoming MySQL ODBC v8.0.19? I REALLY want it to help my customers. If I revert to an older ODBC driver (like v8.0.11), it may contain other bugs that were fixed in v8.0.12 through v8.0.18.

Posted by developer:
 
Duplicate of Bug #29467224 and Bug #30428851, both fixed in 8.0.19.

External/Public bug# of Rafal's above note.

Bug #94623 & Bug #97191

C test case that passes with the new ODBC driver version and fails with 8.0.12 - 8.0.18

Attachment: bug30697308.c (text/x-csrc), 4.67 KiB.