Developers of Sympa

[David Verdin <address@concealed>]

Hi Kristina,

Here is the query we use to select the Messages in spool that are note 
referenced by any packet, i.e. for which the sending was done.

SELECT messagekey_bulkspool AS messagekey FROM bulkspool_table LEFT JOIN 
bulkmailer_table ON messagekey_bulkspool = messagekey_bulkmailer WHERE 
messagekey_bulkmailer IS NULL AND lock_bulkspool = 0

You can delete all the messages whose key is returned by this query.

Another option is to stop sympa.pl, so that no new message is stored in 
the bulkspool_table table, wait for the bulkmailer_table table to empty 
(it is done by bulk.pl) and then delete all the content of the 
bulkspool_table.

Regards,

David

Le 13/04/2010 17:57, Kristina Clair a écrit :
> hi all,
>
> i discovered today that we have a really big problem with the
> bulkspool_table.  the table is currently using 1.7G disk space, with
> 58,000+ rows.  i evaluated this data, and most of the messages in there
> are between one to three weeks old.  i applied the patch from a while back
> when it was released which changes the quotes used when deleting rows from
> the table.
>
> i'm wondering how best to proceed. i manually checked a handful of the
> messages in there, and they appear in the list archives, so they seem to
> have been sent.
>
> i think the issue could have been caused by some problems we had with
> mysql. at this point i'm mostly interested in dealing with the data
> currently in the table, before looking at why it happened in the first
> place.
>
> is there a safe way i can purge this table?  is there a sanity check i can
> do to make sure that no unsent messages get removed from the table?  there
> have been no rows in the bulkmailer_table when i've checked today.
>
> thanks!
> kristina
>
>    

--
David Verdin
Comité réseau des universités