Manitou-Mail Wiki

Before 0.9.10, there were no referential integrity constraints in the schema. The reason was mainly that most RI constraints refer to a message and messages were split between the mail and trashed_mail tables (as opposed to one table only), thus making simple RI constraints impractical. In 0.9.10, those tables are unified so RI constraints are enabled.

For the 0.9.10 upgrade script to succeed on an existing Manitou-Mail installation, these constraints must be satisfied beforehand. In principle that should be the case, but improper manual deletes or malfunctions may have introduced dangling references. The SQL fragments below can be used to check and delete the rows that refer to non-existing messages, adresses, or attachments. Check and delete are separated so that the rows to delete may be examined and moved elsewhere before delete when there is some value in keeping them.

Check entries in mail_addresses that refer to deleted messages

SELECT ma.* FROM mail_addresses ma LEFT JOIN mail m ON ma.mail_id=m.mail_id WHERE m.mail_id IS NULL;

Delete entries in mail_addresses that refer to deleted messages

DELETE FROM mail_addresses WHERE mail_id IN
(SELECT ma.mail_id FROM mail_addresses ma LEFT JOIN mail m ON ma.mail_id=m.mail_id WHERE m.mail_id IS NULL);

Check entries in mail_addresses that refer to deleted addresses

SELECT ma.* FROM mail_addresses ma LEFT JOIN addresses a ON ma.addr_id=a.addr_id WHERE a.addr_id IS NULL;

Delete entries from mail_addresses that refer to deleted addresses

DELETE FROM mail_addresses WHERE addr_id IN
(SELECT ma.addr_id FROM mail_addresses ma LEFT JOIN addresses a ON ma.addr_id=a.addr_id WHERE a.addr_id IS NULL);

Check entries in body that refer to deleted messages

SELECT b.mail_id FROM body b LEFT JOIN mail m ON b.mail_id=m.mail_id WHERE m.mail_id IS NULL;

Delete entries from body that refer to deleted messages

DELETE FROM body WHERE mail_id IN 
 (SELECT b.mail_id FROM body b LEFT JOIN mail m ON b.mail_id=m.mail_id WHERE m.mail_id IS NULL);

Check entries in header that refer to deleted messages

SELECT h.mail_id FROM header h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL;

Delete entries from header that refer to deleted messages

DELETE FROM header WHERE mail_id IN 
 (SELECT h.mail_id FROM header h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL);

Check entries in notes that refer to deleted messages

SELECT h.mail_id FROM notes h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL;

Delete entries from notes that refer to deleted messages

DELETE FROM notes WHERE mail_id IN 
 (SELECT h.mail_id FROM notes h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL);

Check entries in raw_mail that refer to deleted messages

SELECT h.mail_id FROM raw_mail h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL;

Delete entries from raw_mail that refer to deleted messages

DELETE FROM raw_mail WHERE mail_id IN 
 (SELECT h.mail_id FROM notes h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL);

Note that raw_mail contain references to large objects, which are not automatically purged on delete. vacuumlo can be used to purge large objects that are no longer referenced.