I’m looking for advice on a best practice method to check whether a web app is still connected to a MySQL server. I’ve been able to crash the 64 bit MySQL Community server DLL and I suspect it is because I was using DB.Connect for every query. It is tough to know when this app’s various threads are doing something so closing and reconnecting could kill a DB transaction in progress elsewhere. I was thinking that reconnecting each time would solve the “MySQL Has Gone Away” issue I’ve seen occasionally with Desktop apps. Can I just check to see if the database object is Nil? Do I need to try a query and interpret the result to see if the MySQL connection is still alive? What result should I look for?
I really wish there was a method built in to MySQL Community Server Plugin that would test the connection and return true or false.
I think you could run a query on the MySQL’s INFORMATION_SCHEMA, if you get something you’re still connected. INFORMATION_SCHEMA is kept in memory and is very fast.
Tomas Jakobs, I am trying to avoid trying to connect again as a test. I should already be connected and I want an easy reliable way to verify I still am.
Shant Khatcherian, Since my app should be already connected to a different database, doesn’t testing the information schema requires connecting to a different DB. Wouldn’t it better to just create a special table in the DB I’m using called keepalive with a single field and a single record and query that? That would yield a recordset or an error. That still seems kind of kludge.
Is there no other way to tell that the MySQL database object is still connected without doing a query?
There is not. What I do is create a database subclass (a wrapper actually, that way you can change out the db backend without changing your code) that executes the query and if it gets a not connected error, attempts to connect and execute the query again.
Tim Hare, Thanks for verifying that. I guess I’ll have to do something like that unless you’re willing to share your subclass.
I suppose a feature request for the MySQL Community Server plugin for [database].Connected As Boolean is in order since it appears to be a common problem.
The problem is that you can’t know that the connection is gone until you try to use it. A property like you suggest would still have to ping the server.
Unfortunately, I am not at liberty to share my subclass.
You might as well check the availability of the connection with your actual query. Otherwise you’re going to the database twice when most of the time, you only need to do it once.
I would write a small Method with a Boolean result that has the database passed as a parameter. Inside the method would just execute something like ‘SHOW TABLES’ then return True/False on whether the results were obtainable. Optionally, the method could perform the .Connect if there was no response if this returns True then the Method can return True.
In one of my apps, I have a wrapper for SQLExecute which checks errors.
If error is connection lost, it connects again and performs query on new connection.
This way people don’t really notice the reconnect.
I know it’s not best practice to the purist, but I add a timer to the subclassed socket that periodically runs a show databases query. Info_schema would be better though - less load on the server.
I did that too for my in-house tool. It’s not running all the time so it’s okay.
I opted for SELECT now(); as it just returns a timestamp, but keeps the connection alive.
[quote=243683:@Christian Schmitz]In one of my apps, I have a wrapper for SQLExecute which checks errors.
If error is connection lost, it connects again and performs query on new connection.
This way people don’t really notice the reconnect.[/quote]
I do something more purist but it’s working very well:
Dim rs As RecordSet = theDatabase.SQLSelect(“SELECT 1 FROM DUAL”)
If rs = Nil Then
Return WAG3_DB.Connect
End If
Return True
End Function[/code]
Before i run a querrie is just call the ConnectTotheDatabase() Method which is global available in a Module. The “SELECT 1 FROM DUAL” is supported by most (all?) SQL Databases and executes very fast. If i get a NIL Result, i do a Connect and return the result. If i get a non-NIL result, i return True.
I’d just do like christian said
Have a subclass that checks for certain errors & reconnects if it needs to
And it has a timeout or limit on retries so it wont spin & try forever
[quote=243683:@Christian Schmitz]In one of my apps, I have a wrapper for SQLExecute which checks errors.
If error is connection lost, it connects again and performs query on new connection.
This way people don’t really notice the reconnect.[/quote]
Do it this way. Anything else is needless overhead. And seriously, I can actually reboot the server and the users don’t notice.
