[Dbmail] Not Deleting Mail

[rich carroll]

Howdy Folks,

I have a mailbox that I used for testing. It has about 300,000 messages in
it. I can not open it in webmail. I am trying to empty it out.

I have ran:

dbmail-users -e <user> -y

It looks like its doing something for a bit but nothing gets removed from
the box.

I am currently trying to pop them into thunderbird to see if that will
remove them although that is moving very slow. It gets a message every
couple seconds or so.

When I run:

dbmail-util -pd

It tells me:
Ok. [3933954] messages have DELETE status.

but dbmail-util -pdy does not clean them out.

I am wondering if I have a problem. Is there another way to ensure messages
get deleted and to emtpy mailboxes?

Im running dbmail 3.2.3.

I am seeing alot of this in my logs.

Jan 24 09:44:26 mail2017 dbmail/imap4d[103602]: Error:[db] db_exec(+389):
failed query [UPDATE dbmail_messages SET status=2 WHERE
message_idnr=92134366 ]
Jan 24 09:44:26 mail2017 dbmail/imap4d[103602]: Error:[db] db_exec(+388):
SQLException: ERROR: current transaction is aborted, commands ignored
until end of transaction

and I have a dbmail-imapd process that is really working hard.

[rich carroll]

If I do a:

dbmail-export -D -d -u <user>

It seems to empty the mailbox.

Post by rich carroll
Howdy Folks,
I have a mailbox that I used for testing. It has about 300,000 messages in
it. I can not open it in webmail. I am trying to empty it out.
dbmail-users -e <user> -y
It looks like its doing something for a bit but nothing gets removed from
the box.
I am currently trying to pop them into thunderbird to see if that will
remove them although that is moving very slow. It gets a message every
couple seconds or so.
dbmail-util -pd
Ok. [3933954] messages have DELETE status.
but dbmail-util -pdy does not clean them out.
I am wondering if I have a problem. Is there another way to ensure
messages get deleted and to emtpy mailboxes?
Im running dbmail 3.2.3.

These I think were related to imap or imapproxy and and not part of my
unable to delete messages issue. I restarted imap and they went away but I
still have almost 4 million messages mark for trash.

I am seeing alot of this in my logs.

Post by rich carroll
failed query [UPDATE dbmail_messages SET status=2 WHERE
message_idnr=92134366 ]
SQLException: ERROR: current transaction is aborted, commands ignored
until end of transaction
and I have a dbmail-imapd process that is really working hard.
--
Richard Carroll

[Andrea Brancatelli]

Do this:

SELECT user_idnr FROM dbmail_users WHERE userid = "YOUR EMAIL ADDRESS"

Write down the corresponding ID

SELECT mailbox_idnr, name FROM dbmail_mailboxes WHERE owner_idnr = <THE
ID YOU GOT FROM THE PREVIOUS SELECT>

Find the ID of your mailbox.

DELETE FROM dbmail_messages WHERE mailbox_idnr = <THE ID OF THE MAILBOX
YOU JUST FOUND OUT>

Done.

dbmail-util -ay will take care of cleaning out everything else later
(mimeparts, headers, etc), don't worry.

Enjoy.

---

Andrea Brancatelli

Post by rich carroll
Howdy Folks,
I have a mailbox that I used for testing. It has about 300,000 messages in it. I can not open it in webmail. I am trying to empty it out.
dbmail-users -e <user> -y
It looks like its doing something for a bit but nothing gets removed from the box.
I am currently trying to pop them into thunderbird to see if that will remove them although that is moving very slow. It gets a message every couple seconds or so.
dbmail-util -pd
Ok. [3933954] messages have DELETE status.
but dbmail-util -pdy does not clean them out.
I am wondering if I have a problem. Is there another way to ensure messages get deleted and to emtpy mailboxes?
Im running dbmail 3.2.3.
I am seeing alot of this in my logs.
Jan 24 09:44:26 mail2017 dbmail/imap4d[103602]: Error:[db] db_exec(+389): failed query [UPDATE dbmail_messages SET status=2 WHERE message_idnr=92134366 ]
Jan 24 09:44:26 mail2017 dbmail/imap4d[103602]: Error:[db] db_exec(+388): SQLException: ERROR: current transaction is aborted, commands ignored until end of transaction
and I have a dbmail-imapd process that is really working hard.
--
Richard Carroll
_______________________________________________
DBmail mailing list
http://lists.nfg.nl/mailman/listinfo/dbmail

[rich carroll]

Thanks Andrea,

That worked, but was really slow.

I started looking at deleting messages because my server is running really
slow. It turns out its because of the dbmail_messages table is really slow.
It took 5 minutes to delete 300 rows. My dbmail_messages table has 7628586
rows. Is that alot? Our old server with dbmail 2.2 and postgres 8 seemed to
run alot better. I used the 2.2 and postgresql performance guide but I am
not sure how much of that is relevent to 3.2.3 and postgresql 9.5. Is there
a newer guide or can you give me some suggestions to speed it up?

I run dbmail-util -ay every night. I thought I was clustering but when I
look at my indexes I see that the index I think should be clustered is not.

dbmail_messages_1 definition: "CREATE INDEX dbmail_messages_1 ON
dbmail_messages USING btree (mailbox_idnr)" <- Not clustered

should I do?

CLUSTER dbmail_messages USING dbmail_messages_1;


Thanks
Rich









On Thu, Jan 25, 2018 at 3:38 AM, Andrea Brancatelli <

SELECT user_idnr FROM dbmail_users WHERE userid = "*your email address*"
Write down the corresponding ID
SELECT mailbox_idnr, name FROM dbmail_mailboxes WHERE owner_idnr = *<the
ID you got from the previous select>*
Find the ID of your mailbox.
DELETE FROM dbmail_messages WHERE mailbox_idnr = *<the ID of the mailbox
you just found out>*
Done.
dbmail-util -ay will take care of cleaning out everything else later
(mimeparts, headers, etc), don't worry.
Enjoy.
---
*Andrea Brancatelli
*
Howdy Folks,
I have a mailbox that I used for testing. It has about 300,000 messages in
it. I can not open it in webmail. I am trying to empty it out.
dbmail-users -e <user> -y
It looks like its doing something for a bit but nothing gets removed from the box.
I am currently trying to pop them into thunderbird to see if that will
remove them although that is moving very slow. It gets a message every
couple seconds or so.
dbmail-util -pd
Ok. [3933954] messages have DELETE status.
but dbmail-util -pdy does not clean them out.
I am wondering if I have a problem. Is there another way to ensure
messages get deleted and to emtpy mailboxes?
Im running dbmail 3.2.3.
I am seeing alot of this in my logs.
failed query [UPDATE dbmail_messages SET status=2 WHERE
message_idnr=92134366 ]
SQLException: ERROR: current transaction is aborted, commands ignored
until end of transaction
and I have a dbmail-imapd process that is really working hard.
--
Richard Carroll
_______________________________________________
DBmail mailing list
http://lists.nfg.nl/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
http://lists.nfg.nl/mailman/listinfo/dbmail

[rich carroll]

I ended up adding these clusters. I am not sure if I clustered on the
correct indexes but things are moving along much faster. That and the
vacuum full analyze that I ran.

CLUSTER dbmail_messages USING dbmail_messages_1;
CLUSTER dbmail_mailboxes USING dbmail_mailboxes_owner_name_idx;
CLUSTER dbmail_physmessage USING dbmail_physmessage_pkey ;
CLUSTER dbmail_headername USING dbmail_headername_1 ;
CLUSTER dbmail_aliases USING dbmail_aliases_domain_alias_idx ;
CLUSTER dbmail_users USING dbmail_users_2 ;
CLUSTER dbmail_subscription USING dbmail_subscription_pkey ;
CLUSTER dbmail_acl USING dbmail_acl_pkey ;
CLUSTER dbmail_headervalue USING dbmail_headervalue_1 ;
CLUSTER dbmail_messageblks USING dbmail_messageblks_1 ;
CLUSTER dbmail_mimeparts USING dbmail_mimeparts_1 ;
CLUSTER dbmail_partlists USING dbmail_partlists_2 ;

Post by rich carroll
Thanks Andrea,
That worked, but was really slow.
I started looking at deleting messages because my server is running really
slow. It turns out its because of the dbmail_messages table is really slow.
It took 5 minutes to delete 300 rows. My dbmail_messages table has 7628586
rows. Is that alot? Our old server with dbmail 2.2 and postgres 8 seemed to
run alot better. I used the 2.2 and postgresql performance guide but I am
not sure how much of that is relevent to 3.2.3 and postgresql 9.5. Is there
a newer guide or can you give me some suggestions to speed it up?
I run dbmail-util -ay every night. I thought I was clustering but when I
look at my indexes I see that the index I think should be clustered is not.
dbmail_messages_1 definition: "CREATE INDEX dbmail_messages_1 ON
dbmail_messages USING btree (mailbox_idnr)" <- Not clustered
should I do?
CLUSTER dbmail_messages USING dbmail_messages_1;
Thanks
Rich
On Thu, Jan 25, 2018 at 3:38 AM, Andrea Brancatelli <

SELECT user_idnr FROM dbmail_users WHERE userid = "*your email address*"
Write down the corresponding ID
SELECT mailbox_idnr, name FROM dbmail_mailboxes WHERE owner_idnr = *<the
ID you got from the previous select>*
Find the ID of your mailbox.
DELETE FROM dbmail_messages WHERE mailbox_idnr = *<the ID of the mailbox
you just found out>*
Done.
dbmail-util -ay will take care of cleaning out everything else later
(mimeparts, headers, etc), don't worry.
Enjoy.
---
*Andrea Brancatelli
*
Howdy Folks,
I have a mailbox that I used for testing. It has about 300,000 messages
in it. I can not open it in webmail. I am trying to empty it out.
dbmail-users -e <user> -y
It looks like its doing something for a bit but nothing gets removed from the box.
I am currently trying to pop them into thunderbird to see if that will
remove them although that is moving very slow. It gets a message every
couple seconds or so.
dbmail-util -pd
Ok. [3933954] messages have DELETE status.
but dbmail-util -pdy does not clean them out.
I am wondering if I have a problem. Is there another way to ensure
messages get deleted and to emtpy mailboxes?
Im running dbmail 3.2.3.
I am seeing alot of this in my logs.
failed query [UPDATE dbmail_messages SET status=2 WHERE
message_idnr=92134366 ]
SQLException: ERROR: current transaction is aborted, commands ignored
until end of transaction
and I have a dbmail-imapd process that is really working hard.
--
Richard Carroll
_______________________________________________
DBmail mailing list
http://lists.nfg.nl/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
http://lists.nfg.nl/mailman/listinfo/dbmail

--
Richard Carroll
785-288-1144 <(785)%20288-1144>

[Andrea Brancatelli]

Hi Rich.

Our dbmail_messages table has 6 million rows more or less, but we're on
MySQL so I can't really tell you how much is that for pgSQL.

BTW, this is my table definition, with all the corresponding indexes -
obviously in MySQL slang:

CREATE TABLE `dbmail_messages` (
`message_idnr` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`mailbox_idnr` bigint(20) unsigned NOT NULL DEFAULT '0',
`physmessage_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`seen_flag` tinyint(1) NOT NULL DEFAULT '0',
`answered_flag` tinyint(1) NOT NULL DEFAULT '0',
`deleted_flag` tinyint(1) NOT NULL DEFAULT '0',
`flagged_flag` tinyint(1) NOT NULL DEFAULT '0',
`recent_flag` tinyint(1) NOT NULL DEFAULT '0',
`draft_flag` tinyint(1) NOT NULL DEFAULT '0',
`unique_id` varchar(70) NOT NULL DEFAULT '',
`status` tinyint(3) unsigned NOT NULL DEFAULT '0',
`seq` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`message_idnr`),
KEY `physmessage_id_index` (`physmessage_id`),
KEY `mailbox_idnr_index` (`mailbox_idnr`),
KEY `seen_flag_index` (`seen_flag`),
KEY `unique_id_index` (`unique_id`),
KEY `status_index` (`status`),
KEY `mailbox_status` (`mailbox_idnr`,`status`),
KEY `dbmail_messages_seq_index` (`seq`),
CONSTRAINT `dbmail_messages_ibfk_1` FOREIGN KEY (`physmessage_id`)
REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE
CASCADE,
CONSTRAINT `dbmail_messages_ibfk_2` FOREIGN KEY (`mailbox_idnr`)
REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON
UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=140465013 DEFAULT CHARSET=utf8;

---

Andrea Brancatelli
Schema31 S.p.a.
Chief Technology Officier

ROMA - FI - PA
ITALY
Tel: [phone]
Cell: +39.331.2488468
Fax: [phone]
Società del Gruppo OVIDIO TECH S.R.L.

Post by rich carroll
Thanks Andrea,
That worked, but was really slow.
I started looking at deleting messages because my server is running really slow. It turns out its because of the dbmail_messages table is really slow. It took 5 minutes to delete 300 rows. My dbmail_messages table has 7628586 rows. Is that alot? Our old server with dbmail 2.2 and postgres 8 seemed to run alot better. I used the 2.2 and postgresql performance guide but I am not sure how much of that is relevent to 3.2.3 and postgresql 9.5. Is there a newer guide or can you give me some suggestions to speed it up?
I run dbmail-util -ay every night. I thought I was clustering but when I look at my indexes I see that the index I think should be clustered is not.
dbmail_messages_1 definition: "CREATE INDEX dbmail_messages_1 ON dbmail_messages USING btree (mailbox_idnr)" <- Not clustered
should I do?
CLUSTER dbmail_messages USING dbmail_messages_1;
Thanks
Rich
SELECT user_idnr FROM dbmail_users WHERE userid = "YOUR EMAIL ADDRESS"
Write down the corresponding ID
SELECT mailbox_idnr, name FROM dbmail_mailboxes WHERE owner_idnr = <THE ID YOU GOT FROM THE PREVIOUS SELECT>
Find the ID of your mailbox.
DELETE FROM dbmail_messages WHERE mailbox_idnr = <THE ID OF THE MAILBOX YOU JUST FOUND OUT>
Done.
dbmail-util -ay will take care of cleaning out everything else later (mimeparts, headers, etc), don't worry.
Enjoy.
---
Andrea Brancatelli
Howdy Folks,
I have a mailbox that I used for testing. It has about 300,000 messages in it. I can not open it in webmail. I am trying to empty it out.
dbmail-users -e <user> -y
It looks like its doing something for a bit but nothing gets removed from the box.
I am currently trying to pop them into thunderbird to see if that will remove them although that is moving very slow. It gets a message every couple seconds or so.
dbmail-util -pd
Ok. [3933954] messages have DELETE status.
but dbmail-util -pdy does not clean them out.
I am wondering if I have a problem. Is there another way to ensure messages get deleted and to emtpy mailboxes?
Im running dbmail 3.2.3.
I am seeing alot of this in my logs.
Jan 24 09:44:26 mail2017 dbmail/imap4d[103602]: Error:[db] db_exec(+389): failed query [UPDATE dbmail_messages SET status=2 WHERE message_idnr=92134366 ]
Jan 24 09:44:26 mail2017 dbmail/imap4d[103602]: Error:[db] db_exec(+388): SQLException: ERROR: current transaction is aborted, commands ignored until end of transaction
and I have a dbmail-imapd process that is really working hard.
--
Richard Carroll
_______________________________________________
DBmail mailing list
http://lists.nfg.nl/mailman/listinfo/dbmail [1]
_______________________________________________
DBmail mailing list
http://lists.nfg.nl/mailman/listinfo/dbmail [1]

--
Richard Carroll
***@gmail.com
785-288-1144

_______________________________________________
DBmail mailing list
***@dbmail.org
http://lists.nfg.nl/mailman/listinfo/dbmail



Links:
------
[1] http://lists.nfg.nl/mailman/listinfo/dbmail