Updating a remote mysql database from an ASP.NET server

[mquintana]

I'm trying to update a remote MySql database (hosted in a shared php/Linux server) from another server running ASP.NET but sometimes I'm getting this error:

Lost connection to MySQL server during query


I guess it ISN'T closing each connections correctly.. then when they are opened again it crashes because the max pool of connections allowed in the server or even by the timeout, I don't know

I'm using a custom component to establish mysql remote connections: Devart.Data.MySql

This code is executed within a loop... part of the code is disabled..

strSQL2 = "INSERT INTO table (order_no, nolic) VALUES (" & nMax & ",'" & Buffer & "')"

Dim conn5 As New Devart.Data.MySql.MySqlConnection("Host=22.222.222.22;Port=3306;Database=myDatabase; User Id=myId;Password=myPass;")

'conn5.ConnectionTimeout = 3600


conn5.Open()
Dim cmd6 As New Devart.Data.MySql.MySqlCommand(strSQL2, conn5)

Try
cmd6.ExecuteNonQuery() ----> HERE THE ERROR


Catch ex As System.Exception
SendMailOnError(ex.ToString, "2")
Finally
'cmd6.Dispose()
'conn5.Close()


conn5.Dispose()


'conn5.Kill(conn5)
'conn2.Close()
' conn3.Dispose()
'conn3.Kill(conn3)
End Try

After running this code I create/open a new connection and execute an INSERT INTO to another table in the same database then continue with the next iteration.

How can I solve this error?

[Pinturiccio]

We could not reproduce the issue. Could you please specify the following:
1. Exception stack traсe;
2. The fragment of the code with the whole cycle;
3. Does the issue persist if you use Pooling=False in ConnectionString? If turning pooling off helps, you can try using pooling with the Validate Connection option: "Pooling=true;Validate Connection=true;".
4. dotConnect for MySQL version (x.xx.xxx) and MySQL server version.