MySQL Bugs: #20581: Null Reference Exception when closing reader after stored procedure.

Bug #20581	Null Reference Exception when closing reader after stored procedure.
Submitted:	20 Jun 2006 18:01	Modified:	24 Aug 2006 9:59
Reporter:	Tristan Aubrey-Jones	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.7	OS:	Windows (Windows XP)
Assigned to:	MC Brown	CPU Architecture:	Any

Description:
Whenever you call connection.Close() or reader.Close() after executing a stored procedure command a Null Reference Exception is raised.

How to repeat:
MySqlCommand command = new MySqlCommand("stored procedure name", connection);
connection.Open();
try
{
    command.Prepare();
    MySqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow);
    try { reader.Read(); }
    finally { reader.Close(); }
}
finally
{
connection.Close();
}

Suggested fix:
It turns out that it is being raised in the MySqlCommand.Consume() internal method when it checks to see if there are any sql buffers left. Im afraid the only workaround is downloading the driver source and replacing the Consume() method in command.cs with the one below, and recompiling.

		/// <summary>
		/// Executes all remaining command buffers
		/// </summary>
		internal void Consume()
		{
			CommandResult result = GetNextResultSet(null);
			while (result != null)
			{
				result.Consume();
				result = GetNextResultSet(null);
			}

			// if we were executing a stored procedure and we are out of sql buffers to execute, 
			// then we need to perform some additional work to get our inout and out parameters

            // CHANGES BEGIN
            // Checks if sqlBuffers is null before checking its count. 
            // (It will be null when executing a stored procedure)
			if (storedProcedure != null && (sqlBuffers == null || sqlBuffers.Count == 0))
				storedProcedure.UpdateParameters(Parameters);
            // CHANGES END
		}

Fixed build of driver compiled for .NET 2.0

Attachment: MySql.Data.dll (application/octet-stream, text), 140.00 KiB.

Hi Tristan. Thanks for your problem report. Can you please try to repeat it with Connector/NET 1.0.7 and get back to me with result?

Whoops, my mistake - I got the version from an outdated README but it would seem that I am already using 1.0.7.

Tristan, I was unable to repeat the problem you described:
   class Program
    {
       readonly static string connstr = "Server=munja;Database=Bug;Username=root;Password=;Port=3307;allowzerodatetime=true;";

        static void Main(string[] args)
        {
            using (MySqlConnection con = new MySqlConnection(connstr))
            {

                MySqlCommand command = new MySqlCommand("CALL mj_spListRatingsOfRecipe(3)", con);

                con.Open();
                Console.WriteLine("Server version: " + con.ServerVersion);

                try
                {
                    command.Prepare();
                    MySqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow);
                    try { reader.Read(); }
                    finally { reader.Close(); }
                }
                finally
                {
                    con.Close();
                }
            }
        }
Server: MySQL server 5.0.23 BK on Suse10
Client: connector/NET 1.0.7 (svn), WinXP Pro SP2, VS2005
Now, you can either retest and find what's wrong or fill this report with script containing all relevant data (procedure definition, database structure and data...) so I can test in similar conditions. If you provide more data, please do not open new report, reopen this one.

connector libs

Attachment: libs.zip (application/x-zip-compressed, text), 108.04 KiB.

Im terribly sorry I thought my example was clear, I was obviously mistaken. Im afraid I cannot reproduce the "exact" code as the error occured deep within a large system and I dont have the time, however the reason your example did not produce the error is because you are calling the stored procedure in a very odd way, infact defeating most of the security benifits of using stored procedures. You should call a stored procedure by making the CommandType "StoredProcedure" like this:

    class Program
    {
        readonly static string connstr =
            "Server=munja;Database=Bug;Username=root;Password=;Port=3307;allowzerodatetime=true;";

        static void Main(string[] args)
        {
            using (MySqlConnection con = new MySqlConnection(connstr))
            {
                MySqlCommand command = new MySqlCommand("mj_spListRatingsOfRecipe", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(3); // i dunno the name of your parameter

                con.Open();
                Console.WriteLine("Server version: " + con.ServerVersion);

                try
                {
                    command.Prepare();
                    MySqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow);
                    try { reader.Read(); }
                    finally { reader.Close(); }
                }
                finally
                {
                    con.Close();
                }
            }
        }
    }

It obviously wont repeat when you use CALL as the driver just executes it as a query instead of a stored procedure:
even a cursary glance over the driver's source code should make that clear (if the command type isnt StoredProcedure the variable "storedProcedure" wont be true and so obviously the exception would never be raised).

Rebuilding test case with new info provided.

Hi Tristan. I was able to reproduce the error with latest SVN connector code. However I do not agree with severity level. It should be S2 or S3 since workaround exists.

   class Program
    {
       readonly static string connstr = "Server=munja;Database=Bug20084;Username=root;Password=;Port=3307;allowzerodatetime=true;";

        static void Main(string[] args)
        {
            using (MySqlConnection con = new MySqlConnection(connstr))
            {
                MySqlParameter param1;
                MySqlCommand command = new MySqlCommand("mj_spListRatingsOfRecipe", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;

                param1 = command.Parameters.Add("RecipeID", MySqlDbType.Int32);
                param1.Value = 3;

                con.Open();
                Console.WriteLine("Server version: " + con.ServerVersion);

                try
                {
                    command.Prepare();
                    MySqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleRow);
                    reader.Read();
                    //try { reader.Read(); }
                    //finally { reader.Close(); }
                }
                finally
                {
                    con.Close();
                }

            }
        }

System.NullReferenceException was unhandled
  Message="Object reference not set to an instance of an object."
  Source="MySql.Data"
  StackTrace:
       at MySql.Data.MySqlClient.MySqlCommand.Consume() in D:\svn-net\tags\1.0.7\MySqlClient\command.cs:line 243
       at MySql.Data.MySqlClient.MySqlDataReader.Close() in D:\svn-net\tags\1.0.7\MySqlClient\datareader.cs:line 133
       at MySql.Data.MySqlClient.MySqlConnection.Close() in D:\svn-net\tags\1.0.7\MySqlClient\Connection.cs:line 335
       at _6934.Program.Main(String[] args) in C:\TEMP\Bug16934\16934\16934\Program.cs:line 42
       at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()

I have lowered the severity to S2, though I disagree somewhat. There is no really viable workaround: you cannot expect users to rebuild the driver from source, and using CALL is very undesirable as it forces you to embed the parameters in the command string opening up your code to sql injection attacks, lowering execution efficiency and making it very difficult to embed some data types such as BINARY fields.

Tristan, I agree but that's just what S2 is about:
Severity 2
    Represents a *severe loss* of service, *significant functionality* is missing; but a workaround is available.

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/10078

An entry for this bug has been placed into the changelog for 1.0.8.