C# – MySQL Connection timeout on C#

cMySQLtimeout

I just check other posts on stack overflow about the connection timeout, but looks like everybody fixed it using 

com.CommandTimeout = int.MaxValue;

and

dbaccess.ServerAddress = "Server=mysql;Database=MyDB;Connect Timeout=2147483;Uid=username;Pwd=mypassword";

Unfortunately, I still get this error message after 40 seconds:

The timeout period elapsed prior to completion of the operation or the server is not responding.

You can take a look at the two screenshots.

MySQL Workbench works fine and it takes 48 seconds in executing the same query.

ErrorMessage

Workbench

I can not use limits to reduce the subset acquired because I am centralizing information split in different tables. A stored procedure can not be use since the information is stored in different mysql servers too.

Any feedback will be much appreciated.

Fernando

EDIT:

This is the code. I posted the image to show the execution time, not the error message. 

public DataSet ExecuteQuery(string[] Query,  TableMap[] TableMappings)
{
    //Mysql vars
    MySqlConnection con;
    MySqlCommand com;
    MySqlDataAdapter adapter;
    DataSet ds;

    con = new MySqlConnection();
    con.ConnectionString = _serveraddress;

    con.Open();

    com = con.CreateCommand();
    com.CommandType = System.Data.CommandType.Text;

    foreach (string st in Query)
    {
        com.CommandText = com.CommandText + st;
    }

    com.CommandTimeout = int.MaxValue;

    adapter = new MySqlDataAdapter(com.CommandText, con);

    foreach (TableMap tm in TableMappings)
    {
        adapter.TableMappings.Add(tm.SourceTable, tm.TableSet);
    }

    ds = new DataSet();
    adapter.Fill(ds);

    return ds;
}

Best Answer

You problem lies in this line 

 adapter = new MySqlDataAdapter(com.CommandText, con);

Here you pass the command text to the new MySqlDataAdapter. Of course this MySqlDataAdapter has no knowledge of the timeout you have set on the command because the twos (adapter and command) are no linked together.

If you change to 

 adapter = new MySqlDataAdapter(com);

Then your adapter will use the MySqlCommand and its CommandTimeout property

Said that, I really recommend you to start approaching database code following the well established patterns

public DataSet ExecuteQuery(string[] Query,  TableMap[] TableMappings)
{
    // using statement will ensure proper closing and DISPOSING of the objects
    using(MySqlConnection con = new MySqlConnection(_serveraddress))
    using(MySqlCommand com = con.CreateCommand())
    {
        con.Open();
        // Not needed, it is the default
        // com.CommandType = System.Data.CommandType.Text;

        foreach (string st in Query)
        {
            // Are you sure the st is properly terminated with a semicolon?
            com.CommandText = com.CommandText + st;
        }
        com.CommandTimeout = int.MaxValue;
        using(MySqlDataAdapter adapter = new MySqlDataAdapter(com))
        {
            foreach (TableMap tm in TableMappings)
               adapter.TableMappings.Add(tm.SourceTable, tm.TableSet);

            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
       }
    }
}

The using statement is really important here, in particular with MySql that is very sensible to connections not closed correctly and could stop your program with error messages about no more connections available.

Related Topic