Devart Forums

tduong

That's great news! Looking forward to the next build!

tduong

Hi,

I am running into a "transport channel is closed" exception while executing a DataReader against a large table, but this only happens when the database is configured with SSL. I am using version 6.0.58.0.

Here is some code that can reproduce our problem:

Code: Select all


string connString = 

using (IDbConnection conn = new MySqlConnection(connString))
{
    IDbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT * FROM large_table;";
    cmd.CommandTimeout = 0;

    IDataReader reader = dbCommand.ExecuteReader();

    while (reader.Read()) 
    {
        // Sleep for 5 milliseconds to emulate some data processing
        // The exception can be observed even with 1 millisecond sleep.
        // [size=x-large]If no ThreadSleep is issued, then the code runs just fine[/size]
        Thread.Sleep(5); 
    }
}

When I run the above snippet against a large table (5Gb, ~500000 rows with text and blob columns) on an SSL enabled database, I receive a "Lost connection to MySQL server during query" exception. The inner exception is, "Transport Channel is closed".

Exception: Lost connection to MySQL server during query

Inner Exception: Devart.Common.i: Transport channel is closed.
at Devart.Common.s.e(Byte[] A_0, Int32 A_1, Int32 A_2)
Stack: at Devart.Data.MySql.bh.a(Exception A_0)
at Devart.Common.s.e(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.MySql.bk.a(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.MySql.bk.a()
at Devart.Data.MySql.bk.b(Byte[] A_0)
at Devart.Data.MySql.v.a(Byte[] A_0)
at Devart.Data.MySql.v.a(Byte[] A_0, Boolean A_1)
at Devart.Data.MySql.ao.f()
at Devart.Data.MySql.a2.b(Boolean A_0)
at Devart.Data.MySql.MySqlDataReader.a(Boolean A_0)
at Devart.Common.l.a(Int32 A_0, Object A_1, Int32 A_2, Object A_3)
at Devart.Common.l.a.a(Int32 A_0, Int32 A_1, Object A_2, l A_3)
at Devart.Common.l.a(Int32 A_0, Int32 A_1, Object A_2)
at Devart.Common.DbConnectionBase.a(Int32 A_0)
at Devart.Common.DbConnectionBase.Close()
at Devart.Common.DbConnectionBase.Dispose(Boolean disposing)
at System.ComponentModel.Component.Dispose()

The following are some MySql Server and connection specific values that I've played around with. None of them seem to have a direct correlation with the exception that I'm seeing:

Wait_Timeout - This value is by default already 8 hours, the reader is throwing the exception within 4-5 minutes.

Connect_Timeout - This value has been increased to some rather large value even though I do not really suspect this to be the culprit. The value is modified by issuing a "SET Connect_Timeout" command using the same connection object

Net_Read_Timeout
Net_Write_Timeout - Both these values have been increased to 10 minutes with the "SET NET_WRITE_TIMEOUT/NET_READ_TIMEOUT" command using the same connection object that is used to issue the query.

I would also like to mention that if we do not connect to the database with SSL, the above code runs just fine.

When I run the above code, I could see the mysql server executing the "select" command by running a "SHOW FULL PROCESSLIST" from another mysql client application. The status is usually "Sending data" or "Writing to Net". I'm no expert on the inner workings of the DataReader, but logically the data sent from the server must reside somewhere on the client. Is there some threshold that's being hit, causing Devart to close the channel and throw this exception due to the data being so massive?

Thank you for your help!

tduong

Hi Shalex,

In step 2, connection #2 is in open and *active* state because we are doing database operations with it for those 8+ hours. Connection #1, which belongs to a different pool, was closed in step 1 (but the server sees it as idle). After 8+ hours are over, and we're done using connection #2 for step 2, we instantiate a new MySqlConnection object, passing in the connection string that yielded connection #1. This newly instantiated connection gives the "lost connection to MySql server during query".

#Step 1
MySqlConnection firstConn = new MySqlConnection(connString1)
firstConn.Open();
// Perform some small operations
firstConnection.Close();

#Step 2
MySqlConnection secondConn = new MySqlConnection(connString2)
secondConn.Open();
// Perform set of operations for 8+ hours (no problems)
secondConn.Close();

#Step 3
firstConn = new MySqlConnection(connString1)
firstConn.Open();
// Exception occurs hereafter.

tduong

Hi,

We have also encountered this problem recently. Quite some time was spent before coming to the conclusion that this is an issue with the connection being returned from the pool.

Our desktop application uses two different connections - different meaning the strings are not identical, and are thus in separate pools.

Stripping away the details of what these connections strings are used for, our application starts, performs a set of steps, then exits.

Our app uses two connections:

Connection string 1
Connection string 2

Step 1 - Instantiates a MySqlConnection using connection string 1, and closes it. This step is relatively fast
Step 2 - Instantiates a MySqlConnection using connection string 2, and closes it. This step can take several hours. For the particular exception that we encountered, this step took > 8 hours, which is (surprise) the default wait_timeout value as set on the msyql server.
Step 3 - Creates a new MySqlConnection using connection string 1.

Exception occurs in step 3 when step 2 > wait_timeout. The server terminates connection 1, but the pool still returns this connection. You can even call "MySqlConnection.Open()" successfully, but when a command is executed, we get the infamous "lost connection to MySql server during query".

We thought about creating a wrapper around the MySqlConnection instantiation, and to perform validation of the connection ourself, but this seems like it is something that should be done by the connection pool manager that distributes these Connection objects. Another alternative would be for us to invalidate the pool after Step2, but, as you said before, there is a 30 second wait time out.

It would be nice if the underlying code returned a *good* connection.

Are there any future plans to do this?

Thanks

Devart Forums

Search found 4 matches

SSL - Transport channel is closed when using DataReader