I am the software quality assurance engineer for a small startup company. Our web site, running on Windows 2003 server, has an online ordering system that uses VB.NET and a MySQL back end. In testing this web site I discovered some problems with the shopping cart that led me to research some of the more esoteric aspects of ODBC databases.
This discussion covers MySQL 2000.85.1025.00 and MyODBC 3.51.06.00 running under Windows 2003 Server.
A simple black-box test of any small web site shopping cart system is to order one of everything that we sell. (I would not recommend this for testing a site the size of Amazon.com.) On our test site this would immediately cause server errors -- "Too Many Connections." A quick Google search on "MySQL Too Many Connections" yielded tons of hits. This problem has been known throughout the Compact, Known Space, the Alpha Quadrant, and the unfashionable western spiral arm of the galaxy for at least four years. The typical conversation goes like this:
user: My web site keeps generating "too many connections" errors.
expert: Make sure your code closes connections after it's through with them.
user: I checked it. My code is correct. The problem still exists.
expert:
Alternative responses suggest increasing the number of allowed open connections and turning on connection pooling. However, open connections use up memory and will eventually swamp the server.
I checked the code on our web site and verified that connections get closed. In fact, some of the code that causes the problem doesn't even call on the MySQL server directly, but uses fancy .NET constructs that build tables for you. The web page code doesn't open connections directly; the .NET framework opens and (presumably) closes them behind the scenes.
I verified that it is a problem with too many connections: On the server, launch a command window and start \mysql\bin\mysql, and give the command SHOW PROCESSLIST;. Alternatively, use a utility like WinMySQLAdmin and watch the processes. On a web browser, visit the web site. Add a bunch of things to the shopping cart. Back on the server, list the processes. Every time the Shopping Cart page is loaded, a bunch of processes are generated; these processes stick around for a long time. If you click on View Cart link often enough, you exceed the maximum number of processes allowed on the SQL server, and you get the "Server Error" page.
I researched connection pooling on the MySQL web site. There's a large number of web pages that talk about it in the context of Java applets, but that's not the technology we're using, so I skipped them. Other than that there are a few pages that mention it. They all link to each other and to the Microsoft documentation on the subject. However, I was not able to find confirmation anywhere that connection pooling had been implemented in MyODBC.
All the instructions I found for turning on connection pooling have to do with other ODBC drivers and other operating system environments. I discovered a nugget: Connection Pooling was implemented in ODBC Driver Manager 3.0 and later. Was MyODBC written to that specification?
I downloaded the source code for MyODBC and examined it. I read the function headers to get the lay of the land, but found no mention of connection pooling. The Microsoft documentation says that the ODBC application needs to call SQLSetEnvAttr with SQL_ATTR_CONNECTION_POOLING. That's easy enough to search for ... and it doesn't appear anywhere in the source.
In the file changelog it says...
--------------------------------------------- 30-Jan-2002 : monty@mysql.com, venu@mysql.com --------------------------------------------- Release of 3.51.01 with the support of following APIs and/or features: ... -- SQLSetEnvAttr, using this, the driver can be configured its functionality based on the ODBC version
In my_connect.c, main() calls
rc = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,0);
In options.c, SQLSetEnvAttr has a switch statement on the attribute parameter, but it does not have a case for SQL_ATTR_CONNECTION_POOLING.
The string "pool" does not show up anywhere in the code. I believe that MyODBC does not implement connection pooling. I'd love to be proven wrong. Can you show me the code that implements it?
In the ODBC control panel, explicitly turn off connection pooling. I don't
know who actually reads that bit, but I wanted to make sure that all systems
agree that it's off. Open the file c:\windows\my.ini in a text editor.
Look for the section that begins with [mysqld]
Add two lines:
set-variable = wait_timeout=0
set-variable = max_connections=1000
I first tried wait_timeout=10. I repeated the experiment and confirmed that the sleeping processes die sooner and that it takes much more persistent banging on the Shopping Cart icon to get the server to die. but the bug is still there. With a wait_timeout of 3, the problem appeared sooner than expected. With a wait_timeout set to zero, the problem never appears. Clearly the overhead of opening and closing connections means the server is operating at nowhere near its capacity. But this is better than it hanging all the time.