Clean up script not working correctly...

...

I have 14,000 in my sql quarantine...

i run the clean up script each night at default levels....
it says clean 
up successful...

when i run manually i get this....

joe:/var/www/virtual/dido.ca/htdocs/MailZu/scripts#
./mz_db_clean.pl 
--verbose --database mysql
Connecting to SQL database server
Trying dsn 'DBI:mysql:database=mail;host=localhost'
Connection to 'DBI:mysql:database=mail;host=localhost'
succeeded
Executing... Tue Jul 17 09:12:31 2007
DELETE FROM msgs WHERE mail_id IN   (SELECT DISTINCT mail_id
   FROM 
msgrcpt WHERE rs='D')
4 rows affected
Executing... Tue Jul 17 09:12:32 2007
DELETE FROM msgs WHERE time_iso < UTC_TIMESTAMP() -
interval 1 hour  AND 
content IS NULL
0E0 rows affected
Executing... Tue Jul 17 09:12:33 2007
DELETE FROM msgs WHERE time_iso < UTC_TIMESTAMP() -
interval 1 week
0E0 rows affected
Executing... Tue Jul 17 09:12:33 2007
DELETE FROM maddr WHERE NOT EXISTS   (SELECT sid FROM msgs
WHERE 
sid=id)    AND NOT EXISTS  (SELECT rid FROM msgrcpt WHERE
rid=id)
3 rows affected
Database cleanup successful

however i see messages from July 4th still??

Any ideas?

Thanks...

-- 

Rob Morin
Dido InterNet Inc.
Montreal, Canada
Http://www.dido.ca
514-990-4444


------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
Mailzu-users mailing list
Mailzu-userslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mailzu-use
rs

On 7/17/07, Rob Morin <robdido.ca> wrote:
> ...
>
> I have 14,000 in my sql quarantine...
>
> i run the clean up script each night at default
levels.... it says clean
> up successful...
>
> when i run manually i get this....
>
> joe:/var/www/virtual/dido.ca/htdocs/MailZu/scripts#
./mz_db_clean.pl
> --verbose --database mysql
> Connecting to SQL database server
> Trying dsn 'DBI:mysql:database=mail;host=localhost'
> Connection to 'DBI:mysql:database=mail;host=localhost'
succeeded
> Executing... Tue Jul 17 09:12:31 2007
> DELETE FROM msgs WHERE mail_id IN   (SELECT DISTINCT
mail_id    FROM
> msgrcpt WHERE rs='D')
> 4 rows affected
> Executing... Tue Jul 17 09:12:32 2007
> DELETE FROM msgs WHERE time_iso < UTC_TIMESTAMP() -
interval 1 hour  AND
> content IS NULL
> 0E0 rows affected
> Executing... Tue Jul 17 09:12:33 2007
> DELETE FROM msgs WHERE time_iso < UTC_TIMESTAMP() -
interval 1 week
> 0E0 rows affected
> Executing... Tue Jul 17 09:12:33 2007
> DELETE FROM maddr WHERE NOT EXISTS   (SELECT sid FROM
msgs WHERE
> sid=id)    AND NOT EXISTS  (SELECT rid FROM msgrcpt
WHERE rid=id)
> 3 rows affected
> Database cleanup successful
>
> however i see messages from July 4th still??
>
> Any ideas?
>
> Thanks...
>
> --
>
> Rob Morin
> Dido InterNet Inc.
> Montreal, Canada
> Http://www.dido.ca
> 514-990-4444
>

The top of the script has configuration options. I think
the
PostgreSQL options are currently set. Look carefully for
the
$new_interval variable under MySQL. I should really rewrite
this
script and put all the options on the command-line.

------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
Mailzu-users mailing list
Mailzu-userslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mailzu-use
rs

On 7/17/07, Rob Morin <robdido.ca> wrote:
>
>  uncommenting this gave same results
>
>  # MySQL
>  #my($new_interval) = '1 week';
>  #my($new_partial_interval) = '1 hour';
>
>  so now i have
>
>  # MySQL
>  my($new_interval) = '1 week';
>  my($new_partial_interval) = '1 hour';
>

That is odd, my copy of the script has
# MySQL
#my($new_interval) = '7 day';
#my($new_partial_interval) = '1 hour';

So what you want is an '7 day'.

------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
Mailzu-users mailing list
Mailzu-userslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mailzu-use
rs

I am sorry my bad, it was 7 day, but still it deleted
nothing..... is 
there a way i can do it manually?


Rob Morin
Dido InterNet Inc.
Montreal, Canada
Http://www.dido.ca
514-990-4444



Brian Wong wrote:
> On 7/17/07, Rob Morin <robdido.ca> wrote:
>>
>>  uncommenting this gave same results
>>
>>  # MySQL
>>  #my($new_interval) = '1 week';
>>  #my($new_partial_interval) = '1 hour';
>>
>>  so now i have
>>
>>  # MySQL
>>  my($new_interval) = '1 week';
>>  my($new_partial_interval) = '1 hour';
>>
>
> That is odd, my copy of the script has
> # MySQL
> #my($new_interval) = '7 day';
> #my($new_partial_interval) = '1 hour';
>
> So what you want is an '7 day'.

------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
Mailzu-users mailing list
Mailzu-userslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mailzu-use
rs

On 7/17/07, Rob Morin <robdido.ca> wrote:
> I am sorry my bad, it was 7 day, but still it deleted
nothing..... is
> there a way i can do it manually?
>

Take a look at the maintenance queries listed in the
README.sql-mysql
that comes with the amavisd-new distribution.

------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
Mailzu-users mailing list
Mailzu-userslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mailzu-use
rs

Brian wrote:

> On 7/17/07, Rob Morin <robdido.ca> wrote:
>> I am sorry my bad, it was 7 day, but still it
deleted nothing..... is
>> there a way i can do it manually?
>>

> Take a look at the maintenance queries listed in the
README.sql-mysql
> that comes with the amavisd-new distribution.

My clock is set to local time, so I think this is what I'm
going to
use (I don't actually have the server completed yet):

USE amavis;
DELETE FROM msgs WHERE time_iso < now() - INTERVAL 1 hour
AND content IS NULL;
DELETE FROM msgs WHERE time_iso < now() - INTERVAL 14
day;
DELETE FROM maddr WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE
sid=id) AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE
rid=id);
DELETE FROM quarantine WHERE NOT EXISTS (SELECT 1 FROM msgs
WHERE mail_id=quarantine.mail_id);
DELETE FROM msgrcpt WHERE NOT EXISTS (SELECT 1 FROM msgs
WHERE mail_id=msgrcpt.mail_id);
DELETE FROM msgs WHERE mail_id IN (SELECT DISTINCT mail_id
FROM msgrcpt WHERE rs='D');

Is your msgs.time_iso field type TIMESTAMP? Has it always
been?

Gary V



------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
Mailzu-users mailing list
Mailzu-userslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mailzu-use
rs

Rob wrote:

>> Is your msgs.time_iso field type TIMESTAMP? Has it
always been?

>  Everything is default as per install....

But do you get the same thing I have here?

 mysql> use amavis;
 mysql> describe msgs time_iso;
 +----------+-----------+------+-----+-------------------+--
-----+
 | Field    | Type      | Null | Key | Default           |
Extra |
 +----------+-----------+------+-----+-------------------+--
-----+
 | time_iso | timestamp | NO   | MUL | CURRENT_TIMESTAMP |  
    |
 +----------+-----------+------+-----+-------------------+--
-----+

Gary V



------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
Mailzu-users mailing list
Mailzu-userslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mailzu-use
rs

Rob wrote:

> USE amavis;
> DELETE FROM msgs WHERE time_iso < now() - INTERVAL 1
hour AND content IS NULL;
> DELETE FROM msgs WHERE time_iso < now() - INTERVAL
14 day;
> DELETE FROM maddr WHERE NOT EXISTS (SELECT 1 FROM msgs
WHERE sid=id) AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE
rid=id);
> DELETE FROM quarantine WHERE NOT EXISTS (SELECT 1 FROM
msgs WHERE mail_id=quarantine.mail_id);
> DELETE FROM msgrcpt WHERE NOT EXISTS (SELECT 1 FROM
msgs WHERE mail_id=msgrcpt.mail_id);
> DELETE FROM msgs WHERE mail_id IN (SELECT DISTINCT
mail_id FROM msgrcpt WHERE rs='D');

> Is your msgs.time_iso field type TIMESTAMP? Has it
always been?

>  Everything is default as per install....

> Your script you run that in a perl script i presume?  I
figured a
> simple shell script might be nice...
> Hahahaha..... a perl guy i am not!
> 

> Rob Morin

Actually, I save the above in a file: /etc/trim-amavis.sql,
and then
place a simple shell script (I call trim-amavis) in
/etc/cron.daily/:

#!/bin/sh
/usr/bin/mysql -uamavis -ppassword <
/etc/trim-amavis.sql
exit 0

Edit 'password' as needed, and hide it from prying eyes:
chmod 750 trim-amavis

Gary V



------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
Mailzu-users mailing list
Mailzu-userslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mailzu-use
rs

Rob wrote:

>   It is not timestamp, i just changed it to timestamp
and am running the
> script again, its taking some time for it to come
back....
> Rob Morin
> Dido InterNet Inc.

I would guess you just deleted everything. You should have
either
done something like:

UPDATE msgs SET time_iso = NOW( ) WHERE time_iso < 1;

And then waited for time_iso to get older than a week.

Or changed the script to delete on time_num for now.
There should be an example here:
http://www.ijs.si/software/amavisd/README.sql-mysql.txt

Then in a week or so, drop the index that indexes on
time_num,
then create an index that indexes on time_iso and rewrite
the script
so it uses time_iso again.

DROP INDEX msgs_idx_time_num ON msgs;
CREATE INDEX msgs_idx_time_iso ON msgs (time_iso);

Gary V



------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
Mailzu-users mailing list
Mailzu-userslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mailzu-use
rs

On 7/17/07, Rob Morin <robdido.ca> wrote:
>
>  Everything is still there and now has a time stamp of
0000-00-00 00:00:00
> oh boy....
>
>  When i try to delete a couple emails via the gui i get
a db error  do i
> have to re-index something?
>

A delete is just a simple update. Turn on debugging in
MailZu to find
out the exact query that is causing problems. I am sure that
your
database is in really bad shape at this point if you can not
do a
simple update statement. I would just drop the database and
start
again.

------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
Mailzu-users mailing list
Mailzu-userslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mailzu-use
rs

Rob wrote:

>  I get...

mysql>> delete FROM msgs WHERE time_num  <
1183653026;

> ERROR 1205 (HY000): Lock wait timeout exceeded; try
restarting
> transaction

> I guess i will just drop the database  and start
again... however i
> want to confirm the structure of the database... can
you please send me
> your structure?

> Thanks...

> Here is mine....

> **NOTE: THAT I JUST CHANGE TIME_ISO FROM CHAR(16) TO
TIMESTAMP....

> CREATE TABLE `msgs` (
>   `mail_id` varchar(12) NOT NULL,
>   `secret_id` varchar(12) default '',
>   `am_id` varchar(20) NOT NULL,
>   `time_num` int(10) unsigned NOT NULL,
>   `time_iso` timestamp NOT NULL default
CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP,
>   `sid` int(10) unsigned NOT NULL,
>   `policy` varchar(255) default '',
>   `client_addr` varchar(255) default '',
>   `size` int(10) unsigned NOT NULL,
>   `content` char(1) default NULL,
>   `quar_type` char(1) default NULL,
>   `quar_loc` varchar(255) default '',
>   `dsn_sent` char(1) default NULL,
>   `spam_level` float default NULL,
>   `message_id` varchar(255) default '',
>   `from_addr` varchar(255) default '',
>   `subject` varchar(255) default '',
>   `host` varchar(255) NOT NULL,
>   PRIMARY KEY  (`mail_id`),
>   KEY `msgs_idx_sid` (`sid`),
>   KEY `msgs_idx_time_num` (`time_num`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Have you tried shutting down apache, amavisd-new and mysql
and then
bringing them back up?

Gary V



------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
Mailzu-users mailing list
Mailzu-userslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mailzu-use
rs

Rob wrote:

>  Ok now i did this...

mysql>> delete FROM msgs WHERE time_iso = '0000-00-00
00:00:00';

> Query OK, 41335 rows affected (9 min 52.33 sec)

> Now i only have 215 in there..

> Now all new emails are being stored as 2007-07-17
11:40:13  rather than
> 20070717114013

> But just need to know if anything else is need with
respect to
> re-indexing?

> Thanks to all for your help!

Make sure you have this in amavisd.conf:
$timestamp_fmt_mysql = 1;

If you are going to delete based on time_iso (and not
time_num),
then you should create an index for time_iso (as mentioned
earlier):

CREATE INDEX msgs_idx_time_iso ON msgs (time_iso);

and drop the index for time_num:
DROP INDEX msgs_idx_time_num ON msgs;

Gary V



------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
Mailzu-users mailing list
Mailzu-userslists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/mailzu-use
rs