server went away error

[n8kindt]

hi guys, i am also posting this question in the MySQL forum but am putting the detailed post on here. here is the message i am having problems with:



i've done a few experiments on the cause of this error and i've noticed tendancies but do not know of any way to eliminate this problem. this error seemingly occurs at random but i can always make the error occur whenever i add or drop a field in the MySQL database. closing and reopening access will fix the problem about 1 out of 10 times. i also test the odbc connection thru the MySQL ODBC 3.5.1 interface and it checks out. if closing and reopening the access database does not fix the problem, that particular table is always blocked by this error for 10-20 minutes. sometimes, all other tables have the same error when i try opening them and other times the error is limited to one particular table. after that, the problem goes away.

this error is also very sporadic--it can go weeks without the error and then have 3+ of these errors in one day. the MySQL database is used for our webstore and the person who programmed it has no idea why this error would be occurring but he does not know much about access. he and i are the only ones with our hands on it. i believe the problem lies with ODBC driver or Access b/c the store has never had a problem connecting--even when i am experiencing the error.

i have changed the reconnect settings in access. i currently have the ODBC refresh interval at 10secs--the default is 60 i believe. and i have seen previous posts that stated putting code in a form whose record source is the table that is giving troubles to refresh the form ever 30 secs or so and that has also not gotten me anywhere.

i've been delaying this post for quite some time hoping i could fix it myself but i have exhausted all my resources. does anyone know how i can keep this error from happening? i'm using access 2007 and MySQL ODBC 3.51

[Atli]

I don't know much about Access or ODBC, but the MySQL Reference Manual has a whole page about this error.

It mentions that this can happen if you are running large queries, or if the queries are incorrect.
Are you using any user input that could be messing up the query. Un-escaped quote-marks or such.
Or are you using any large BLOB fields?

[n8kindt]

I don't know much about Access or ODBC, but the MySQL Reference Manual has a whole page about this error.

It mentions that this can happen if you are running large queries, or if the queries are incorrect.
Are you using any user input that could be messing up the query. Un-escaped quote-marks or such.
Or are you using any large BLOB fields?

thanks for your reply, Atli. that was a good resource. i found similar suggestions before but that was quite a bit more in depth. however, the only one that stuck out at me was

You have encountered a timeout on the server side and the automatic reconnection in the client is disabled (the reconnect flag in the MYSQL structure is equal to 0).

that might be the best bet b/c i can simply be browsing simple tables thru odbc and it will give me that error as well. and i'm not running any code so the connection is never opened or closed that way. also, i'm not running any large BLOB fields. i have no idea what they are either so i doubt i'm running any at all lol. no user input either. so it can't be b/c of rogue code, size, blob fields, or user input.

so i will try the automatic reconnection flag thing. if that is the source of trouble, i would feel better about curing the problem--not the symptom. how can i keep it from timing out?

Prior to MySQL 5.0.19, even if the reconnect flag in the MYSQL structure is equal to 1, MySQL does not automatically reconnect and re-issue the query as it doesn't know if the server did get the original query or not....

i have MySQL 5.1 so this shouldn't be a problem right??

...The solution to this is to either do a mysql_ping() on the connection if there has been a long time since the last query (this is what MyODBC does) or set wait_timeout on the mysqld server so high that it in practice never times out.

i'm not exactly a MySQL expert so i'm not sure if i even have access to changing this? it is not a dedicated server. we are set up thru www.aplus.net. if i am able to configure this setting, where would i do so?

thanks guys,
nate

[n8kindt]

well, the early returns of changing the reconnect flag seems to be working. i hope there isn't a negative side effect of changing this setting b/c i looked into changing the server timeout variable and i have no access to doing this since it is not our server. also running into other major problems with some other projects i had in mind b/c the MySQL server is an ancient 4.1. aplus will not upgrade the server since we have a shared server account so the only solution to this is getting our own server.

if i have any more problems, i will be back! thanks, Atli for your reference to the MySQL reference manual. you saved me on this one. it might not have seemed like you did a lot but thanks to that link i was able to figure out a way to not have that annoying error message (knock on wood).

thanks guys!

[n8kindt]

yep, it's back. the reconnect time is nowhere near the time it was before but i'm still wondering how to keep this from happening.

Quote:
Originally Posted by http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
Prior to MySQL 5.0.19, even if the reconnect flag in the MYSQL structure is equal to 1, MySQL does not automatically reconnect and re-issue the query as it doesn't know if the server did get the original query or not. The solution to this is to either do a mysql_ping() on the connection if there has been a long time since the last query (this is what MyODBC does) or set wait_timeout on the mysqld server so high that it in practice never times out.


i have MySQL 4.1... am i pretty much screwed if i can't edit the server variables? or how do i set up a ping?

[n8kindt]

yep, it's back. the reconnect time is nowhere near the time it was before but i'm still wondering how to keep this from happening.

Quote:
Originally Posted by http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
Prior to MySQL 5.0.19, even if the reconnect flag in the MYSQL structure is equal to 1, MySQL does not automatically reconnect and re-issue the query as it doesn't know if the server did get the original query or not. The solution to this is to either do a mysql_ping() on the connection if there has been a long time since the last query (this is what MyODBC does) or set wait_timeout on the mysqld server so high that it in practice never times out.


i have MySQL 4.1... am i pretty much screwed if i can't edit the server variables? or how do i set up a ping?

i still have not found a solution to this. i have avoided the error message by populating the entire form using DAO so the user can proceed even when the connection is not available. previously an annoying "object not set" popped up 10-20 times when i used queries to populate the form. but i'm still wondering if there is a fix to this solution??? thank you for any and all replies.

--nate

[n8kindt]

ok, i think i finally got it! i found a way to ping the server. hopefully i don't jinx myself again, but i think this is working!

Public Sub PingMySQL()
	
	Shell "ping 127.0.0.1 -t", vbHide

End Sub

the "-t" forces it to do a continuous ping. if you only want it to ping on a timer i would put 'Shell "ping 127.0.0.1", vbHide' into an ontimer event on a form. i simply called the sub when a user logs in. furthermore, if you want to see the results you can change vbHide to vbNormalFocus
i got the code from HERE. it was a big help and there are some additional tips on how to ping servers from vba

[n8kindt]

ok, i think i finally got it! i found a way to ping the server. hopefully i don't jinx myself again, but i think this is working!

Public Sub PingMySQL()
	
	Shell "ping 127.0.0.1 -t", vbHide

End Sub

the "-t" forces it to do a continuous ping. if you only want it to ping on a timer i would put 'Shell "ping 127.0.0.1", vbHide' into an ontimer event on a form. i simply called the sub when a user logs in. furthermore, if you want to see the results you can change vbHide to vbNormalFocus
i got the code from HERE. it was a big help and there are some additional tips on how to ping servers from vba

of course this didn't work. i have to ping the actual MySQL server using the mysql_ping command. does anyone know if this is possible thru vba? i've gone thru so many different codes--my head is spinning. i've tried using dll's and vba code to do it and i couldnt get it to work (although i will admit, it might be b/c it was off a japanese language website), using DAO and my latest attempt is using ADODB which works fine as a connection but i don't know how to execute mysql commands from there (i'm starting to think it's impossible)... can anyone help please???

[n8kindt]

of course this didn't work. i have to ping the actual MySQL server using the mysql_ping command. does anyone know if this is possible thru vba? i've gone thru so many different codes--my head is spinning. i've tried using dll's and vba code to do it and i couldnt get it to work (although i will admit, it might be b/c it was off a japanese language website), using DAO and my latest attempt is using ADODB which works fine as a connection but i don't know how to execute mysql commands from there (i'm starting to think it's impossible)... can anyone help please???

here's the code for doing just that:

when using vba (or visual basic, etc) there is a dll that can be downloaded HERE

Public Sub Ping_MySQL()

On Error GoTo PingError
 Set GCnn = New MYSQL_CONNECTION
    GCnn.OpenConnection "xx.xxx.xx.xx", "your db name", "your password", "user name"
    If GCnn.State = MY_CONN_OPEN Then
    GCnn.ping
    Debug.Print "connection successfully pinged " & Now()
    Else: Debug.Print "ping command unavailable"
    End If
Set GCnn = Nothing
Exit Sub
PingError:
Debug.Print "ping error"
Exit Sub
End Sub

'if u're using this code, don't forget to add the VBAMySQL.dll in your references

BUT

the problem still remains. i'm not sure if the problem is b/c i'm using an odbc connection and pinging it using a different method. i thought it would work since the ping command was coming from the same ip address. there have been a few times where i've been able to restore the connection using that function but others i can call that procedure all i want and it doesn't do any good. the conclusion i'm coming to is there is an additional problem. HELP?

so... SOS

i'm running out of time to get this problem fixed. i've looked into populating a continuous form using command lines programmed into the dll--which would be similar to using DAO or ADO but programming a continuous form looks to be very painful if not near impossible since no one else has had the need to do it before. can ANYBODY throw me a lifeline here? i would very much appreciate it.