mod_perl DBI question 1

[perl21]

Hi All,

I had to go live with a well tested mod_perl system this morning and after enough people got on it started to give internal server errors because a DBI connection couldn't be found anymore. Offline it never did that I guess because it was just me testing it.

I am reading through my mod_perl books right now to try and fix it. For now I changed the code to just connect every time but I need a persistent connection.

Here is what I'm currently using

------------------------------------------------------
use DBI;
use strict;

use vars qw($dbh);
unless (defined($dbh)) {

$dbh = DBI->connect("DBI:mysql:$config->{sqlDatabase}", "$config->{sqlUserName}", "$config->{sqlPass}") or print "Couldn't open database: $DBI::errstr; stopped";

}
------------------------------------------------------

I origionally wrote this system which has about 25 mod_perl files now, in perl with speedy_cgi. A system similary to fast_cgi where it runs each cgi script as a daemon. This is what the speedy_cgi people recommended for a persistent connection. And it seemed to work in mod_perl so I kept it.

It seems like mod_perl is creating multiple processes of the same script and only the origional has a connection.

Can anyone recommend how I can achive a persistent connection that isn't going to fail?

Thanks a lot.
Tony

 

[mbaranski]

You might be seeing the fact that each apache instance creates it's own script, with the daemon/cgi thing. For testing, try making only 1 instance of apache run (in httpd.conf) and see if it helps.

 

[perl21]

Hello mbaranski,

I made the change, and the phone isn't ringing anymore. It appears the errors have stopped. I have about 20-50 people on the system at any time and this morning some people would be using the system and receive can not prepare statement errors because the script had no connection.

Your fix has really saved the day, but I need to etablish a way to get each server to run it's scripts with connections to the mysql database.

Can anyone help me to understand why the code above doesn't do that already?

I'm reasearching Apache:BI or Apache2:BI right now, I'm sure the answer is there somewhere.

Oh, and one more thing. Working on a problem that's invisible is pretty difficult. I can only replicate the scenario that produces the error if I up the # of servers apache2 is running and a lot of people are on the server. And since it works great in single server mode, how can I figure out the problem that's happening in multi-server mode.

Thank you,
Tony

 

[perl21]

Well,

The solution was simple.

Just needed to add PerlModule Apache:BI to the httpd.conf file.

Or add use Apache:BI; to the startup.pl file.

Apache:BI makes sure the connection is created if ever lost. The code I used instead of the above code is

$dbh = DBI->connect
("DBI:mysql:$sqlDatabase:localhost",
"$sqlUserName",
"$sqlPass",
{
PrintError => 1, # warn() on errors
RaiseError => 0, # don't die on error
AutoCommit => 1, # commit executes immediately
}
);

There is no need to use DBI; in the mod_perl file or use Apache:BI;

As it turns out it's loaded no matter what because it was loaded in the httpd.conf file or your startup file.

Tony