Bug #65696 Memory Leak on MySql.Data.MySqlClient.MySqlCommand

• Submitted: 21 Jun 2012 7:00
• Modified: 28 Sep 2012 18:56
• Reporter: ronan colobong
• Status: Closed
• Category: Connector / NET
• Severity: S5 (Performance)
• Version: 6.5.4 and 5.2.7
• OS: Windows
• Assigned to: Fernando Gonzalez.Sanchez
• CPU Architecture: Any
• Tags: insert leak memory

[21 Jun 2012 7:00] ronan colobong

Description:
I have the vb.net code below in which I use mysql to insert logs.

Dim dbinsertlogs As New MySql.Data.MySqlClient.MySqlCommand()

dbinsertlogs.CommandText = "insert into logs(`transaction_id`, `app_id`, `modulename`, `app_event`, `message_type`, `datetime`, `log_level`, `log`) values(?transaction_id, ?app_id, ?modulename, ?app_event, ?message_type, ?datetime, ?log_level, ?log)"
dbinsertlogs.Parameters.Add("?transaction_id", MySql.Data.MySqlClient.MySqlDbType.VarChar, 36)
dbinsertlogs.Parameters.Add("?app_id", MySql.Data.MySqlClient.MySqlDbType.VarChar, 20)
dbinsertlogs.Parameters.Add("?modulename", MySql.Data.MySqlClient.MySqlDbType.VarChar, 30)
dbinsertlogs.Parameters.Add("?app_event", MySql.Data.MySqlClient.MySqlDbType.String, 20)
dbinsertlogs.Parameters.Add("?message_type", MySql.Data.MySqlClient.MySqlDbType.String, 20)
dbinsertlogs.Parameters.Add("?datetime", MySql.Data.MySqlClient.MySqlDbType.DateTime)
dbinsertlogs.Parameters.Add("?log_level", MySql.Data.MySqlClient.MySqlDbType.Byte)
dbinsertlogs.Parameters.Add("?log", MySql.Data.MySqlClient.MySqlDbType.MediumText)

dbinsertlogs.Parameters("?transaction_id").Value = eventparam.TransactionID
dbinsertlogs.Parameters("?app_id").Value = eventparam.AppID
dbinsertlogs.Parameters("?modulename").Value = eventparam.ModuleName
dbinsertlogs.Parameters("?app_event").Value = eventparam.AppEvent.ToString
dbinsertlogs.Parameters("?message_type").Value = eventparam.MessageType.ToString
dbinsertlogs.Parameters("?datetime").Value = eventparam.Datetime
dbinsertlogs.Parameters("?log_level").Value = eventparam.LogLevel
dbinsertlogs.Parameters("?log").Value = eventparam.Log

dbinsertlogs.ExecuteNonQuery()

I'm now optimizing my application and found that my IO.MemoryStream undisposed instances were increasing through .NET Memory Profiler by SciTech (www.memprofiler.com). Can you give a quick check if you will have the same result, btw, I'm running the code in this platform/framework.

1. Windows 7 Home Premium
2. Dotnet framework 2.0
3. Mysql 5.1.41 (xampp)
4. Visual Studio 2005
5. Mysql Net Connector 6.5.4

How to repeat:
1. create mysql schema somedb

2. create logs table

delimiter $$

CREATE TABLE `logs` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `transaction_id` varchar(36) NOT NULL,
  `app_id` varchar(20) NOT NULL,
  `modulename` varchar(30) NOT NULL,
  `app_event` varchar(20) NOT NULL,
  `message_type` varchar(20) NOT NULL,
  `datetime` datetime NOT NULL,
  `log_level` int(1) unsigned NOT NULL,
  `log` mediumtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Index_2` (`transaction_id`),
  KEY `Index_3` (`app_id`,`datetime`,`app_event`,`message_type`,`log_level`)
) ENGINE=InnoDB AUTO_INCREMENT=611 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC$$

3. Create a VB2005 console application project with the sub main below.

    Sub Main()

        Dim dbconnection As New MySql.Data.MySqlClient.MySqlConnection
        Dim dbconnectionbuilder As New MySql.Data.MySqlClient.MySqlConnectionStringBuilder
        dbconnectionbuilder.Database = "somedb"
        dbconnectionbuilder.Server = "127.0.0.1"
        dbconnectionbuilder.UserID = "root"
        dbconnectionbuilder.Password = ""

        dbconnection.ConnectionString = dbconnectionbuilder.ToString()

        Dim dbinsertlogs As New MySql.Data.MySqlClient.MySqlCommand()

        dbinsertlogs.CommandText = "insert into logs(`transaction_id`, `app_id`, `modulename`, `app_event`, `message_type`, `datetime`, `log_level`, `log`) values(?transaction_id, ?app_id, ?modulename, ?app_event, ?message_type, ?datetime, ?log_level, ?log)"
        dbinsertlogs.Parameters.Add("?transaction_id", MySql.Data.MySqlClient.MySqlDbType.VarChar, 36)
        dbinsertlogs.Parameters.Add("?app_id", MySql.Data.MySqlClient.MySqlDbType.VarChar, 20)
        dbinsertlogs.Parameters.Add("?modulename", MySql.Data.MySqlClient.MySqlDbType.VarChar, 30)
        dbinsertlogs.Parameters.Add("?app_event", MySql.Data.MySqlClient.MySqlDbType.String, 20)
        dbinsertlogs.Parameters.Add("?message_type", MySql.Data.MySqlClient.MySqlDbType.String, 20)
        dbinsertlogs.Parameters.Add("?datetime", MySql.Data.MySqlClient.MySqlDbType.DateTime)
        dbinsertlogs.Parameters.Add("?log_level", MySql.Data.MySqlClient.MySqlDbType.Byte)
        dbinsertlogs.Parameters.Add("?log", MySql.Data.MySqlClient.MySqlDbType.MediumText)

        dbconnection.Open()
        dbinsertlogs.Connection = dbconnection

        While True
            dbinsertlogs.Parameters("?transaction_id").Value = System.Guid.NewGuid.ToString()
            dbinsertlogs.Parameters("?app_id").Value = "1"
            dbinsertlogs.Parameters("?modulename").Value = "anymodule"
            dbinsertlogs.Parameters("?app_event").Value = "logs"
            dbinsertlogs.Parameters("?message_type").Value = "logs"
            dbinsertlogs.Parameters("?datetime").Value = DateTime.Now
            dbinsertlogs.Parameters("?log_level").Value = 1
            dbinsertlogs.Parameters("?log").Value = "test"

            dbinsertlogs.ExecuteNonQuery()
            Threading.Thread.Sleep(100)
        End While
    End Sub

[28 Sep 2012 18:56] John Russell

Added to changelog for 6.4.6, 6.5.5, 6.6.3: 

When building commands through the
MySql.Data.MySqlClient.MySqlCommand() class, memory could be leaked
because some IO.MemoryStream instances were not being freed
efficiently. The memory leak could be an issue in SQL-heavy
applications, for example a logging application processing large
numbers of INSERT statements.