Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Foxpro and MySQL: Server has gone away

Foxpro and MySQL: Server has gone away

Foxpro and MySQL: Server has gone away

Hi, I am Jay
I am developing a desktop app in VFP 9 SP2, and DBMS MySQL 5.0.24, connect with Mysql ODBC 5.1.11
The DB Engine is InnoDB

When the app is running and doing some query, sometimes came out the error "MYSQL Server has gone away" unexpectedly. Even with simple query like "select * from ...." from just one table.
If the user logout from app, and login again (the app makes a connection again to Mysql server), that query is running well. But sometimes it happened again in other query code. I use sqlexec() function to do query to the mysql DB

I have tried:
1.Modify wait_time_out = 31536000 and max_allowed_packet=256M in MySQL configuration(my.ini)
but the error still happening
2. running mysqlcheck to detect any corrupt table -> all table are ok.

I think the connection is somehow disconnected by the server or maybe by the ODBC on client PC (but I don't know the real cause)
I would appreciate if someone could help me..
Many thanks

RE: Foxpro and MySQL: Server has gone away


base question is: what record count in a table?


RE: Foxpro and MySQL: Server has gone away

Hi mJindrova,
Thank you for your response

I just counted it, the table with largest row has 1.120.111 rows (just 1 tables),
the other tables (3 tables has around 100.000 rows), and other below 100.000
all table is 240 tables

RE: Foxpro and MySQL: Server has gone away

Hmm, what is reason read all records from a table?


RE: Foxpro and MySQL: Server has gone away

Oh sorry, I misinterpret your question smile
I think you want to know how large is the database or table
The error: MySQL Server gone away is unexpectedly, sometimes the error when I run the SQLEXEC from one table with 1 row (the read some reference)

The connection to mysqlserver just lost I think.


RE: Foxpro and MySQL: Server has gone away

HI MJindrova,

Yes, sometimes the error when running a simple query to just 1 table with 1 row (and yes, no primary key).
But sometimes the error when running a query to a table with several rows with a primary key on it.

Thank you for the source about EXPLAIN. I have read it slightly, it will give more detailed information about the query I ran right ? so I can know what is causing "server gone away" error ?. I will try it...

Do you think some of the queries I made causied the error? The symptoms are sometimes it generates an error, sometimes don't (quite random)

Many thanks

RE: Foxpro and MySQL: Server has gone away

"MYSQL Server has gone away" tell you, your query takes a long time.
I don't know how sets "your" MySQL server.
But first question is: Can MySQL use index for filtering data?


RE: Foxpro and MySQL: Server has gone away

Hi mJindrova

Yes, I agree with you, if the error happened when the query is complex or abundant rows are going to be fetched.
But the error sometimes happens in a very simple query like: "select * from address" (in which the table only 1 row). I've already trapped the error with AERROR(merr) after the SQLEXEC() statement

I think, it's impossible if such a query will take such a long time (unless as I've read, there's is a corrupted table (So, I've done mysqlcheck and all tables are ok).

I've already made the setting "max_allowed_packet=256MB" and "wait_timeout=31536000".
Is there maybe any setting of Mysql concerning this error?

About your question about INdex: yes MySQL could use index. and I have primary or secondary index as needed for multiple join tables

Many thanks

RE: Foxpro and MySQL: Server has gone away

Hi mJindrova,
Yes, Mysql is in the Local Area Network
Yes, I've also read that source, so I tried modifying the wait_timeout extremely large (31536000)
I've checked all the tables and all are OK.

Anything I must check ?


RE: Foxpro and MySQL: Server has gone away

Quote (haydenjames.io)

you need to check ALL related error logs with the same timestamp to determine whether another issue may be to blame
Have you done that?

There are so many more hints on what can be done aside of setting wait_timeout larger.

Quote (haydenjames.io)

By default, MySQL will close connections after eight hours (28800 seconds) if nothing happens
If this would be the case, that means the timeout was sometimes before you ran the query that returns this error.
You're not informed about a disconnection, you're just informed about it when you try to use it again. And then it doesn't matter what you do, it doesn't cause this error, the disconnect was already done and you just get notice of that now.

I doubt that, because every query resets the timeout again. This connection would need to have been idle very long, for that to be the reason. So the reason should be something else you find in other logs. Not necessarily at about the same timestamp, as the disconnect could have happend anywhere between your last query that worked and this one.

One strategy is to have a timer that does a very simple querry every 30 minutes, like SELECT NOW() FROM DUAL;
That always resets the connection timout.

The other thing to do when you get this error is connect and redo the query. If you use a class for MySQL queries that would be simple to add to the class design,, handling that specific error by connecting and redoing the last query, that would still be available as parameter of a method doing the SQLExec. At least that would work with a class implementing SQL passthrough. I don't know what exactly you use, remote views are not easy to wrap into an OOP strategy, cursor adapters are very obviously candidates for OOP.


RE: Foxpro and MySQL: Server has gone away

Hi Chris,
Thank you for your valuable suggestion.
I agree with you that is likely something is disconnect the client and the server before I ran the query.

I've also check the mysql error log and found interesting like this (actually there are several date that mysql starting crash recovery, about 5 times from year 2021):

210324 4:59:49 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
210324 4:59:53 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 102314462.
InnoDB: Doing recovery: scanned up to log sequence number 0 102314462
210324 4:59:53 InnoDB: Started; log sequence number 0 102314462
210324 4:59:53 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.24a-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL)

The server is in my client companies, according to their statement the server is equipped with UPS to prevent suddenly power loss.
But, because I am a newbie in Mysql, what is actually happened that cause "Database was not shutdown normally"
Is this the cause of error "Server Gone Away" randomly ? and How to prevent it in the future ?

What should I do to repair this kind of crash ? Because I check the table with mysqlcheck, the table are all OK.

Actually, I am developing the desktop app for recording business transaction for my client (sales, purchasing, cash payment, etc)
I am using VFP to develop the app, just with ordinary statement of SQLEXEC() to SELECT from mysql table, put in into cursor, display it into a FORM, after the user interacation, then save to the mysqldb with insert/update...

Many thanks, hope you can give me some hints again to overcome this error...

RE: Foxpro and MySQL: Server has gone away

Well, first of all SQL Server crashes are not only happening with power failures. If someone resets the MySQL server, there would be a normal shutdown of connections and then databases. So what else happened, if not a power failure?

Server hardware maintenance, for example. In that case, you would shut down the whole server and when doing so also the MySQL server. And if you don't shut the software/processes/services down before shutting down the hardware, that can lead to unfinished shutdowns. Then that's a maintenance error.

I would look into the windows event log about that.

But in the end, it doesn't really matter what's the cause, you have to expect it to happen, simply. No system runs forever and always. At best you plan the maintenance shutdowns and do them correctly.

You can't force anything to run forever without any shutdowns, that's thinking along the wrong lines.

One thing you can improve is that you keep the connection alive by doing something, anything, just like querying the server time with the query I did. That takes care of the reason of an idle connection that disconnects you from the server side.

But even with that prevention, you can't force the connection handle to be valid forever. So you do a reconnect on the fly, as I already said. That means, if an SQLEXEC brings up that your server is gone away, then at least you try whether it still is gone away right now by reconnecting. That's all you can do from the client side.


RE: Foxpro and MySQL: Server has gone away

Problem can be on HW: (desktop) network card - switch/router - (server) network card
or is too heavy traffic.


RE: Foxpro and MySQL: Server has gone away

Hi Jay.
I've done quite a few MySQL connections and SELECT commands from VFP and most of the times it works fine.
The instances I've had something like losing the connection to the MySQL server (table) while doing a query, even a simple one, is when there are many records pulled. And, I've noticed something more important then: the record count of the while table might not be high (let's say < 10K of them) but with many, many fields. Increasing the default MySQL server settings did the trick for me.
If you haven't done that, one thing I'd try is running the same query directly from Workbench and/or Heidi - I got the same going-forever-and-time-out result there too, i.e. MySQL server going away. Which lead me to believe it wasn't the connection speed, bandwidth etc. It'll happen with a local WAMP MySQL server connection too. Just my 2 cents on this.
Good luck,

RE: Foxpro and MySQL: Server has gone away

Hi all

@Chris, thank you again for your explanation
I just want to make sure: Is the error log containing several UNNormal Shutdown normal? and we can leave it as it is and no need to do anything?

And your last statement to put a query to check the connection first, I agree.
Because until now I don't know the real cause of the error "server gone away", I have put a dummy query just to check the connection before I ran the real SQLEXEC, if the connection fails, then I put SQLSTRINGCONNECT to open the connection again. But, is it the common (or a must) approach when developing VFP app with MySQL?

@mJindrova: thanks for your advice.
@Sime: would you share what kind of MySQL setting you have increased when you experienced a similar situation?

Many thanks all

RE: Foxpro and MySQL: Server has gone away

Quote (Jay9988)

Is the error log containing several UNNormal Shutdown normal?
I don't understand what you want to know.

As you have already seen when MySQL restarts it detects whether it has been shutdown normally, i.e. the service mysqld has been shut down and wasn't just interrupted because of the hardware shutting down. It's just like with any other and visible software If you shut down a computer while Microsoft Office Word runs, Word is shut down by the computer shutdown. Well, word will warn and delay the shutdown, if there is an unsaved document, but if you don't react in a timeout Word will be shutdown anyway. And then, if you start Word after the computer is restarted, it detects that there was a shutdown and offers you to reopen the document you edited. So is there a better way of shutting down a server? Of course. You first shut down services like MySQL, then shutdown the computer.

The reason you don't need to shut down an SQL Server like MySQL but not need to care about all the many other system services is, that a MySQL Server is not just able to shut down on the system shutdown signal when 10 clients are connected and 5 queries from them are currently run. You can imagine such a service that's there to respind to client requests is not as simple to shut down. I'm sure MySQL does detect the system shutdown, every process - also services - gets a signal, a system event message of the shutdown, and it does the best to at least get into a state where no database structure is harmed. But ideally MySQL is first shut down before the system s shut down.

So, just like you close applications before you shutdown a workstation, you close applications and some of the services like MySQL that service clients, before you shutdown the server. That's that side of the problem.

The other side is, whether this actually was a manual server shutdown. You said there was no usage of the UPS, so there was no power failure that cause this. Well, was there a server hardware shutdown. I told you to look into the Windows event log. A Server OS will be able to tell you whether a server shutdown was planned or unplanned, as the user shutting down a server will need to specify the reason for the shutdown. See here, for reference:

And then you find this in the system event log:

Or are you not actually running a Windows server and just use one client as the server with MySQL installed on it? Well, that's not really professional for a business application, is it? You'll still find shutdowns in the Windows event log with the Event Viewer of Windows and maybe find out why the server was shut down.

It could also be overheating or, as Martina already mentioned, other HW problems that made MySQL shut down itself. I doubt it, as you said it detected an unexpected shutdown, if it shut down itself for any reason it itself detected, that's surely not planned, but not unexpected. Unexpected means the process was killed by someone or something that the service didn't detected itself and didn't see coming. Even a server shutdown isn't like that, it would be shutting down the server by pressing the power button for 3 seconds, for example, the emergenc shutdown, if the system was in a statenothing reacted anymore.

And if really not the server itself but only MySQL shut down unexpected, that is the mysqld service wasn't asked to shutdown but the mysqld.exe process still was killed by anything, then, well, you may not find anything in any log about the reason, but it would point out someone killed the mysqld task in task manager, for example, as one way of unprofessionally shutting down a service.

It all boils down to this: If you want to do planned shutdowns of a server, you have to know what you have to do. If the MySQL admin wanted to do something then there are better ways than killing the mysql service process to get exclusive access, there are ways to cut connections, maybe also do this at a time nobody is connected anyway. If this wasn't a manual shutdown of the server, but a kill of some processes, then you should at least find that mentioned in Windows event log. I'm not sure. But in the end you might not find the reason anywhere.

I'll respond to the perhaps more important aspect, whether to change anything or not, in a separate answer, later. Just a hint: I already told you to do the minimum you can to prevent idle connections to be closed. And you didn't get it fully. The short answer is yes, of course.

But this answer should give you some ideas of what went wrong in server maintenance and how you should do that in better ways. I donÄt assume MySQL simply runs on one of your clients and it was simply the employee working at that client that shut it down as he was going, or anything stupid like that. There are valid reasons to shut a server down for hardware maintenance, even if a server is located in a server room with laminar air flow installed, you will do monthly or quarterly cleaning of the hardware. Dust is an enemy even in that professional conditions. And there are many other reasons for maintenance, hardware failures or upgrades, of course. You should know that, but maybe you even won't know that in detail.


RE: Foxpro and MySQL: Server has gone away

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:

Quote (myself)

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:

Quote (myself)

The other thing to do when you get this error is connect and redo the query.

You got this idea from it:

Quote (Jimmy)

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: Howto Programming Three-tiered Object Oriented?

I quote the ful answer from foxdev (Robert Bradley):

Quote (foxdev(Robert Bradley))

The SQL objects I've created and/or used in the past have properties such as:


and methods:


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.
oSQL.cSQLCommand = "select * from MyTable"
* -- Execute method sets nError and cErrorMessage
if oSQL.nError <> 0

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


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
   * perhaps log crsDummy.tNow into a log file
   Use in crsDummy

And in the Execute method, you could have something like


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]
      If This.nError = <some error number about connection problems>
      lnRetries = lnRetries - 1
      lnRetries = 0

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.


RE: Foxpro and MySQL: Server has gone away

Hi Chris,

Thank you again for your extensive explanation. I will read it carefully.

Firstly, about my question about error log

Quote (Me)

Is the error log containing several UNNormal Shutdown normal?
Sorry my English not very goodsmile
I want to know whether the error log with several case of "not normally shutdown" possibly could also cause "server gone away" error?

I believe every afternoon when the companies closed, they shutdown the Windows. The companies use Windows 10 for the server.
Yes, I agree with your opinion to use Windows Server smile

About your suggestion to looking into Event Viewer, I will check on next Monday. And thanks a lot about your coding examples using classes (I have to learn it more)..
Many thanks,

RE: Foxpro and MySQL: Server has gone away

Okay, about that last question:

"Did not normally shutdown" is pointing out the MySQL service ended not by a normal shutdown that could have been done gracefully, like when you use the list of services to stop the service or use NET STOP MYSQLD.

It's even simpler. Any shutdown, normal or not, will mean the server is gone away for the client. So all of these can mean the server has gone away error for the clients. That error does not tell you whether the server has gone away because it was shutdown planned or by failures, and whether it's gone because the server itself was shutdown or just the mysqld service was stopped.

I thought that was already clear. You looked into the MySQL server log and found this, I think, related in time with the "server has gone away" message. So that matches, but it still doesn't tell you why the service was abnormally ended. And the Windows event log might tell you, no matter whether it's a Server or Windows 10. The event log also would log some failures. So you should have by now looked into the windows event log already to finally find out, perhaps, why the MySQL service stopped. That's what you wanted to know all the time, didn't you?


RE: Foxpro and MySQL: Server has gone away

HI Chris,

I've check the Event Viewer on Win10 of the PC Server, and you're right.
I have found several message warning and error, like this:

23 Feb, on 07:45
The previous system shutdown at 8:54:21 PM on ‎2/‎22/‎2023 was unexpected.
--> this explained the message "not normally shutdown on Mysql server" -> so, thank you for your suggestion

But on the other dates, I found several warnings and errors, which is:
- The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID

Name resolution for the name t-ring-fallbacks2.msedge.net timed out after none of the configured DNS servers responded.

The luafv service failed to start due to the following error:
This driver has been blocked from loading

Could those warnings/errors cause the MySQL server gone away randomly?

many thanks again

RE: Foxpro and MySQL: Server has gone away


I would concentrate on this one :

>23 Feb, on 07:45
>The previous system shutdown at 8:54:21 PM on ‎2/‎22/‎2023 was unexpected.
>--> this explained the message "not normally shutdown on Mysql server" -> so, thank you for your suggestion

As Chris mentioned have a look on the text of the error. There may be error codes like 0x800... .
To get a clue (exspecially if the same code appears more then once) why it was shutdows, you may google it. Or post the text or a screenshot.


RE: Foxpro and MySQL: Server has gone away

Quote (Jay9998)

The previous system shutdown at 8:54:21 PM on ‎2/‎22/‎2023 was unexpected.
That surely also caused a MySQL shutdown.

I can't tell you whether the other errors are MySQL related. Errors are associated with an EXE, so what is the EXE? They are also logged with date/time, was it at times MySQL server wasn't available? You should know that and look into it.

Many things happen in parallel, also on a Win10 client, so not all errors have to do with all processes, of course. Or even block any other process.


RE: Foxpro and MySQL: Server has gone away

Hi Tom, Chris

Thank you your input..

RE: Foxpro and MySQL: Server has gone away


Apologies for the late reply - hectic few days here.

If you still find this relevant, I retrieved my notes (actually screenshots) from September '21 on a similar problem I had.

I believe playing with the buffer size and stuff like that helped me execute commands (delete in my case) while having indexes that weren't helpful in those operations. And a Substr command that's very expensive. Deleting around 1 million records or so, I'd say. Probably same for any other queries. And this was right from Workbench. It will just quit. I don't remember if the MySQL server was still functioning, I was just starting it again as a way to get to the Workbench. Directly on the Windows 2012 server.

The last snapshot would be closer related to your issue too: I had a command (INSERT this time) of record by record of a small file, around 60k records. But it was a not-normalized DBF going into MySQL. Circa 250 fields total. That's when I got the consistent "Server gone away" error, after timing out. My workaround was to split the fields (in the AFIELDS array) and upload the value in several shots/commands.


RE: Foxpro and MySQL: Server has gone away

Hi Sime
Thank you for sharing your experience

Do you mean, I should enlarge the innodb_buffer_pool_size ?


RE: Foxpro and MySQL: Server has gone away

Looking at my notes when I encountered those issues, it was splitting the query in two, which helped me with the "server has gone away" error.
Extending the buffer fixed a long operation (like deleting 500k records in one shot) with an inefficient index.
Two issues from about the same time, same Schema. It won't hurt to extend the max_allowed_packet to the maximum, as an easier try - you can always reset it to the default.

RE: Foxpro and MySQL: Server has gone away

Hi Sime,
Many thanks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close