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.

 

[tomk3]

I think he is using the standard VFP connection designer, on connection "GPlusconn". when I start it I get connection =15s idle=0

 

[Chris Miller]

Thanks for pointing out,I didn't realize this as the VFP dialog. But "GplusConn"? Why?

If I add a new connection into a DBC the dialog looks the same, but the icon and GplusConn name are unfamiliar. The VFP dialog has the Fox icon...



By default the idle time is 0, which will mean to not include such an option in the connection string. If you think an idle time is good and don't understand it you only learn by experiencing your decision as bad decision. If you don't know something don't guess, look up what it means...

Now I wonder about the different icon. The name GplusConn seems just to be the connection name, Connect1 is the default, if you don't give a name. But the icon differing? Maybe it'll just be the _screen icon, whatever you set it to. It was making me think this is a third party tool and not a VFP dialog.

Anyway, the recommedation remains what it was: With the final connection string look up what it means. In this case in MySQL documentation.

 

[Chris Miller]

Okay, and one more thing: The connection object is more than just the connection string. Things like the idle time are not influencing the connection string itself. The idle time is set by the connection doing SQLSetProp(connhandle,"IdleTimeout",5) after the connection is established.

This is therefore documented in VFP helps SQLSetProp as:

The idle timeout interval in minutes. Active connections are deactivated after the specified time interval. The default value is 0 (wait indefinitely). Read/write.

Could be better phrased as "Idle connections". It means "active" in the sense of existing current connections listed by ASQLHANDLES, not necessarily currently really "active".

As I already pointed out that's also a configuration setting of MySQL Server, and it should be high. very high, or from the VFP side 0 for indefinitely. With 5 min you actually stress your users to be finished with any dialog in less than 5 minutes, very roughly said.

You could relieve that stress by defining a timer that does something simple every 4 minutes, like a SELECT NOW(); that could even be used to monitor time zone differences changing. Anyway, it would make your connection not idle 5 minutes or longer because you do something every 4 minutes. Unless something runs that even stops timer events to happen. Possible, from the top of my head a SQL query taking longer would cause that, Of course, a query that's not using that connection. That makes it less likely to ever happen. But the simple solution is not a timer doing such a default action to keep the connection alive, but to not set the idle time at all.