MySQL Bugs: #46763: Memory leak with MySqlCommand object

Bug #46763	Memory leak with MySqlCommand object
Submitted:	17 Aug 2009 17:33	Modified:	28 Jan 2010 7:12
Reporter:	Morgan Bell	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.0.4	OS:	Windows (Vista Ultimate x64)
Assigned to:		CPU Architecture:	Any
Tags:	memory leak, MySqlCommand

Description:
Instances of the MySqlCommand object are not properly disposed of when the Dispose method is called, resulting in a memory leak.

How to repeat:
I created an application to read information from a text file and write it back to the database.  The input text files are about 6GB in size, so the amount of time required to insert the text fragments into the database was considerable.  As a result, I created a delegate to pass into ThreadPool.QueueUserWorkItem and allow that to handle the insert operation.  Here is a code snippet from my app showing how the bug can be reproduced:

		private static void PerformInsert(object info)
		{
			StatementInfo statementInfo = info as StatementInfo;

			if (statementInfo != null)
			{
				using (MySqlConnection conn = new MySqlConnection(ConnectionString))
				{
					conn.Open();

					using (MySqlCommand command = new MySqlCommand(InsertFormat, conn))
					{
						command.Parameters.AddWithValue("Statement", statementInfo.Statement);
						command.Parameters.AddWithValue("Ordinal", statementInfo.Ordinal);

						try
						{
						     command.ExecuteNonQuery();
						}
						catch (Exception ex)
						{
						     Console.WriteLine("INSERT ERROR:\r\n" + ex.Message);
						}
						finally
						{
						     Interlocked.Decrement(ref WorkingItemCount);
						}
					}
				}
			}
		}

Hi Morgan and thanks for your report.

Could it be related to bug#36027? Can you try building 6.0.5 and check?

Version 6.0.5 seems nowhere to b found, so I pulled down 6.1.0.  The same behavior persists in the newer version.  If you can give me an url for the 6.0.5 version, I'd be more than happy to try it out.

Morgan, I meant to build 6.0.5 from sources. But never mind, let me check on this.

On a second thought, this snippet is not enough... Can you please attach full project I'll need to check on this problem along with connection string, database info and such?

Also, can it be simplified? Ie. repeatable without 6GB files and such... Long going queries can be achieved simply by putting SLEEP(XX) in query.

The application is very straight forward, and is as follows:

using System;
using System.IO;
using System.Threading;
using MySql.Data.MySqlClient;

namespace Parser
{
	class Program
	{
		private const string ConnectionString = "Server=localhost;Port=3305;Database=InboundParser;Uid=USER_ID;Pwd=PASSWORD;pooling=true;";
		private const string InsertFormat = "INSERT INTO Segments (Segment, Ordinal) VALUES (@Segment, @Ordinal);";

		private static long WorkingItemCount;

		static void Main(string[] args)
		{
			ParseFileAndPushStatements(args[0]);
		}

		private static void ParseFileAndPushStatements(string inputPath)
		{
			using (FileStream fs = new FileStream(inputPath, FileMode.Open, FileAccess.Read))
			{
				using (StreamReader reader = new StreamReader(fs))
				{

					string previousLine = string.Empty;
					string fragment = string.Empty;
					int ordinal = 0;

					while (reader.Peek() != -1)
					{
						string line = reader.ReadLine();

						if (!string.IsNullOrEmpty(line))
						{
							string[] segments = line.Split(new char[] { ',' });

							foreach (string segment in segments)
							{
								SegmentInfo info = new SegmentInfo();
								info.Segment = segment;
								info.Ordinal = ordinal;

								Interlocked.Increment(ref WorkingItemCount);

								ThreadPool.QueueUserWorkItem(new WaitCallback(AsyncInsert), info);

								ordinal++;
							}
						}
					}
				}
			}

			while (Interlocked.CompareExchange(ref WorkingItemCount, 0, 0) != 0)
			{
				Console.WriteLine("Items remaining: " + WorkingItemCount);
				Thread.Sleep(5000);
			}
		}

		private static void AsyncInsert(object info)
		{
			SegmentInfo segmentInfo = info as SegmentInfo;

			if (segmentInfo != null)
			{
				using (MySqlConnection conn = new MySqlConnection(ConnectionString))
				{
					conn.Open();

					using (MySqlCommand command = new MySqlCommand(InsertFormat, conn))
					{
						command.Parameters.AddWithValue("Segment", segmentInfo.Segment);
						command.Parameters.AddWithValue("Ordinal", segmentInfo.Ordinal);

						try
						{
							command.ExecuteNonQuery();
						}
						catch (Exception ex)
						{
							Console.WriteLine("INSERT ERROR:\r\n" + ex.Message);
						}
						finally
						{
							Interlocked.Decrement(ref WorkingItemCount);
						}
					}
				}
			}
		}

	}

	internal sealed class SegmentInfo
	{
		public string Segment;
		public int Ordinal;
	}
}

The only table in question is also very simple and has three columns; Id [int, autoincrement] Segment [longtext] and Ordinal [int].

The behavior can be demonstrated by commenting out sections of the AsyncInsert methhod.  First, comment out the entire method body to and run the app.  No memory leak will present itself.  Next, only comment out the MySqlCommand instantiation and execution.  Again, no memory leak will occur.  Finally, run the entire application as written, and it will start to claim memory that will never get released.

For sample data, just create a file with several pieces of comma delimited data per row.  In practice, each segment can be up to 2MB in size, but smaller segments will do the trick.  A file that looks like the following will work:

test,test,test,test,test
test,test,test,test,test
test,test,test,test,test
test,test,test,test,test

You'll probably have to make the file about 100MB to see the full effect.  In this example the database is on my local machine, Vista x64 MySql Version 5.1.37.  The machine is a Intel Core 2 Quad 8200, with 8GB of memory.

As an aside, I've updated the Dispose method of the MySqlCommand object in source to also dispose of the connection, and the leak goes away.  I'm not certain however that this is the correct fix as the connection is unintentionally closed in the process.

Morgan, I see you opening connection but there's just no Close/Dispose, not even if something goes wrong (try/finally). So my guess is you're just trying to cut test case out of actual code which is not helping me (nor you for that matters).

Please try making proper test case without usage of files (SLEEP(XX) or something) and attach it here.

Ah, and my box is almost exactly yours, only I use W2K8SE x64 instead of Vista.

Tonci,

The "using" construct takes care of calling dispose for me.  When the connection falls out of scope, it is made eligible for disposal.  It would be the equivalent of the following:

MySqlConnection conn = null;

try 
{
  _conn = new MySqlConnection("SOME CONNECTION STRING");
  _conn.Open();

  //DO SOME WORK
} 
catch(MySqlException myEx) 
{
  //Handle exception
} finally 
{
  if(_conn != null)
    _conn.Dispose();
}

The code that I attached in my previous post is nearly identical to the actual application, with the exception of the string parsing.  The string parsing routine returns a string[] just like split, but searches the string for a series of tokens.  Phrases around the tokens are removed with substring calls and aggregated into a string array that gets returned.  Because none of that code leaks or interacts with the database in any way, I elected to omit it.  If you *REALLY* must have the parsing routines, I can include them, but they causing any kind of leak.

Morgan, no I do not think I need parsing routines for now. I said what I need, I need you to retest *without* them, possibly using SLEEP(XXX) or some other long going query instead. In my opinion, this will narrow down the problem and save me from going through parts of your code.

Alright, I've updated the query to omit any insert and sleep for 500 milliseconds instead.  The memory performance is unchanged.  To make sure I'm clear, the memory leak occurs when I create the MySqlCommand object.  I never have to actually execute the command against the database in order to see the memory leak.

Morgan, after reviewing the case I think it's duplicate of Bug#31996. I'll close it as such if you agree.

Let me re-test with pooling disabled, and see if the behavior persists.  I'm not convinced yet that these are related as I can create as many connections as I'd like with very little variance in memory usage.  The problem occurs when the MySqlConnection is passed to the MySqlCommand.  I'll re-test this afternoon and get back to you.

Thank you Morgan, waiting on your results.

Tonci,

I'm sorry I've been so slow to get back to you; the company I work for is moving into busy season, so things are getting a little crazy.

I've re-tested with connection pooling disabled, and the behavior is unchanged- memory is still being leaked.

As a side note, I've worked around the problem by performing large bulk inserts every several thousand iterations.  The scenario isn't ideal, but it works around the issue.

Morgan, glad there is a workaround but I'd still like to see small, self-sufficient test case demonstrating this problem... Let's say something like my test case in Bug#29010 ([15 Jun 2007 10:01] Tonci Grgin), although I do realize it is not always possible to put problem in single procedure test.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This is still not resolved properly.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Although there was no feedback we discussed this bug internally and we believe this is a reflection of server and c/NET leak described in Bug#36027 which Wlad has fixed.