visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: How can I automatically disconnect idle connections which remain from mysql.exe batch-mode clients?
By: wumpus2 U.S.A.  Date: 07/05/2003 00:00:00  English  Points: 500 Status: Answered
Quality : Excellent
In a Windows login script I am collecting inventory information and import this values with mysql.exe and mysqlimport.exe to a central database on a Windows 2000 Server (2x1,2gig processors and 1,5gig memory). This process takes about 5 Seconds per client. We have about 8500 Clients, and after a short while we were getting 'too many connections' errors. We than increased the max_connections parameter in my.ini from the default value 100 to 1000 and were getting the same error again. Even during 'login rush hour' early in the morning there are seldom more than 10 processes active and the processor load and memory usage was very low, so we tried to increase the max_connecstion parameter even further, but after setting it to 5000 and waiting a couple of hours the database was not responding anymore, although we got no error and there was still no load on the server. So as the only workaround we have set the max_connections to 1000 and are restarting the mysqld-nt every half hour. This is not an satisfactory situation.

on <A HREF="http://www.mysql.com/doc/en/Windows_vs_Unix.html">http://www.mysql.com/doc/en/Windows_vs_Unix.html</a> I found the following information:
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.

Even if we would have a unix server, we would have thousands of connections long before 8 hours have passed. I can't believe that there is no solution.

Any help welcome
By: VGR Date: 07/05/2003 20:18:00 English  Type : Comment
are you using persistent connections ? (pconnect() calls)
By: wumpus2 Date: 07/05/2003 20:42:00 English  Type : Comment
>>are you using persistent connections ? (pconnect() calls)

The client's connect to the DB directly with mysql.exe and mysqlimport.exe not through a PHP-Script or an API etc. I could not find a option with these two mysql clients to control wether they do a persistent connect or not.
By: VGR Date: 07/05/2003 20:50:00 English  Type : Comment
so you in fact do this (like a DOS BATch) :
mysql.exe --user=... --password=... < commandfile.sql

right ?
By: wumpus2 Date: 07/05/2003 20:51:00 English  Type : Comment
>>so you in fact do this (like a DOS BATch) :
>>mysql.exe --user=... --password=... < commandfile.sql
>>
>>right ?

correct.
By: VGR Date: 07/05/2003 21:00:00 English  Type : Answer
hummmmmmmmmm and you encounter the famous "too many connections" error... hummmm

I would recommend trying (just try, it won't hurt :D ) to call, not mysql.exe directly, but php.exe directly (that's the CGI-BIN version delivered with all *nix distribs and nowadays with Windows ones). It doesn't need a webserver to run (unlike the ISAPI module verison)

the PHP page passed to it would just contain :
-connect to the DB
-execute your commandfile.sql
-disconnect from the DB

it's a matter of minutes to set this up and give it a try
By: wumpus2 Date: 07/05/2003 22:38:00 English  Type : Comment
ok, I gave it a try (even though my php is much worse than my english..) and it worked. It's a workaround, but as a solution for me it has the following drawbacks:

a) My Batchfile generates CSV-Files and SQL Files; I have to change this to php-syntax (I could live with that, since I'm gaining some functionality)
b) I was using compression for the connection between mysql.exe and the database (because we have slow WAN-links between client and server) --> Is there a way with php?
c) The size of the needed tools is slightly bigger (1327KB for php.exe and php4ts.dll versus 532KB for mysql.exe and mysqlimport.exe) --> a remote client with a 64Kbps line needs over 3 Minutes to download this; since it's deployed only once, it's still bearable)

If there is a easy way to implement compression, can you point this out in this sample php-file?

<?
mysql_connect("x.x.x.x","user","password");
mysql_select_db("mydatabase");
$query = "insert into mydatabase values ('myvalues');";
$result = mysql_query($query)
or die(mysql_error());
?>

Overall, I still can't believe that by using mysql.exe (notabene the official mysql client) there is no way to clean up the unused connections.
By: VGR Date: 07/05/2003 22:53:00 English  Type : Comment
the problem with mysql.exe (truly the only one MySqlD client I use), is that it is for INTERACTIVE use. Hence the 8 hours limit. Althought I never noticed your problem (of course, 'cause I'm the only one using mysql.exe, other connections pass via PHP) I believe what you wrote.


I'll have a look et the compression issue. Could you be more specific about this ? What do you use exactly ?

As for CSV and SQL files, even if PHP can handle them, perhaps it's not useful to modify too much your BAT ; perhaps CALLing the "conneting" BAT from the other one (doing the CSV etc) would be enough

Given I don't have your BAT under my eyes, those ideas are under all reserves ;-)
By: wumpus2 Date: 07/05/2003 23:19:00 English  Type : Comment
>>compression issue. Could you be more specific about this ?
I just use the command line parameter -C --compress because the clients and the db are connected with small-bandwidh WAN-links.

>>As for CSV and SQL files, even if PHP can handle them,
>>perhaps it's not useful to modify too much your BAT ;
>>perhaps CALLing the "conneting" BAT from the other one
>>(doing the CSV etc) would be enough
I think you're right; but I have no idea how the php-syntax is for sending pre-created csv-files (and sql.files). But if that's just a couple of lines and you can post them, then You earned your 500 Points.
By: VGR Date: 07/05/2003 23:49:00 English  Type : Comment
sending CSV files/data from where to where ?
By: wumpus2 Date: 08/05/2003 00:04:00 English  Type : Comment
>>sending CSV files/data from where to where ?
from the client to the server..
1. A client on a remote site logs in
2. In the login script a batch file is called, which generates CSV/SQL files with inventory data
3. At the end of the batch-file the CSV/SQL files are sent to the central db mit mysql.exe and mysqlimport.exe over a WAN-Link
(I have sent you an email with the batch and the csv/sql files)
By: VGR Date: 08/05/2003 07:19:00 English  Type : Comment
would you mind if I post my script here so that we adhere to EEE guidelines ?
By: wumpus2 Date: 10/05/2003 21:57:00 English  Type : Comment
Of course I don't mind. Go ahead.
Thnx again for your help!
By: VGR Date: 10/05/2003 23:00:00 English  Type : Comment
ok, here it is, for executing three SQL files's contents and importing two CSVs (I could have written also a function for this, given that 'loginprinter.csv' refers always to table 'loginprinter', for example)

#!/usr/bin/php
<?php
// functions
function execsql($fn) {
GLOBAL $linkID;
$commands=file($fn);
foreach($commands as $command) $result=mysql_query($command,$linkID) or die ("bad query '$command' : ".mysql_error());
} // execsql Procedure

// connect
$linkID=mysql_connect('XXX.XXX.XXX.XXX','DBUSER','PASSWORD') or die ("bad connect ".mysql_error());
mysql_select_db('inventar',$linkID) or die ("bad select db. ".mysql_error());
// execute sql files
execsql('c:\temp\clearsoftware.sql');
execsql('c:\temp\clearprint.sql');
execsql('c:\temp\software.6');
// import CSV data
$handle = fopen ('c:\temp\logincomputer.csv','r');
if ($handle) {
while ($data = fgetcsv ($handle, 1000, ",")) {
// build query for replace (-r on cmdline) on implicit tablename transmitted
$query='REPLACE INTO logincomputer VALUES(';
$query.="'".$data[0]."'"; // ok for type cast ?
for ($c=1; $c < count($data); $c++) $query.=",'".$data[$c]."'"; // typecast ok ?
$query.=')';
// exec query
$result=mysql_query($query,$linkID) or die ("bad query '$query' : ".mysql_error());
} // while data (line) found
fclose ($handle);
} // if handle
$handle = fopen ('c:\temp\loginprinter.csv','r');
if ($handle) {
while ($data = fgetcsv ($handle, 1000, ",")) {
// build query for replace (-r on cmdline) on implicit tablename transmitted
$query='REPLACE INTO loginprinter VALUES(';
$query.="'".$data[0]."'"; // ok for type cast ?
for ($c=1; $c < count($data); $c++) $query.=",'".$data[$c]."'"; // typecast ok ?
$query.=')';
// exec query
$result=mysql_query($query,$linkID) or die ("bad query '$query' : ".mysql_error());
} // while data (line) found
fclose ($handle);
} // if handle
// disconnect
mysql_close($linkID);
?>

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 359.869960 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page