Foxpro and MySQL: Server has gone away 1

[Jay9988]

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

 

[mJindrova]

Hi,

base question is: what record count in a table?

mJindrova

 

[Jay9988]

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

 

[mJindrova]

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


mJindrova

 

[Jay9988]

Oh sorry, I misinterpret your question
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.

tks

 

[mJindrova]

Hmm,

Is it one row by primary key or not?
Do you know EXPLAIN command?

https://dev.mysql.com/doc/refman/8.0/en/using-explain.html

https://dev.mysql.com/doc/refman/8.0/en/explain.html

mJindrova

 

[Jay9988]

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

 

[mJindrova]

"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?



mJindrova

 

[Jay9988]

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

 

[mJindrova]

We excluded long query now.

Is MySQL in local network or not?

https://dev.mysql.com/doc/refman/8.0/en/gone-away.html

https://haydenjames.io/mysql-server-has-gone-away-error-solutions/

mJindrova

 

[Jay9988]

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 ?

thanks

 

[Chris Miller]

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.

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.



Chriss

 

[Jay9988]

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...

 

[Chris Miller]

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.

Chriss

 

[mJindrova]

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

mJindrova

 

[Sime Atanasov]

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,
Sime

 

[Jay9988]

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

 

[Chris Miller]

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.

Chriss

 

[Chris Miller]

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:

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:

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

You got this idea from it:

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):

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

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

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

 

[Jay9988]

Hi Chris,

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

Firstly, about my question about error log

Is the error log containing several UNNormal Shutdown normal?

Sorry my English not very good
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

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,