myodbc connection problems

I am using myodbc 3.51.12 where I observed a lot of #2013 errors "Cannot
connect to MySQL server". The main use is from MS-Access 2002 on my
Win-XP Pro SP-2 to access the MySQL databases residing on the webserver
at my webhost.

To remedy this problem I tried to add "set wait_timeout=999999" in
initial statement. This solved my connection problems, but gave a
problem as the web server administrator noticed a lot of pipes hanging
for several hours.

Then I tried to reduce this number to 200, and with this relatively low
value I were still able to use MS-Access. However, it seems as if the
old high wait_timeout value still remains, as the problem with hanging
pipes still exist.

If I remove the wait_timeout value entirely, I can only run 1-2 queries
- then I get these #2013 errors. When this happens I have to close down
MS-Access and restart. Then I have 1-2 "shots" again.

Therefore I would very much like to know how I can make sure that
1) My calls does not create load problems on the server
2) That I can use various queries without constantly getting #2013 errors.

Thank you

Mogens

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

RE: myodbc connection problems

> The main use is from MS-Access 2002 on my Win-XP Pro SP-2 to
> access the MySQL databases residing on the webserver at my webhost.
>
> [snip] solved my connection problems, but gave a problem as the web
> server administrator noticed a lot of pipes hanging for several hours.

You're using named pipes over NetBIOS to connect to a remote host?
Out of curiosity, why aren't you using MySQL over TCP/IP?

> However, it seems as if the old high wait_timeout value still remains,

You can check that for yourself. Set wait_timeout to something low:
SET wait_timeout=3D120
Ensure that it's been set correctly:
SHOW SESSION VARIABLES LIKE 'wait_timeout'
SHOW GLOBAL VARIABLES LIKE 'wait_timeout'
Wait 5 minutes without any activity on the connection.
Then execute a random query and note whether you receive #2013 or not.

> as the problem with hanging pipes still exist.

If you want to fix that, you could fix your application to do a ping
on the connection at any place where there is a possibility that the
last query was a long time ago. For example, if you execute queries
in response to user input, in all your user-invoked event handlers
(that query the database) run "SELECT version()" and recreate the
connection if you get #2013 as the first thing you do.

> 1) My calls does not create load problems on the server

You could increase max_connections on the server and just ignore
that there are idle ("hanging") connections. Each connection does
use statically a small amount of memory, the size depending on
settings in my.{cnf/ini}, but it does not cause "load" problems per
se, if that's CPU load you're talking about.

A third possible solution is to try the 3.51.13 prerelease from
ftp://ftp.mysql.com/pub/mysql/hidden/connectors/odbc
It might magically fix your problem by automatically reconnecting
without letting your application know that anything has happened.
(3.51.11 and earlier did this too, bug #14639 prevents 3.51.12 from
doing it.)


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg