By jwatte on

I have some code that is loaded as a custom mail sending handler. The idea is to get around the "web server can't send e-mail" rule of SourceForge.net, for user registrations. The mail sending handler gets called with some data for e-mails (I test this with the contact page), but the db_query() fails. When I attempt to write the query to a readable file, the mysql error string is empty.

How do I debug this? (Code follows)

/*
CREATE TABLE 'outgoing_mail' (
'mailkey' VARCHAR( 100 ) NOT NULL ,
'mailto' VARCHAR( 100 ) NOT NULL ,
'subject' VARCHAR( 100 ) NOT NULL ,
'body' TEXT NOT NULL ,
'from' VARCHAR( 100 ) NOT NULL ,
'headers' TEXT NOT NULL ,
'inserttime' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
'sendtime' TIMESTAMP NULL ,
'id' INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
INDEX ( 'sendtime' )
) ENGINE = MYISAM ;
 */
function drupal_mail_wrapper($mailkey, $to, $subject, $body, $from, $headers) {
  db_set_active();
  if (!db_query("insert into outgoing_mail values('%s', '%s', '%s', '%s', '%s', '%s')",
    $mailkey, $to, $subject, $body, $from, $headers)) {
    $f = fopen("/tmp/persistent/kwxport-files/error-$mailkey", "wb");
    fwrite($f, "Error to: $to\n".mysql_error($active_db)."\n");
    fclose($f);
  }
  return true;
}

function drupal_send_mail() {
  include_once "./sites/default/settings.php";
  include_once "./includes/bootstrap.inc";
  include_once "./includes/database.inc";
  $user = getenv("USER");
  $host = getenv("HOSTNAME");
  db_set_active();
  $r = db_query("begin transaction;");
  $r = db_query("select * from outgoing_mail where sendtime is NULL;");
  while ($mail = db_fetch_object($r)) {
    mail($mail['mailto'], $mail['subject'], $mail['body'], "From: $user@$host\r\n".$mail['headers']);
  }
  $r = db_query("update outgoing_mail set sendtime = current_timestamp where sendtime is NULL;");
  $r = db_query("commit transaction;");
}

function main($argv) {
  drupal_send_mail();
}

Note that the other functions are for actually sending the queued mail, to be called from a host where mail sending is allowed. My problem right now, though, is that drupal_mail_wrapper doesn't manage to insert anything into the table.

⋅ Categories: Drupal 5.x

Comments

dropcube’s picture

dropcube commented

Devel is a module containing helper functions for Drupal developers and admins. This module prints out a summary of all database queries for each page request at the bottom of each page. The summary includes how many times each query was executed on a page, and how long each query.

This module provides a function db_queryd , debugging version of db_query() that echoes the query to the browser. Also a dprint_r($array) function is provided, which pretty prints arrays. Useful during development. Similarly, a ddebug_backtrace is offerred.

Good luck.

cog.rusty’s picture

cog.rusty commented

I think the INSERT INTO table ('value1', 'value2', 'value3' etc) syntax requires to supply values to *all* the db fields.

Try INSERT INTO table SET field1='value1' etc instead, to see what happens.

jwatte’s picture

jwatte commented

I have now debugged my code; thanks for all the helpful suggestions! Other things to look out for, for reference:

- look in the log for errors

- ISAM doesn't support transactions, but will come back as a "syntax error" from MySQL

Here is the code that I ended up with. Hopefully, this will help someone else trying to get mail out to work from a sourceforge hosting account.

/*
CREATE TABLE 'outgoing_mail' (
'mailkey' VARCHAR( 100 ) NOT NULL ,
'mailto' VARCHAR( 100 ) NOT NULL ,
'subject' VARCHAR( 100 ) NOT NULL ,
'body' TEXT NOT NULL ,
'from' VARCHAR( 100 ) NOT NULL ,
'headers' TEXT NOT NULL ,
'inserttime' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
'sendtime' TIMESTAMP NULL ,
'id' INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
INDEX ( 'sendtime' )
) ENGINE = InnoDB ;
 */
function drupal_mail_wrapper($mailkey, $to, $subject, $body, $from, $headers) {
  global $active_db;
  db_set_active();
  if (!db_query("insert into outgoing_mail(mailkey, mailto, subject, body, mailfrom, headers) values('%s', '%s', '%s', '%s', '%s', '%s')",
      $mailkey, $to, $subject, $body, $from, join("\r\n", $headers))) {
    return false;
  }
  return true;
}

function drupal_send_mail() {
  if (!isset($_SERVER)) {
    $_SERVER = array();
  }
  if (!isset($_SERVER['HTTP_HOST'])) {
    $_SERVER['HTTP_HOST'] = getenv("HTTP_HOST");
  }
  if (!isset($_SERVER['SCRIPT_FILENAME'])) {
    $_SERVER['SCRIPT_FILENAME'] = getenv("SCRIPT_FILENAME");
  }
  require_once './includes/bootstrap.inc';
  drupal_bootstrap(DRUPAL_BOOTSTRAP_DATABASE);
  $user = getenv("USER");
  $host = getenv("HOSTNAME");
  db_set_active();
  $r = db_query("begin;");
  if (!$r) {
    print("mysql error: ".mysql_error()."\n");
  }
  $r = db_query("select * from outgoing_mail where sendtime IS NULL;");
  if (!$r) {
    print("mysql error: ".mysql_error()."\n");
  }
  while ($mail = db_fetch_object($r)) {
    print("Sending $mail->mailkey to $mail->mailto\n");
    mail($mail->mailto, $mail->subject, $mail->body, "From: $user@$host\r\n");
  }
  $r = db_query("update outgoing_mail set sendtime = current_timestamp where sendtime is NULL;");
  if (!$r) {
    print("mysql error: ".mysql_error()."\n");
  }
  $r = db_query("commit;");
  if (!$r) {
    print("mysql error: ".mysql_error()."\n");
  }
}

$cmd = @$argv;
if (isset($cmd)) {
  $cmd = $argv[1];
}
if (!isset($cmd)) {
  $cmd = getenv("MYSQL_MAIL_CMD");
}
if (isset($cmd) && $cmd == "--send") {
  drupal_send_mail();
}

This script is both installed as the plug-in mailer for Drupal, and is invoked (from a shell host where outgoing mail works) using cron once an hour, with the command --send to actually send the mail. Turns out that I can't get the argv to work when called from cron (but it works from command line), hence the getenv on the MYSQL_MAIL_CMD environment variable, which I can set.

Thanks for the help, and I hope this helps someone else with the same sourceforge mail problems.