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

[27 Aug 2004 15:26] Umberto Ballestrazzi
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;
}
[4 Oct 2004 16:23] Matthew Lord
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
[4 Oct 2004 17:12] Umberto Ballestrazzi
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
[4 Oct 2004 19:23] Matthew Lord
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
[5 Oct 2004 14:16] Matthew Lord
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.
[14 Oct 2004 23:49] Reggie Burnett
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