Mysql Exception on ExecuteReader

Question

Well i had a weird exception on my program so i tried to replicate it to show you guys, so what i did was to create a table with id(int-11 primary), title(varchar-255) and generated 100k random titles with 40 chars lenght, when i run my method that reads the count for each id it throws an exception check below for more.

What i found is that this was because of timeouts so i tried this for the timeouts.

set net_write_timeout=99999; set net_read_timeout=99999;
Tried pooling=true on connection
Tried cmd.timeout = 120;

I also tried adding MaxDegreeOfParallelism i played with multiple values but still the same error appears after a while.

My exception:

Could not kill query, aborting connection. Exception was Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

public static string db_main = "Server=" + server + ";Port=" + port + ";Database=" + database_main + ";Uid=" + user + ";Pwd=" + password + ";Pooling=true;";

private void button19_Click(object sender, EventArgs e)
{
    List<string> list = db.read_string_list("SELECT id from tablename", db.db_main);
    //new ParallelOptions { MaxDegreeOfParallelism = 3 },
    Task.Factory.StartNew(() =>
    {
        Parallel.ForEach(list, id =>
        {
            string sql = "SELECT COUNT(*) FROM tablename where id=" + id;
            var ti = db.read_int(sql, db.db_main);
            Console.WriteLine(ti);
        });
    }).ContinueWith(_ =>
    {
        Console.WriteLine("Finished");
    });
}

public static int? read_int(string sql, string sconn)
{
    var rdr = MySqlHelper.ExecuteReader(db.db_main, sql);
    if (rdr.HasRows)
    {
        rdr.Read();
        return rdr.GetInt32(0);
    }
    else
        return null;
}

Alternate Method to read int with timeout option.

public static int? read_int2(string sql, string sconn)
{
    using (var conn = new MySqlConnection(sconn))
    {
        using (var cmd = new MySqlCommand(sql, conn))
        {
            //cmd.CommandTimeout = 120;
            conn.Open();
            using (var rdr = cmd.ExecuteReader())
            {
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return rdr.GetInt32(0);
                }
                else
                    return null;
            }
        }
    }
}

What can be causing this? any clues?

In your first example of read_int you don't close the reader. Do you have the same problem with the read_int2? — Steve, Nov 03 '14 at 15:39
Actually on this table no i dont have the problem but in my method and on the actual table read_int_2 throws the same exception. — Incognito, Nov 03 '14 at 16:10
Finally to solve this you need to adjust net_read_timeout and limit the queries. — Incognito, Nov 04 '14 at 19:08
I think that you could post your own answer because the info could be ho help to future readers. — Steve, Nov 04 '14 at 19:09

Incognito · Answer 1 · 2014-11-11T22:01:00.080

So finally my solution on this was to increase net_read_timeout variable (im pointing out net_write_timeout because that can happen when executing a long query too)

Run these queries *Note: After you restart your PC default values will take place again.

set @@global.net_read_timeout = 999;
set @@global.net_write_timeout = 999;

or you can add this on the connection string

default command timeout=999;

Finally i used this method to read the values.

public static int? read_int(string sql, string sconn)
{
    try
    {
        using (MySqlDataReader reader = MySqlHelper.ExecuteReader(sconn, sql))
        {
            if (reader.HasRows)
            {
                reader.Read();
                return reader.GetInt32(0);
            }
            else
                return null;
        }
    }
    catch (MySqlException ex)
    {
        //Do your stuff here
        throw ex;
    }
}

Mysql Exception on ExecuteReader

1 Answers1