Bug #83698 | Out parameters from a stored procedure are not passed correctly when using ADO | ||
---|---|---|---|
Submitted: | 4 Nov 2016 19:39 | Modified: | 15 Nov 2016 13:07 |
Reporter: | Kurt Peterson | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | Connector / ODBC | Severity: | S2 (Serious) |
Version: | 5.3.6, 5.3.9 | OS: | Windows (Windows 7) |
Assigned to: | CPU Architecture: | Any | |
Tags: | ADO, C++, microsoft, ODBC, stored procedure |
[4 Nov 2016 19:39]
Kurt Peterson
[15 Nov 2016 13:07]
Chiranjeevi Battula
Hello Kurt Peterson, Thank you for the bug report and testcase. Verified this behavior on MySQL Connector/ODBC 5.3.6 Driver. Thanks, Chiranjeevi.
[15 Nov 2016 13:08]
Chiranjeevi Battula
http://bugs.mysql.com/bug.php?id=83824 marked as duplicate of this one.
[11 Jan 2017 14:59]
Scott Richardson
Please share any targets for when this issue might be fixed?
[23 Jan 2017 16:03]
Ola MÃ¥rtensson
Hi, I am also seeing this issue. It would be nice get an update on the status of this bug. /Ola
[24 Jan 2017 11:09]
Ola MÃ¥rtensson
Hi, I did some testing on this issue and found something that might be of interest. I installed three MySQL servers (64-bit only) locally on a Windows 7 64-bit machine; 5.5.54, 5.6.35 and 5.7.17 (latest version to-date of their respective branch) and I created the stored procedure from Kurt Peterson's "how to repeat"-section on each server. Running Kurt's C++/ADO example plus two C# examples that I created (one using the MySQL ADO.NET connector [version 6.9.9] and one using the ODBC connector [tested version 5.3.6 and 5.3.7]) I could replicate the issue ONLY with the 5.7 server. My results are as follows: C++/ADO: 5.5: OK 5.6: OK 5.7: NOK - Getting seemingly random numbers from the output parameter. C#/ODBC: 5.5: OK 5.6: OK 5.7: NOK - Only getting DBNull from the output parameter. C#/MySQL ADO.NET: 5.5: OK 5.6: OK 5.7: OK From what I understand this would point towards some kind of compatibility issue between the ADO/ODBC connectors and the 5.7 version of the MySQL server? /Ola
[24 Jan 2017 11:37]
Arnoud Klaren
Ola, thanks for your input which confirms my remark that ODBC 5.3.6 works okay with a (remote) 5.6.22 server (see http://bugs.mysql.com/bug.php?id=83824). I downgraded to ODBC 5.3.4 as a workaround but would also like an update on this bug (which has probably caused problems for lots of people that did not find this bug report).
[24 Jan 2017 13:55]
Remco Kuijer
I'm also affected by this problem. This is my test case: 1. Create a simple stored procedure that just returns the input parameter into the output parameter: CREATE PROCEDURE pr_mysql_check1(IN p_in_id integer , INOUT p_out_id integer ) BEGIN select p_in_id into p_out_id; END 2. Test in C++ code using the ODBC Connector 5.3.6 connected to an 5.7 server: unique_ptr <TADOStoredProc> sp (new TADOStoredProc(NULL)); sp->Connection = ADOConnection; sp->ProcedureName = "pr_mysql_check1"; sp->Parameters->CreateParameter("p_in_id", ftInteger,pdInput,0,NULL)->Value=1337; sp->Parameters->CreateParameter("p_out_id", ftInteger, pdInputOutput,0,NULL)->Value=1338; sp->ExecProc(); int p_out_id = sp->Parameters->ParamValues["p_out_id"].operator int(); Result of p_out_id is, interesting enough, always 1338 (as I initialized it). Thats not a garbage number, but still wrong, should be 1337. If I don't initialize p_out_id or set its IO mode to pdOutput, the output of p_out_id is a garbage number. Its looking like the ODBC Connector is returning an uninitialized variable. Running the same test on an 5.6 server, the p_out_id is always 1337, as it should be. Since there's no error thrown we've had some data corruption because of this, making it a severe and dangerous problem. Nobody notices, and if they do, its too late. Luckily we noticed this problem in a test case, but for anyone else running into this, they might be needing a backup to restore... Reading all other comments I would say it's an issue in the ODBC Connector. I can confirm it all works correctly with MySQL Server 5.6 (and have been working for years). So I'm hoping that this will be fixed soon. Until that happens, we have dropped support for MySQL 5.7.x and stay at 5.6.x until this problem is resolved. I know we could have been supporting Server 5.7.x and use an older ODBC connector (5.3.4) that should work, but we can't prevent that the end-user (or his admin) upgrades his own connector to >=5.3.6, and then runs into the same problem with really big consequences. Since we have the MySQL servers under our control, thats our way of making sure all is ok, and therefore run 5.6.x. Any timewindow for a fix would be a nice to have.
[6 Aug 2017 9:39]
Arnoud Klaren
@Chiranjeevi: there have been 3 updates so far (5.3.7, 5.3.8 and 5.3.9), but I don't see any reference to this bug being fixed. Can you please confirm that this issue is being addressed with high priority as this can corrupt a database (overwriting fields with random data) without being noticed (no errors/warnings).
[29 Jan 2018 22:35]
Davi Furuya
I've just tested the latest version which is 5.3.9 and I can confirm that this bug is still *not* fixed. As Arnoud Klaren said, this can silently ruin a database or an application which is very dangerous. While it's not fixed, I'll keep using version 5.3.4.
[7 Nov 2019 4:39]
Davi Furuya
Late follow up: Bugged: MySQL 5.6.46 with ODBC Connector 8.0.18. Bugged: MySQL 5.7.16 with ODBC Connector 5.3.14. Bug free: MySQL 5.6.46 with ODBC Connector 5.3.14. Bug free: MySQL 5.7.16 with ODBC Connector 8.0.18. Advice: if you don't need features from 5.7.x and 8.0.x (mostly JSON things?), stick with MySQL 5.6.x and ODBC Connector 5.3.x.
[27 Jan 2020 12:08]
Remco Kuijer
This is still an issue with the versions available today. Advise is to NOT use MySQL Server 8: ODBC Connector MySQL Server Bugged: 5.3.14 8.0.19 8.0.19 8.0.19 Not bugged with this bug: 5.3.14 5.6.46 8.0.19 5.6.46
[28 Nov 2020 20:51]
Arnoud Klaren
Even after 4 years, this bug is still there. Since I could not get any MySQL 5.3/8.0 ODBC driver to work with MySQL Server 8.0.22 and I did not want to be held hostage by this bug any longer, I decided to switch from a stored procedure (with an output parameter) to a stored function (with a return value).
[23 Feb 2021 12:23]
MySQL Verification Team
Bug #89536 marked as duplicate of this one.