Using MySQL persistently with plugin

[Robin Bowes]

Hi,

I'm writing an "accepted" plugin using the queue_post hook recently added.

I'm doing something like this:

=====================================================
sub init {
    my ( $self, $qp ) = @_;

    use DBI;
    my $dsn='DBI:mysql:database=qpsmtpd;host=localhost';
    my $dbuser='qpsmtpd';
    my $dbpass='password';
    $self->{_dbh} = DBI->connect(
                        $dsn,
                        $dbuser,
                        $dbpass,
                        {raiseError => 1, AutoCommit => 1});
    $self->{_dbh}->{mysql_auto_reconnect} = 1;
    $self->{_sth} =
        $self->{_dbh}->prepare("INSERT INTO
accepted(host,msgid,date,subject,sender,recipients) VALUES (?,?,?,?,?,?)");
}

sub hook_queue_post {
    my ($self, $transaction) = @_;

    my $headers = $transaction->header();
    my $sth = $self->{_sth};
    $sth->execute(
        $self->qp->{_config_cache}->{me}[0],
        $headers->get('Message-Id') || '',
        $headers->get('Date') || '',
        $headers->get('Subject') || '',
        $transaction->sender->format,
        join(';', $transaction->recipients())
    ) or warn ("Error writing record: " . $sth->errstr);

    return (DECLINED);
}
=====================================================

The idea is, I create the MySQL connection + prepare the statement
handle just once in init() so I only need to execute the prepared
statement in the queue_post hook.

This works for the first message received, but not for subsequent messages.

Any idea why? Is my concept flawed, i.e. is it not possible to maintain
persistent DB connections between messages?

Thanks,

R.