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

Bug #5256	MySqlDataReader and 'show tables from ...' behaviour
Submitted:	27 Aug 2004 17:26	Modified:	15 Oct 2004 1:49
Reporter:	Umberto Ballestrazzi
Status:	Closed
Category:	Connector/Net	Severity:	S3 (Non-critical)
Version:	76	OS:
Assigned to:	Reggie Burnett	Target Version:

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