CiviCRM Community Forums (archive)

Hi,

We're using CiviMail for all our bulk mailing for a number of months now. We're very happy with the performance and the features the module offers. There is one problem though. Once in a while, a mailing stops working (and blocks the remaining scheduled jobs too).

When the system is in that state and i run the civimail cron job from the browser i get:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

=> -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_activity (source_contact_id , source_record_id , activity_type_id , subject , activity_date_time , status_id ) VALUES ( 145698 ,  241 ,  3 , 'Subject of the mailing' ,  20091209155307 ,  2 )  [nativecode=1205 ** Lock wait timeout exceeded; try restarting transaction]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_activity (source_contact_id , source_record_id , activity_type_id , subject , activity_date_time , status_id ) VALUES ( 145698 ,  241 ,  3 , 'Subject of the mailing' ,  20091209155307 ,  2 )  [nativecode=1205 ** Lock wait timeout exceeded; try restarting transaction]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_activity (source_contact_id , source_record_id , activity_type_id , subject , activity_date_time , status_id ) VALUES ( 145698 ,  241 ,  3 , 'Subject of the mailing' ,  20091209155307 ,  2 )  [nativecode=1205 ** Lock wait timeout exceeded; try restarting transaction]"]
)

Apparently, the  "Lock wait timeout exceeded; try restarting transaction" error indicates a InnoDB transaction that cannot obtain a row lock. What I've been doing until now is to restart MySQL server every time this happens. After the restart, the mailing resumes and everything works normal.

The problem occurs intermittent and does not seem to be related to the size of the mailing. Mailings to more than 50,000 contacts may succeed, while a mailing to 5000 contacts hits the error. I've been experimenting CiviMail configuration settings (civicrm/admin/mail?reset=1) without any luck.
 
I'm in doubt on what to do next. Any advice on what causes this problem and how it can be solved?

We're running CiviCRM 2.2.8 on drupal with MySQL 5.0.51a and Apache/2.2.9 (Debian) PHP/5.2.6-1+lenny3.

Thanks

Chris



I've been experimenting with the mailer settings since the error occured for the first time. Mainly focussing on different combinations of mail spool period and mail spool limit until i found out these seetings might not be implemented (see: http://forum.civicrm.org/index.php?topic=9499.0). Mailer batch limit was unlimited at first but i have been decreasing the parameter every time the error occured. Currently it is set to 1000. Civimail cronjob runs every 5 minutes.

I'll see if i can find a relation between the time or the number of mails sent since the last MySQL restart. I'll keep you posted.

Thanks for the help.