MySQL Connection timeout on C#

Question

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

Please show the full code starting at instantiating what ever is behind dbaccess till opening the connection. — C4d
– C4d, Commented Nov 24, 2016 at 10:04
@Seteve Sorry for the picture. I was trying not to show the error message but the execution time. — Fernando Rodríguez
– Fernando Rodríguez, Commented Nov 24, 2016 at 10:12
This cant be your code. dbaccess does not exist in your edit. — C4d
– C4d, Commented Nov 24, 2016 at 10:24

Steve · Accepted Answer · 2016-11-24 10:50:34Z

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.

C4d · Accepted Answer · 2016-11-24 10:29:00Z

0

Change Pwd to password. Also the property server should be set to an ip or domain.

conn = "Server=[ip/domain];...password=mypassword";

Wrong credentials would result in a failed login, not a timeout. I guess it really tries to connect to mysql. If the server is running on your local computer just set localhost as server.

answered Nov 24, 2016 at 10:29

C4d

3,2905 gold badges31 silver badges53 bronze badges

3 Comments

Fernando Rodríguez Over a year ago

@Steve Thank you. It works! I took your advice on the "using" command. I would modify the rest of mysql call to use the "using". Again, thank you

Fernando Rodríguez Over a year ago

In the server parameter i am using the computer name since this is use on the itnernal network or the VPN. Should i use the FQDN of the internal domain name? What is your recomendation in here? I am open to improvements

C4d Over a year ago

That strongly depends on your software. On local network I guess there are no big benefits for one of them. I generally provide settings to change the db-connection form user-side so this question would fall off. The only thing I can say for sure is to rather use a representation of the ip like a FQDN or computer-name (never tried that) to keep on the connection even if the ip changes.

Collectives™ on Stack Overflow

MySQL Connection timeout on C#

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related