Losing connectiion to mySql db after small period of inactivity

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
spoowizAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jaime OlivaresConnect With a Mentor Software ArchitectCommented:
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
 
Jaime OlivaresSoftware ArchitectCommented:
>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
 
spoowizAuthor Commented:
tell me more, please, about the "dummy timer", thanks
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Jaime OlivaresSoftware ArchitectCommented:
Forgot to mention, Timer1 is a timer object you must to insert into your form.
0
 
spoowizAuthor Commented:
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
 
Jaime OlivaresSoftware ArchitectCommented:
I don't think so, timer function is made in parallel with your process, mySQL support multiple queries from the same source.
0
 
spoowizAuthor Commented:
thanks
0
 
spoowizAuthor Commented:
jaime - where can i find the timer object?
0
 
Jaime OlivaresSoftware ArchitectCommented:
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
 
spoowizAuthor Commented:
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
 
Jaime OlivaresSoftware ArchitectCommented:
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
 
spoowizAuthor Commented:
thanks again. i didn't realize it was a standard one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.