Disconnections/Net Packets Out Of Order

NickG · Post by **NickG** » Mon 09 Jun 2008 14:00

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?

NickG · Post by **NickG** » Mon 09 Jun 2008 14:02

Ah can you move this to the right forum? sorry, this is for a MyDirect .Net program

Alexey.mdr · Post by **Alexey.mdr** » Mon 09 Jun 2008 14:23

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.

NickG · Post by **NickG** » Mon 09 Jun 2008 15:26

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

Alexey.mdr · Post by **Alexey.mdr** » Tue 10 Jun 2008 08:11

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.

NickG · Post by **NickG** » Tue 10 Jun 2008 13:35

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

Alexey.mdr · Post by **Alexey.mdr** » Wed 11 Jun 2008 08:34

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.

NickG · Post by **NickG** » Thu 12 Jun 2008 16:12

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

Alexey.mdr · Post by **Alexey.mdr** » Fri 13 Jun 2008 08:01

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.

NickG · Post by **NickG** » Fri 13 Jun 2008 14:11

I am using separate threads and when one connection breaks both of them start to fail

Alexey.mdr · Post by **Alexey.mdr** » Tue 17 Jun 2008 08:49

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?

NickG · Post by **NickG** » Mon 23 Jun 2008 20:35

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

NickG · Post by **NickG** » Tue 24 Jun 2008 20:06

Any ideas?

anton.connect · Post by **anton.connect** » Wed 25 Jun 2008 10:04

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.

NickG · Post by **NickG** » Wed 25 Jun 2008 13:44

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)