MySQL Server Has Gone Away

Several of my users are getting this error message and it’s quite random. Generally happens at the end of the day when they are exporting their daily data entry to the server.

Anyone have any info on how to deal with this either in the XOJO app or in MySQL itself?

I’ve tried a few suggestions I found online in the MySQL end of it but those haven’t worked so far.

Thanks

Are they maintaining a connection to the server throughout the day? By default, the server will disconnect you after 8 hours. You can try changing that setting.

hi rick, what error message did you get??

Simply call a helper method before doing queries which pings mysql and reconnects if server is gone away.

There are two “server” connections … one connection to the main file server, the other to their local MySQL instance.

They query data stored on the main file server all day long.

They record information after each query and that is stored on the local MySQL instance.

At the end of their shift, the local data is transferred to the main file server. The error occurs at that time. I’m not sure which of the two server connections has “gone away”.

I do have code in the app that checks for a loss of server connection and produces a message but that specific message I created does not appear. Having said that, I don’t believe I’ve coded it to check the local server, just the file server.

I’ll try to add some code to that effect and see what happens.

Richard Duke … the error is simply “MySQL Server has gone away” … nothing else.

Thanks Tim, I think this might be the answer. Although they are connected to the server throughout the day, it’s a different database on the server. The database used at the end of the day is the one that is probably timing out. Your comment tweaked my brain to that.

I have exactly the same problem, with these 8 hours disconnection.

Can I put a timer into the “open” event of a WebApp, to connect every 7 hours again?
Or is the open event not the right place for a timer?

I updated my method of connecting to my remote database the other day. I pass what I hope is a live database to it at the start of each method that needs database access.

First I check if the internet is connected, as this stops long database timeouts if it is down. Next I check if it’s still alive and connected — even if it is, I run a simple dummy command. If not alive, I try to connect to it again. If all this fails, I try to rebuild the database connection from scratch.

I keep one database per session and assign a method-local DB and recordSet each time I need to. This keeps the database fresh whenever I need it.

I use SQLDatabaseMBS, but the ideas are transferrable to other databases.

[code]Public Function getMyDB(currentDB As SQLDatabaseMBS) as SQLDatabaseMBS
Dim db As SQLDatabaseMBS
Dim rs As RecordSet

if not commonNetwork.isInternetAvailableWAD then Return nil 'don’t freeze on PostgreSQL timeout!

'check if we only need to refresh an existing DB!
if currentDB <> nil then
if currentDB.isConnected and currentDB.isAlive then 'check if still connected
rs = currentDB.SQLSelectMT(“SELECT 1”) 'dummy command to keep alive!
Return currentDB
end if

'if lost connection, see if we can reconnect!
if currentDB.ConnectMT then
  if currentDB.isAlive and currentDB.isConnected then
    Return currentDB
  end if
end if

end if

db = … 'whatever method you use to connect

Return db

End Function
[/code]