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.
emailfacebooktwittermessengersteamxboxliveretroburn
Tags
2013 3d alphalabs amazon apple archivirtual asynchronous battlefield bad company 2 ben 10 bepu beta blackmagic design blog blue marble bootcamp borderlands bsp calibration charity charvel childsplay comments competition content tracker counter-strike crash csgo css3 cycling dear esther deferred deus ex develop conference direct x discipline documentation doom 3 bfg dpi dr bott eidos elite force email deliverability eurogamer expo facebook focus fresnel game development game horizon game republic gamedev games gaming geoip girls make games global offensive grid guitar half-life 2 hawken hd7 hobbyist htc humble indie bundle imac indie indie trials indietrials intensity pro ip-countryside iron man 3 jamulus rift jquery kids kinect launch conference left 4 dead live lost mac mac osx manchester manhacks mass effect 2 matrox maya minecraft mirrors edge montreal morrowind movies museum of the microstar music mxo2 mini mysql nausea network networking nokia normal mapping obj oculus rift omnitrix ouya pedal for pounds php physics playstation suite port25 portal portal 2 positron posters powermta project aedra project euler promotion properties proton pulse ps vita ps4 psn racer reddit rendering retroburn game studios reviews rift racer riftracer roadkill roller coaster sdl2 shadow racers sharks shoct skyrifters snds space cadet spam trap star trek steam stencyl storage super stock sd1 fr superhot team fortress 2 tesselating tesselation texture editor thunderbird thunderclap ticktock games tiga track builder track bulder trials tv twitter uk ultimatrix usergroup vequencer video vireio visual assist visual studio vorpx voucher vr vr cinema war thunder warren web willow windows 8 windows 8.1 windows phone 7 workbench wp7 wp7dev xbla xblig xblig network xbox xbox live indie games xna xnaukug xperia play zombies on the holodeck
Archive
Links
Web
XNA
Games
Email Deliverability
Friday, June 24th 2011 / Blog

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$fpserialize$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?
6