What if you'd write all your SQL-Statements in a fully qualified manner? ---------------8<--------------- SELECT * FROM $DB1.$TABLE1; SELECT * FROM $DB2.$TABLE1; ---------------8<---------------
Tom Boysenberry Payne wrote: > 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 > > >
signature.asc
Description: OpenPGP digital signature