MySQL connections Best Practices and Timeout issues

[jwilson58]

Background: I have a VFP Point of Sale system in a multi-location store system. Each store’s software runs independently from the others. We gather and re-distribute relevant information on a nightly basis. I have written a system to store the generated invoices and inventory changes in a cloud-server MySQL database in real time. Each location can retrieve invoice and inventory information for the other locations from the MySQL database on demand. This system allows quicker access to this information than the overnight process does.

This is my first integration using MySQL with VFP. Everything is working great but I have a couple of questions ….
- What are best practices for when to make the connection to the MySQL database from VFP… I currently open the connection at the start of the POS system and disconnect when the POS system is closed. So the connection is always available for the on-demand requests.
- This does cause a problem … It seems that the connection is lost about every 10 minutes when there is no activity. I handle this by testing the connection and re-connecting when I lose it. Is there a timeout setting that I can set either in the ODBC connection or on the MySQL server that would lengthen this timeout and therefore lessen the need for the test/reconnect process?
Thanks for your help!

 

[Mike Lewis]

What are best practices for when to make the connection to the MySQL database from VFP…

Opinions differ on this point as there are pros and cons to both approaches. I've always opened the connection the first time I need to use it, and then keep it open for the rest of the session. In general, that works for me, but not everyone will agree.

Is there a timeout setting that I can set either in the ODBC connection or on the MySQL server that would lengthen this timeout and therefore lessen the need for the test/reconnect process?

Yes. You can do this at the connection level. In the Connection Designer, there are four setting that are relevant (in the bottom-right of the dialogue).

The two that are most relevant are:

- Connection Timeout (the time (in seconds) before VFP returns a failed connection error; set it to 0 to specify an indefinite wait).

- Idle Timeout (the time (in minutes) after which the connection will be deactivated if there is no activity; again, set to to 0 to specify an inedefinite time, which is probably what you want).

You can also make these setting via SQLSETPROP()

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

I just remembered that I once wrote some generic code to handle issues such as timeouts and dropped connections. Jwilson, it might not be completely relevant to your situation, but you are welcome to have a look at it to see how I did it. See here.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[jwilson58]

Mike, thanks for your input. I have already looked at both the ConnectTimeOut and IdleTimeOut settings and they are already set to 0 by default, I double-checked using SQLGETPROP(). Yet I am still experiencing the issue.

John

 

[Steve Meyerson]

I've also had the disconnect problem on a client's machine. He uses wifi at home (actually a boat) and while traveling. Often with a weak signal he can access the internet but the MySQL database disconnects.

So I issue SQLDISCONNECT(0) after each database access. When access is needed I check if already connected (in case for some reason it, or I, didn't disconnect. Then call this method with predetermined property values for the required parameters:

LOCAL connected
WITH THISFORM
 TRY
  connected = SQLEXEC(.Fh,'--') > 0
 CATCH
  connected = .F.
 ENDTRY
 IF NOT m.connected
  .Fh = SQLSTRINGCONNECT(;
    + 'Driver='    + .SqlDriver   ;
    + ';Server='   + .SqlServer   ;
    + ';Database=' + .SqlDbPfx + ALLTRIM(.SqlDatabase) ;
    + ';UID='      + .SqlUserPfx + ALLTRIM(.SqlUser) ;
    + ';Password=' + .SqlPassword,.T.)
 ENDIF
 RETURN .Fh
ENDWITH

FWIW I realize this is a simple and brute force method, but it works for me so long as I can keep the returned data as small as possible so delays are not noticeable (except at startup).

Steve

 

[jwilson58]

Steve, that is basically what I am doing ... prior to any call to the sql server, I check the connection and reconnect if necessary. That is working fine. But, when it does disconnect because of a timeout, the connection still shows in the Client Connection list on the MySQL server. Using up a connection. The server drops it eventually according to its inactivity timeout, I think 8 hours. I just hate to leave something like this unfinished! It bugs me that I cannot figure out how to control this timeout behavior.

Thanks for your input,
John

 

[Steve Meyerson]

jw,

I don't use a Client Connection list on the server", mainly because I have only a single user now and connect to a maximum of two databases at once. I don't think they stay open when the program forces a disconnect because the file handle is always 1 or 2 when connected each time.

So, sorry I can't help you on the timeout issue. Hopefully, someone can chime in here.

Steve

 

[jwilson58]

Mike, thanks for that code. That is basically what I am already doing. I check for a good connection before each transaction involving the remote MySQL server and reconnect if necessary. But, what I really want to do is to negate the need for doing it in the first place! I'm trying to figure out what settings I can make to cause the connection to persist, not timeout. I will always check for a connection, but I was hoping that the connection will persist for longer than 10 minutes to lessen the number of reconnections and get rid of the time it takes to do the reconnection.

I did not think to check for the "ConnectBusy" condition and I will look into that. Thank-you!

John

 

[Steve Meyerson]

John,

Perhaps as a not-very-clever interim solution (i.e. workaround) you could use

 TRY
  connected = SQLEXEC(.Fh,'--') > 0
 CATCH
  connected = .F.
 ENDTRY

in a timer (e.g. every 5 minutse?) to check if the connection is still live and if not, reconnect.

Just a thought.

Steve

 

[jwilson58]

Steve, I am doing as you suggest currently. I have a process that runs in the idle time between invoices to update data from the main office, etc. I have added a call to the sql server during this process. It generally runs at least once every 10 minutes and this call keeps the connection alive. I'm still hoping there is a setting somewhere that will keep the connection persistent without these types of workarounds. In our environment, the calls to keep the connection alive are much more frequent than the actual calls to read/update data, so it seems a waste to do it this way.

Thanks for you input.
John