I'm trying to periodically send email notifications from a cron hook with notifications_lite_send(). When multiple notifications are sent to the same user during the same cron run I get this warning:

user warning: Duplicate entry '1-0-mail' for key 'PRIMARY' query: INSERT INTO notifications_sent(uid, send_interval, send_method, sent) VALUES(1, '0', 'mail', 1283131036) in /etc/drupal/all/modules/notifications/notifications.cron.inc on line 386.

Looking at the emails, I can see that both were sent during the same second (1283131036).

The warning is being triggered here:

function notifications_update_sent($account, $method, $interval, $time) {
db_query("UPDATE {notifications_sent} SET sent = %d WHERE uid = %d AND send_interval = '%d' AND send_method = '%s'", $time, $account->uid, $interval, $method);
if (!db_affected_rows()) {
db_query("INSERT INTO {notifications_sent}(uid, send_interval, send_method, sent) VALUES(%d, '%d', '%s', %d)", $account->uid, $interval, $method, $time);
}
}

I think what's happening is that when the second message is sent the UPDATE query doesn't affect any rows because all four fields are the same, so db_affected_rows() returns FALSE. The code wrongly assumes that the row doesn't exist, and attempts an INSERT.

CommentFileSizeAuthor
#1 notifications-897018-avoid-double-insert.patch832 bytesjgraham

Comments

jgraham’s picture

jgraham commented
Version: 6.x-2.2 » 6.x-2.3
StatusFileSize
new notifications-897018-avoid-double-insert.patch832 bytes

I was experiencing the exact same issue and can confirm. We are batch updating workflow states for nodes in a book, when this is triggered there are several edits on nodes that may have the same timestamp as a result the UPDATE does not actually change the row since as mwm indicates all values to that query are the same as the existing row.

As a result db_affected_rows() is an inappropriate call here.

Attached is a patch that changes that call to a simple SELECT to determine if we need to do an INSERT.

ducdebreme’s picture

ducdebreme commented

subscribing

ravi.kumar’s picture

ravi.kumar commented

Hi,
I was having the same issue previously and was sending mail every hour prior to the send interval daily as set by user. So the issue in my case was table structure, please check your table structure and fix the primary keys for the tables as in my case it was "mdid and send_interval", I changed this to "uid, send_interval, send_method" and this solved my issue.

AgentJay’s picture

AgentJay commented

Hello,

I have this warning as well. However I'm not so good at databases... I do have phpmyadmin installed. How could I go about resolving the error?

The way I understand it so far is that I' need to change the primary keys from mdid and send_interval to uid, send_interval, and send_method?

Thanks.

ravi.kumar’s picture

ravi.kumar commented

Hi,

Yes you need to change primary key to uid, send_interval and send method, because in this send interval and mdid might be same.

Thanks
Ravi Kumar

eiland’s picture

eiland commented

From which table exactly?

I haver notifications, notifications_event, notifications_fields, notifications_queue and notifications_sent, but none have primary keys....