I'm a professional game developer from Wakefield, England, working as a senior programmer for Rebellion North.

I'm a married father of five and I a also sometimes do Retroburn stuff.

Martin 'Bytrix' Caine

Father. C++ Games Programmer. Cyclist. Guitarist.

Store PowerMTA Accounting Data in a MySQL Database

I've been meaning for some time now to start sharing some of my PowerMTA work and share some of my experience working within the field of Email Deliverability but it keeps me so busy I have very little time to write anything up!

I see many people on the Port25 forums asking for scripts and programs to do simple little things and so I'm going to start sharing some of my own.

One script we used since pretty much day one with PowerMTA was an logging script to store the accounting data in a MySQL database. I believe this original script actually came from someone on the Port25 forums, however the scripts and programs I currently use for logging are quite different from what I'm presenting below. This should be used as a base for you to build your own tailored logging script.

The script makes use of the 'pipe' method for PowerMTA accounting. What this means, is that when PowerMTA starts (or is reloaded) it starts an instance of your script or program and begins piping accounting data to it via STDIN.

All your script has to do is connect to the database, then continue looping through each new line supplied via STDIN. Each new line is converted into a query and inserted into our MySQL database. Note that some fields are escaped before inserting. When I began using PowerMTA I found some entries were not being added to the database because they included quotes, as in the following example:

smtp;554 delivery error: dd This user doesn't have a yahoo.com account (xyz@yahoo.com)

The script below escapes all the fields which might contain characters which need to be escaped (my own script is slightly different so I've just rewritten this from memory using mine as a guide).



<?
// pmta2mysql.php | PowerMTA MySQL Logger
// Written by Martin Caine, 2011-06-24 (martin@martincaine.com)       
// ------------------------------------------------------------------ //

// Config
// ------------------------------------------------------------------ //
// In PowerMTA's logging file, add the following lines:               //
//                                                                    //
// <acct-file |/usr/bin/php /etc/pmta/pmta2mysql.php>                 //
//   records d,b,t #,tq,r                                             //
// </acct-file>                                                       //
//                                                                    //
// specify which types you want to pass in to your logs, but be aware //
// that the script below is written to take the fields supplied when  //
// d,b,t are specified.                                               //
// ------------------------------------------------------------------ //

$db_host = 'localhost';
$db_user = 'martin';
$db_pass = 'password';

$database = 'pmta';
$table = 'stats';

// connect to the database
$link = mysql_connect( $db_host, $db_user, $db_pass ) 
                or die( 'unable to connect to database' );
                
// note that if you can't connect you might want to defer output to a
// lof file instead of attempting to write to the database.

// continue reading each line from STDIN
while( ( $data = fgetcsv(STDIN)) !== FALSE )
{    
    // you might want to put another check for database connection here.
    // if !$link, reconnect.
    
    $sql = 'INSERT into `pmta`.`acct_today`
                    ( `type`,
                        `timeLogged`,
                        `timeQueued`,
                        `timeImprinted`,
                        
                        `orig`,
                        `rcpt`,
                        `orcpt`,
                        
                        `dsnAction`,
                        `dsnStatus`,
                        `dsnDiag`,
                        `dsnMta`,
                        
                        `bounceCat`,
                        
                        `srcType`,
                        `srcMta`,
                        
                        `dlvType`,
                        `dlvSource`,
                        `dlvDestination`,
                        `dlvEsmtpAvailable`,
                        `dlvSize`,
                        `vmta`,
                        `jobId`,
                        `envId` )
                    VALUES
                    ( "'.$data[0].'", 
                        "'.$data[1].'",
                        "'.$data[2].'",
                        "'.$data[3].'",
                        
                        "'.mysql_escape_string( $data[4] ).'",
                        "'.mysql_escape_string( $data[5] ).'",
                        "'.mysql_escape_string( $data[6] ).'",
                        
                        "'.$data[7].'",
                        "'.$data[8].'",
                        "'.mysql_escape_string( $data[9] ).'",
                        "'.mysql_escape_string( $data[10] ).'",
                        
                        "'.$data[11].'",
                        
                        "'.$data[12].'",
                        "'.mysql_escape_string( $data[13] ).'",
                        
                        "'.$data[14].'",
                        "'.$data[15].'",
                        "'.$data[16].'",
                        "'.$data[17].'",
                        "'.$data[18].'",
                        "'.$data[19].'",
                        "'.$data[20].'",
                        "'.$data[21].'" );';
    // insert into the database
    $qc = mysql_query( $sql, $link );
    
    // check for errors here, and log them in to a file for error checking.
    // most commonly you might find something isn't being escaped.
    if( mysql_error( $link ) )
    {
        $fp = fopen( 'errors.log', 'a' );
            fwrite( $fp, '--------'.mysql_error( $link )."\n" );
            fwrite( $fp, serialize( $data )."\n" );
        fclose( $fp );                                                        
    }
    
} // continue on to the next line of STDIN


// Notes
// ------------------------------------------------------------------ //
// This script momentarily seems to stop PowerMTA from sending while  //
// each line of STDIN is processed. Say if the table is locked and    // 
// the query to insert takes some time, you'll notice outbound email  //
// will drop until the script moves on to the next line of STDIN.     //
// ------------------------------------------------------------------ //
// I've had very few problems with this in the past, the only time    //
// we've ever really noticed is when running a large query on the     //
// live logging table.                                                //
// ------------------------------------------------------------------ //
?>

Please do let me know if you find this script useful or if you require assistance with setting up your PowerMTA server for logging to a database (my email address is in the script above).

If you found this post helpful please leave a comment below:

Comments

william / 2011-12-28 01:18:17

What does the schema look like? I have a horrible inefficient database design setup and would like to correct it. I can't see to find any information on max field lengths, etc.

Mike / 2013-05-09 00:16:24

I'm getting the following.

2013-05-08 23:21:22 Error writing to "/usr/bin/php /etc/pmta/accounting_pipe.php": IOException, status = EPIPE

Ernest / 2013-09-01 18:57:15

This kind of solutions slows very down sending of messages as You noticed in last code comments.

santu / 2014-02-11 08:33:28

cheap bulk email server setup with powerMTA setup
DKIM, SPF, RDNS configuration
Contcat : san2roy@gmail.com
Skype : san2roy

Bulk Email Marketing solution
Contcat : san2roy@gmail.com
Skype : san2roy

Cheap Dedicated server with multiple IP Address
Contcat : san2roy@gmail.com
Skype : san2roy

PowerMTA 4.0 Nulled For sale
Contcat : san2roy@gmail.com
Skype : san2roy

PowerMTA Server configuration
DKIM, SPF, RDNS configuration
Contcat : san2roy@gmail.com
Skype : san2roy

Robert / 2014-04-15 13:31:23

Nice; just one small thing: I'd rather give the full path to the errors log so that you know where that file ends up.

Pradip Barua / 2017-02-06 13:03:26

The following errors are showing while sending mail through pmta:
Error writing to "/usr/bin/php /etc/pmta/pmta2mysql.php": IOException, status = EPIPE

can you please highlight on that?

Tags: email deliverability mysql php port25 powermta