VFP 9 MySQL Persistent Connection

Hello all.  I've been working with an app for the past could of years that connects to a MySQL db and everything has been just great, up until the last couple of weeks that is.

All of a sudden my MySQL connections appear to be timing out.

If I'm on a record in my app, and walk away for five minutes, when I return and try to go to the next record, the MySQL connection is gone and nothing will bring it back except to exit the program and restart.

I don't know where the issue lies. My host doesn't seem to think it's with the server, but nothing in my app or on my PC has changed and it has worked well these past couple of years.

I know this isn't much of a question - I guess I'm really just thinking out loud (through the keyboard)...

Anyway, it is possible to create a persistent connection to MySQL in FP and if so, how?

I thought I'd give that a try if it's possible to see if it helps any.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
The only things about persistent connections that I can find relate to PHP drivers for MySQL.  What driver are you using to connect to MySQL?  Is it built-in to VFP or is the ODBC connector from MySQL?  

And things always change... or else it would still be working.  The question is what.  Has the usage of the database and/or server increased recently?  Is the network path simply busier?  And if you do your Windows updates, your PC has changed.
formadmirerAuthor Commented:
It's the MySQL ODBC driver. No windows updates on this old PC, and the server is used by only me so there's been no increase.

I do know that a cPanel update was performed on the server recently and I'm betting the trouble is tied to that.

I've been toying with SQLSETPROP and SQLSTRINGCONNECT adding the Sharable switch but nothing I've done on my side has made any difference. The connection still goes away.

I even turned on Connection Pooling for the ODBC driver in Windows - no difference.

Plus I tried adjusting the wait_timeout on the MySQL server. No affect there either.

At this point, just so I can get some work done, I'm thinking about opening and closing the connection on each and every query.

I know it would be slow, but with the way it's working right now, slow would be an improvement.
Dave BaldwinFixer of ProblemsCommented:
I'm thinking about opening and closing the connection on each and every query.
That's the way PHP always works, even with persistent connections.  Persistent connections are not exactly the same as a 'keep-alive' connection that is always on because it is checked every few seconds.  Here is the description from PHP:  http://php.net/manual/en/features.persistent-connections.php  Note that persistent connections do timeout, just not immediately.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
formadmirerAuthor Commented:
I wound up making the changes to the way the connections are handled, even though they have worked for years the way they were, and while it's still not perfect, it is better than before. Thank you for your help.
Dave BaldwinFixer of ProblemsCommented:
You're welcome.  Keep an eye out to see if you can find out what changed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.