VFP with MYSQL - mySQL server gone away

[Premal Vala]

Dear Experts

I am working with mysql database with VFPA version and facing a strange issues.

To give you details.

I have my database (DBC) file and I have created a connection within database named "MyConn" to mysql database with connection string (please note I am not using ODBC but using direct connection string).

Then I have created different remote views for different tables within database using same connection.

I have set all properties to add, update, delete records on remote views.

I am using form to add edit delete the record. Within form init event I have open the database and used the table with Use myDB!Mytable

Everything is working fine as expected any record added, edited or deleted are reflected to mysql server.

The issue I am facing is that if I take more than few seconds to enter all the fields and if I try to save the record. It says "Mysql server has gone away".

It works fine if I finish adding data within few seconds.

I have checked DB connection timeout and it is set to 0 - infinite.

Tried all possible settings, but after few seconds, mySQL server connection keep going away.

Any help on this topic is highly appreciated.

Thanks in Advance,
PREMAL

 

[Chris Miller]

Your description isn't enough to tell why your connection is closed. I would look into server settings, connections could be closed from the server.

By the way using a connection string is telling you use ODBC, you might use a connection databse object in a DBC based on a cennection string, not using a DSN, not using SQLConnect, not using SQLStringConnect, but what do you think a DBC connection object is based on? Using an ODBC driver.

You stress out that you don't use ODBC, do you think ODBC wouldn't work at all or why are you even pointing it out, explicitly?

The point about a connection you make, is that it can become disconnected from both VFP and MySQL server, not only from the side that establishes it. And that's not only a MySQL thing, a server can be configured to close idle connections after a timeout, automatically. It's not only a matter of the VFP side of things to have no timeout from the VFP side doesn't mean MySQL also can't timeout the connection, that can be a server setting applied to any connection coming in.

 

[Premal Vala]

Hi Chris,

Thank you for your response.

My bad on ODBC, apologies, what I mean was not using DSN as I don't won't to create DSN on user PCs hence not using it and using connection string directly. (Sorry, as new to this ODBC and MYSQL world)

I also agree to your point of connection being disconnected from MYSQL side, but my issues is that connection is closing very fast, and was wondering if Database connection property Connection Timeout plays any role here?

Thanks,
PREMAL

 

[Chris Miller]

Well, what are you using in your connection string?

According to MySQL documentation I don't see a timeout documented as being infinite when set to 0, you better set them to a high value.
On the other hand defaults for both global and session timeouts of connections are 8 hours.

 

[Premal Vala]

Hi Chris,

I am referring to this property within Database Connection Designer and I tried all values here.



Secondly I am referring to this help of VFP within DBGETPROPERTY



Does that help to understand my problem.

Sorry if I am not putting things correctly but as said this is totally new world for me but all your help is great favor.

 

[Chris Miller]

TZhe idle time setting is important here. 5 min means after 5 mninutes of idle (not using the connection) the server disconnects after 5 minutes of "not hearing from you".

When you work with the views, you're idle from MySQL's perspective. You're only getting busy again, when the views commit their changes by TABLEUPDATE - presumably.

Seems to be a good tool, but you're just not understanding the timings. The idle time usually is 28800. See https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_wait_timeout
If you wouldn't touch it you wouldn't have had that bad experience.

I recommend you look into the final connection string this tool generates and then find documentation about what each connection option means to verify that it really is what you want.