Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

writing a bulletproof perl daemon with persistant mysql connection

Time is of the essence while sending "report" data back to a mysql database over the internet.  My goal is to write a bulletproof daemon that handles a constant connection to this database.

My server daemon pre-forks "worker" children to do time consuming work, and pre-forks a "reporting" child to handle sending reports from the "workers" back to the database.

In the "reporting" child I have a 60 second alarm signal inside of an eval{} statement that encloses the mysql database work.  No database work should take longer than a few seconds.  After a day or two of uptime, the "reporting" child hangs for approx 1 hour or more where nothing takes place and then the "reporting" child dies because of the alarm signal.  The eval{} statement is unable to trap the die().

I have 3 of these daemons running on different machines across the internet, and while 1 is hanging, 2 of the 3 are still able to report to the mysql database just fine.

Here is my code:

my $dbh;
RECONNECT:
eval {
      my $dsn = "DBI:$driver:database=$database;host=$hostname;mysql_connect_timeout=10";
      $dbh = DBI->connect($dsn, $username, $password, { RaiseError => 1, PrintError => 0, AutoCommit => 1 });
      $dbh->{mysql_auto_reconnect} = 1;
};
if( $@ ) {
      goto RECONNECT;
}

while( 1 ) {
      eval {
            #      Setup ALRM handler signal
            local $SIG{ALRM} = sub { die "timeout\n" };
            alarm( 60 );

            $dbh->do("INSERT INTO table_name VALUES ( data, ... )");

            alarm( 0 );
      };
      if( $@ ) {
            if( $@ =~ /timeout/ ) {
                  print "Timed out\n";
            } else {
                  alarm( 0 );
            }
      }
}

When bad things happen, how would you go about setting up a daemon that has the best possible connection with the mysql server?
0
rivusglobal
Asked:
rivusglobal
1 Solution
 
NovaDenizenCommented:
Maybe you should just open a connection when you need to send a report in, then close it right after.  That way you don't have to depend on auto-reconnection behavior.  If you can deal with a 60-second lag before your alarm, the second or two it takes to connect every time should be trivial.

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now