This project is read-only.

Hang connecting to MYSQL

Jan 10, 2013 at 5:03 PM

Hi,
The code below works fine and connects securely to the server and then the database but once its run a second time its hangs for about 5-6 seconds while opening the MYSQL connection and running a query. If I run this without SSH it works fine no matter how many times I run it. Is this a problem with the code or the server?

Simple form with connection details in textboxs and a datagridview to display the results.

Please help going a bit mental with the coffee trying to figure it out!!!!!

Cheers,

John

        private DataTable MYSQL_GET(string sQuery)
        {
            DataTable dt = new DataTable();

            using (var client = new SshClient(txtSSH_Address.Text, txtSSH_Username.Text, txtSSH_Password.Text))
            {
                client.Connect();

                var port = new ForwardedPortLocal("127.0.0.1", Convert.ToUInt32(txtSQL_Port.Text), "localhost", Convert.ToUInt32(txtSQL_Port.Text));
                client.AddForwardedPort(port);

                port.Start();
              
                string connstr = "server=localhost;user=" + txtSQL_Username.Text + ";database=store;port=" + txtSQL_Port.Text + ";password=" + txtSSH_Password.Text + ";";

                using (MySqlConnection conn = new MySqlConnection(connstr))
                {
                    conn.Open();

                    MySqlDataAdapter da = new MySqlDataAdapter(sQuery, connstr);
                    da.Fill(dt);

                    conn.Close();
                }                          

                port.Stop();
                client.Disconnect();

                return dt;
            }
        }
Coordinator
Jan 13, 2013 at 11:36 PM

Hi,

 

It would be hard for me to tell where problem might be, but what you can try to do here is may be to run in debug mode

and when it connects for second time and hangs, I would pause and see where it is right now in the code, somewhere in SSH library or not.

If its waiting for reply then most likely its a server issue.

 

Hope it helps a little.

 

Thanks,

Oleg

Jan 14, 2013 at 11:00 AM

Hi Oleg,

It pauses for a few seconds on the following lines after its passed through the first time:

conn.Open();

da.Fill(dt);

When you connect without SSH the code is fine now matter how many time you connect. With SSH first time fine any time after that pauses for a few seconds is there something not closing properly? Soon as I stop the program and start it again its fine the first time again.

Thanks,

John

Coordinator
Jan 14, 2013 at 9:03 PM

Hi,

 

Can you try to add this:

                port.Exception += delegate(object sender, ExceptionEventArgs e)
                {
                    Console.WriteLine(e.Exception.ToString());
                };
                port.RequestReceived += delegate(object sender, PortForwardEventArgs e)
                {
                    Console.WriteLine(e.OriginatorHost + ":" + e.OriginatorPort);
                };

First handle will tell you if there any exception occurred during port forwarding that you might not see.

The second one will notify you as soon as request received.

Just see if you have any delay after you call Open and receiving request as well see if you getting any exception.

 

Thanks,

Oleg

Jan 15, 2013 at 9:19 AM

Hi,

I get the following PortForwardEventArgs each time I hit conn.Open(); & da.Fill(dt);

127.0.0.1:53109             conn.Open(); // First time round

127.0.0.1:53110             da.Fill(dt);     // First time round

127.0.0.1:53112             conn.Open(); // Second time round

127.0.0.1:53113             da.Fill(dt);     // Second time round



 

Thanks,

John

Coordinator
Jan 15, 2013 at 3:45 PM

Hi,,

 

Well, it will not tell me much, except that there is no exception occurs.

I guess what I wanted you to see is how much time occurs between calls to conn.Open and when you see that request was expected, same for da.Fill.

Just see if this time are always the same regardless first time you call it or second or not.

If time elapsed is the same then may be something being delayed before I start forwarding connection if times are different then may be this delay coming from database connection or somewhere else.

Thanks,

Oleg

Jan 16, 2013 at 4:20 PM

Hi Oleg,

Just added a timer to check the time between each and its near enough always 15 seconds for each excluding the first pass.

 

Thanks,

John

Jan 16, 2013 at 8:42 PM

John,

I used your code and implemented mysql ports but I am getting this error "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"

Does not seem like an error with SSH.Net portforwarding (forward to port localhost:81 works for example) I can connect to my mysql using putty. What ports did you use for mysql connection? Could you check for this error if you have time?

P.S I did not input info into text boxes

Thanks

Ben

using (client == new SshClient("IP", portnumber, "username", "password")) {
    client.Connect();

    object port = new ForwardedPortLocal("127.0.0.1", 3306, "localhost", 3306);
    client.AddForwardedPort(port);

    port.Start();

    string connstr = "server=localhost;user=;database=;port=3306;password=;";
    using (MySqlConnection conn = new MySqlConnection(connstr)) {
        conn.Open();
        MessageBox.Show("Connected to mysql");
        conn.Close();
    }
    port.Stop();
    client.Disconnect();
}


 

Jan 16, 2013 at 8:52 PM

Figured it out. I put localhost for portforward detestation, when I should of put the server IP. Sorry for wasting any time!

 

Thanks,

Ben

Coordinator
Jan 17, 2013 at 2:43 AM

John,

 

I just made some improvments and fixes to port forwarding feature and seems to be working better, at leas after I ran this test:

            using (var ssh = new SshClient(connectionInfo))
            {
                ssh.Connect();

                //var port1 = ssh.AddForwardedPort<ForwardedPortLocal>("localhost", 8084, "www.renci.org", 80);
                var port1 = new ForwardedPortLocal("localhost", 8084, "www.renci.org", 80);
                ssh.AddForwardedPort(port1);
                port1.Exception += delegate(object sender, ExceptionEventArgs e)
                {
                    Console.WriteLine(e.Exception.ToString());
                };
                port1.RequestReceived += delegate(object sender, PortForwardEventArgs e)
                {
                    Console.WriteLine(e.OriginatorHost + ":" + e.OriginatorPort);
                };
                port1.Start();

                var globalCounter = 0;

                System.Threading.Tasks.Parallel.For(0, 10000,
                    new ParallelOptions
                    {
                        MaxDegreeOfParallelism = 40,
                    },
                    (counter) =>
                    {
                        Interlocked.Increment(ref globalCounter);
                        var req = HttpWebRequest.Create("http://localhost:8084");
                        using (var response = req.GetResponse())
                        {
                            var r = new StreamReader(response.GetResponseStream());
                            var output = r.ReadToEnd();
                            Console.WriteLine(string.Format("{3}\tRequest: {0}\tTime: {1}\tLength: {2}", counter, DateTime.Now, output.Length, globalCounter));
                        }
                    }
                );


                Thread.Sleep(1000 * 60 * 10);

                port1.Stop();

                ssh.Disconnect();
            }

Can you please checkout latest code and see if it fixes your problem?

 

Thanks,

Oleg

Jan 17, 2013 at 10:19 AM

Hi,

Sorry, still getting the same issue. Tried moving the return outside of the SSH, Disposing of all the objects but still nothing!!!!!

I can connect fine with HeidiSQL and putty?

 

Thanks,

John

Coordinator
Jan 17, 2013 at 2:35 PM

Hmm,

then I guess I will have to try to recreate this scenario here and get back to it a little bit later since wont have time for it now :(:(.

 

Do you mind log it as a issue and describe how to configure it so I could get back to it later?

 

I am just wondering if anybody else ran into this problem since I think some people mentioned using port forwarding to connect to MySQL before.

 

Thanks,

Oleg

Jan 17, 2013 at 4:34 PM

No problem I will do.

Thanks Oleg,

 

John

Aug 27, 2013 at 7:50 PM
I am experiencing this same issue. The first time I connect to my MYSQL database using a port forward it's fine. On subsequent attempts to connect, my code hangs on:

pDBConn.Open()

for about 15 or so seconds. Then it connects just fine and runs my queries. Any solution to this yet?
Aug 27, 2013 at 8:03 PM
It seems that this works a lot better when I use a shared variable to establish the SSH connection and port forward. Since I'm developing a multi-threaded service with this, it would seem to make sense to basically establish the SSH connection and port forward a single time when the service starts up and simply leave it connected. When I use the library in this manner, I have no hanging issues with opening my MYSQL database connection. Hope this helps!