Bug #36027 Memory leak when using output parameter in stored procedure (called from .NET)
Submitted: 13 Apr 2008 15:11 Modified: 11 Aug 2009 14:16
Reporter: Thomas W. Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.0.4 OS:Microsoft Windows (Windows 2003 Server and Windows Vista Business Edition)
Assigned to: Vladislav Vaintroub CPU Architecture:Any
Tags: .net Connector, memory leak, output parameter, stored procedure

[13 Apr 2008 15:11] Thomas W.
Description:
Calling a stored procedure with an output parameter through Connector/NET causes a memory leak. Calling the same stored procedure without an output parameter does not suffer from the memory leak.

How to repeat:
Run the C#2.0 script below which repetitively calls a stored procedure with an output parameter. Important: Always restart the mysql service before you run the script. Within one minute you will already note how the mysql process in the task manager will consume more and more memory. Remove the output parameter from the stored procedure and from the C# script and the memory consumption of the mysql process will stay stable.

-------------------------------------------------------

MySqlConnection mySqlConnection = null;
MySqlCommand mySqlCommand = null;

int i = 1;
while (true)
{
	mySqlConnection = new MySqlConnection(Settings.Default.ConnectionString);
	mySqlConnection.Open();

	mySqlCommand = new MySqlCommand();
	mySqlCommand.Connection = mySqlConnection;
	mySqlCommand.CommandType = CommandType.StoredProcedure;
	mySqlCommand.CommandText = "spNumber";

	mySqlCommand.Parameters.AddWithValue("?Number", i);
	mySqlCommand.Parameters.Add("?ReturnValue", MySqlDbType.Int32);
	mySqlCommand.Parameters["?ReturnValue"].Direction = ParameterDirection.Output;

	mySqlCommand.ExecuteNonQuery();

	if (mySqlCommand != null)
		mySqlCommand.Dispose();

	if (mySqlConnection != null)
	{
		mySqlConnection.Close();
		mySqlConnection.Dispose();
	}

	i++;
}

-------------------------------------------------------

SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE `number` (
  `Number` int(11) NOT NULL,
  `ts` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `spNumber`(IN Number INT, OUT ReturnValue INT)
BEGIN

  SET @Number=Number;
  UPDATE Number
   SET Number=@Number;

 SET ReturnValue=1;

END;;
DELIMITER ;
[14 Apr 2008 6:35] Tonci Grgin
Hi Thomas and thanks for your report.

I believe this to be the side-effect of Bug#17898 but let me check it out.
[6 May 2008 20:52] Thomas W.
Already any news on this?
[20 Jul 2009 10:27] Tonci Grgin
Thomas, sorry for the delay. Reports are linked together cause there was no way of dealing with Output params in MySQL before. For workaround in c/NET it's not strange to have a leak as proper patch would be in server.
[20 Jul 2009 12:38] Tonci Grgin
Thomas, using remote MySQL server 5.1.31 and c/NET 6.0.4 I am not able to reproduce this problem running loop 15000 times. Connections, allocated memory ... stays constant.

Is this still a problem for you?
[20 Jul 2009 12:50] Tonci Grgin
Ummm, must revert ruling...

RAM allocation for mysqld process goes up, steadily and predictably, for about 1MB each ~32000 repetitions.

Verified as described using MySQL 5.1.31x64 server on remote OpenSolaris server using following test case:
            MySqlConnection mySqlConnection = null;
            MySqlCommand mySqlCommand = null;

            mySqlConnection = new MySqlConnection("DataSource=opensol;Database=test;UserID=**;Password=**;PORT=**;logging=True;charset=utf8;allow user variables = true;");
            mySqlConnection.Open();

            long i = 1;
            while (i < 150001)
            {

                mySqlCommand = new MySqlCommand();
                mySqlCommand.Connection = mySqlConnection;
                mySqlCommand.CommandType = CommandType.StoredProcedure;
                mySqlCommand.CommandText = "spBug36027";

                mySqlCommand.Parameters.AddWithValue("?Nmb", i);
                mySqlCommand.Parameters.Add("?ReturnValue", MySqlDbType.Int32);
                mySqlCommand.Parameters["?ReturnValue"].Direction = ParameterDirection.Output;

                mySqlCommand.ExecuteNonQuery();

                i++;
            }
            if (mySqlCommand != null)
                mySqlCommand.Dispose();

            if (mySqlConnection != null)
            {
                mySqlConnection.Close();
                mySqlConnection.Dispose();
            }

        }

DDL:
mysql> CREATE TABLE `bug36027` (
    ->   `Number` int(11) NOT NULL,
    ->   `ts` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT
_TIMESTAMP
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql> DELIMITER ;;
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `spBug36027`(IN Nmb INT, OUT
ReturnValue INT)
    -> BEGIN
    ->   SET @Nr=Nmb;
    ->   UPDATE bug36027
    ->    SET Number=@Nr;
    ->  SET ReturnValue=1;
    -> END;;
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
[8 Aug 2009 23:21] Vladislav Vaintroub
Since the leak occurs in the server, I'd classify it is a server bug. I might be mistaken,in that case , pls return the bug back to Connector/NET :)

I tested with 5.1.36 and multiple  call of the same stored procedure within the same connection with output parameter indeed leaks memory. When connection terminates, the memory is returned to the OS.

Here follows the cleaned-up stacktrace of the offending allocation for the stored procedure described in the bug.

	mysqld!malloc
	mysqld!my_malloc
	mysqld!get_variable
	mysqld!Item_func_set_user_var::set_entry
	mysqld!Item_func_set_user_var::fix_fields
	mysqld!Item_func_get_user_var::set_value
	mysqld!sp_head::execute_procedure
	mysqld!mysql_execute_command
	mysqld!mysql_parse
	mysqld!dispatch_command
	mysqld!do_command
	mysqld!handle_one_connection
	mysqld!pthread_start
	mysqld!_callthreadstart
	mysqld!_threadstart
	kernel32!BaseThreadInitThunk
	ntdll!RtlUserThreadStart
[11 Aug 2009 0:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/80500

741 Vladislav Vaintroub	2009-08-11
      Bug #36027 Memory leak when using output parameter in stored procedure
      
      The reason for the memory leak was that we Connector/Net created a session
      variable with unique name for each stored procedure call.  Each session variable takes 
      some memoryon the server, hence the leak.
      
      The fix is not using unique names for stored procedure, instead we give all output 
      variables a common prefix.
      
       It would be nice to get rid of the prefix too, however
      1 ) there is a complicated logic inside resolve/bind that makes it not possible at the
       moment.
      2) stored procedure parameter names could in theory might collide with user 
      variables names.
[11 Aug 2009 1:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/80504

741 Vladislav Vaintroub	2009-08-11
      Bug #36027 Memory leak when using output parameter in stored procedure
      
      The reason for the memory leak was that we Connector/Net created a 
      session variable with unique name for each stored procedure call. 
      Each session variable takes some memory on the server, hence the leak.
      
      The fix is not using unique names for stored procedure, instead we give 
      all output or inout variables a common prefix.
      
       It would be nice to get rid of the prefix too, however
      1 ) there is a complicated logic inside resolve/bind that makes it not 
      possible at the moment.
      2) stored procedure parameter names could in theory  collide 
      with user variables names.
[11 Aug 2009 12:44] Vladislav Vaintroub
fixed in 6.0.5, 6.1-next
[11 Aug 2009 14:16] Tony Bedford
An entry was added to the 6.0.5 and 6.1.1 changelogs:

Calling a Stored Procedure with an output parameter through MySQL Connector/NET resulted in a memory leak. Calling the same Stored Procedure without an output parameter did not result in a memory leak.