1 Topic by answerman

  • answerman
  • Member
  • Offline
  • Registered: 2011-01-07
  • Posts: 69

Topic: Completely wipe the quarantine?

==== Required information ====
- iRedMail version: 0.7.3
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Linux/BSD distribution name and version: CentOS 5.6
- Related log if you're reporting an issue:
====

Basically, I want to wipe the quarantine completely.  I am planning a long overdue upgrade and I am trying to tidy things up first.  The problem is that some time back I edited the iRedAdmin-PRO 1.3 interface to go directly to the domain list at login, and as such the quarantine cleanup that happens on the Dashboard screen hasn't been happening.  The Dashboard now takes almost a half hour to load, when it does load, and trying to remove anything from the quarantine results in a long wait followed by an error (1205, 'Lock wait timeout exceeded').  The tables amavisd.msgs and amavisd.quarantine are both huge, over 2GB in size, and amavisd.msgrcpt is almost 800MB, so I'm sure that's part of the problem.

So, I would like to just run the SQL query myself and bypass the iRedAdmin-PRO interface to start over.  I have command line access to the machine, but I can't find the query to run to delete all the messages.  I assume it's somewhere in the files in /var/www/iRedAdmin-Pro-MySQL-1.3.0/, but I haven't found it yet.  I assume also that the query needs to address all three tables, and I don't want to cause any orphan records between the tables.

So, what is the query run when the Dashboard loads?

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by answerman (edited by answerman 2014-01-31 06:58:27)

  • answerman
  • Member
  • Offline
  • Registered: 2011-01-07
  • Posts: 69

Re: Completely wipe the quarantine?

Right after I posted this question, I tried searching the forum again and found an answer (though it's for 0.8.6, not 0.7.3). 

Before I go ahead with this, I am looking for confirmation and clarification.

That post (http://www.iredmail.org/forum/topic5359 … login.html) contained the details of the script at /var/www/iredadmin/tools/cleanup_amavisd_db.py, which I don't have (it was maybe added in a later version?).   It also had an answer from Zhang stating that there is no harm in just deleting all the records in the quarantine table, which I have no problem with as I'm sure none of the messages are any good.

So, I took the liberty of copying that script and boiling it down into a few MySQL queries:

DELETE FROM msgs WHERE quar_type = 'Q' AND time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY));

DELETE msgrcpt.* FROM msgrcpt INNER JOIN msgs ON msgrcpt.mail_id=msgs.mail_id WHERE msgs.time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY));

DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY));

DELETE FROM msgrcpt WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE mail_id=msgrcpt.mail_id);

DELETE FROM quarantine WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE mail_id=quarantine.mail_id);

DELETE FROM maddr WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE sid=id) AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id);

Shouldn't I just be able to run these commands (assuming they don't time out on me) from the command line or phpMyAdmin?  And, if they do time out on me, could I just completely wipe the amavisd database to start over or at least these four (maddr, msgs, quarantine, and msgrcpt) tables?  I would think if I was going to do that, I should probably stop the amavisd service so it's not trying to access the database, right? 

And once done, just put the above commands into a cron job to run nightly?

Also, what happens to the messages themselves?  Where are they stored?  I want to make sure they get deleted too, not just the database records.

Ultimately, I want to get upgraded to the current version, so that I can just put these quarantined messages into the users' Junk folders (since I already have a cron job clearing any messages in the Junk folders that are more than 7 days old) and dispense with the quarantine completely.  It's a rather busy server with about 250 domains and about 900 users, processing about 12,000 messages a day.

3 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,790

Re: Completely wipe the quarantine?

*) The easiest way to wipe the quarantined emails is deleting all records in SQL table "amavisd.quarantine".
*) Quarantined emails are all stored in SQL table "amavisd.quarantine", so you don't need to check other places/folders.
*) After upgraded to the latest iRedAdmin-Pro, you can add a daily cron job to execute script 'tools/cleanup_amavisd_db.py' under iRedAdmin-Pro directory.

4 Reply by answerman

  • answerman
  • Member
  • Offline
  • Registered: 2011-01-07
  • Posts: 69

Re: Completely wipe the quarantine?

Thanks Zhang... I was looking for confirmation on just emptying the amavisd.quarantine table.  After I do that, do I still need to run the other SQL queries above (altering them as necessary) to delete the orphan records from the other three tables?  I was looking for confirmation that those SQL queries are valid for 0.7.3.

5 Reply by answerman

  • answerman
  • Member
  • Offline
  • Registered: 2011-01-07
  • Posts: 69

Re: Completely wipe the quarantine?

Alternatively, what would the effect of emptying the other three tables?  (maddr, msgs, and msgrcpt)  I don't really care about losing the statistics for sent and received messages etc.

6 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,790

Re: Completely wipe the quarantine?

If you don't care about statistics, it's ok to delete all records in "amavisd" database. After you upgraded iRedAdmin-Pro, you can add a daily cron job to delete old records with script shipped within iRedAdmin-Pro: tools/cleanup_amavisd_db.py.