CODEWITHSUNDEEP
c#mysqlexceptionmysqldatareader
Incognito
Incognito

Reputation: 455

Mysql Exception on ExecuteReader

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.

  1. set net_write_timeout=99999; set net_read_timeout=99999;
  2. Tried pooling=true on connection
  3. 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?

Upvotes: 1

Views: 1770

Answers (1)

Incognito
Incognito

Reputation: 455

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;
    }
}

Upvotes: 1

Related Questions