non-persistant odbc connection

[ellenr]

Access 2010 FE, mysql BE via dsnless connection to Arvixe hosted domain. Smooth until yesterday. Now, I am losing connection a few minutes after opening the program, even while I am actively using it. ("mysql server has gone away"). If I don't close the program and then check back in 8 or 9 minutes, I find it connected again. I have been timing the on/off cycles for an hour, and the off periods appear to be fairly regular. Does anyone have any idea what I can do from my end to remain connected? It's not like we are overusing our allotted piece of the shared pie, as there are only 4 users of this program, and I am the only one using more than a few minutes per week while I am polishing the code.

When I put in a trouble ticket, I was told:

"High wait_timeout value results in performance issues thats why the value was lowered to a reasonable value so that the idle connections do not just sit there occupying a connection slot. Unfortunately on shared server, this value cannot be changed/increased."

BTW: my connection string is:
MyConnectionString = "ODBC;Driver={MySQL ODBC 5.1 Driver};" & _
"Server=108.175.149.128;Database=" & "dbname" & ";" & _
"OPTION=4194339;INITSTMT=SET @@wait_timeout=28800;" & _
"User=" & "dbusername" & ";" & _
"Password=" & "xxxx" & ";"

 

[James Deckert]

You say you're not overusing it but apparently your tech support people think you are. I don't know how to stay connected when your networking people are booting you off by the settings they have.
Maybe you could trap the error that occurs and reconnect? Sounds like the user will get frustrated at having to wait though.
I wonder why it reconnects by itself if you're not doing anything? Seems like if you are booted off, you wouldn't be reconnected without closing and reopening the program.
I'm sorry I'm not more help.

 

[ellenr]

My response from trouble ticket:

wait_timeout has been lowered to 60 seconds for all users of the server. This is intentional change and yes it applies to all the users on the server.

Exactly what does this mean? If I open an Access db that has input screens and reports and several dlookup fields on the front page, does that mean I only have one minute to do whatever I need to do? This is a recent change they say. Do I just need to look elsewhere for a host, or is there a way around the problem?

 

[James Deckert]

ellen,
so your back end is hosted by someone else?
then the wait timeout would be the amount of time from them receiving an instruction from your front end (the code/forms/etc) for them to process the response before they give up. In other words if you do a query of the data which takes over 60 seconds, they will give up and assume it didn't work. Therefore, you'll need to make sure all forms/queries/reports are optimized to not take over 60 seconds.
I belive this is what your error means, but you could do some testing to find out. Is there a specific form/report that is consistently having a problem? If so, change it to be faster (reduce the records returned) to see if the problem clears up. Take one which is working fine, and slow it down and see if the problem occurs.

 

[ellenr]

The mysql is hosted at Arvixe. There is nothing that takes longer than 60 secs. that I can see. But, for example, I just opened the program, opened up a form wherein I entered a one sentence update to one of the help files. I spent more than a minute changing my wording, etc. I closed the form as soon as I finished, and got the message that odbc had gone away and that it couldn't save the record.

 

[ellenr]

Just to wind up this topic: I finally received this answer from Arvixe to my trouble ticket:

wait_timeout defines the number of seconds the server waits for activity on a noninteractive connection before closing it. As it is now set to 60 seconds so mysql will close the connection if it remains non-interactive for 60 seconds. I am sorry but I don't have any solution for it because this value has been set to all servers with the new MySQL setup so it cannot be changed and with 60 seconds wait_timeout limit, your application doesn't work.

I was able to continue using this host by adding a 45 second timer to refresh an always open form. So far, so good!

 

[zpy2]

Do you use link table？
if you bound link tables to a form, this means the connection always open.
In multiple users environment, this may cause some unexpected problem some time