Bug #14967 Windows ephemeral port exhaustion when database name not in connect string
Submitted: 16 Nov 2005 5:51 Modified: 15 Dec 2005 18:50
Reporter: Matthew Kerr Email Updates:
Status: Not a Bug Impact on me:
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.12 OS:Microsoft Windows (Win XP)
Assigned to: Bogdan Degtyariov CPU Architecture:Any

[16 Nov 2005 5:51] Matthew Kerr
I discovered that a difference in my ODBC connect string caused MyODBC to exhaust all the available ephemeral port numbers in Windows during bulk operations, as described in the MySQL 5.0 manual section A.2.2.1. However, I was able to narrow down the cause.

This appears to happen when the connect string does not specify a database name, but rather relies on the DSN to supply that information.

MSKB 256986 provides a workaround, but it's not enough for high-volume use.

How to repeat:
Open the mysql client and note your connection ID, or run "netstat -a" to show your current port allocations.

In Access, programetrically set some linked tables' DSN strings to "ODBC;DSN=foo". (with DSN foo properly configured to connect to your database)

Open and close some the linked tables a few times in Access.

Run another "netstat -a" or re-run the mysql client and note the new connection ID. You will see that many connections have been allocated to perform simple operations.

Repeat the above sequence, but set the tables' connection strings to "ODBC;DSN=foo;DATABASE=bar". You will then see that only one or two additional ports are allocated regardless of the number of operations you perform.

Suggested fix:
Make MyODBC create connections the same way (i.e., using a single TCP connection instead of 32,700 connections) regardless of if the database name is specified in the stored DSN or in the connection string.
[12 Dec 2005 20:14] Bogdan Degtyariov
Hi Matthew, could you describe the procedure how to set programmatically the connection string for a linked table in Access? 
Thank you for update.
[13 Dec 2005 6:20] Matthew Kerr
Access: Iterates through all ODBC-linked tables and changes connection strings

Attachment: ResetLinks.bas (text/plain), 993 bytes.

[13 Dec 2005 6:21] Matthew Kerr
I attached some VB code to alter the ODBC links for my tables. 
The comments should explain more.

[15 Dec 2005 18:40] Bogdan Degtyariov
Hi Matthew,

Thank you for the files. I was able to reproduce the bug. Also I have performed the debug of MySQL Connector/ODBC. Here are some results I got from the debug:
 - When the connection string doesn't contain "DATABASE=.." parameter, MS Access creates 4 connections at once! And each time the connection string passed to MyODBC Driver is different. For example: For the first time Access adds UID=Admin parameter, the second connection doesn't have this parameter, third one has it again, and fourth - doesn't. Access makes connection attempts every time when I try to get the data from my test table.

- All is ok, when I use DATABASE=test parameter in the connections string. Even more - Access doesn't open a new connection when I'm trying to re-open the table.

I compared the connection parameters during the debug in the both cases. I can confirm that stmt->dbc->mysql->database field is set to the name of the DB from the DSN. It doesn't depend whether I add the DATABASE parameter to the connection string or do not add.

To make sure that my suppositions are right I wrote a .c test program, which gets some data from MySQL test DB. It makes one connection with the DATABASE=test parameter, and the second connection string doesn't contain that parameter. Each time only one connection had been established.
The problem with Access doesn't related with MySQL ODBC driver.
[15 Dec 2005 18:48] Bogdan Degtyariov
Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

Not a bug because it's not on MySQL Connector/ODBC side