Solved

Losing connectiion to mySql db after small period of inactivity

Posted on 2004-10-22
184 Views
Last Modified: 2010-05-02
Hi all,
I'm losing connection to a mySql database on the WAN after a period of inactivity (only a few minutes).
I get a "Lost connection" error when I do a rst.update (funny thing, if I do it 2 more times, it seems to re-connect by itself and rst.update works, which is good because all the recordset info is intact when it re-connects by itself).
The question is: is there a way or command to make it re-connect without having to to rst.update 2 more times?
Better yet, how can I make it so that it won't loose connection after any period of inactivity?
(btw, in previous solutions cn.connectiontimeout was recommended. this is not correct).
thanks
phil
0
Question by:spoowiz
    12 Comments
     
    LVL 55

    Expert Comment

    by:Jaime Olivares
    >Better yet, how can I make it so that it won't loose connection after any period of inactivity?
    A connection timeout parameter could be configured in the mySQL server.
    Or you can make a "keep alive" function by making dummy queries every minute using a timer.
    0
     

    Author Comment

    by:spoowiz
    tell me more, please, about the "dummy timer", thanks
    0
     
    LVL 55

    Accepted Solution

    by:
    You have to create a timer somewhere in your code.

    Here is some demo code:

    Dim StartTime As Date

    Private Sub KeepAliveBtn_Click()
        Timer1.Interval = 60000     ' produces an event every minute
        Timer1.Enabled = True
        StartTime = Now
        Me.Caption = StartTime
    End Sub

    Private Sub Timer1_Timer()
         ' Put an dummy sql query here. Something like "SELECT 1"
    End Sub
    0
     
    LVL 55

    Expert Comment

    by:Jaime Olivares
    Forgot to mention, Timer1 is a timer object you must to insert into your form.
    0
     

    Author Comment

    by:spoowiz
    Looks promising. Learning something new...
    What would happen when the interval time arrives and the program is doing something? Does it have potential to interrup the going process and cause a problem?
    0
     
    LVL 55

    Expert Comment

    by:Jaime Olivares
    I don't think so, timer function is made in parallel with your process, mySQL support multiple queries from the same source.
    0
     

    Author Comment

    by:spoowiz
    thanks
    0
     

    Author Comment

    by:spoowiz
    jaime - where can i find the timer object?
    0
     
    LVL 55

    Expert Comment

    by:Jaime Olivares
    Timer is a standard ActiveX control which icon is a little clock. You have to drag it to your form.
    Have a look to this:
    http://sunsite.iisc.ernet.in/virlib/html/platinum/f50-19.gif
    0
     

    Author Comment

    by:spoowiz
    I'm using VB6. I don't see it under "Components". (Your screen says "additional controls" at top, mine doesn't). How do I get to your screen?
    0
     
    LVL 55

    Expert Comment

    by:Jaime Olivares
    Sorry, I have grabbed from the Internet because I haven't installed VB in my office machine.

    I have gone to another computer to take a screenshot. Have a look to:
    http://www.micrologicaperu.com/vbtimer.html

    As you can see, Timer is a standard (fundamental) control you can find in your control toolbar in VB, if not, just have to search and installl in components dialog.
    Select it and "draw" it in your form, then double click to generate a Timer event, and fill it with a dummy query.
    0
     

    Author Comment

    by:spoowiz
    thanks again. i didn't realize it was a standard one.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Cisco Complete Network Certification Training

    If you’re an IT engineer or technician, it's time you take your career to the next level. This elite training bundle is brimming with all of the information you need to learn to sit for Cisco CNNA, CCNP, and CCENT certification exams.

    Suggested Solutions

    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now