Bug #397 mysql.exe uses persistent connections which leads to 'too many connections'
Submitted: 8 May 2003 5:13 Modified: 22 Dec 2003 10:20
Reporter: Thomas Salvisberg Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:4.0.10-gamma-nt OS:Windows (Windows 2000 SP3)
Assigned to: CPU Architecture:Any

[8 May 2003 5:13] Thomas Salvisberg
Description:
We were using mysql.exe and mysqlimport.exe in batch mode within a network login script to collect inventory information from all company clients. Every time mysql.exe (ore mysqlimport.exe) is executed, the connection remains active on the mysql server. This leads to the 'too many connections' error (we have >8000 clients).

As a workarouond we are now replacing mysql.exe and mysqlimport.exe with php-scripts, which are executed on every client. The drawback from this solution is that we loose the capability to compress the data, which the clients are sending to the database (the -C --compress parameter of mysql.exe). The compression was a nice feature, because most of the clients are connected with slow bandwith-WAN-Links.

Happy to be of service,
Cheers, Thomas

P.S.
Under the following link is some additional information about this issue, but the above mentionned scenario would also create problems with a *nix based Mysql-Server (if you have more than 1000 clients).

http://www.mysql.com/doc/en/Windows_vs_Unix.html

2.6.2.7 MySQL-Windows Compared to Unix MySQL

Blocking read 
MySQL uses a blocking read for each connection. This means that: 
A connection will not be disconnected automatically after 8 hours, as happens with the Unix version of MySQL. 
If a connection hangs, it's impossible to break it without killing MySQL. 
mysqladmin kill will not work on a sleeping connection. 
mysqladmin shutdown can't abort as long as there are sleeping connections. 
We plan to fix this problem when our Windows developers have figured out a nice workaround. 

How to repeat:
1. Check the number of connections under the 'server' tab in winmysqladmin.exe
2. run mysql.exe and quit
3. Click the refresh button in winmysqladmin.exe to check the number of connections again. You will see how the number of connections increases with every time mysql.exe is executed

(same behaviour with mysqlimport.exe)

Suggested fix:
My personal opinion is, that mysql.exe (and mysqlimport.exe) should by default create non-persistent connections. If in some situations it makes sense to create persistent connections with mysql.exe (I can't think of any), then it should be a optional command line parameter (Like php, where you have to use pconnect() explicitly to create persistent connections).

Furthermore, it would be a good feature to automatically clear inactive connections after a time which could be defined with a my.ini set parameter. This could lead to better performance and that should be in the interest of everybody.
[14 May 2003 12:41] Vasily Kishkin
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

I tried to test it, but I could't find that connections was still open.
I tested this by way....connecting, looking at connection count, disconnecting, look at connection count.

Count of connections I checked by executing 'mysqladmin status' repeatadly.
[22 Dec 2003 10:10] Moreno Gentili
I too have to report this same bug BUT on MySQL version 4.1.0-alpha-max-nt on WindowsXP prof.
Please, test it with that setup. The problem is that the wait_timeout variable is ignored and sleeping threads are never killed.
[22 Dec 2003 10:20] MySQL Verification Team
This bug was thoroughly fixed in the versions 

4.0.17

and

4.1.2

Please test the first one from the tarball.

You can test a second one from our BK repository.
[22 Apr 2005 15:11] jair enrique gonzalez barragan
espero que me puedad dar infromacion de como utilizar mysql+