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

 

[Chris Miller]

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?

Chriss

 

[Jay9988]

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
{2593F8B9-4EAF-457C-B68A-50F6B8EA6B54}

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

 

[tomk3]

Hello,

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.

Regards
tom

 

[Chris Miller]

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.


Chriss

 

[Jay9988]

Hi Tom, Chris

Thank you your input..

 

[Sime Atanasov]

Jay,

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.

Sime

 

[Jay9988]

Hi Sime
Thank you for sharing your experience

Do you mean, I should enlarge the innodb_buffer_pool_size ?

Thx

 

[Sime Atanasov]

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.

 

[Jay9988]

Hi Sime,
Many thanks