Cleaning up the CiviMail event queue

[netvigator]

There was a problem with the last optimize statement (missing ",").

I was asked to rework the script to remove records older than a certain date; here it is (with the optimize problem corrected):

Code: [Select]

use <database_name>;

create temporary table temp_mail_job_ids
select id as job_id, mailing_id
from civicrm_mailing_job
where start_date < date '2009-01-01' ;

create index temp_job_id     on temp_mail_job_ids ( job_id     ) ;
create index temp_mailing_id on temp_mail_job_ids ( mailing_id ) ;

delete from civicrm_mailing_trackable_url
where mailing_id in ( select mailing_id from temp_mail_job_ids ) ;

delete from civicrm_mailing_group
where mailing_id in ( select mailing_id from temp_mail_job_ids ) ;

delete from civicrm_mailing_event_unsubscribe
where time_stamp < date '2009-01-01' ;

delete from civicrm_mailing_event_trackable_url_open
where time_stamp < date '2009-01-01' ;

delete from civicrm_mailing_event_reply
where time_stamp < date '2009-01-01' ;

delete from civicrm_mailing_event_queue
where job_id in ( select job_id from temp_mail_job_ids ) ;

delete from civicrm_mailing_event_opened
where time_stamp < date '2009-01-01' ;

delete from civicrm_mailing_event_delivered
where time_stamp < date '2009-01-01' ;

delete from civicrm_mailing_event_bounce
where time_stamp < date '2009-01-01' ;

delete from civicrm_mailing
where id in ( select mailing_id from temp_mail_job_ids ) ;

delete from civicrm_activity_history
where activity_type = 'Email sent' and
      activity_id in ( select mailing_id from temp_mail_job_ids ) ;

delete from civicrm_mailing_job
where id in ( select job_id from temp_mail_job_ids ) ;

optimize table civicrm_activity_history ;
optimize table civicrm_mailing_event_delivered ;
optimize table civicrm_mailing_event_bounce ;
optimize table civicrm_mailing ;
optimize table civicrm_mailing_event_opened ;
optimize table civicrm_mailing_event_queue ;
optimize table civicrm_mailing_event_reply ;
optimize table civicrm_mailing_event_trackable_url_open ;
optimize table civicrm_mailing_job ;
optimize table civicrm_mailing_trackable_url ;
optimize table civicrm_mailing_group ;
optimize table civicrm_mailing_event_unsubscribe ;

If you want to remove records older than a different date, do a find/replace and put in the date you want.  Note that the date is in ANSI format, YYYY-MM-DD.

[netvigator]

I have done an about face, and I am now recommending that we archive the old email records before deleting them.  I had always maintained that there should be some way to access the old emailings "just in case", but my proposal for doing that got shot down.

I am proposing that we keep the archive tables in the same database -- that way, they are less likely to get lost.

There is a separate script for creating the archive tables, as that only needs to be run once (not every time we want to purge old emails).

Code: [Select]

use <database_name> ;

create table z_archive_activity_history
   select * from civicrm_activity_history limit 5 ;

create table z_archive_email_history
   select * from civicrm_email_history limit 5 ;

create table z_archive_mailing
   select * from civicrm_mailing limit 5 ;

create table z_archive_mailing_event_bounce
   select * from civicrm_mailing_event_bounce limit 5 ;

create table z_archive_mailing_event_delivered
   select * from civicrm_mailing_event_delivered limit 5 ;

create table z_archive_mailing_event_opened
   select * from civicrm_mailing_event_opened limit 5 ;

create table z_archive_mailing_event_queue
   select * from civicrm_mailing_event_queue limit 5 ;

create table z_archive_mailing_event_reply
   select * from civicrm_mailing_event_reply limit 5 ;

create table z_archive_mailing_event_trackable_url_open
   select * from civicrm_mailing_event_trackable_url_open limit 5 ;

create table z_archive_mailing_event_unsubscribe
   select * from civicrm_mailing_event_unsubscribe limit 5 ;

create table z_archive_mailing_group
   select * from civicrm_mailing_group limit 5 ;

create table z_archive_mailing_job
   select * from civicrm_mailing_job limit 5 ;

create table z_archive_mailing_trackable_url
   select * from civicrm_mailing_trackable_url limit 5 ;

truncate table z_archive_activity_history ;
truncate table z_archive_email_history ;
truncate table z_archive_mailing ;
truncate table z_archive_mailing_event_bounce ;
truncate table z_archive_mailing_event_delivered ;
truncate table z_archive_mailing_event_opened ;
truncate table z_archive_mailing_event_queue ;
truncate table z_archive_mailing_event_reply ;
truncate table z_archive_mailing_event_trackable_url_open ;
truncate table z_archive_mailing_event_unsubscribe ;
truncate table z_archive_mailing_group ;
truncate table z_archive_mailing_job ;
truncate table z_archive_mailing_trackable_url ;

alter table z_archive_activity_history                  engine = archive ;
alter table z_archive_email_history                     engine = archive ;
alter table z_archive_mailing                           engine = archive ;
alter table z_archive_mailing_event_bounce              engine = archive ;
alter table z_archive_mailing_event_delivered           engine = archive ;
alter table z_archive_mailing_event_opened              engine = archive ;
alter table z_archive_mailing_event_queue               engine = archive ;
alter table z_archive_mailing_event_reply               engine = archive ;
alter table z_archive_mailing_event_trackable_url_open  engine = archive ;
alter table z_archive_mailing_event_unsubscribe         engine = archive ;
alter table z_archive_mailing_group                     engine = archive ;
alter table z_archive_mailing_job                       engine = archive ;
alter table z_archive_mailing_trackable_url             engine = archive ;

I prepended a "z" to each archive table name, so they will show up at the bottom of table listings.

Here is the script to archive and delete the old records:

Code: [Select]

use <database_name> ;

create temporary table temp_mail_job_ids
select id as job_id, mailing_id
from civicrm_mailing_job
where start_date < date '2009-01-01' ;

create index temp_job_id     on temp_mail_job_ids ( job_id     ) ;
create index temp_mailing_id on temp_mail_job_ids ( mailing_id ) ;

start transaction ;

insert into z_archive_activity_history
  select * from civicrm_activity_history
    where activity_type = 'Email sent' and
          activity_id in ( select mailing_id from temp_mail_job_ids ) ;

delete from civicrm_activity_history
where activity_type = 'Email sent' and
      activity_id in ( select mailing_id from temp_mail_job_ids ) ;

commit ;

start transaction ;

insert into z_archive_email_history
  select * from civicrm_email_history
    where sent_date  < date '2009-01-01' ;

delete from civicrm_email_history
    where sent_date  < date '2009-01-01' ;

commit ;

start transaction ;

insert into z_archive_mailing
  select * from civicrm_mailing
    where id in ( select mailing_id from temp_mail_job_ids ) ;
   
delete from civicrm_mailing
where id in ( select mailing_id from temp_mail_job_ids ) ;

commit ;

start transaction ;

insert into z_archive_mailing_event_bounce
  select * from civicrm_mailing_event_bounce
    where time_stamp < date '2009-01-01' ;
   
delete from civicrm_mailing_event_bounce
where time_stamp < date '2009-01-01' ;

commit ;

start transaction ;

insert into z_archive_mailing_event_delivered
  select * from civicrm_mailing_event_delivered
    where time_stamp < date '2009-01-01' ;
   
delete from civicrm_mailing_event_delivered
    where time_stamp < date '2009-01-01' ;

commit ;

start transaction ;

insert into z_archive_mailing_event_opened
  select * from civicrm_mailing_event_opened
    where time_stamp < date '2009-01-01' ;

delete from civicrm_mailing_event_opened
    where time_stamp < date '2009-01-01' ;

commit ;

start transaction ;

insert into z_archive_mailing_event_queue
  select * from civicrm_mailing_event_queue
    where job_id in ( select job_id from temp_mail_job_ids ) ;
   
delete from civicrm_mailing_event_queue
    where job_id in ( select job_id from temp_mail_job_ids ) ;

commit ;

start transaction ;

insert into z_archive_mailing_event_reply
  select * from civicrm_mailing_event_reply
    where time_stamp < date '2009-01-01' ;
   
delete from civicrm_mailing_event_reply
    where time_stamp < date '2009-01-01' ;

commit ;

start transaction ;

insert into z_archive_mailing_event_trackable_url_open
  select * from civicrm_mailing_event_trackable_url_open
    where time_stamp < date '2009-01-01' ;
   
delete from civicrm_mailing_event_trackable_url_open
    where time_stamp < date '2009-01-01' ;

commit ;

start transaction ;

insert into z_archive_mailing_event_unsubscribe
  select * from civicrm_mailing_event_unsubscribe
    where time_stamp < date '2009-01-01' ;

delete from civicrm_mailing_event_unsubscribe
where time_stamp < date '2009-01-01' ;

commit ;

start transaction ;

insert into z_archive_mailing_group
  select * from civicrm_mailing_group
    where mailing_id in ( select mailing_id from temp_mail_job_ids ) ;
   
delete from civicrm_mailing_group
    where mailing_id in ( select mailing_id from temp_mail_job_ids ) ;

commit ;

start transaction ;

insert into z_archive_mailing_trackable_url
  select * from civicrm_mailing_trackable_url
    where mailing_id in ( select mailing_id from temp_mail_job_ids ) ;
   
delete from civicrm_mailing_trackable_url
    where mailing_id in ( select mailing_id from temp_mail_job_ids ) ;

commit ;

; z_archive_mailing_job must come after most other tables
; cuz it is the source of the temp table IDs

start transaction ;

insert into z_archive_mailing_job
  select * from civicrm_mailing_job
    where id in ( select job_id from temp_mail_job_ids ) ;
   
delete from civicrm_mailing_job
    where id in ( select job_id from temp_mail_job_ids ) ;

commit ;

optimize table civicrm_activity_history ;
optimize table civicrm_email_history ;
optimize table civicrm_mailing ;
optimize table civicrm_mailing_event_bounce ;
optimize table civicrm_mailing_event_delivered ;
optimize table civicrm_mailing_event_opened ;
optimize table civicrm_mailing_event_queue ;
optimize table civicrm_mailing_event_reply ;
optimize table civicrm_mailing_event_trackable_url_open ;
optimize table civicrm_mailing_event_unsubscribe ;
optimize table civicrm_mailing_group ;
optimize table civicrm_mailing_job ;
optimize table civicrm_mailing_trackable_url ;

This approach preserves full CiviMail functionality for recent emails (such as looking them up and getting reports), while clearing old email info from the main tables.

So if we ever needed to access purged emails, we would do this on a copy of the live database: append the rows in the archive tables back into the main tables.