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:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.3.6 OS:Microsoft Windows (Windows 7)
Assigned to: CPU Architecture:Any
Tags: ADO, C++, microsoft, ODBC, stored procedure

[4 Nov 2016 19:39] Kurt Peterson
Description:
Out parameters from a stored procedure are not correct and change between run when you get their value using MS ADO and the 5.3.6 ODBC driver. 

How to repeat:
I found other bug reports that seemed related (see https://bugs.mysql.com/bug.php?id=27632 for example) but nothing was related to this particular behavior. I did try previous drivers, but it seems like this feature was only recently supported. In older drivers, I saw the error that was reported in the above bug report. When I tried the same method on a functionally similar stored procedure on an MS SQL DB, I was able to get the specified parameter. 

Stored procedure:

USE `autoinc_table`;
DROP procedure IF EXISTS `get_out_param`;

DELIMITER $$
USE `autoinc_table`$$
CREATE PROCEDURE `get_out_param`(out id int4, in station_id varchar(255), in execution_time double)
BEGIN
	set id = 9001;
END$$

DELIMITER ;

When you execute the following SQL commands in the MySQL workbench, you will get the expected out value:  

call autoinc_table.get_out_param(@last_id, "KTest4", 1.232454);
select @last_id

@last_id will be 9001. 

C++ program:

#include "stdafx.h"
#include <iostream>
#import "C:\\Windows\\winsxs\\x86_microsoft-windows-m..ents-mdac-ado15-dll_31bf3856ad364e35_6.1.7601.22012_none_0ebfc67ce80861b4\\msado15.dll" \
	rename ("EOF", "AdoEOF")

using namespace std; 

int _tmain(int argc, _TCHAR* argv[])
{
	CoInitialize(nullptr);
	HRESULT hr = 0;
	string DAM = "My DB";
	_variant_t vColVal;

	try 
	{

		// declare variables 
		ADODB::_ConnectionPtr	pConn;
		ADODB::_CommandPtr	CommandStoredProc;
		ADODB::_RecordsetPtr	Rs1;
		ADODB::_ParameterPtr	Param1;
		ADODB::_ParameterPtr	Param2;
		ADODB::_ParameterPtr	Param3;

		// open connection
		hr = pConn.CreateInstance(__uuidof(ADODB::Connection));
		_bstr_t mySQLConnection = "Driver={MySQL ODBC 5.3 ANSI Driver};Server=10.2.101.32;Database=autoinc_table;Uid=user;Pwd=fakepassword";
		_bstr_t stationID = "Cookie_Test_Station";

		hr = pConn->Open(mySQLConnection, "", "", ADODB::adConnectUnspecified);

		// Open recordset 
		CommandStoredProc.CreateInstance(__uuidof(ADODB::Command));
		CommandStoredProc->ActiveConnection = pConn;
		CommandStoredProc->CommandText = "get_out_param";
		CommandStoredProc->CommandType = ADODB::adCmdStoredProc; 
		Rs1.CreateInstance(__uuidof(ADODB::Recordset));

		CommandStoredProc->Parameters->Refresh();

                // Used to verify the number of parameters in the stored procedure.
		int iCount = CommandStoredProc->Parameters->GetCount();
		
		// Used to verify the name of the Parameter
		_bstr_t tempname = CommandStoredProc->Parameters->Item[_variant_t((long)0)]->Name;

		CommandStoredProc->Parameters->Item[_variant_t((long)0)]->Value = _variant_t((long)10);
		CommandStoredProc->Parameters->Item[_variant_t((long)1)]->Value = stationID;
		CommandStoredProc->Parameters->Item[_variant_t((long)2)]->Value = _variant_t((long)9009);

		// example: https://support.microsoft.com/en-us/kb/185125 

		Rs1 = CommandStoredProc->Execute(NULL, NULL, ADODB::adCmdStoredProc);
		Rs1->Close(); 
		vColVal = CommandStoredProc->Parameters->Item[_variant_t((long)0)]->Value;
		pConn->Close(); 
		
	}

	catch (_com_error& e)
	{ 
		
	}

	return 0;
}

If you check the value of the value of vColVal in the debugger, you will see it is an I4 value that can vary wildly for example I had -1163005939 in one run. Again, if you have a similar MS SQL stored procedure and pass a constant as an out parameter, you will get the expect value, 9001 in this case. 

Suggested fix:
N/A. At very least, throw an error instead of passing an incorrect value.
[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.