| 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: | |
| Category: | Connector / NET | Severity: | S2 (Serious) |
| Version: | 6.0.4 | OS: | 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 | ||
[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.
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 ;