Forking and Database Connection problem

[Vovin]

Hi,

I'm having a bit of a problem losing database connections. I have a perl script that forks 2 children. Each child connects to the same database. The first child completes it's processing correctly but the second loses it's database connection.

I put some logging into the code and it looks like all the children are using the same DBI database handle. I was thinking that this could be the problem. I have all my db functions in a seperate module. Someone told me that if I changed this module into an object that this would have the effect of creating a different database handle for each child. My logging seems to disagree with this though.

Does anyone have any ideas on how I can get a new database handle for each child?

I've copied and pasted code for my main script and for my db object below (N.B. for security reasons I've put asterix's where passwords etc... would be):

#!/bin/perl -w

use Env;
use lib "$HOME/bin";
use mp_db;
# TODO: module names should really begin with an uppercase letter.

$SIG{ABRT} = \&mp_db::disconnectDB;
$SIG{FPE} = \&mp_db::disconnectDB;
$SIG{HUP} = \&mp_db::disconnectDB;
$SIG{ILL} = \&mp_db::disconnectDB;
$SIG{KILL} = \&mp_db::disconnectDB;
$SIG{QUIT} = \&mp_db::disconnectDB;
$SIG{SEGV} = \&mp_db::disconnectDB;
$SIG{TERM} = \&mp_db::disconnectDB;
$SIG{STOP} = \&mp_db::disconnectDB;
$SIG{__DIE__} = \&mp_db::disconnectDB;

# -------
# Forking
# -------
use Errno qw(EAGAIN);
use POSIX ":sys_wait_h";
local $SIG{CHLD} = 'IGNORE';

my $forkNumber = 0; # number of of forked processes.
my %childProcesses; # hash of process id's

# small script to test database connection

# --------------------------------------------------------
# MAIN is a bare block to localise the scope of variables.
# --------------------------------------------------------
MAIN:
{
# create database object
my $db_obj = mp_db::->new;

# parent connect to database
print STDOUT "Parent connecting to database\n";
$db_obj->connectDB();

# parent disconnect from datase
print STDOUT "Parent disconnecting from database\n";
$db_obj->disconnectDB();

# Now do the fork to spawn the child process
my $pid = 0;
for ($i=0;$i<3;$i++)
{
# fork 2 child processes that connect to database
FORK:
{
# Parent process if -fork- returns a non-zero child PID
if ($pid = fork)
{
$childProcesses{$pid} = 0;
}
# Child process if -fork- returns zero; call -getppid- for parent PID
elsif (defined $pid)
{
# We must restore the default CHLD handler here, otherwise nested -system-
# calls will fail due to their own children being reaped automatically
local $SIG{CHLD} = 'DEFAULT';

print STDOUT "Child process <$i>....\n";

# create database object
my $db_obj = mp_db::->new;

# Connect child to database
print STDOUT "Child connecting to database\n";
$db_obj->connectDB();

# do something or other
if (0 == $i)
{
print "Sleeping...";
sleep 10;
print "Awakening...";
}

dummyChildFunction($db_obj);

# End child's database connection
print STDOUT "Child disconnecting from database\n";
$db_obj->disconnectDB();

# End of child process
print STDOUT "Exiting child process <$i>....\n";
exit;
}
# Otherwise, i.e. if $pid is undefined, we try again after a short period of relaxation
elsif ($! == EAGAIN)
{
print STDOUT "EAGAIN: recoverable fork error...\n";
sleep 5;
redo FORK;
}
}
} # end for

# create database object
$db_obj = mp_db::->new;

# parent connect to database
print STDOUT "Parent connecting to database\n";
$db_obj->connectDB();

# parent disconnect from datase
print STDOUT "Parent disconnecting from database\n";
$db_obj->disconnectDB();
}

sub dummyChildFunction
{
my ($db_obj) = @_;
print STDOUT "Inside dummyChildFunction\n";
$db_obj->getListOfMonitoredDirectories();
}

-------------------------------------------------------

#!/bin/perl

package mp_db;

use Env;
use DBI;

#$ENV{'ORACLE_HOME'}="/data/null";

# constructor
sub new
{
print STDOUT "mp_db::constructor called\n";
my $self = {
'databaseHandle' => 0,
'connected' => 0
};

return bless $self;
}

# Connect to the database using values from the $config hash reference
sub connectDB
{
my $invocant = shift;
print STDOUT "mp_db::connectDB called\n";

my $pagdb_tns = "********";
my $pagdb_connect = "dbi:Oracle:host=*******;sid=*******;port=****";
my $pagdb_user = "*********";
my $pagdb_password = "*******";

$invocant->{'databaseHandle'} = DBI->connect($pagdb_connect, $pagdb_user, $pagdb_password, {'privateid'=>rand()}) or die "
\nFailed to connect to database $!";
$invocant->{'connected'} = 1;
print STDOUT "mp_db::connectDB databaseHandle is $invocant->{'databaseHandle'}\n";

return $invocant->{'connected'};
}

# -----------------------------------------------------------------
# Name:
# getListOfMonitoredDirectories
#
# Purpose:
# gets list of directories, that are to be monitored for incoming
# files, from the database.
#
#
# Parameters:
# none
#
# Returns:
# list of directories (full path including root directory)
# -----------------------------------------------------------------
sub getListOfMonitoredDirectories
{
my $invocant = shift;
print STDOUT "Getting list of monitored directories from the database\n" if $verbose;

my $statementHandle = $invocant->{'databaseHandle'}->prepare("Select DIRECTORY from COMPILETYPE_STATES WHERE ISPROCESS = 0
AND ISERROR = 0");
$statementHandle->execute() || die $invocant->{'databaseHandle'}->errstr;

my @data;
my @returnArray;

while (@data = $statementHandle->fetchrow_array())
{
print STDOUT "Found $data[0]\n";
my $fullDirPath = "${ROOT_DIRECTORY}${data[0]}";
push(@returnArray, $fullDirPath);
}

if ($statementHandle->rows == 0)
{
print STDOUT "ERROR: Unable to get list of monitored directories from the database\n" if $verbose;
$log->LogErr($Logging::error, "Unable to get list of monitored directories from the database");
exit;
}

$statementHandle->finish;

return @returnArray;
}

#####################################################################
# Name : disconnectDB
# Function :
# Parameters :
# Warning :
# Returns :
# Notes :
#####################################################################
sub disconnectDB
{
my $invocant = shift;

if ( $invocant->{'connected'} == 1) {
$invocant->{'connected'} = 0;
#DBI->disconnect_all();
print STDOUT "mp_db::disconnectDB: disconnecting: $invocant->{'databaseHandle'}>\n";
$invocant->{'databaseHandle'}->disconnect;
}
}

#####################################################################
# Name : handlers
# Function :
# Parameters :
# Warning :
# Returns :
# Notes : ensures the database is disconnected on termination
#####################################################################


1;

 

[travs69]

Just glancing at your code lit ooks okay... can you cut it down to the bare essentials having problems though? Does our DB allow multiple simultanous connections for the same user? (this is configurable on most DBs so I don't know if your the DBA or not).

 

[Vovin]

Hi Travs69,

thanks for the suggestions. I'm not a DBA but the DB does allow multiple connections. At the moment I'm looking at my forked children calling "system" and executing an external program to do any database connections that I need. It's a horrible messy solution though and it still doesn't seem to be working.

 

[travs69]

I do the same thing as you, I fork my children and each has it's own connection to the DB. I have no problems now.. but when I started doing it the default user the DBA created for me was limited to a max of 2 connections at one time. So my script would die for no reason and I couldn't figure out why