Okay, now to what you can improve from the client side to at least cope with temporary network faults or disconnection of clients done by the MySQL server when it deems this is okay as the client is not using it for too long.
I said:
myself said:
One strategy is to have a timer that does a very simple query every 30 minutes, like SELECT NOW() FROM DUAL;
That always resets the connection timout.
And I said:
myself said:
The other thing to do when you get this error is connect and redo the query.
You got this idea from it:
Jimmy said:
I have put a dummy query just to check the connection before I ran the real SQLEXEC
It's not a wrong idea. But it does a lot of unnecessary queries, because you do it before every query.
No, I mean it exactly as I posted it, with a timer you ensure that the connection doesn't get judged idle by the MySQL server. As you know and set the wait_timeout, you know how often you need to do something - anything - so this timeout doesn't happen. It's not necessary to set the timeout so high, I even wonder if such a timeout value is accepted. The default 28800 seconds mean you only need to do such a "stay alive" query every 8 hours, that's what 28800 seconds are. So actually you could even ignore this aspect, unless your clients will be idle for the whole work shift, which usually is 8-10 hours with lunch break, for example. The MySQL documentation talks of a function mysql_ping() the MySQL ODBC drivers provide, no idea how you would cause that, but SELECT NOW() FROM DUAL; is simple enough and you could even log the dateime values you get back to perhaps see if this fails when that was last working.
But the main deal is using a good architecture about all your queries by using a class that'S responsible for all of them, so you also have this proccess of tr/reconnect/retry in one place for all your application.
This would be a simple class to use, found in forum search about OOP three tier architecture - thread184-39177
I quote the ful answer from foxdev (Robert Bradley):
foxdev(Robert Bradley) said:
The SQL objects I've created and/or used in the past have properties such as:
cSQLCommand
nHandle
cServerType
cErrorMessage
nError
and methods:
Connect
Execute
To get fancy and make a very flexible one, you'd set the cServerType to a value that dictates what special handling you might incorporate; but this is purely optional, and only if you want to support multiple data systems in one object.
Here's an example of how it would work:
oSQL = CreateObject("SQL")
* -- UID/PW could be properties instead of parameters
if not oSQL.Connect("MyUserID", "MyPassword")
return .F.
endif
oSQL.cSQLCommand = "select * from MyTable"
oSQL.Execute()
* -- Execute method sets nError and cErrorMessage
if oSQL.nError <> 0
MessageBox(oSQL.cErrorMessage)
endif
Robert Bradley
It's not fully fledged OOP, but has the essential parts you need to always be able to react to an error like "server has gone away" with the same pattern:
1) There is a central class definition, you don't program single SQLSTRINGCONNECTS scattered thourghout your code, is beceome class instance initialization to make a connection and store the connection handle.
2) Every query you execeute you execute with the Execute method of this class, so everything around that, including error detection and reaction also is in one place, you program it once for all your sql.
I would change some details, for example make the sql a parameter of the execute function, just like it is in SQLExec, but that's also a matter of taste and style.
And now let's incorporate what I said you could do:
3) Base this class on the Timer and set interval to 30000 (that's 30 seconds). Within the timer event you then do something like
Code:
This.Execute('SELECT NOW() as tNow FROM DUAL;','crsDummy')
If This.nError = <some error number about connection problems>
This.Connect("MyUserID", "MyPassword")
* perhaps retry This.Execute('SELECT NOW() as tNow FROM DUAL;','crsDummy') to see if the reconnect suceeded, perhaps have that in the Connect method
Else
* perhaps log crsDummy.tNow into a log file
Use in crsDummy
Endif
And in the Execute method, you could have something like
Code:
Lparameters tcSQL, tcResultcursorname
Local lnRetries
lnRetries = 2
Do While lnRetries>0
If SQLExec(This.nHandle,tcSQL,tcResultcursorname)<0
If AERROR(laError)>0
This.nError = laError[1,1]
This.cError = laError[1,2]
*...
Endif
If This.nError = <some error number about connection problems>
This.Connect(...)
Endif
lnRetries = lnRetries - 1
Else
lnRetries = 0
Endif
Enddo
And yes, it is normal to program in ways you expect an external service to fail and to react to that and ideally catch the failed query.
And if you now think that's too much to change inn every place you do SQLExec in your current code, well just notice that all you actually need to do instead of SQLEXEC(handle,sql,cursorname) is oSQL.Execute(sql,cursorname), so that's not very much of a change, but since this now isn't just the VFP sqlexec function but the execute method of a class, it also does everything necessary aside from the core SQLEXEC to catch error conditions. The only thing necessary once in application startup is creating that oSQL object, maybe public, as a goSQL sql handler or as goApp.oSQL or _screen.oSQL or whatever suits you.
It's really a simple and normal strategy of programming things. And it's even not hard to incorporate this into already existing code. This is OOP.
Edit: There are some further things to do, quite straight forward, the destroy event of that class would disconnect, for example. the Execute method I sketched could now return the value 1 for success as usually SQLExec does or -1 for failure, but you already did the error handling in the SQL class. So all code you already have doesn't have to check for AERROR, this is already done in the Execute method code, so you could simple return .T. or .F., the query success can also be seen by the presence of the result cursor, though.
It's all a matter of taste, but one thing is solved by using a class or object: You don't need to program the same thing over and over in all places you do SQLEXEC.
Chriss
![[smile]](/data/assets/smilies/smile.gif "[smile] [smile]")
