MySQL Bugs: #5256: MySqlDataReader and 'show tables from ...' behaviour

Bug #5256	MySqlDataReader and 'show tables from ...' behaviour
Submitted:	27 Aug 2004 15:26	Modified:	14 Oct 2004 23:49
Reporter:	Umberto Ballestrazzi	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	76	OS:
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
When you run the statement 'show tables from mydb' where mydb does not exists the database connection remains in state Fetching. If you reproduce the problem with the program below this happens because usually mysql server responds with an error during query send while in show tables statement it responds when CheckForRows is called.

How to repeat:
Run this

MySqlConnection connection = new MySqlConnection("Host=turtle;Database=mysql;user id=simpleuser;password=mypwd");

connection.Open();

MySqlCommand command;
MySqlDataReader dr;

//-----------------------------------------------------------------------------------
command = connection.CreateCommand();

command.CommandText = "show tables from qaaa";

try
{
 dr = command.ExecuteReader();

 while (dr.Read())
  Console.WriteLine(dr[0]);

 dr.Close();
}
catch (MySqlException ex)
{
 Console.WriteLine(ex.Message);
}
command.Dispose();

Console.WriteLine(connection.State);

connection.Close();

Console.ReadLine();

Suggested fix:
This is my new NextResult method in datareader.cs (just added try-catch-finally)

/// <summary>
/// Advances the data reader to the next result, when reading the results of batch SQL statements.
/// </summary>
/// <returns></returns>
public bool NextResult()
{
 if (! isOpen)
  throw new MySqlException("Invalid attempt to NextResult when reader is closed.");

 // clear any rows that have not been read from the last rowset
 if (currentResult != null)
  currentResult.Clear();

 // tell our command to continue execution of the SQL batch until it its
 // another resultset
 currentResult = command.ExecuteBatch(true);

 // if there was no more resultsets, then signal done
 if (currentResult == null) 
 {
  canRead = false;
  return false;
 }

 // When executing query statements, the result byte that is returned
 // from MySql is the column count.  That is why we reference the LastResult
 // property here to dimension our field array
 connection.SetState( ConnectionState.Fetching );
 
 _fields = new MySqlField[ currentResult.ColumnCount ];
 for (int x=0; x < _fields.Length; x++) 
  _fields[x] = currentResult.GetField();

 // try/catch/finally added for show databases behaviour
 try
 {
  hasRows = currentResult.CheckForRows();
  canRead = hasRows;   
 }
 catch (Exception ex)
 {
  canRead = false;
  throw ex;
 }
 finally
 {
  connection.SetState( ConnectionState.Open );
 }

 return true;
}

Hi Umberto,

Thank you for your bug report!

When I run this program I get the error I would expect and the word Fetching.  Here is the exact 
output I get (I'm connecting to mysql 4.0.21 on windows 2000):

Can't read dir of '.\qaaa\' (Errcode: 2) 
Fetching

Is this what you were getting?  I don't see what the problem is yet.  If this is what you're getting 
could you help me understand the problem.  Is it that Fetching is printed and although the error 
was returned the connector is still in the "fetch" state?

Best Regards

Hi Matthew,
I think (just an hopinion) that if you have an error the Connection should not remain in Fetching state.
Pratically:
1. The connection is unusable.
2. It is the only case that after an error the state is Fetching (if I write a wrong field name or a wrong table name or a wrong database name it does not happen).
3. It does not happen in any ADO provider different from this
Teorically:
1. The driver is not fetching anything because of the error.

Thanks for your interest!

Umberto

Hi,

OK, I see you point and I agree.  I will try this with the new beta version of the official MySQL 
.NET driver and see if it has been fixed already.

Best Regards

I verified this using the ByteFX .76 driver and the MySQL 1.0 beta driver.

I used a windows 2000 server machine for the tests.

The driver remains in the Fetch state even though show tables from blah has returned an error.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html