You could alter your SQL to use fully qualified table names, then you don't need to execute the use statment.
ie. SELECT whatever FROM database.table WHERE id = key instead of SELECT whatever FROM table WHERE id = key This works in the version in mysql 4 and greater for sure, and quite possibly in older versions as well. -----Original Message----- From: Boysenberry Payne [mailto:[EMAIL PROTECTED] Sent: Thursday, February 09, 2006 10:14 AM To: mod_perl Subject: Re: I'm getting max connection errors for Apache::DBI The only difference in each connection is the database. I tried connecting to the same database then using $h-do( "USE new_database" ); But it doesn't switch to the new database (works fine using DBI.) I decided as a temporary hack I'd try the following: sub clean_processes { my $self = shift; my $dbs = $self->{dbs}; my $h = $dbs->{local}; my $sth = $h->prepare( "SHOW FULL PROCESSLIST" ) or warn "error " . $h->errstr; $sth->execute or warn "error " . $h->errstr; my $tbl_ary_ref = $sth->fetchall_arrayref({}); return unless( int( @$tbl_ary_ref ) > 50 ); foreach( @$tbl_ary_ref ) { if( $_->{Command} ) { if( ( $_->{Command} eq "Sleep" ) || ( $_->{Command} eq "Killed" ) ) { if( $_->{Time} ) { warn "Killing ID: $_->{Id} USER: $_->{User} DB: $_->{db}"; my $sth2 = $h->prepare( "KILL $_->{Id}" ) or warn "error " . $h->errstr; $sth2->execute or warn "error " . $h->errstr; } } } } return; } Not only does it not kill the processes, but it stops allowing for a connection to any database giving the following error: [error] [client 127.0.0.1] DBD driver has not implemented the AutoCommit attribute at /usr/local/lib/perl5/site_perl/5.8.7/Apache/DBI.pm line 250.\n, referer: http://127.0.0.1/web_address/ When I watch the error_log and the $Apache::DBI::DEBUG = 2 I get one of the two following: need ping: yes 12844 Apache::DBI new connect to 'database=boysie_habitat;host=localhostrooth1jAcK36AutoCommit=1PrintError=1RaiseError=1Username=root' 12844 Apache::DBI disconnect (overloaded) or: need ping: yes 12848 Apache::DBI already connected to 'database=boysie_habitat;host=localhostUsernamePasswordAutoCommit=1PrintError=1RaiseError=1Username=root' 12848 Apache::DBI disconnect (overloaded) I will be a list of them 5 at a time (I'm guessing one for each child.) Then as long as I only use that database no problem. As soon as I switch databases which is the only thing that changes in my new connections, I spawns all new database threads. So if I have 100 databases to connect to I get ( 100 * as many child apache process as the requests came in on). I don't think setting my max connections 700+ makes sense especially since the remaining connections are shown as sleeping. Is there a way to connect to more than one database without spawning "extra" mysql connections? Also, my local development is on OSX, where as my production environment is Red Hat Linux. I'm using Apache2 and MP2 for both, only proxy in the production environment though. PS For some reason my email responses aren't making it to the list, sorry... Thanks, Boysenberry boysenberrys.com | habitatlife.com | selfgnosis.com On Feb 8, 2006, at 6:22 PM, Perrin Harkins wrote: On Wed, 2006-02-08 at 11:51 -0600, Boysenberry Payne wrote: What I'm seeing is 37 mysql threads being created for 7 apache children. It would seem as though I would need to create a 5/1 ratio of mysql connections to apache children. Is that normal? No. If you use the same connection info every time, you should only see one connection per child. If you have two databases, you should see two connections per child. Maybe you are still not using the same connect string each time? The debug output should tell you if it thinks it has a connection already or not. - Perrin
