Page 1 of 2
Disconnections/Net Packets Out Of Order
Posted: Mon 09 Jun 2008 14:00
by NickG
I am trying to use your software in a mockup of a program for my company (we will be buying it as soon as i can resolve these issues as the are relatively show stopping)
My program is setup as such:
I have the main thread with all the forms, two database threads (one for blocking and one for non-blocking calls). The two database threads each have a queue in them. "Commands" (instances of an inherited class) are added to the queue. The queue then executes each command passing it a mysql connection object. in the command a new MySQLCommand is created, and the command executes. my problem is that i am receiving frequent failed connections and the command is failing. i ping the connection every time before i pass it to a command o make sure it is working and open. My program essentially just stops writing to the database after a while due to connection failures. i am getting a lot of "Lost connection to mysql server during query" Simply restarting the program makes it all work normally again. I am really at a loss for whats going on i have disabled connection pooling but that did NOT seem to help. Any ideas?
Posted: Mon 09 Jun 2008 14:02
by NickG
Ah can you move this to the right forum? sorry, this is for a MyDirect .Net program
Posted: Mon 09 Jun 2008 14:23
by Alexey.mdr
Hello,
If I'm correct you are using multithreading, right?
Do you use an independent connection for each thread?
could you please provide a small sample block of code?
Please inform me what version/edition of MyDirect .NET you are using.
P.S. the topic was moved from OraDirect .NET
Regards,
Alexey.
Posted: Mon 09 Jun 2008 15:26
by NickG
Yes i'm using multithreading. My nonblocking class (where the majority of the writes happen) looks like this: (Btw each thread gets it's on specific mysql connection object. Connection pooling is turned off.)
Sorry if this is too much code.
Code: Select all
Public Class masteriDB
'Private queryPQ As PriorityQueue(Of iDb)
Private queryPQ As Queue(Of iDb)
Private events As ManualResetEvent
Private _ret As String
Private running As Boolean
Private query As MySqlConnection
Private Shared log As ILog = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType)
Public Sub New(ByVal cs As String)
Try
queryPQ = New Queue(Of iDb)()
events = New ManualResetEvent(False)
running = True
query = New MySqlConnection(cs)
query.ConnectionTimeout = 99999
query.Open()
Catch ex As Exception
MsgBox("Problem connecting to database!")
log.Warn("Could not connect to database", ex)
End Try
End Sub
Public Sub addCommand(ByVal q As iDb)
If q Is Nothing Then
Throw New Exception("q is nothing@!#!@")
End If
SyncLock queryPQ
queryPQ.Enqueue(q)
End SyncLock
'queryPQ.Add(q, q.Priority)
events.Set()
End Sub
Public Sub finishAndDie()
'While queryPQ.Empty = False
While queryPQ.Count > 0
doQuery()
End While
running = False
events.Set()
End Sub
Public Sub commandLoop()
While running
If queryPQ.Count = 0 Then
events.Reset()
events.WaitOne()
End If
doQuery()
End While
End Sub
Public Sub doQuery()
If queryPQ.Count = 0 Then Exit Sub
Dim q As iDb
Try
If query.Ping True Then
query.Open()
End If
SyncLock queryPQ
q = queryPQ.Dequeue
End SyncLock
If q Is Nothing Then
log.Error("Nothing in queue")
Exit Sub
End If
log.Debug("Executing: " & q.Name)
q.Execute(query)
Catch ex As Exception
log.Error("Error executing query(" & q.Name & ")", ex)
End Try
End Sub
End Class
Posted: Tue 10 Jun 2008 08:11
by Alexey.mdr
1. Do you have to hold a MySqlConnection opened? In the code you posted the connection instance is never closed. Probably you can use a connection pooling?
2. You should lock any operation that uses the connection, i.e.
- query.Ping
- q.Execute(query)
What is the exact exception message you receive, “Net Packets Out Of Order” or "Lost connection to mysql server during query”?
Let me know on the results.
Posted: Tue 10 Jun 2008 13:35
by NickG
Okay i will lock everything, i disabled connection pooling i can use it but i thought it could be what was causing the issues. The reason i am holding the connection open is that i run a lot of quieres and didn't want the overhead of opening/closing a connection every time i run one but if there is not alot of overhead (or if it is supposed to be opened/closed each time) i can easily do that
Posted: Wed 11 Jun 2008 08:34
by Alexey.mdr
Well, actually both approaches are fine.
If you will use connection pooling there won't be much overheads,
as the physical connections are not closed for some time span (10 to 40 seconds).
It's likely the main problem is in the unlocked method:
q.Execute(query)
If several threads execute this method simultaneously, you will definitely see the network error.
Posted: Thu 12 Jun 2008 16:12
by NickG
Okay i locked the variable and am still recieving errors. If i have two seperate mysqlconnection objects in my program (both goto the same database/same login etc) if i use both of those connections objects at the same time in two different threads will that break? My program records data fine but seems to randomly break after 6+ hours
Posted: Fri 13 Jun 2008 08:01
by Alexey.mdr
Most likely using separate connections in different threads will solve the problem.
You can try running the application in debug mode and see where it throws the exception and what threads were running.
Presumably, as soon as you get the call stack and the error message you will find the source of the problem.
Please let me know on the results.
Posted: Fri 13 Jun 2008 14:11
by NickG
I am using separate threads and when one connection breaks both of them start to fail
Posted: Tue 17 Jun 2008 08:49
by Alexey.mdr
Please send me (alexeyman*crlab*com) a small test project to
reproduce the problem.
It is desirable to use 'test' schema objects, otherwise
include the definition of your own database objects.
If it is impossible for you to create the test project, send
us a piece of your code where the error occurs.
1) What is the version/edition of the product you use?
2) What exception do you get?
Posted: Mon 23 Jun 2008 20:35
by NickG
We actually own this component now =) It is very well written once i get passed these problems i will be very happy with it. I did some additional testing and i found that if i unplug the client machine and plug it back in even though the net connection is restored it never reconnects to the database. So what seems to be happening is the component is just reusing dead connections? Here is the relevant code (this runs in a loop using it's own MysqlConnection that no other object has access to so it isn't a threading issue, there are other threads (1 other that actually talks to mysql) but it also has a completely separate MysqlConnection object)
Notice that i clear all the connection pools on a failed query (they never really fail fro any reason but a loss of connection) I am beginning to think that this is a bug within the component vs. something i am doing wrong because it should definitely be able tor reconnect if i just pull the network plug and plug it back in. Thanks!
Public Sub doQuery()
If queryPQ.Count = 0 Then Exit Sub
Dim q As iDb = Nothing
Try
SyncLock queryPQ
q = queryPQ.Peek
End SyncLock
If q Is Nothing Then
log.Error("Nothing in queue")
Exit Sub
End If
log.Debug("Executing: " & q.Name)
SyncLock query
query.Open()
q.Execute(query)
query.Close()
End SyncLock
SyncLock queryPQ
queryPQ.Dequeue()
End SyncLock
Catch ex As Exception
If q Is Nothing Then
log.Error("Error executing query(Q is Nothing!)", ex)
Else
log.Error("Error executing query(" & q.Name & ")", ex)
End If
MySqlConnection.ClearAllPools()
backupQueue()
End Try
End Sub
Posted: Tue 24 Jun 2008 20:06
by NickG
Any ideas?
Posted: Wed 25 Jun 2008 10:04
by anton.connect
Hello, I cannot understand your code exactly. Please, explain to me, what is iDb? Is it an interface or not? What do this lines of your code do:
query.Open()
q.Execute(query)
query.Close()
If you physically loose the connection and then restore it, you need to catch this situation and reopen the connection to the database. It doesn't happen automatically.
Posted: Wed 25 Jun 2008 13:44
by NickG
query is a very poorly named variable, it is actually the server connection. Dosn't doing an open/close reopen the connection to the database (ie physical connection is lost, but i DO close, then open the connection and still never regain connection) iDb is an interface for a custom database command object (essentially i pass a MysqlConnection to a class which implements iDb and it uses that to execute a command)