MySQL Bugs: #31617: ExecuteReader returns closed MySqlDataReader and Permanently Corrupts Connection

Bug #31617	ExecuteReader returns closed MySqlDataReader and Permanently Corrupts Connection
Submitted:	15 Oct 2007 18:01	Modified:	22 Oct 2007 8:42
Reporter:	pasha bitz	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.1.3	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	.net Connector, MySqlCommand, MySqlDataReader

Description:
When calling MySqlCommand.ExecuteReader with an sql statement that times out, a MySqlDataReader in state IsClosed=true is returned. This is on itself not the expected behavior.

In addition, the connection used by the MySqlCommand is permanently corrupted, because isOpen field of MySqlDataReader contains false but the dataReader field of the connection contains the MySqlDataReader (!=null). So subsequent calls to MySqlCommand.ExecuteReader with the same connection will throw exception "There is already an open DataReader associated with this Connection which must be closed first" from CheckState.

How to repeat:
1 create table:

create table t1(f1 int not null);
insert into t1 values(1);

2 create stored procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `my_schema`.`long_sp`$$

CREATE DEFINER=`root`@`%` PROCEDURE `long_sp`()
BEGIN
select SLEEP(40) s, f1 from t1;

END$$

DELIMITER ;

3 use the following code:

public void foo(MySqlConnection c)
{
            MySqlDataReader reader = null;
            try
            {
                c.Open();
                MySqlCommand m = new MySqlCommand("long_sp", c);
                m.CommandType = CommandType.StoredProcedure;

                using(MySqlDataReader reader = m.ExecuteReader())
                {
                    reader.Read(); //throws exception 'Invalid attempt to Read when reader is closed'
                }
            }
            catch(MySqlException x)
            {
                //if you try to open a new reader
               MySqlCommand m = new MySqlCommand("select * from t1", c);
               MySqlDataReader reader = m.ExecuteReader(); //throws 'There is already an open DataReader associated with this Connection which must be closed first'
            }
 

Suggested fix:
in method public MySqlDataReader ExecuteReader(CommandBehavior behavior) of MySqlCommand, instead of 

if (this.canceled || this.timedOut)
        {
            reader.Close();
        }
        this.connection.Reader = reader;

do:

if (this.canceled || this.timedOut)
        {
            reader.Close();
        }
else
        this.connection.Reader = reader;

Hi Pasca and thanks for your report. I am unable to repeat it using c/NET 5.1.3GA with MySQL server 5.0.50 on WinXP Pro SP localhost:

Test case:
        {
//create table bug31617(f1 int not null);
//insert into bug31617 values(1);
//DELIMITER $$
//DROP PROCEDURE IF EXISTS `test`.`bug31617_sp`$$
//CREATE DEFINER=`root`@`%` PROCEDURE `bug31617_sp`()
//BEGIN
//select SLEEP(40) s, f1 from bug31617;
//END$$
//DELIMITER ;

//my.ini: wait_timeout = 15

      MySqlConnection conn = new MySqlConnection();
      conn.ConnectionString = "DataSource=localhost;Database=test;UserID=root;Password=***;PORT=3306;Allow Zero Datetime=True;logging=True;";
      try
      {
          conn.Open();
          MySqlCommand m = new MySqlCommand("bug31617_sp", conn);
          m.CommandType = CommandType.StoredProcedure;
          using (MySqlDataReader dataReader = m.ExecuteReader())
          {
              dataReader.Read();
          }
      }
      catch(MySqlException x)
      {
          Console.WriteLine("ERROR: " + x.Message);
          MySqlCommand m = new MySqlCommand("select * from bug31617", conn);
          MySqlDataReader dataReader = m.ExecuteReader();
          dataReader.Read();
          Console.WriteLine(dataReader["f1"]);
          Console.WriteLine("DONE!");
      }
  }

Output:
[22.10.07 10:36:09] - Executing command QUERY with text ='SHOW VARIABLES'
[22.10.07 10:36:09] - Executing command QUERY with text ='SHOW COLLATION'
[22.10.07 10:36:09] - Executing command QUERY with text ='SET NAMES utf8;SET character_set_results=NULL'
[22.10.07 10:36:09] - Executing command QUERY with text ='SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME='bug31617_sp''
[22.10.07 10:36:10] - Executing command QUERY with text ='SHOW CREATE PROCEDURE `test`.`bug31617_sp`'
Retrieving procedure metadata for test.bug31617_sp from server.
[22.10.07 10:36:10] - Executing command QUERY with text ='call bug31617_sp ()'
ERROR: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
[22.10.07 10:36:40] - Executing command QUERY with text ='SHOW VARIABLES'
[22.10.07 10:36:40] - Executing command QUERY with text ='SHOW COLLATION'
[22.10.07 10:36:40] - Executing command QUERY with text ='SET NAMES utf8;SET character_set_results=NULL'
[22.10.07 10:36:40] - Executing command QUERY with text ='KILL QUERY 13'
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
[22.10.07 10:36:40] - Executing command QUERY with text ='select * from bug31617'
1
DONE!
The thread 0x1268 has exited with code 0 (0x0).
The thread 0x1270 has exited with code 0 (0x0).
The program '[5496] bug16126.vshost.exe: Managed' has exited with code 0 (0x0).

If you have any more new info to provide please do so and reopen the report.

I am also getting mentioned bugs with mysql connector 6.0.4.0. I would like to know the status of this bugs.. very urgent...please help

anyone knows if this bug was fixed ?

Guys, the status of the "bug" is clearly marked in report. All of the info, code, logs etc. are clearly noted under my post, [22 Oct 2007 10:42] Tonci Grgin.

Pity none responded to it.