MySQL Bugs: #20826: MySqlConnection.State does not report closed connection

Bug #20826	MySqlConnection.State does not report closed connection
Submitted:	3 Jul 2006 16:51	Modified:	26 Sep 2006 19:28
Reporter:	vincent ugenti	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.7	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
After querying MySqlConnection.State property to determine if the connection is closed, and executing a query, the query will sometimes fail with the following error:

Inner Exception: System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host
   at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
Source: System
Message: Unable to read data from the transport connection.
Stack trace: 
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   at System.IO.BufferedStream.ReadByte()
   at MySql.Data.MySqlClient.PacketReader.ReadHeader()
   at MySql.Data.MySqlClient.PacketReader.OpenPacket()
   at MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64& lastInsertId)
   at MySql.Data.MySqlClient.CommandResult.ReadNextResult(Boolean isFirst)
   at MySql.Data.MySqlClient.NativeDriver.SendQuery(Byte[] bytes, Int32 length, Boolean consume)
   at MySql.Data.MySqlClient.MySqlCommand.GetNextResultSet(MySqlDataReader reader)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchemaFromCommand(Object data, SchemaType schemaType, IDbCommand command, String srcTable, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType schemaType, IDbCommand command, String srcTable, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType schemaType)

There is more to the stack trace but it simply points to the location in my source code that executed FillSchema.

How to repeat:
Start up MySQL and client application, establish a connection.  Terminate the conenction abnormally.  When you query the MySqlConnection.State property, it will show ConnectionState.Connected.  When attempting to execute query using the connection, you will receive a similar stack trace to what is shown in "Description".

Suggested fix:
The MySqlConnection.State property should actively query the server to determine if the connection is still present at the time the property is accessed.  This way if the connection is abnormally terminated, the client application can know before executing a query against the connection.

Hi Vincent and thanks for your problem report.
Verified as described by reporter on MySQL server 5.0.24 BK on Suse 10, Win XP SP2 client:
Output
Server version: 5.0.24
Conn state: Open
Shuting down server here
Conn state(server down): Open

Code snipet
   class Program
    {
       readonly static string connstr = "Server=munja;Database=Bug20084;Username=root;Password=;Port=3307;allowzerodatetime=true;";
        static void Main(string[] args)
        {
            using (MySqlConnection con = new MySqlConnection(connstr))
            {
                //20826
                con.Open();
                Console.WriteLine("Server version: " + con.ServerVersion);
                Console.WriteLine("Conn state: " + con.State);
                Console.WriteLine("Shuting down server here");
                Console.WriteLine("Conn state(server down): " + con.State);

This is not a bug. The state property does not and should not query the server to make sure it's still alive.  SqlClient doesn't do this either.   Code that exeuctes queries has to be guarded with try catch.  Checking the state property is not enough.  The server could go down after the state property is checked and before the query is executed.

You can see if yu're disconnected by sending a Ping() to MySQL:

If ConexionMysql.Ping() = False Then
...