Mysql server has gone away error

[geniekids]

Hi
I have a mysql database on my web server and an MS Access (2002) frontend on my desktop. I am using ODBC 5.1 to connect the access frontend to the mysql database. However i keep getting this error almost every few minutes
QUOTE:
ODBC call failed
[MYSQL][ODBC MYSQL 5.1 Driver][mysqld-5.0.45-community]Mysql server has gone away (#2006)
UNQUOTE

Once this error comes i need to close the ms access and open it again - it works for few minutes and then the same error.

Any help will be really appreciated.
(i am quite a newbie to mysql and odbc)
ratnesh

[Al McNicoll]

Dear Ratnesh,

I develop using Access / MyODBC / MySQL 5.x on a daily basis, and I have to
admit that I've found that MyODBC 5 has been less reliable than 3.51.x -
maybe try installing 3.51.x as well (they can co-exist peacefully) and see
if you have fewer issues with that...

Otherwise, I recommend the following:
- Check in the documentation that you're using the right connection
settings:
http://dev.mysql.com/doc/refman/5.0/en/myodbc-configuration-connection-param
eters.html
- Look on the MySQL / Access forum: http://forums.mysql.com/list.php?65
- Post back to this list, explaining how you're connecting to the server
(are you using a System Data Source, a User Data Source - are you connecting
using ADO?)

Al McNicoll
Integritec Limited


-----Original Message-----
From: geniekids [mailto:geniekids@...]
Sent: 05 June 2008 06:35
To: myodbc@...
Subject: Mysql server has gone away error


Hi
I have a mysql database on my web server and an MS Access (2002) frontend on
my desktop. I am using ODBC 5.1 to connect the access frontend to the mysql
database. However i keep getting this error almost every few minutes
QUOTE:
ODBC call failed
[MYSQL][ODBC MYSQL 5.1 Driver][mysqld-5.0.45-community]Mysql server has gone
away (#2006)
UNQUOTE

Once this error comes i need to close the ms access and open it again - it
works for few minutes and then the same error.

Any help will be really appreciated.
(i am quite a newbie to mysql and odbc)
ratnesh
--
View this message in context:
http://www.nabble.com/Mysql-server-has-gone-away-error-tp17662496p17662496.h
tml
Sent from the MySQL - ODBC mailing list archive at Nabble.com.




--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/myodbc?unsub=lists@...

[geniekids]

Thanks Al
Actually i was using ODBC 3.51.x only - but since i was facing the same problem with that i thought upgrading to 5.x will help.

I also looked into the links that you have provided below - but nothing seems to be different
I am using  a user data asource and i don;t think i am using any ADO (though possibly i am not sure :-(
I am attaching a screen shot of how i am doing the connection

connection+window.jpg

thanks for all the help - but really need somebody like you to guide me though and get this working - a bunch of my faculty (we are a child dev. org) are waiting for this program to work so that they can enter their teaching plan into it!!

I am even ok with emailing you the domain name, password etc for the mysql and and the msaccess file - so that you can try from your local comp.

ratnesh

Al McNicoll wrote:

Dear Ratnesh,

I develop using Access / MyODBC / MySQL 5.x on a daily basis, and I have to
admit that I've found that MyODBC 5 has been less reliable than 3.51.x -
maybe try installing 3.51.x as well (they can co-exist peacefully) and see
if you have fewer issues with that...

Otherwise, I recommend the following:
- Check in the documentation that you're using the right connection
settings:
http://dev.mysql.com/doc/refman/5.0/en/myodbc-configuration-connection-param
eters.html
- Look on the MySQL / Access forum: http://forums.mysql.com/list.php?65
- Post back to this list, explaining how you're connecting to the server
(are you using a System Data Source, a User Data Source - are you connecting
using ADO?)

Al McNicoll
Integritec Limited


-----Original Message-----
From: geniekids [mailto:[e-mail]]
Sent: 05 June 2008 06:35
To: [e-mail]
Subject: Mysql server has gone away error


Hi
I have a mysql database on my web server and an MS Access (2002) frontend on
my desktop. I am using ODBC 5.1 to connect the access frontend to the mysql
database. However i keep getting this error almost every few minutes
QUOTE:
ODBC call failed
[MYSQL][ODBC MYSQL 5.1 Driver][mysqld-5.0.45-community]Mysql server has gone
away (#2006)
UNQUOTE

Once this error comes i need to close the ms access and open it again - it
works for few minutes and then the same error.

Any help will be really appreciated.
(i am quite a newbie to mysql and odbc)
ratnesh
--
View this message in context:
http://www.nabble.com/Mysql-server-has-gone-away-error-tp17662496p17662496.h
tml
Sent from the MySQL - ODBC mailing list archive at Nabble.com.




--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/myodbc?unsub=[e-mail]

[Bob_A]

-----Original Message-----
From: geniekids [mailto:[e-mail]]
Sent: 05 June 2008 06:35
To: [e-mail]
Subject: Mysql server has gone away error
Hi
I have a mysql database on my web server and an MS Access (2002) frontend on
my desktop. I am using ODBC 5.1 to connect the access frontend to the mysql
database. However i keep getting this error almost every few minutes
QUOTE:
ODBC call failed
[MYSQL][ODBC MYSQL 5.1 Driver][mysqld-5.0.45-community]Mysql server has gone
away (#2006)
UNQUOTE
Once this error comes i need to close the ms access and open it again - it
works for few minutes and then the same error.
Any help will be really appreciated.

Geniekids:
I too had been struggling with this (or at least a very similar problem) for a week.  I too am a newbie.  The problem is with the timout setting and can be solved within the DSN.

1) I'm using MSAccess Offic 2003 as a front end.
2) MYSQL database is on a remote host.  It's a 4.0, not 5.0, if that matters.
3) I first installed the the appropriate ODBC 3.5.1 driver for MySQL.  You can download it from the URL:
http://www.mysql.com/products/connector/odbc/ 
4) Once this driver is in place you can then create a DSN connection to the MYSQL Server. (Start>programs>administrative tools>Data Sources (OBDC)>Add> and find your new MYSQL Driver).  You must then know its IP address, your username and password for the MYSQL database.   But, there's a key setting you must make when creating the DSN.   (Or you can edit the existing one.  See Solution below.)

All this allowed me to establish a linked table in MSAccess (with your .mdb open and looking at the Tables tab,  use File>Get External Data>Linked Table>ODBC Database ()" [i.e. the file type] and then use your newly configured DSN for the MYSQL database and select the table you want.

Cool.  I could open Access and the linked table would be there just fine. I could open the table, view its content, edit fields, create forms with data linked to the table, etc. BUT, WITHOUT THE SOLUTION BELOW if I let it sit for 100 secs, it would basically time out and I'd get the message you got and have to close Access and then it would be linked again.  

SOLUTION:  To solve my 100 second timeout problem, when creating the the DSN connection (I did a USER connection, not a SYSTEM connection), look at the "Connect Options" tab.  In the "Initial Statement" box type "Set wait_timeout=28800" (without the quotes).  Apparently that is in seconds so 28800 would be a long time.  But since we have very, very few people hitting this database, I don't think it will cause any problems.

If you've already created the DSN connection, use the ODBC manager (Start>programs>administrative tools>Data Sources (OBDC).  Highlight the DSN you created and then Configure>Connect Options Tab and supply the above initial statement.

By the way, if you've already created the hot-linked table in Access, you'll have to delete it and recreate the linked table  (File>Get External Data>Linked Table>ODBC Database ()" [i.e. the file type] and then use your newly configured DSN to establish it again.  The new initial timeout setting (for whatever value you set it at) will then be set in the linked table.  Just changing the DSN won't flow the new timeout unless you re-link the table, thus picking up the net setting.
 
If you haven't found it, the following link is a good primer for Access front-end and MYSQL backend.
http://www.aspfree.com/c/a/Microsoft-Access/An-Access-Front-End-to-MySQL/