MySQL Bugs: #54069: Possible Race Condition Calling Stored Procedures with Output Parameters

Bug #54069	Possible Race Condition Calling Stored Procedures with Output Parameters
Submitted:	28 May 2010 15:34	Modified:	22 Jul 2010 15:58
Reporter:	Rob Cooke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.2.2.0, trunk	OS:	Any
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any

Description:
When calling a stored procedure using MySqlCommand.ExecuteReader(), there appears to be a race condition on the ability to retrieve output parameters passed to the procedure. The only way to guarantee that the output parameters will be valid appears to be to wait until the reader has been closed before attempting to access output parameters. 

This could problematic if the value of the output parameters was intended to control iterating through the reader.

How to repeat:
1. Create a stored procedure that returns a row set and has an integer output parameter.

2. Execute the stored procedure using Command.ExecuteReader().

3. Attempt to access the output parameter before iterating and closing the returned MySqlDataReader. 

4. Notice that upon several iterations of this that sometimes the value of output parameter will be valid, but sometimes it will not.

MySqlCommand command = new MySqlCommand("MySPName");
MySqlParameter parameter = new MySqlParameter("outputParam", MySqlDbType.Int32);
parameter.Direction = ParameterDirection.Output;
command.Parameters.Add(parameter);

int myValue;
using(MySqlConnection connection = new MySqlConnection(connectionString))
{
  command.Connection = connection;
  connection.Open();
  using(MySqlDataReader reader = command.ExecuteReader())
  {
    //Note: The next line may or may not throw a NullReferenceException!
    myValue = ((int)(command.Parameters["outputParam"].Value));
    while(reader.Read())
    {
      //Do something...
    }
  }
}

Edit: When setting up the MySqlCommand, be sure to set the command type to CommandType.StoredProcedure. I had this in my application code, but forgot it when I typed in the example.

command.CommandType = CommandType.StoredProcedure;

Hi Rob and thanks for your report.

In my opinion, this is not a bug but rather a side effect of Bug#17898. Due to this bug, *all* of the connectors are forced to use a workaround (for pre MySQL server 5.4.4) putting OUT parameter into user variable. You can confirm this by looking into server's general query log.
So, I do not think this can be solved if you're using MySQL server older than 5.4.4, or even 5.5.3.

I can believe that this issue may be a side effect of the Bug#17898 workaround implemented for Connector/Net. That said, it seems to me that Connector/Net should have a consistent specification for when an output parameter has been populated. (Ideally, this would be immediately after the ExecuteReader() call returns.)

As it is in Connector/Net 6.2.2.0, it seems completely random whether or not the output parameter has been populated before iterating through the MySqlDataReader.

Verified as described. After call to ExecuteReader there is no accompanying SELECT @_cnet_param_ReturnValue in general query log.
(int)returnParam.Value; or
((int)(mySqlCommand.Parameters["?ReturnValue"].Value)); is NULL.

    MySqlConnection mySqlConnection = null;
    MySqlCommand mySqlCommand = null;

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

    mySqlCommand = new MySqlCommand();
    mySqlCommand.Connection = mySqlConnection;

    mySqlCommand.CommandType = CommandType.StoredProcedure;
    mySqlCommand.CommandText = "spBug36027";

    MySqlParameter returnParam = new MySqlParameter();
    returnParam.ParameterName = "?ReturnValue";
    returnParam.Direction = ParameterDirection.ReturnValue;
    //mySqlCommand.Parameters.Add(returnParam);
            
    long myValue = 0;
    long i = 1;

    while (i < 21)
    {
        mySqlCommand.Parameters.AddWithValue("?Nmb", i);
        mySqlCommand.Parameters.Add(returnParam);
        //mySqlCommand.Parameters.Add("?ReturnValue", MySqlDbType.Int32);
        //mySqlCommand.Parameters["?ReturnValue"].Direction = ParameterDirection.Output;

        using (MySqlDataReader reader = mySqlCommand.ExecuteReader())
        {
            //Note: The next line may or may not throw a NullReferenceException!
            myValue = (int)returnParam.Value; //((int)(mySqlCommand.Parameters["?ReturnValue"].Value));
            while (reader.Read())
            {
                i = i + 1;
            }
            reader.Close();
            reader.Dispose();
         }
         mySqlCommand.Parameters.Clear();
         i++;
     }

mysql> show create procedure spBug36027;
+------------+----------+-------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------+----------------------+------------------
----+--------------------+
| Procedure  | sql_mode | Create Procedure

                                      | character_set_client | collation_connect
ion | Database Collation |
+------------+----------+-------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------+----------------------+------------------
----+--------------------+
| spBug36027 |          | CREATE DEFINER=`root`@`localhost` PROCEDURE `spBug3602
7`(IN Nmb INT, OUT ReturnValue INT)
BEGIN
  SET @Nr=Nmb;
  UPDATE bug36027
   SET Number=@Nr;
 SET ReturnValue=1;
END | latin1               | latin1_swedish_ci    | utf8_general_ci    |
+------------+----------+-------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------+----------------------+------------------
----+--------------------+
1 row in set (0.04 sec)

mysql> show create table bug36027;
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| Table    | Create Table

                               |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| bug36027 | CREATE TABLE `bug36027` (
  `Number` int(11) NOT NULL,
  `ts` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMEST
AMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
mysql> select * from bug36027;
+--------+---------------------+
| Number | ts                  |
+--------+---------------------+
|      0 | 2010-06-09 10:34:59 |
+--------+---------------------+

This is how it works and probably sheould be documented so.
When stored procedure is called, connector does

call proc(@_cnet_param_ReturnValue)

_cnet_param_ReturnValue is session variable, that serves as placeholder for the return value

Server then sends the result set, Connector/Net reads off the result set from (this is what MySqlReader.Read()s are doing). When MySqlReader is being closed, and result set is fully read, connector/net sends additional "SELECT @_cnet_param_ReturnValue" to read the output parameter and sets it in commandParameters.

This means, Connector cannot know the output value until server has sent the full result set and Connector has fully read it.

So the answer in short is: output parameter is set after MySqlReader returned by ExecuteReader() is closed.

Note added to docs as requested.