Connecting to 10 mysql servers
Hello,
i am trying to connect to 10 mysql servers and get aprox. 5000 rows. If i make the test from local network it works but if i take the test using mysql servers (same version) from different city it shows me the error
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"
I also tried adding the line
comand1.CommandTimeout = 0 , it has the same effect.
The code i am using is (datagridview1 contains servers ip) :
Code:
For i = 0 To numberofstores - 1
Try
Dim conn1 As New MySqlConnection
Dim comand1 As New MySqlCommand
Dim myconnstring1 As String = "server=" & DataGridView1.Rows(i).Cells(0).Value.ToString _
& ";" & "user id=" & My.Settings.Item("mysql_user") & _
";" & "password=" & My.Settings.Item("mysql_pass") & _
";" & "database=" & My.Settings.Item("mysql_dbase")
Dim reader1 As MySqlDataReader
conn1.ConnectionString = myconnstring1
comand1.Connection = conn1
comand1.CommandText = "SELECT col1,col2,col3,col4,col5,col6 from table1 where dateofimport !='" & Date.Now.ToString("yyyy-MM-dd") & "' ;"
conn1.Open()
reader1 = comand1.ExecuteReader
oWrite1 = IO.File.CreateText("C:\" & DataGridView1.Rows(i).Cells(0).Value.ToString & ".txt")
While reader1.Read
oWrite1.WriteLine(reader1.GetString(0) & ";" & reader1.GetString(1) & ";" & reader1.GetString(2) & ";" & reader1.GetString(3) & ";" & reader1.GetString(4) & ";" & reader1.GetString(5))
End While
oWrite1.Close()
reader1.Close()
conn1.Close()
Catch ex As Exception
'error code
End Try
Next
Any ideas on how to make this work?
Thank you very much!
Re: Connecting to 10 mysql servers
Where does it time out? When making the connection? Or when running the query? since you are accessing servers over the internet, keep in mind that it's going to take longer than it would over a local network connection.
Two things you'll want to read up on are "ConnectionTimeout" ... this property on the connection determines how long to wait before throwing a Timeout exception. Default is 30 seconds I think. The other is "CommandTimeout" on the Command object. this determines how long to wait for a command to complete before timing out. Try playing with values for those properties until you get a combination that works.
-tg