Re: [Dbmail] SQLException: innodb_log_file_size headache

[Jonathan Feally]

I was able to change without extra work with mysql56-server-5.6.27, but 
I agree that care should be taken depending on your version of MySQL.


See my startup log:

2016-03-21 01:38:14 69664 [Note] /usr/local/libexec/mysqld: Shutdown 
complete


160321 01:38:14 mysqld_safe mysqld from pid file 
/var/db/mysql/db.domain.com.pid ended
160321 01:38:17 mysqld_safe Starting mysqld daemon with databases from 
/var/db/mysql
2016-03-21 01:38:18 0 [Warning] TIMESTAMP with implicit DEFAULT value is 
deprecated. Please use --explicit_defaults_for_timestamp server option 
(see documentation for more details).
2016-03-21 01:38:18 0 [Note] /usr/local/libexec/mysqld (mysqld 
5.6.27-log) starting as process 75322 ...

2016-03-21 01:38:19 75322 [Note] Plugin 'FEDERATED' is disabled.
2016-03-21 01:38:19 802807400 InnoDB: Warning: Using 
innodb_additional_mem_pool_size is DEPRECATED. This option may be 
removed in future releases, together with the option 
innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2016-03-21 01:38:19 75322 [Note] InnoDB: Using atomics to ref count 
buffer pool pages

2016-03-21 01:38:19 75322 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-21 01:38:19 75322 [Note] InnoDB: Mutexes and rw_locks use GCC 
atomic builtins

2016-03-21 01:38:19 75322 [Note] InnoDB: Memory barrier is not used
2016-03-21 01:38:19 75322 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-03-21 01:38:19 75322 [Note] InnoDB: Using CPU crc32 instructions
2016-03-21 01:38:19 75322 [Note] InnoDB: Initializing buffer pool, size 
= 10.0G
2016-03-21 01:38:20 75322 [Note] InnoDB: Completed initialization of 
buffer pool
2016-03-21 01:38:20 75322 [Note] InnoDB: Highest supported file format 
is Barracuda.
*2016-03-21 01:38:21 75322 [Warning] InnoDB: Resizing redo log from 
2*320 to 2*64000 pages, LSN=184525750777**
**2016-03-21 01:38:21 75322 [Warning] InnoDB: Starting to delete and 
rewrite log files.**
**2016-03-21 01:38:21 75322 [Note] InnoDB: Setting log file 
./ib_logfile101 size to 1000 MB**

**InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000**
**2016-03-21 01:38:34 75322 [Note] InnoDB: Setting log file 
./ib_logfile1 size to 1000 MB**

**InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000**
**2016-03-21 01:38:45 75322 [Note] InnoDB: Renaming log file 
./ib_logfile101 to ./ib_logfile0*
2016-03-21 01:38:45 75322 [Warning] InnoDB: New log files created, 
LSN=184525750777

2016-03-21 01:38:45 75322 [Note] InnoDB: 128 rollback segment(s) are active.
2016-03-21 01:38:45 75322 [Note] InnoDB: Waiting for purge to start
2016-03-21 01:38:45 75322 [Note] InnoDB: 5.6.27 started; log sequence 
number 184525750777
2016-03-21 01:38:46 75322 [Note] Server hostname (bind-address): '*'; 
port: 3306

2016-03-21 01:38:46 75322 [Note] IPv6 is available.
2016-03-21 01:38:46 75322 [Note]   - '::' resolves to '::';
2016-03-21 01:38:46 75322 [Note] Server socket created on IP: '::'.
2016-03-21 01:38:50 75322 [Note] Event Scheduler: Loaded 0 events
2016-03-21 01:38:50 75322 [Note] /usr/local/libexec/mysqld: ready for 
connections.



On 3/21/2016 3:39 AM, Reindl Harald wrote:



Am 21.03.2016 um 10:07 schrieb Jonathan Feally:

After taking a look in lmtp logs I found:
Mar 21 01:28:57 mail.consult-sc dbmail-lmtpd[74282]: [0x804c21810]
Error:[message] blob_insert(+203): SQLException: The size of BLOB/TEXT
data inserted in one transaction is greater than 10% of redo log size.
Increase the redo log size using innodb_log_file_size


that's a change in MySQL 5.6 / MariaDB 10.0.x

remember you can't change "innodb_log_file_size" without shutdown the 
server and delete the currect "ib_logfile0" and "ib_logfile1" because 
otherwise mysqld won't start again



Here is a link to explain what changed in MySQL:
http://dba.stackexchange.com/questions/75328/row-size-error-with-mysql

This message is to make sure that nobody else starts to run into this
issue.

My change to my.cnf:
###innodb_log_file_size=5242880 # Too Small!!
innodb_log_file_size=1048576000 # Close to 100MB accepted now which is
large enough for most normal email setups


mysql:
innodb_log_file_size = 512M

postfix:
message_size_limit = 26214400

dbmail:
max_message_size = 26214400



___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

[Dbmail] SQLException: innodb_log_file_size headache

[Jonathan Feally]

FYI,
After performing a number of upgrades on my FreeBSD ports based install 
on an older release of FreeBSD 9.1-STABLE, I started getting corrupt 
messages. Since it was seemingly random on message with attachments, and 
I had upgraded postfix, mailscanner, dbmail, and mysql all the the same 
time,  it took a while before I could capture a queued message.
I finally was able to determine that postfix and mailscanner were not to 
blame, but rather somewhere in dbmail/mysql.


After taking a look in lmtp logs I found:
Mar 21 01:28:57 mail.consult-sc dbmail-lmtpd[74282]: [0x804c21810] 
Error:[message] blob_insert(+203): SQLException: The size of BLOB/TEXT 
data inserted in one transaction is greater than 10% of redo log size. 
Increase the redo log size using innodb_log_file_size.


Here is a link to explain what changed in MySQL:
http://dba.stackexchange.com/questions/75328/row-size-error-with-mysql

This message is to make sure that nobody else starts to run into this issue.

My change to my.cnf:
###innodb_log_file_size=5242880 # Too Small!!
innodb_log_file_size=1048576000 # Close to 100MB accepted now which is 
large enough for most normal email setups


-Jon

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] {Spam?} Re: dbmail and multimaster mysql replication

[Jonathan Feally]

I tested MySQL proxy last year, and the delay inserted because of the 
proxying was huge. DBMail has a lot of queries just to do simple things 
(a lot of IMAP compliance) and it just slows it way down. I think 
logging into a mailbox with a user will very little email in the INBOX 
went from 0.5 seconds to 5.0 seconds. This was on a 100 Mbit/sec LAN 
with 2 DB servers, and dbmail with the proxy on a 3rd server. I have not 
tested the newer proxy code, but I doubt that it has improved so much 
that it would be a viable solution for most end users expectations of 
how fast their server should give them their mail.

-Jon

ITronic Harald Leithner wrote:
> Has anyone thought about using mysql proxy to split "select" and  
> "manipulation" querys to 2 Servers?
>
> I used MySQL Proxy just for testing and a sms script, but I think it  
> should be possible to use LUA to
> split the queries to do a simple load balancing. If you like I can try to  
> write such a script for you.
>
>
>
> Am 19.05.2010, 04:15 Uhr, schrieb N Sj :
>
>   
>> I've read through the archives and noticed that multimaster replication  
>> is really discouraged to be used with dbmail, but I'd still like to see  
>> if I can figure out a way to use my second mysql server to take some  
>> load off the master server.  Please find the scenario below where all  
>> the pop/imap clients and SMTP from internet will be pointing to  
>> mysql-server1 and all the webmail clients to mysql-server2. Both  
>> mysql-server1 and mysql-server2 are configured with multimaster  
>> replication.
>>
>> Is it possible to do something like this or will it completely break  
>> dbmail.
>>
>> pop/imap >mysql-server1
>> webmail >mysql-server2
>>
>> both mysql1 and mysql2 are configured Master and slave to each other for  
>> multimaster replication.
>> mysql1<=>mysql2
>>
>> Thank you for your feedback.
>>
>>
>>
>>
>> ___
>> DBmail mailing list
>> DBmail@dbmail.org
>> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>> 
> ___
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
>   


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] Version

[Jonathan Feally]

NetBSD's simple comment out seems to have done the trick nicely. I'm not 
seeing any ill effects from it on my production box.

-Jon

Paul J Stevens wrote:
> Looks like a mhash upstream distribution bug.
>
> Debian and netbsd have fixed this themselves. So should Freebsd if it
> hasn't already. Short of a mhash upstream release that fixes this, of
> course, which it won't.
>
> http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=473204
> http://mail-index.netbsd.org/pkgsrc-bugs/2008/10/19/msg028900.html
>
> The netbsd patch is just too trivial.
>
> If you have an elegant solution please share.
>
>
> Jonathan Feally wrote:
>   
>> Paul,
>> This problem still exists on FreeBSD - just did a fresh git and compile. 
>> The version 0.9.9 is the version of mhash on the system as it is setting 
>> the PACKAGE_VERSION defines/declares before we try to set them again to 
>> our version. Perhaps we can just create our own DBMAIL_VERSION to be 
>> used for this case?
>>
>> -Jon
>>
>> On 3/8/2010 12:21 AM, Paul J Stevens wrote:
>> 
>>> Andrea Brancatelli wrote:
>>>
>>>   
>>>> Now, that's funny :-)
>>>>
>>>> [r...@mirtillo ~]# pkg_info | grep dbmail
>>>> dbmail-2.3.6An SQL database-based mail system (POP3 and IMAP)
>>>>
>>>> [r...@mirtillo ~]# dbmail-imapd -V
>>>> This is DBMail version 0.9.9
>>>>
>>>> Is this "correct"? :-)
>>>>  
>>>> 
>>> It isn't, but it's already fixed.
>>>
>>>
>>>
>>>
>>>   
>> 
>
>
>   


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] Version

[Jonathan Feally]

Paul,
This problem still exists on FreeBSD - just did a fresh git and compile. 
The version 0.9.9 is the version of mhash on the system as it is setting 
the PACKAGE_VERSION defines/declares before we try to set them again to 
our version. Perhaps we can just create our own DBMAIL_VERSION to be 
used for this case?

-Jon

On 3/8/2010 12:21 AM, Paul J Stevens wrote:
> Andrea Brancatelli wrote:
>
>> Now, that's funny :-)
>>
>> [r...@mirtillo ~]# pkg_info | grep dbmail
>> dbmail-2.3.6An SQL database-based mail system (POP3 and IMAP)
>>
>> [r...@mirtillo ~]# dbmail-imapd -V
>> This is DBMail version 0.9.9
>>
>> Is this "correct"? :-)
>>  
> It isn't, but it's already fixed.
>
>
>
>


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] DBMail 2.2.12 released

[Jonathan Feally]

Aleksander Kamenik wrote:
> Bret Baptist wrote:
>   
>> Also looks like the "internaldate as UTC" is causing issues with Mac Mail 
>> and 
>> older Outlook clients.  They are subtracting the time zone offset from the 
>> received time.
>> 
>
> I can confirm this. After upgrade to 2.2.12 MS Outlook 2000 user 
> complained that incoming email have correct date when they come in and 
> are unread. However after reading the message the dates change 3 hours 
> which is the local difference with UTC.
>
> Regards,
>
>   
We have fixed this in the git tree.

Paul,
Can we get a 2.2.12.1 release because of the bugs and missing configure 
related files?

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] DBMail 2.2.12 internal date offset wrong

[Jonathan Feally]

Jonathan Feally wrote:
> The correct operation would be to have the UTC timestamp stored in the 
> database. When read from the database, it is not adjusted other than to 
> add + for a timestamp to the reformatted time. The patches
> http://git.dbmail.eu/cgit/cgit.cgi/paul/dbmail/commit/?h=dbmail_2_2&id=296c72b96ad148a812a0c681271bca52ca10c226
> http://git.dbmail.eu/cgit/cgit.cgi/paul/dbmail/commit/?h=dbmail_2_2&id=01032167d087d675b92f6cca16527726db06ee84
>
> only dealt with the reading part, not with insertion. So you should not 
> be having an issue with this change. I will quickly test it out though 
> to see if there is any difference for me.
>
> -Jon
And the results: (insertion with lmtp)
2.2.11 - Date inserted is wrong, ie local time - thus adding the 
timezone offset of the same as that of the insertion yields a valid 
internaldate and matching when it was received.
2.2.12 - Date inserted is wrong, ie local time - returned time is then 
off by the timezone offset from insertion.
2.3.5+ - Date inserted is UTC - returned time is then correct as we 
don't tweak it, just add + to it reformatted.

2.3.5+ uses dbmail_message_new() to create a new message in which the 
internal_date is set to the current time, not done via the 
SQL_CURRENT_TIMESTAMP.
2.2.x is not having internal_date set, thus the fall back is to use 
SQL_CURRENT_TIMESTAMP. So if the database server is not UTC, then time 
will be wrong.


I've run out of brain power for tonight to do up a patch. Maybe you can 
take a look at the internal date being set on dbmail-message.c line 884 
Paul?

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] DBMail 2.2.12 released

[Jonathan Feally]

Paul J Stevens wrote:
>
>> It looks like it is happening for post 2.2.12 inserted email.  Perhaps only 
>> with IMAP clients, but I was not able to confirm before I rolled back to 
>> 2.2.11.
>> 
>
> Jon,
>
> Any ideas here?
>
>   

Oh thanks for passing the ball to me. What timezone is the server set 
for? Also is this a pipe or lmtp insertion? If you do a simple select on 
dbmail_phymessage for both a before and after 2.2.12 upgrade messages, 
how does that compare to the received header timestamp? They should be 
pretty close once the timezone adjustment is made.

The correct operation would be to have the UTC timestamp stored in the 
database. When read from the database, it is not adjusted other than to 
add + for a timestamp to the reformatted time. The patches
http://git.dbmail.eu/cgit/cgit.cgi/paul/dbmail/commit/?h=dbmail_2_2&id=296c72b96ad148a812a0c681271bca52ca10c226
http://git.dbmail.eu/cgit/cgit.cgi/paul/dbmail/commit/?h=dbmail_2_2&id=01032167d087d675b92f6cca16527726db06ee84

only dealt with the reading part, not with insertion. So you should not 
be having an issue with this change. I will quickly test it out though 
to see if there is any difference for me.

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] DBMAIL error on SELECT

[Jonathan Feally]

Michael Monnerie wrote:
> On Donnerstag 17 September 2009 Paul J Stevens wrote:
>   
>> No way around that without full text indexing.
>> 
>
> Any chance to have FTI support? PostgreSQL 8.3 has that built-in AFAIK, 
> so it could be interesting. Dbmail could support it if the DB supports 
> it, I just don't know how much effort that would be. Maybe just a 
> configuration line, or auto-detection?
>
> mfg zmi
>   

FTI would help searching greatly, however on MySQL the default is to 
only index words 4 chars or longer. Thus it makes it unreliable when 
searching for a small word of 3 chars or less. You can change it to 
index 3 chars, which would probably be sufficient, but since this is a 
global change on the whole database server and would require a restart, 
not everyone would be able to use it (hosted databases). I am unsure of 
SQLite FTI. I really can't see support of SQLite remaining beyond 2.4 
because of the multi-server over a network requirement of 2.5 Hydra.

Do you know what PostgreSQL does in as far as what is indexed?

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] IMAP quick resync?

[Jonathan Feally]

George Vieira wrote:
> Yeah I got dbmail configured for multiple servers hosting the same domain (or 
> others) but need an option to move users to another server hence the question 
> came up during this post.
>   
DBMail Hyrda - (2.5.x) is planning on a split front-end, multi-back-end 
setup to accomplish just what you are doing.

The current plan is to have a front-end database containing user and 
mailbox schema tables. (This front-end could be circular replicated as 
data here doesn't change much)
The back-end would contain messages for mailboxes that were assigned to 
it. The mailboxes would be defined with a unique_id on the front end 
with a pointer to a back-end-database. While we will probably target 
having a single back-end assigned to all mailboxes of a user. Public 
folders and other user folders may not be on the same back-end, so 
access to those mailboxes would require the daemons to cross-connect to 
all the back-ends. If a back-end is down when a select is done, then a 
temporary error will be returned.

Migration tools would be part of this setup, allowing single mailboxes, 
or all mailboxes owned by one user to be moved to a different back-end.

Database wise, this is a simple task, but coding wise requires some 
major work as we have it coded to use a connection out of a single pool 
and no concept of which server has what.

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] MySQL tweaks?

[Jonathan Feally]

Simon Gray wrote:
> Michael Monnerie wrote:
>   
>> Note:
>> Everybody who care about their data, you should leave this setting on 
>> it's default "1":
>> innodb_flush_log_at_trx_commit=1
>>   
>> 
> Also, this will only affect writes - rather than reads.
>
>   
If you have minimal writes and the server is on a UPS, this setting 
won't make too much difference, but if you have a lot of messages coming 
in, then a setting of 1 would be safer, but further reduce read 
performance under a mixed load. Moving towards 2.3.x should lend some 
speed increases depending on what searching you are doing. If you are 
searching single folders, then reducing the number of messages in each 
folder will help speed it up. Moving messages by date received to a 
sub-folder of year, quarter, or month can make a big difference. Bigger 
mailing lists I use by month "2009-09_Sep" to keep the message count < 
10k per folder.

I agree that the majority of your problems lie in disk i/o bandwidth. 
Use a good hardware raid 10 (not a fake hardware raid that is really 
software aka promise sata) with disks that have low seek times, a larger 
on disk buffer, and good sustained read throughputs. You should never 
use a raid5 for a database as the write performance will be very poor 
when writing out data that is less than the stripe size, causing all 
disks to be read, computed, then written to the affected disks. I use a 
3ware 9650SE-4LPML, with 4 SATA 1 disks in raid 10. My database is only 
20GB though and I have 6.5 GB of ram on mysqld. I'm planning on 
replacing the disks with some SATA 2 drives in the future, but just 
haven't procured them yet. I have about 550K messages right now in the 
database spread over 10 users, my account being the largest. With 2.3.x 
my database is about 2x the amount of mail stored, which is an 
improvement over 2.2.x.

You might also check into OS level tweaks for your disks to get every 
last bit out of them and the controller.

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] MySQL tweaks?

[Jonathan Feally]

Ok - but your sliderule is bent too.
139KB/message looks more correct. I must have had too many 1024's in 
there to the first time.

Ken D'Ambrosio wrote:
> Thanks for all the pointers!  Though I'm afraid your sliderule* dropped a
> decimal: 1*10^11 / 7.5*10^5 = 13., or 13K/message.  I'm running
> 2.2.9 (Ubuntu package).  I'll kick the tires and see what happens.
>
> Again, thanks!
>
> -Ken
>
> * Use of term "sliderule" not intended as a pejorative, but, rather, from
> the perspective of someone who's lost all too many decimal points while
> using same.
>
>
>   
>> You need to clean and optimize your tables. With a quick calc, that
>> would put you at about 140MB/message. Are you on 2.2.x or 2.3.x?
>>
>> You should first remove any messages that have been deleted and just
>> waiting to be deleted from the tables
>>
>> run this twice: dbmail-util -ay
>>
>> You should then take some downtime to do for each table
>> OPTIMIZE TABLE dbmail_tablename;
>>
>>
>> If you are running primarily MySQL on your server, then you will want to
>> target about 1.5 GB to 1.75GB of ram usage for it.
>>
>> The following should get you a bit better. You may need to tweak the
>> numbers a bit. If you have too much, then it will fail to start. I think
>> these will work with 2GB of ram. Probably put you at about 1.75GB of ram
>> usage.
>>
>> my.cnf
>>
>> innodb_file_per_table skip-locking innodb_flush_method=O_DIRECT
>>
>> sort_buffer_size=64M read_buffer_size=64M read_rnd_buffer_size=64M
>>
>> innodb_buffer_pool_size=1G innodb_additional_mem_pool_size=512MB
>> innodb_flush_log_at_trx_commit=2
>>
>> query_cache_size=128M
>>
>>
>> --
>> Scanned for viruses and dangerous content by MailScanner
>>
>>
>> ___
>> DBmail mailing list
>> DBmail@dbmail.org
>> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>>
>>
>> --
>> This message has been scanned for viruses and
>> dangerous content by MailScanner, and is believed to be clean.
>>
>>
>> 
>
>
>
>   


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] MySQL tweaks?

[Jonathan Feally]

Ken D'Ambrosio wrote:
>> How much data are you searching through? (how big is your dbmail db?)
>> 
>
> Roughly 100 GB; it's got 750K messages or so.
>   
You need to clean and optimize your tables. With a quick calc, that 
would put you at about 140MB/message. Are you on 2.2.x or 2.3.x?

You should first remove any messages that have been deleted and just 
waiting to be deleted from the tables

run this twice: dbmail-util -ay

You should then take some downtime to do for each table
OPTIMIZE TABLE dbmail_tablename;

If you are running primarily MySQL on your server, then you will want to 
target about 1.5 GB to 1.75GB of ram usage for it.

The following should get you a bit better. You may need to tweak the 
numbers a bit. If you have too much, then it will fail to start. I think 
these will work with 2GB of ram. Probably put you at about 1.75GB of ram 
usage.

my.cnf

innodb_file_per_table
skip-locking
innodb_flush_method=O_DIRECT

sort_buffer_size=64M
read_buffer_size=64M
read_rnd_buffer_size=64M

innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=512MB
innodb_flush_log_at_trx_commit=2

query_cache_size=128M


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] IMAP quick resync?

[Jonathan Feally]

Shane,
Briefly looking at the two RFC's, i think we could do this, but it will 
take some work. This would have to go into the 2.3.x branch. What client 
are you using that is supposed to support CONDSTORE and QRESYNC?

Paul,
I think we can add a modseq bigint to the messages table. This value 
would be copied from the mailboxes seq. So we update a message, update 
the seq, copy seq to that modified message, repeat for other messages. 
This should satisfy the CONDSTORE requirements. Getting to protocol 
responses and handling correct would take a while to cover all the 
scenarios.

-Jon

Shane Kerr wrote:
> Hello,
>
> Can any of the developers give any indication of how painful it would be
> to add support for quick synchronization?
>
> http://www.rfc-archive.org/getrfc.php?rfc=4551
> http://www.rfc-archive.org/getrfc.php?rfc=5162
>
> I'm not asking for a feature (although it would be nice), but rather a
> sense of the scale of the task.
>
> It makes me sad to have to check the status of every message in a
> mailbox to see if any changes were made. ;)
>
> Cheers,
>
> --
> Shane
>
> ___
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
>   


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] DBMAIL error on SELECT

[Jonathan Feally]

What database server and version are you using? This seems like an older 
database not able to handle that query.

-Jon

David Young wrote:
> Hi, I recently upgraded my dbmail to 2.2.11 and decided to create a 
> new email account/mailbox to make sure everything is working 
> correctly.  I sent myself an email and retrieved it via outlook using 
> dbmail-imapd.  Below is the error I received in dbmail.err
>
> The email displays correctly, but wondering why I'm getting this 
> error.  My email accounts that existed before I upgrade don't cause 
> this error.  I should also mention that I also converted database type 
> from SQL_ASCII to UTF8.
>
> Thanks,
>
> /var/log/dbmail.err
>
> Sep 10 00:39:49 node1.the.matrix dbmail-imapd[4957]: Error:[sql] 
> dbpgsql.c,db_query(+287): query failed [SELECT m.message_idnr, v.header
> value, k.messageblk FROM dbmail_messageblks k JOIN dbmail_physmessage 
> p ON k.physmessage_id=p.id  JOIN dbmail_messages m ON 
> m..physmessage
> _id=p.id  JOIN dbmail_headervalue v ON 
> v.physmessage_id=p.id  WHERE m.mailbox_idnr=53 AND 
> m.status in (0,1) HAVING SUBSTRING(v.headervalue
> ,0,255) ILIKE '%multipart/encrypted%' OR k.messageblk ILIKE 
> '%multipart/encrypted%'] : [ERROR:  operator does not exist: bytea ~~* 
> unkno
> wn
> LINE 1: ...55) ILIKE '%multipart/encrypted%' OR k.messageblk ILIKE '%mu...
>  ^
> HINT:  No operator matches the given name and argument type(s). You 
> might need to add explicit type casts.
> ]
>
> Sep 10 00:39:49 node1.the.matrix dbmail-imapd[4957]: Error:[mailbox] 
> dbmail-mailbox.c,mailbox_search(+1374): could not execute query
>
>
>
> -- 
> David
>
> -- 
> Scanned for viruses and dangerous content by *MailScanner* 
> 
> 
>
> ___
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>   


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] recipient redirect

[Jonathan Feally]

sendmail_destination_recipient_limit = 1
This could be the wrong option, but I'm not sure of the right one if it 
is needed at all.

Jonathan Feally wrote:
> You can try to make your distribution lists using an external forward, 
> and set postfix to send the message to each recipient instead of trying 
> to group them into a single message. This would effectively make the 
> message be received by dbmail-lmtpd and then resend the messages back to 
> postfix for each of the users. Then postfix will re-deliver the message 
> to each of the real recipients on the correct servers. This will avoid 
> postfix needing to do anything with aliases keeping postfix as dumb as 
> possible. With this approach you don't need to worry about keeping both 
> databases synced as there is nothing to sync from a to b.
>
> main.cf:
> sendmail_destination_recipient_limit = 1
>
> dbmail-users -x sa...@domain.com -t 
> j...@domain.com,j...@domain.com,theotherd...@domain.com
>
> -Jon
>
> George Vieira wrote:
>   
>> My correction, alias doesn't work because it's matching username only..argh, 
>> forgot. Damnit, gonna patch one thing or another.
>> GV
>>
>> -Original Message-
>> From: dbmail-boun...@dbmail.org [mailto:dbmail-boun...@dbmail.org] On Behalf 
>> Of George Vieira
>> Sent: Monday, 7 September 2009 8:19 PM
>> To: 'DBMail mailinglist'
>> Subject: Re: [Dbmail] recipient redirect
>>
>> OK, this works only when aliases is for 1 person. I cannot get this method 
>> to work when 2 people are in a sales@ mail alias group.
>> Since the domain is not in mydestination, i think alias_maps doesn't get 
>> used so it gets bypassed, am I right? Because i just tried it and it 
>> complained of no user when i switched away from your views and used an 
>> alias_map. :(
>> This sounds more like a postfix issue again.lol
>>
>> ___
>> DBmail mailing list
>> DBmail@dbmail.org
>> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>>
>>   
>> 
>
>
>   


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] recipient redirect

[Jonathan Feally]

You can try to make your distribution lists using an external forward, 
and set postfix to send the message to each recipient instead of trying 
to group them into a single message. This would effectively make the 
message be received by dbmail-lmtpd and then resend the messages back to 
postfix for each of the users. Then postfix will re-deliver the message 
to each of the real recipients on the correct servers. This will avoid 
postfix needing to do anything with aliases keeping postfix as dumb as 
possible. With this approach you don't need to worry about keeping both 
databases synced as there is nothing to sync from a to b.

main.cf:
sendmail_destination_recipient_limit = 1

dbmail-users -x sa...@domain.com -t 
j...@domain.com,j...@domain.com,theotherd...@domain.com

-Jon

George Vieira wrote:
> My correction, alias doesn't work because it's matching username only..argh, 
> forgot. Damnit, gonna patch one thing or another.
> GV
>
> -Original Message-
> From: dbmail-boun...@dbmail.org [mailto:dbmail-boun...@dbmail.org] On Behalf 
> Of George Vieira
> Sent: Monday, 7 September 2009 8:19 PM
> To: 'DBMail mailinglist'
> Subject: Re: [Dbmail] recipient redirect
>
> OK, this works only when aliases is for 1 person. I cannot get this method to 
> work when 2 people are in a sales@ mail alias group.
> Since the domain is not in mydestination, i think alias_maps doesn't get used 
> so it gets bypassed, am I right? Because i just tried it and it complained of 
> no user when i switched away from your views and used an alias_map. :(
> This sounds more like a postfix issue again.lol
>
> ___
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
>   


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] recipient redirect

[Jonathan Feally]

Ok,
I will chime in now with your answer,

In your databases create the view to merge users with their aliases:
CREATE ALGORITHM=UNDEFINED definer=`ro...@`localhost` SQL SECURITY 
DEFINER VIEW `dbmail_postfix_map` AS select `dbmail_users`.`userid` AS 
`user`,`dbmail_users`.`passwd` AS `passwd` from `dbmail_users` union 
select `dbmail_aliases`.`alias` AS `alias`,`dbmail_users`.`passwd` AS 
`passwd` from (`dbmail_aliases` join `dbmail_users` 
on((`dbmail_users`.`user_idnr` = `dbmail_aliases`.`deliver_to`)));

I use a full primary email address as my username, but then have 
multiple alliases with full email address pointing to my users.
vult...@netvulture.com (my login)
j...@netvulture.com (alias to user_idnr 5)

The view will let me lookup user='either address' to get the answer.

You will want to have the user created only once, not on both servers, 
otherwise mail will default to the first server listed.

In main.cf:
mydestination = $myhostname, localhost, mail.domain.tld (if dns name 
different than hostname), listofdomainsweacceptmailfor.tlds...
transport_maps = mysql:/etc/postfix/dbmail_relay_transports_server_a.cf, 
mysql:/etc/postfix/dbmail_relay_transports_server_b.cf,
local_recipient_maps = mysql:/etc/postfix/dbmail_mailboxes_server_a.cf, 
mysql:/etc/postfix/dbmail_mailboxes_server_b.cf

If you need to check that a sender is a valid user you will also need:
smtpd_sender_login_maps = 
mysql:/etc/postfix/dbmail_mailboxes_server_a.cf, 
mysql:/etc/postfix/dbmail_mailboxes_server_b.cf


dbmail_mailboxes_server_x.cf:
user = dbmail
password = dbmail
dbname = dbmail
hosts = server_a/b's IP
query = SELECT user FROM dbmail_postfix_map WHERE user='%s'

dbmail_relay_transports_server_x.cf:
user = dbmail
password = dbmail
dbname = dbmail
hosts = server_a/b's IP
query = SELECT 'lmtp:server_a/b_ip:24' FROM dbmail_postfix_map WHERE 
user='%s'

The result is that mail will be directed to the correct lmtp based on 
what user it is destined for.

server_a_u...@domain.tld -> server_a:24
server_b_u...@domain.tld -> server_b:24


Further postfix stuff you might need if you use smtp auth for sending
smtp_sasl_type = cyrus
broken_sasl_auth_clients = yes
smtpd_sasl_auth_enable = yes
smtpd_sasl_security_options = noanonymous
smtpd_sasl_authenticated_header = yes

smtpd_recipient_restrictions =
permit_mynetworks,
reject_invalid_hostname,
reject_non_fqdn_sender,
reject_non_fqdn_recipient,
reject_unknown_recipient_domain,
reject_unauth_pipelining,
reject_unknown_sender_domain,
permit_sasl_authenticated,
reject_rbl_client sbl-xbl.spamhaus.org,
reject_rbl_client bl.spamcop.net,
check_helo_access hash:/etc/postfix/helo_access,
reject_unauth_destination,
check_recipient_access hash:/etc/postfix/access,
check_policy_service unix:private/policy
smtpd_sender_restrictions =
permit_mynetworks,
check_helo_access hash:/etc/postfix/helo_access,
reject_authenticated_sender_login_mismatch,
permit_sasl_authenticated,
reject_sender_login_mismatch,
reject_unknown_sender_domain,
check_sender_access 
hash:/etc/postfix/sender_access


sender_access: (tabs not spaces between key and value)
mydomain.tld   553 not logged in
my2nddomain.tld   553 not logged in
*   OK

sasl2/smtpd.conf:
pwcheck_method: auxprop
auxprop_plugin: sql
allowanonymouslogin: no
allowplaintext: yes
mech_list: PLAIN LOGIN
srp_mda: md5
password_format: crypt
srvtab: /dev/null
opiekeys: /dev/null
sql_engine: mysql
sql_hostnames: server_ip
sql_user: dbmail
sql_passwd: dbmail
sql_database: dbmail
sql_verbose: yes
sql_select: SELECT passwd FROM dbmail_postfix_map WHERE (user) = ('%...@%r')
log_level: 10
sql_verbose: yes


With this setup, all of my users need to use smtp auth to send mail out. 
If someone tries to send mail from one of my valid users with out it, 
the message is rejected with the 553. I can use a helo or network_table 
to allow those matching to send with out auth. Mail coming in un-auth 
must come from some other domain to a valid users bases on the view. For 
simplicity all my users have plaintext passwords. During my last 
upgrade, I lost the ability to get my sasl to match a crypt password and 
had to change.

This should solve your receiving issue. If you have postfix on both 
sites, then you can just have users send through their local postfix to 
solve the problem of the sasl lookup only going to one server.

Let me know how that turns out.

-Jon

George Vieira wrote:
> This doesn'

Re: [Dbmail] stunnel replacement

[Jonathan Feally]

Michael Monnerie wrote:

Why not just download source and compile it?

Packages are nice, but I never restrict myself to just the available 
packages. If it isn't available as a package for a production box, then 
test in dev real quick, then get it compiled in production and move on.

Of course 2.3.x has native SSL support, making stunnel just added overhead.

-Jon
> In openSUSE 10.2, I used stunnel to provide pop3s/imaps, but that 
> package isn't anymore in 11.1. Anybody got an idea what I could use 
> instead? It would be best if it's included in os11.1, makes life easier 
> ;-)
>
> mfg zmi
>   

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] Calculate mailbox size?

[Jonathan Feally]

OK - 2nd try forgot the 
WHERE msg.status < 2

If you don't include the msg.status < 2, then you will be counting all 
messages, whether deleted or not.
status=2 deleted (expunge done), status=3 deleted waiting for purge run to 
actually be deleted.



Well,

If you are looking for the used space of the user in general, then 
dbmail_users.curmail_size is the column you want.
SELECT curmail_size FROM dbmail_users WHERE userid="theuser";

But I think you want to get a per mailbox (folder) look, so:

SELECT users.userid user, mbx.name mailbox, COUNT(msg.message_idnr) 
message_count, SUM(phys.messagesize) size FROM dbmail_mailboxes mbx JOIN 
dbmail_users users ON (mbx.owner_idnr=users.user_idnr) JOIN 
dbmail_messages msg ON (mbx.mailbox_idnr=msg.mailbox_idnr) JOIN 
dbmail_physmessage phys ON (phys.id=msg.physmessage_id) WHERE msg.status < 2 
GROUP BY 
mbx.mailbox_idnr ORDER BY users.userid, mbx.name;

That will give you all users, all mailboxes, order by user then mailbox. 
If you want just a paticular userid and maybe just  one mailbox of 
theirs, then add to the where clause
AND users.userid="theuser"

AND users.userid="theuser" AND mbx.name="INBOX"


-Jon



Simon wrote:
> Hi There,
>
> We have a external client management app that we would like to be able 
> to calculate the size of a dbmail mailbox with a SQL query. Can anyone 
> give me some pointers here to get this right please?
>
> Thanks
>
> Simon
>
> -- 
> Scanned for viruses and dangerous content by *MailScanner* 
> 
> 
>
> ___
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>   


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] Calculate mailbox size?

[Jonathan Feally]

Well,

If you are looking for the used space of the user in general, then 
dbmail_users.curmail_size is the column you want.
SELECT curmail_size FROM dbmail_users WHERE userid="theuser";

But I think you want to get a per mailbox (folder) look, so:

SELECT users.userid user, mbx.name mailbox, COUNT(msg.message_idnr) 
message_count, SUM(phys.messagesize) size FROM dbmail_mailboxes mbx JOIN 
dbmail_users users ON (mbx.owner_idnr=users.user_idnr) JOIN 
dbmail_messages msg ON (mbx.mailbox_idnr=msg.mailbox_idnr) JOIN 
dbmail_physmessage phys ON (phys.id=msg.physmessage_id) GROUP BY 
mbx.mailbox_idnr ORDER BY users.userid, mbx.name;

That will give you all users, all mailboxes, order by user then mailbox. 
If you want just a paticular userid and maybe just  one mailbox of 
theirs, then add a
WHERE users.userid="theuser"

WHERE users.userid="theuser" AND mbx.name="INBOX"

-Jon

Simon wrote:
> Hi There,
>
> We have a external client management app that we would like to be able 
> to calculate the size of a dbmail mailbox with a SQL query. Can anyone 
> give me some pointers here to get this right please?
>
> Thanks
>
> Simon
>
> -- 
> Scanned for viruses and dangerous content by *MailScanner* 
> 
> 
>
> ___
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>   


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] 2.3.6 delivery failed with malformed subject

[Jonathan Feally]

Piotr Wadas wrote:
> Error:[message] _header_value_get_id(+1577): SQLException: ERROR:  value too
> long for type 
> character varying(255)
>   
> 
>   
>   
> 
Ah hah! I'll take a look at this and figure out something for it.

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] 2.3.6 delivery failed with malformed subject

[Jonathan Feally]

You are using dbmail-deliver with sendmail?

Try setting your syslog to level 511 and running again. I don't like the 
syslog logging as it cuts off messages, but it should give us more than 
stdout/stderr that is lost with sendmail piping to dbmail-deliver.

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] Error dbmodule

[Jonathan Feally]

You will need to be more verbose about your problems when requesting 
help. We are not mind readers.

If you are trying to run 2.2.x, then you need to compile --with-mysql or 
--with-pysql
If you are trying to run 2.3.x, then your libzdb needs to be compiled 
with the correct database support.

With out further information, we can't say much

-Jon

Edson F. Cunha wrote:
>
> Jul 29 10:17:02 x..xxx.xx dbmail-pop3d[4521]: FATAL:[db] 
> dbmodule.c,db_load_driver(+80): could not load db module - turn up 
> debug level for details
>
> #
> # directory for locating libraries (normally has a sane default 
> compiled-in)
> #
> library_directory   = /usr/lib/dbmail
>
> [r...@filesrvpostfix dbmail]# ls -lh /usr/lib/dbmail/
> total 412K
> lrwxrwxrwx 1 root root   20 Jul 29 10:02 libauth_sql.so -> 
> libauth_sql.so.0.0.0
> lrwxrwxrwx 1 root root   20 Jul 29 10:02 libauth_sql.so.0 -> 
> libauth_sql.so.0.0.0
> -rwxr-xr-x 1 root root  23K Jul  6 02:12 libauth_sql.so.0.0.0
> lrwxrwxrwx 1 root root   18 Jul 29 10:02 libdbmail.so.0 -> 
> libdbmail.so.0.0.0
> -rwxr-xr-x 1 root root 317K Jul  6 02:12 libdbmail.so.0.0.0
> lrwxrwxrwx 1 root root   22 Jul 29 10:02 libsort_sieve.so -> 
> libsort_sieve.so.0.0.0
> lrwxrwxrwx 1 root root   22 Jul 29 10:02 libsort_sieve.so.0 -> 
> libsort_sieve.so.0.0.0
> -rwxr-xr-x 1 root root  19K Jul  6 02:12 libsort_sieve.so.0.0.0
> lrwxrwxrwx 1 root root   18 Jul 29 10:02 libsqlite.so -> 
> libsqlite.so.0.0.0
> lrwxrwxrwx 1 root root   18 Jul 29 10:02 libsqlite.so.0 -> 
> libsqlite.so.0.0.0
> -rwxr-xr-x 1 root root  44K Jul  6 02:12 libsqlite.so.0.0.0
>
>
> 
> Instale o novo Internet Explorer 8 otimizado para o MSN. Download aqui 
> 
> -- 
> Scanned for viruses and dangerous content by *MailScanner* 
> 
> 
>
> ___
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>   


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] Error

[Jonathan Feally]

Edson F. Cunha wrote:
>
>
> Jul 28 14:13:42 x.x.xxx.xx lt-dbmail-pop3d[23598]: 
> Error:[server] server.c,server_run(+278): unable to drop privileges
> Jul 28 14:17:09 x.x.xxx.xx lt-dbmail-pop3d[23674]: 
> Error:[misc] misc.c,drop_privileges(+76): could not find group nogroup
Looks like you need to change the group and possibly the user to run 
dbmail as in your dbmail.conf. You may have the group nobody instead of 
nogroup.

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] dbmail looses mail address in From: header

[Jonathan Feally]

Michael Monnerie wrote:
> Is this a know bug?
>
> When I receive mail from this guy, in Zarafa/Outlook it stores the 
> header like this:
> From: =?windows-1252?Q?M._Oostergo?= 
>   
Is this when you retrieve the message from a non-dbmail pop3/imap server?

> But when received over dbmail, the mail part is lost:
> From: =?windows-1252?Q?M._Oostergo?=
>
>   
Is this when the message is inserted via dbmail-smtp/dbmail-lmtp or an 
IMAP APPEND?
Is it ok when first inserted, but then the client changes it?

-Jon


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] client groups, group quotas, ldap etc.

[Jonathan Feally]

Piotr Wadas wrote:
> Hello,
> I've read this (quite old)
>
> http://www.nabble.com/Group-quotas-td7814443.html#a7814443
>
> What's the current status of this ? client_idnr column is still present,
> and there's field_cid in ldap configuration in dbmail.conf 
>
> Can I ask some up-to-date explanation how this works with field_quota,
> if still does? My groups are based on posix gidNumber.
>
> Regards,
> Piotr
>   
As it sits now, there are no group quotas or per mailbox(folder) quotas. 
Only the entire storage of a user has a quota on it. We do not have a 
table to store the group quota information at this point.

You could probably script something together to make a psuedo group 
quota by taking the sum of the used space on your users and comparing 
against your target limit. If it exceeds your limit, you could set a per 
user quota equal to thier current usage to stop insertion of messages 
until it falls below that limit. When the script runs again and finds it 
lower than your target, set their quota back to 0 (off).

I have a couple of goals already to get into the 2.3.x branch, but I 
think we can look at improving the quota handling and maybe work on 
getting per-user, per-mailbox, and per-group all in there and setup an 
automatic message to appear in the mailbox when the quota is exceeded. 
This message would not be able to be deleted or moved. The body of the 
message could be user configurable. That is my thought on that, but not 
this week.

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] Annoying "DONT DELETE THIS MESSAGE" with previously used imap inbox folder

[Jonathan Feally]

Michael Monnerie wrote:
>
> select * from dbmail_messages where physmessage_id IN (select 
> physmessage_id from dbmail_subjectfield where subjectfield like 
> 'Annoying%DELETE THIS MESSAGE%');
>
> Instead of "select *" simply write "DELETE" and those messages are gone.
>
> mfg zmi
>   

I'm glad you responded first. I forgot about those views and was going 
to give a very nasty query back to do it. That should work in both 2.2.x 
and 2.3.x

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] auto replies / vacation messages

[Jonathan Feally]

Casper Langemeijer wrote:
> Hi All,
>
> What is the correct way to implement out of office replies for dbmail 
> 2.2? I see people on this list using sieve (vacation), but I've used 
> an entry in dbmail_auto_replies, and I think (but am not sure) that 
> I've seen that one work before.
>

auto-reply and auto-notify has been removed from 2.3.x. The only way to 
do it is with sieve. You would need to use the vacation or notify method.

Examples Below. Note that the true can be replaced with other ways of 
matching messages specifically. Rules to sort mailing list messages into 
other mailboxes should be first and include the stop command so that the 
vacation doesn't go back to the mailing list. Sieve scripts do not get 
updated if the mailbox is renamed, so if you rename a mailbox, you need 
to update the script as well.

if anyof (header :contains ["to", "cc"] "dbmail@dbmail.org",
header :contains ["to", "cc"] "dbmail-...@dbmail.org")
{
fileinto  "Lists/DBMAIL";
stop;
}


if true
{
vacation :days 7 :addresses ["vult...@netvulture.com"] :subject "On 
Vacation Until Forever"  text:
I will look at your email when I get back.
..
;
}

if true
{
keep;
notify :method "mailto" :options "otherem...@domain.com" :low :message 
"You have received a message from $from$ with subject $subject$";

}


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] URGENT: Accidentally delete mail user with dbmail-users -d

[Jonathan Feally]

Perhaps we should have a status column on dbmail_users like we have on 
messages.

0 = disabled
1 = enabled
2 = deleted
3 = purge ok


Then on the dbmail-util run it will moved deleted to purge  - then 
really delete the purge like we do with messages.

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] MySQL 5.1 dependancy?

[Jonathan Feally]

Dan,
I'm not sure who put that in the UPGRADING doc, but 5.0.x will work 
fine. I have not tested 5.1.x for performance fixes in a while. 5.1.x 
could be ok now, but if you can run 5.0.83 then do that for best bets. I 
have not tried 5.4.x, so I can't comment on that other than its pretty 
new. The 6.x is a definite no as index key sizes are not big enough with 
utf-8 to accommodate some of  our indexs on multi-columns.

-Jon
 
Dan wrote:
> Hi people.
>
> I just did a 'git pull' and was about to start building, when I though
> "I'd better check the UPGRADING notes".
>
> This doc states that MySQL 5.1 is required. Now last time I checked,
> this upgrade wasn't such a good idea - particularly with DBMail if I
> remember messages from a couple of months back. Is it now OK to upgrade
> to MySQL 5.1? Is it really a dependancy, or is 5.0.x OK?
>
> Thanks.
>
> Dan
>
> ___
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
>   


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] DBMail 2.3.6 released ( works fine )

[Jonathan Feally]

Something is missing in the constraints or dbmail-util clean up. With 0 
messages, then headernames/headervalues should be 0 as well. I'm glad to 
see that headers is 0 as it should be.

These should clean up the extra rows. Please note that these will take a 
while on a database with a lot of header caching and messages still 
present. Took 15 mins to kill 36899 rows (5037478 rows remain) for just 
the first one for me (mysql). Second one took only 25 seconds to kill 
241 rows (5328 rows remain). I have 492531 physmessages.

DELETE FROM dbmail_headervalue where id NOT IN (SELECT 
DISTINCT(headervalue_id) FROM dbmail_header);
DELETE FROM dbmail_headername where id NOT IN (SELECT 
DISTINCT(headername_id) FROM dbmail_header);


-Jon

Piotr Wadas wrote:
> actually this is what left after delete/purge/vacuum (PG 8.3)
> dbmail_users, dbmail_subscriptions, and dbmail_mailboxes is reasonable,
> but I expect headernames and header values to be empty too.
>
>   table_name  | how_many_rows
> +--
>  dbmail_aliases |0
>  dbmail_authlog |0
>  dbmail_envelope|0
>  dbmail_filters |0
>  dbmail_header  |0
>  dbmail_mimeparts   |0
>  dbmail_pbsp|0
>  dbmail_physmessage |0
>  dbmail_referencesfield |0
>  dbmail_replycache  |0
>  dbmail_sievescripts|0
>  dbmail_usermap |0
>  dbmail_keywords|0
>  dbmail_messageblks |0
>  dbmail_messages|0
>  dbmail_acl |0
>  dbmail_partlists   |0
>  dbmail_users   |5
>  dbmail_mailboxes   |6
>  dbmail_subscription|6
>  dbmail_headername  |   58
>  dbmail_headervalue | 2052
> (24 rows)
>
>   


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] Repeated Folder Names

[Jonathan Feally]

There is a sql migration from 2.2 to 2.3 that needs to be done in 
addition to dbmail-util runs. This could take longer than you expect to 
do. I'm not sure what the response difference is between 2.2 and 2.3 
that needs to be corrected. Perhaps I'll test it later to see what I get.

-Jon

Gordan Bobic wrote:
> On Mon, 29 Jun 2009 15:15:58 +0200, Paul J Stevens  wrote:
>   
>> Gordan Bobic wrote:
>>
>> 
>>> Is this a DBmail misconfiguration? Or corruption in my mail database? Is
>>> there anything that can be done about this?
>>>   
>> this is a known dbmail bug in 2.2, fixed already in 2.3.
>>
>> But since no of my dbmail-2.2 users complain, there's not much incentive
>> for me to fix this...
>> 
>
> I just tried building 2.3 on RHEL5, and it rapidly turns into dependency
> hell because of gmime version requirement inflation. Is there a patch for
> this that went into 2.3 that could be thumped a bit to fit 2.2? This issue
> is particularly crippling for mobile users because it makes their IMAP sync
> megabytes rather than kilobytes in size. Alternatively, is there an older
> version of 2.2 that doesn't suffer from this issue?
>
> Thanks.
>
> Gordan
> ___
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
>   


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] DBMail 2.3.6 released

[Jonathan Feally]

Jake Anderson wrote:
> Any chance of debs of this?
> It makes upgrading much easier ;->
>
> Oh and is there anything to migrate headers over to the new storage as 
> well?
Debs would be Paul's dept.

Part of upgrading to 2.3.6 removes the old header caching tables and 
creates new ones. dbmail-util then needs to be run to finishing the 
upgrade by rebuilding the cache which will be in the new single-instance 
header caching. You can further finish up with migrating 2.2.x stored 
messages with the -M option to move those messages into the new 
single-instance storage of mimeparts.

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] [Dbmail-dev] DBMail 2.3.6 released

[Jonathan Feally]

Paul J Stevens wrote:
> New features in this release:
>   
There is also a new option -M on dbmail-util to migrate 2.2 messageblks 
stored messages to the 2.3 single-instance storage. Messages are moved 
1 per run of dbmail-util. You can change the 1 limit with a -m # 
argument.

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] Unable to have dbmail work on Fedora 11

[Jonathan Feally]

Jean Naimard wrote:
> $ dbmail-users -l *
> Jun 26 17:34:45 domus dbmail-users[11177]: Error:[sql] 
> dbmysql.c,db_connect(+172): mysql_real_connect failed: Access denied for 
> user 'dbmail'@'x' to database '/var/lib/dbmail/dbmail.db'
> Failed. Could not connect to database (check log)
> Command failed.
>
> The log reads:
>
> Jun 26 17:34:45 domus dbmail/user[11177]: Error:[sql] 
> dbmysql.c,db_connect(+172): mysql_real_connect failed: Access denied for 
> user 'dbmail'@'x' to database '/var/lib/dbmail/dbmail.db'
>   
It would appear that you have the path to the database not the name of 
it in your dbmail.conf as it is trying to use /var/lib/dbmail/dbmail.db
make sure that you have "db = dbmail" in your dbmail.conf. I'm guessing 
that you have set driver = mysql already.

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] emptying database

[Jonathan Feally]

Piotr Wadas wrote:
> The count(*) result is zero, anyway rows are still available in mentioned 
> tables, as listed :/
>   
Well then it sounds like the tables just need to be shrunk/optimized in 
postgres. I'm not sure what the syntax is to do that as I'm a mysql guy.

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] emptying database

[Jonathan Feally]

Piotr Wadas wrote:
>  dbmail_physmessage|   994
>  dbmail_fromfield  |   994
>   
These match - that is good - should be one from header for each message
>  dbmail_envelope   |  1057
>   
A couple extra maybe?? Not too far off.
>  dbmail_tofield|  5011
>   
This is about 5x the correct size - this is just a view so nothing can 
be deleted from it. This count be close to the fromfield count. Only one 
to and from header per message.
>  dbmail_mimeparts  | 10001
>  dbmail_partlists  | 11467
>   
This would mean that we have 11.5 or so parts per message? I have an 
average of 3.75 parts per messages.
>  dbmail_headervalue| 18115
>   
This seems to be about right at 18 headers per message.


So from looking at this I think that partlists rows are being left 
around, thus the mimeparts can't be dropped because they are still being 
pointed to.

Try:
select count(*) from dbmail_partlists left join dbmail_physmessage on 
(dbmail_physmessage.id=dbmail_partlists.physmessage_id) where 
dbmail_physmessage.id is null;

That should confirm if there are any parts being left there. Count=0 
means no leftovers, Count>0 then there are extra parts not being cleaned 
up. Perhaps a constraint was missed, or maybe you just need to optimize 
all of the tables to regain the space.

-Jon


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] emptying database

[Jonathan Feally]

What tables still have a lot of rows?
select count(*) from dbmail_xxx;

For a single user, you should probably have <10 rows in all of the 
tables. Let me know what tables still have more than that.

-Jon

Piotr Wadas wrote:
>   
>>> Garbage collection on dbmail_mimeparts is still missing.
>>>   
>
> Shall I expect the same behaviour, no matter what kind of backend
> (mysql/pgsql/sqlite) I'll use?
>
> Any simple sql hint to cleanup this table manually, something like
>
> delete 
>  from dbmail_mimeparts 
> where 
>message_id 
> is not in ( select message_idnr from dbmail_messages )
>
> or similar?
>   


-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] emptying database

[Jonathan Feally]

Jorge Bastos wrote:
> I think there's something incomplete on that function 'cause when I used the
> git version when that was added, messages were not being completely removed.
>   
It might be that we are missing the partlists cleanup. If the parts 
aren't being deleted, then that db_icheck_mimeparts() won't do anything.

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] emptying database

[Jonathan Feally]

Paul J Stevens wrote:
> delete from dmail_mimeparts
> where id not in (select part_id from dbmail_partlists)
>
> though this is of course a very slow query
Hmm. I thought we have put in that cleanup in the dbmail-util for trunk 
(2.3.x) already?? dm_db.c:db_icheck_mimeparts(t/f)

-Jon

-- 
Scanned for viruses and dangerous content by MailScanner

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] dbmail performance test

[Jonathan Feally]

libzdb-2.4 will work fine with the latest git. It may have issues with 
2.3.5 release.

Download link: http://www.tildeslash.com/libzdb/dist/libzdb-2.4.tar.gz
2.2 link: http://www.tildeslash.com/libzdb/dist/libzdb-2.2.tar.gz

Give that a shot.

-Jon

Andreja Babic wrote:
> Hi guys,
>
> I am trying to install dbmail-2.3.5 but without success by now.
>
> What I tried is to install is dbmail-2.3.5 + libzdb-2.5. As there is no 
> libzdb debian package for debian-5.0 I have installed libzdb from source. 
> But, after all I got an error while doing "make all": 
>
> error
> _db.c  -fPIC -DPIC -o .libs/libdbmail_la-dm_db.o
> dm_db.c: In function 'db_connect':
> dm_db.c:200: warning: comparison between signed and unsigned
> dm_db.c: In function 'db_exec':
> dm_db.c:315: error: void value not ignored as it ought to be
> dm_db.c: In function 'db_stmt_set_str':
> dm_db.c:398: error: void value not ignored as it ought to be
> dm_db.c: In function 'db_stmt_set_int':
> dm_db.c:403: error: void value not ignored as it ought to be
> dm_db.c: In function 'db_stmt_set_u64':
> dm_db.c:408: error: void value not ignored as it ought to be
> dm_db.c: In function 'db_stmt_set_blob':
> dm_db.c:413: error: void value not ignored as it ought to be
> dm_db.c: In function 'db_stmt_exec':
> dm_db.c:417: error: void value not ignored as it ought to be
> dm_db.c: In function 'db_begin_transaction':
> dm_db.c:477: error: invalid use of void expression
> dm_db.c: In function 'db_commit_transaction':
> dm_db.c:485: error: invalid use of void expression
> dm_db.c: In function 'db_rollback_transaction':
> dm_db.c:496: error: invalid use of void expression
> dm_db.c: In function 'user_idnr_is_delivery_user_idnr':
> dm_db.c:795: warning: dereferencing type-punned pointer will break 
> strict-aliasing rules
> dm_db.c:798: warning: dereferencing type-punned pointer will break 
> strict-aliasing rules
> make[3]: *** [libdbmail_la-dm_db.lo] Error 1
> make[3]: Leaving directory `/usr/local/src/dbmail-2.3.5/src'
> make[2]: *** [all-recursive] Error 1
> make[2]: Leaving directory `/usr/local/src/dbmail-2.3.5/src'
> make[1]: *** [all-recursive] Error 1
> make[1]: Leaving directory `/usr/local/src/dbmail-2.3.5'
> make: *** [all] Error 2
> error
>
> then I found this thread : 
> http://www.nabble.com/problem-compiling-2.3.5-with-libzdb-2.4-(trunk)-td21558842.html
>  where Paul says that libzdb-2.2 should be used. 
>
> On official site of libzdb there is no chance to download required version: 
> http://www.tildeslash.com/libzdb/download.html and I found this option:
>
> # svn list http://libzdb.googlecode.com/svn/tags/
> release-2-2/
> release-2-2-1/
> release-2-2-2/
> release-2-2-3/
> release-2-3/
> release-2-4/
> release-2-5/
>
> So, at the end I downloaded required version of libzdb from here svn checkout 
> http://libzdb.googlecode.com/svn/tags/release-2-2 libzdb-2-2 
>
> but there is no configure file. I am stuck here. Any help would be 
> appreciated.
>
> Thanks in advance,
>
> Andreja
>
> -Ursprüngliche Nachricht-
> Von: dbmail-boun...@dbmail.org [mailto:dbmail-boun...@dbmail.org] Im Auftrag 
> von Andreja Babic
> Gesendet: Donnerstag, 28. Mai 2009 09:37
> An: DBMail mailinglist
> Betreff: Re: [Dbmail] dbmail performance test
>
> Hi Jon,
>
> Interesting idea. I will do so and let you know the results.
>
> Andreja
>
> -Ursprüngliche Nachricht-
> Von: dbmail-boun...@dbmail.org [mailto:dbmail-boun...@dbmail.org] Im Auftrag 
> von Jonathan Feally
> Gesendet: Donnerstag, 28. Mai 2009 03:46
> An: DBMail mailinglist
> Betreff: Re: [Dbmail] dbmail performance test
>
> It would be very nice if you were to compare the new 2.3.x from git vs. 
> 2.2.x. I suspect that the size of the database will be much less than 
> your 2.2 install and would like to see what performance difference there 
> really is. If you were to run this test, please copy all the messages 
> into a fresh database vs. converting a copy of your 2.2 database. That 
> way all messages are stored in the single-instance-storage and all 
> header caching is done in a single table.
>
> Thanks,
> -Jon
>
>
> Andreja Babic wrote:
>   
>> Hi,
>>
>> I am using dbmail for several years. Recently I have made performance
>> test of dbmail and cyrus. I have 350 users in dbmail with total 4
>> million mails. 
>> The biggest mailbox has a bit more than 15 mails.
>>
>> Then I created the same users for cyrus and with imapsync copied all
>> mails from dbmail to cyrus. Yes, it t

Re: [Dbmail] dbmail performance test

[Jonathan Feally]

It would be very nice if you were to compare the new 2.3.x from git vs. 
2.2.x. I suspect that the size of the database will be much less than 
your 2.2 install and would like to see what performance difference there 
really is. If you were to run this test, please copy all the messages 
into a fresh database vs. converting a copy of your 2.2 database. That 
way all messages are stored in the single-instance-storage and all 
header caching is done in a single table.

Thanks,
-Jon


Andreja Babic wrote:
> Hi,
>
> I am using dbmail for several years. Recently I have made performance
> test of dbmail and cyrus. I have 350 users in dbmail with total 4
> million mails. 
> The biggest mailbox has a bit more than 15 mails.
>
> Then I created the same users for cyrus and with imapsync copied all
> mails from dbmail to cyrus. Yes, it took few days to finish.
>
> This is an test environment so I had exclusive access to it.
>
> Dbmail version 2.2.11 compiled from source
> Cyrus version 2.2.13-14+b3 (debian package)
>
> Both imaps use mysql for authentication.
>
> Database dbmail took 48G while cyrus mail storage had 30G for the same
> amount of mails.
>
> I run simple test with expect scripts and measuring time difference
> between establishing and closing connection to each imap.
>
> What I have got from tests is that search is much faster on dbmail and
> fetch is much faster on cyrus.
>
> For example on the mailbox which had 39661 unread mails search on cyrus
> imap took 20 seconds. The same operation on identical mailbox on
> dbmail took 12.7 seconds.
>
> On the other side, fetching headers from all mails in inbox with "fetch
> 1:* body[header]" took on dbmail 20 seconds while the same operation
> on cyrus took 3 seconds.
>
> I want to point that originaly I had mysql parameter tmp_table_size set
> to 30M and then search on dbmail took ages on 3000 big maibox.
> After I increased this parameter to 500M search was flying and tests
> mentioned above are done when tmp_table_size was set to 500M.
>
> I am using roundcube webmail as front end for dbmail imap server and
> after login it takes ages to open huge mailbox. I would appreciate if
> you know and tell me if there is any "trick" to increase speed of fetch
> on dbmail.
>
>
> Regards,
>
> Andreja
> ___
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
>   


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] LDAP Schema

[Jonathan Feally]

I'm not a dbmail LDAP user so I can't help you much on getting your 
directory setup. As far as I know, you do not need to add users to the 
database as upon delivery or login, the account would automatically be 
created. Paul would know how it works better. You should have also read 
README.ldap from the tarball.

-Jon

Gordan Bobic wrote:
> OK, I got the one from the latest tar ball, thanks.
>
> Now that I tried that, I have another question - I have an existing LDAP 
> database that is already used in production to authenticate 
> UNIX/Windows/Samba accounts.
>
> I have gotten as far as authenticating IMAP users via the LDAP database 
> (it seems that this doesn't require the dbmailUsers class), but lmtpd, 
> clearly, won't work because the mailbox aliases don't exist.
>
> I added the schema to my OpenLDAP slapd.conf and I can add new dbmail 
> users using the dbmail-users tool, but I cannot convert existing users 
> to include the dbmailUsers objectClass. I tried adding it manually, but 
> I get objectClass class violations, and dbmail-users -c does the same.
> dbmail-users -a says the user already exists.
>
> How do I add dbmailUsers objectClass to existing users to I can 
> authenticate dbmail off the same user account?
>
> Thanks.
>
> Gordan
>
> Jonathan Feally wrote:
>   
>> See - 
>> http://git.dbmail.eu/cgit/cgit.cgi/paul/dbmail/tree/dbmail.schema?h=dbmail_2_2
>> We no longer use svn. I'm not sure as to how correct this schema is, but 
>> it should be better than what that old svn version shows.
>>
>> -Jon
>>
>>
>> Gordan Bobic wrote:
>> 
>>> Hi,
>>>
>>> Can anyone confirm if this is the most up to date LDAP schema that 
>>> should be used with 2.2.11?
>>> http://svn.ic-s.nl/websvn/filedetails.php?repname=DBMail&path=%2Ftrunk%2Fdbmail%2Fdbmail.schema&rev=1828&sc=0
>>>
>>> Thanks.
>>>
>>> Gordan
>>> ___
>>> DBmail mailing list
>>> DBmail@dbmail.org
>>> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>>>
>>>   
>>>   
>> 
>
> ___
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
>   


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] LDAP Schema

[Jonathan Feally]

See - 
http://git.dbmail.eu/cgit/cgit.cgi/paul/dbmail/tree/dbmail.schema?h=dbmail_2_2
We no longer use svn. I'm not sure as to how correct this schema is, but 
it should be better than what that old svn version shows.

-Jon


Gordan Bobic wrote:
> Hi,
>
> Can anyone confirm if this is the most up to date LDAP schema that 
> should be used with 2.2.11?
> http://svn.ic-s.nl/websvn/filedetails.php?repname=DBMail&path=%2Ftrunk%2Fdbmail%2Fdbmail.schema&rev=1828&sc=0
>
> Thanks.
>
> Gordan
> ___
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
>   


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] Odd Behavior Under Mutt

[Jonathan Feally]

Kevin Monceaux wrote:
> I tried to build 2.3.5 and hit bug 757.  The version of libzdb in
> ArchLinux's package repository is version 2.4.  
>   
libzdb 2.3 and 2.4 support will be in 2.3.6. I think I'm holding up the 
2.3.6 branch with my latest ideas. I'll get to work on giving Paul the 
final patch for his testing and approval.

-Jon

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] Odd Behavior Under Mutt

[Jonathan Feally]

Kevin,
Since you are just trying it out, give the 2.3 branch a test. When 2.3.6 
comes out, I would say you would be relatively safe to move to the new 
2.3.x storage infrastructure. A few more changes are going to be in the 
database schema in 2.3.6. Possible a couple more that I'm working on 
that will simplify it a bit.

-Jon

Paul J Stevens wrote:
> Kevin,
>
> this is a know bug in 2.2.11 introduced by one of the release candidates.
>
> Kevin Monceaux wrote:
>   
>> DBMail Users,
>>
>> I've recently given DBMail a try.  I'm a e-mail pack-rat and am
>> exploring options to improve performance with some relatively large
>> mailboxes.  After creating a couple of test mailboxes and copying some
>> messages to them I am seeing some odd behavior under Mutt.
>>
>> I have piles of hierarchical mailboxes.  I created a few test mailboxes.  As
>> an example, two of the mailboxes I created are named Announcements and
>> General which are under an ArchLinux "folder," as:
>>
>> INBOX/ArchLinux/Announcements
>> INBOX/ArchLinux/General
>>
>> The hierarchical folders seem to be confusing Mutt.  When I'm at the
>> level where it should be displaying the ArchLinux folder, it's
>> displaying it, and other folders with mailboxes under them, multiple
>> times:
>>
>>  1   ../
>>  2   00-Inbox
>>  3   ArchLinux/
>>  4   ArchLinux/
>>  5   ArchLinux/
>>  6   MiscComp/
>>  7   MiscComp/
>>  8   Test
>>
>> If I select any of the ArchLinux folders listed above, the mailboxes
>> under ArchLinux display normally.  I was using Alpine a while back and
>> fired it up to compare.  I'm seeing similar symptoms under Alpine:
>>
>> 00-Inbox Test ArchLinux/   ArchLinux/   ArchLinux[/]
>> MiscComp/MiscComp[/]
>>
>> Also, Mutt is displaying all unread messages as new messages, even if
>> I close a mailbox and go back into it.  None of the unread messages
>> are showing up as "old" messages as they should be.  I'm not seeing
>> that problem under Alpine.
>>
>> I've used Mutt, and Alpine, with Cyrus in the past with no problems.
>> After trying DBMail, installed locally on my home desktop box, I
>> installed Dovecot locally and tested with similar mailboxes and had no
>> problems with either Mutt or Alpine.
>>
>> I'm running DBMail 2.2.11 installed via the ArchLinux package manager.
>>
>>
>>
>>
>>
>> 
>
>
>   


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] #noattach mode

[Jonathan Feally]

Not to burst anyone's bubble, but we are talking about having dbmail 
intelligently rewrite messages for clients on demand.  This could pose a 
big problem with the fact that all messages in a mailbox would have to 
be rewritten before a simple list command could be fulfilled as the size 
of the messages would be different than what is stored already in a 
column. And future versions of dbmail may fix the message differently, 
thus storing the new value could also prove pointless. While I like the 
idea, I don't think it will be a feasible solution without a lot of work 
to the code, and a huge load on the database and dbmail servers at 
runtime. As an IT administrator, I don't want to be the one who gets the 
call from the CEO when his email on his blackberry is missing the very 
important forwarded as an attachment email is replaced with a message - 
attachment(s) is/are available on your non-mobile client.

2 more cents
-Jon

Paul J Stevens wrote:
> Michael Monnerie wrote:
>   
>> On Montag 11 Mai 2009 Paul J Stevens wrote:
>> 
>>> Of course, you could have some mailclient use the virtual userid
>>> directly without using usermap, but with usermap all you'd have to
>>> explain to your mobile device users would be to use an other
>>> servername.
>>>   
>> But that servername needs another IP, right? I'd prefer not to waste an 
>> IP just for #mobile mode. Another port is sufficient. It should be 
>> possible to use it with another IP, though, as some clients might need 
>> that.
>> 
>
> Your choice. Usermap is keyed on the socket. As long as it's unique,
> it's ok. Dedicated port, dedicated ip, should all be fine.
>
>   
>> Not to be nasty with the name, but I think #mobile is not as good a name 
>> as #noattach, as I might want #mobile mode also when I'm at an internet 
>> cafe, or other environment.
>> 
>
> ?? Most people are mobile when in a cafe. Also #mobile sounds better (no
> negative) to me.
>
>
>
>   


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] /tmp full

[Jonathan Feally]

A simple way to determine who is at fault is to set 
tmpdir=/usr/mysql.tmp in my.cnf.

cd /usr
mkdir mysql.tmp
chown mysql:mysql mysql.tmp
vi /usr/local/etc/my.cnf
insert or change tmpdir=/usr/mysql.tmp (this does not effect the 
/tmp/mysql.sock)

/usr/local/etc/rc.d/mysql-server restart
restart dbmail daemons as well.

Test for a while and report.

Also, you should be testing with the maxconnections=100 and the patch 
that Paul posted a couple of days ago.

-Jon

Andrea Brancatelli wrote:
> litio# df -h
> Filesystem SizeUsed   Avail Capacity  Mounted on
> /dev/ad1s1a1.9G1.1G670M63%/tmp
>
> litio# /usr/local/etc/rc.d/dbmail-imapd restart
> Stopping dbmail_imapd.
> Waiting for PIDS: 78180, 78180.
> Starting dbmail_imapd.
>
> litio# df -h
> Filesystem SizeUsed   Avail Capacity  Mounted on
> /dev/ad1s1a1.9G 18K1.8G 0%/tmp
>
>
>
> -- 
> This message has been scanned for viruses and
> dangerous content by *MailScanner* , and is
> believed to be clean.
> 
>
> ___
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>   


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] OT: DBMail Administrator (DBMA) Performance Fix

[Jonathan Feally]

Wallace Tan wrote:
> And what's the best admin interface for DBMail?
>
>   

Not to toot my own horn, but I did up a php based administration tool 
for managing your mailboxes and aliases along with added functionality 
to partition your users into manageable groups. See 
http://www.netvulture.com/nvcapps.php to download. Extract to your 
htdocs and follow INSTALL to get it up and running.

-Jon

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

[Dbmail] Are we alive or am I dead?

[Jonathan Feally]

Testing the lists. I haven't gotten anything since 3/19.

-Jon

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: [Dbmail] Resources on the project

[Jonathan Feally]

Anyone can help contribute to the project

Testing is always needed. Having people to checkout out that commits are 
doing what they are supposed to do along with not causing new issues 
helps keep the active commiters on track and being able to fix new bugs 
introduced faster.


Database optimization is one area where active development is not 
currently focused on, but can benefit everyone. Anyone who wants to help 
speed up queries, please keep in mind that we are now using LibZDB and 
are active on SQLite, PostGres, and MySQL. New queries should be 
compatible on all the backends. We are trying to avoid putting in 
different queries for different database systems.


I believe that Paul is currently focusing on improving the daemons 
efficiency by removing the single char reads with buffered reads.


I've been asleep at the wheel for a bit, so I don't really have much 
going with my own development and I'm really more in testing mode now.


The main thing to do, if you are going to work with the C code, is to:
1. Check with the dev list to see if your goal(s) are inline with the 
projects goal(s). There is nothing worse than putting in hours of work 
on a patch to add a new feature where that feature was already shot down 
in the past and would leave you with a patch that won't be committed.
2. Coordinate with others actively coding to make sure that you are 
working on a different problem, so that your changes won't conflict with 
other's changes, making a nightmare to take A + B to turn it into AB

3. Format your patches using git diff and send them to the dev mailing list.

Potential and Current Developers should checkout:
http://git.dbmail.eu/?p=paul/dbmail;a=summary
http://www.dbmail.org/dokuwiki/doku.php

To get started with your own git copy
`git clone git://git.dbmail.eu/paul/dbmail`
After that - update your copy with new commits
`git pull`

Thats all for now - Jon - A.K.A. The NetVulture

Jorge Bastos wrote:

Paul always let more people to participate on the project.
Just send a patch and he will include it.

I don't think he's not having time for the project, he may be 50% of his
time on it!




  

-Original Message-
From: dbmail-boun...@dbmail.org [mailto:dbmail-boun...@dbmail.org] On
Behalf Of Joan Moreau
Sent: quarta-feira, 25 de Fevereiro de 2009 21:24
To: DBMail mailinglist
Subject: [Dbmail] Resources on the project


Hi Paul,

It seems that you barely have any time on this project. How to move on
and let more people contribute to this project ?

Thanks

JM
___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail



___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

  



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] sieve error on 2.2.10

[Jonathan Feally]

This error would be coming from the fact that your script is trying to 
use notify. As far as I can tell, dbmail is currently not supporting the 
use of notify, despite setting sieve_notify=yes. There is processing of 
notify, but not supported nor maintained as far as I can tell given:

excerpt from src/modules/sortsiecve.c:
  if (sieve_config.notify) {
   TRACE(TRACE_ERR, "Sieve notify is not supported in this 
release.");

   sieve2_callbacks(sieve2_context, notify_callbacks);
   }

Please check the script for the affected user(s) for the use of notify 
and remove that call. That should stop your issue.


-Jon

7CREWZ wrote:

hello to all

 i already set to  SIEVE_NOTIFY  = no nut error still 
comout to user


On Mon, Jan 19, 2009 at 1:28 PM, Niki Guldbrand > wrote:


Hi.

On Mon, 2009-01-19 at 10:33 +0800, 7CREWZ wrote:
> Hello to all
>
> Can any body know what is this and howto stop this message below
from
> entering user mailbox im using dbmail 2.2.10
> please help urgent 

Have you had a look in /etc/dbmail/dbmail.conf, and made sure that
this
option is disabled ?

#
# Turn on/off the Sieve Notify extension
#
SIEVE_NOTIFY  = no


>
>  Subject:  Sieve script parse
>   errorFrom:  dbmail-mai...@dbmail
>
>  Your
> Sieve script [phpscript] failed to parse correctly.
>  Messages
> will be delivered to your INBOX for now.
>  The error message
> is:
>  unsupported feature: notify


___
DBmail mailing list
DBmail@dbmail.org 
https://mailman.fastxs.nl/mailman/listinfo/dbmail



--
This message has been scanned for viruses and
dangerous content by *MailScanner* , and is
believed to be clean.


___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
  



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] Installation issues

[Jonathan Feally]

in your dbmail.conf change
pid_directory = /var/run/dbmail*

*then create the /var/run/dbmail directory owned by nobody:nogroup.

Launching the daemon will then work properly.

-Jon

Cody Stewart wrote:
Thanks. That fixed that issue. Now I am having an issue with starting 
the imapd.

version = dbmail 2.3.5

Jan 14 16:01:47 hostname dbmail/pop3d[3372]: [0x8309cc8] 
EMERGENCY:[pidfile] pidfile_create(+113): Cannot open pidfile 
[/var/run/dbmail-pop3d.pid], error was [Permission denied]


the nogroup and nouser are there.



On Wed, Jan 14, 2009 at 12:48 PM, Paul J Stevens > wrote:



Your version of glib is too old. This particular function appeared in
glib-2.14



Cody Stewart wrote:
> make all returns this error. Any ideas?
>
>
>
> creating libdbmail.la  
> (cd .libs && rm -f libdbmail.la 
 && ln -s
> ../libdbmail.la  
libdbmail.la  )
> if gcc -DHAVE_CONFIG_H -I. -I. -I.. -I..-g -O2
> -I/usr/include/glib-2.0 -I/usr/lib/glib-2.0/include
> -I/usr/include/gmime-2.0 -I/usr/incl/glib-2.0/include   -W -Wall
> -Wpointer-arith -Wstrict-prototypes -MT main.o -MD -MP -MF
> ".deps/main.Tpo" -c -o main.o main.c; \
> then mv -f ".deps/main.Tpo" ".deps/main.Po"; else rm -f
> ".deps/main.Tpo"; exit 1; fi
> /bin/sh ../libtool --tag=CC --mode=link gcc  -g -O2
> -I/usr/include/glib-2.0 -I/usr/lib/glib-2.0/include
> -I/usr/include/gmime-2.0 -I/usr/incb/glib-2.0/include   -W -Wall
> -Wpointer-arith -Wstrict-prototypes -export-dynamic -lcrypt -L/lib
> -lglib-2.0   -L/lib -lgmime-2.0 -lz -lnsl -l  -lm -lmhash
-levent -lssl
> -lzdb -o dbmail-deliver  main.o libdbmail.la
 
> gcc -g -O2 -I/usr/include/glib-2.0 -I/usr/lib/glib-2.0/include
> -I/usr/include/gmime-2.0 -I/usr/include/glib-2.0
> -I/usr/lib/glib-2.0/include - -Wstrict-prototypes -o
> .libs/dbmail-deliver main.o -Wl,--export-dynamic  -lcrypt -L/lib
> -lgmime-2.0 -lz -lnsl -lgobject-2.0 -lglib-2.0 -lm -zdb
> ./.libs/libdbmail.so -Wl,--rpath -Wl,/usr/local/lib/dbmail
> ../.libs/libdbmail.so: undefined reference to
`g_string_append_vprintf'
> collect2: ld returned 1 exit status
> make[3]: *** [dbmail-deliver] Error 1
> make[3]: Leaving directory `/downloads/dbmail-2.3.5/src'
> make[2]: *** [all-recursive] Error 1
> make[2]: Leaving directory `/downloads/dbmail-2.3.5/src'
> make[1]: *** [all-recursive] Error 1
> make[1]: Leaving directory `/downloads/dbmail-2.3.5'
> make: *** [all] Error 2
>
>
>
>
>

>
> ___
> DBmail mailing list
> DBmail@dbmail.org 
> https://mailman.fastxs.nl/mailman/listinfo/dbmail


--
 
 Paul Stevens  paul at nfg.nl

 NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
 The Netherlandshttp://www.nfg.nl



--
This message has been scanned for viruses and
dangerous content by *MailScanner* , and is
believed to be clean.


___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
  



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] adding database rows and customize insert querys

[Jonathan Feally]

I think we could defiantly store the individual parts encrypted in 
dbmail_mimeparts and still have the hash on the un-encrypted data. But 
the key to encrypt and decrypt would need to be stored/obtained 
somewhere for imapd and pop3d to know how to read and write those blobs. 
The header caching though would have the un-encrypted lines for the main 
envelope (subject line too). Now even if the message is stored as 
encrypted blobs, the network traffic will still transmit the message to 
the client un-encrypted.


Again this comes back to what parts of the email you need to have encrypted.

-Jon

Michael Monnerie wrote:

On Donnerstag 18 Dezember 2008 Paul Stevens wrote:
  

cat message.eml|myencryptingfilter.sh --key $pgpid|dbmail-smtp -u
$user



Yes, but I think he meant you cannot read it with POP/IMAP then. His 
goal seems to be to have encryption on the server, only for the stored 
e-mail. BUT then the encryption+key has to be on the server, so in case 
of a hacker attack it's still easy for the hacker to get db contents + 
encryption tool+keys. And then I ask: what do you want to reach?


mfg zmi
  



___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
  



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] git head no sieve?

[Jonathan Feally]

Confirmed. Working again.

-Jon

Uwe Kiewel wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Uwe Kiewel schrieb:
  

Paul J Stevens schrieb:


Uwe Kiewel wrote:
  

Paul J Stevens schrieb:


I haven't touched the sieve layer at all. Just return values from sql
calls setting the is_active field.
Uwe Kiewel wrote:
  

Paul J Stevens wrote:


fixed now.

  

Sure. Your fixed the command line utility. But maybe you broke the sieve
functionality?


No warranties, remember?
But: git-log -p is your friend.
  

This script won't work:


Plz include some logs.
  

lmtp is selecting the sieve script, but thats it about sieve. Loglevel
was 511.


selecting yes, but not running. Fixed now.
  

Update done. Lets see what happens.



Well done, Paul. It works.

Thanks a lot,
Uwe
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.7 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iQIVAwUBSUbTTEJXG7BUuynnAQISKA/7BtTzNeiVIDlhLeT8C5uWfH4wa3M+4ExW
xQpg9DeCTVGQsy7Tabbdt6ABuuTD11kZUzyiPExuomb9AkU+pG0RDTE6i+81lOtN
j2n45b38ljcLn+rzzvttyyxkT+wwIwxyatC/wGBz/AhGPs0FKgDyGERL9CR4SSVJ
97DZVc2mzrGhmx/iOaEVPOKhzVNzvD66dlTP70AuPftMy40Kt75PYCIphmlm67S7
eeozigm8uBIByAJEO00+ZzjSrbRN29z5L+2551dZe/3PmFHG+fQQZBsYtpww6r+q
iI/ucG4fUJickzFIRAta/oSngeAn7q5gV0yhAOzj/CK8DVPdpXpon6Kb/0bO1/Vn
gIauf4P44sSJN/sulLJWNsT50YsXDxZQbGCrramGcuSb/x1e+R5JkNs2cfTc8Jyh
KEme36kWabixGhTr4oKsopBs4EQsr3GTm8/ARzBnMXwSmRLYT5lF6RgxgbqqQV3u
L528FHfE634jQ1vqaqo2rd1tD3w2+OqFCXDvS1zWo6E4H5MkKbWZbi+UxAc+whln
hTd05EmvlZ3N4toVrCv5OoltmOo/E4LQ9Nn+SRM7HVtWHoe9E1Zls9eIN1lr2ube
gfGSgXAyFVNUtG1k6uy3JyOsEGQ7PlBW08lKIiWwryjRIM/r79gingl+11r4i7z2
Hbso9aXhsuo=
=B75m
-END PGP SIGNATURE-
___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

  



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] git head no sieve?

[Jonathan Feally]

Testing now. Will report back shortly.

-Jon

Paul J Stevens wrote:

Uwe Kiewel wrote:
  

Paul J Stevens schrieb:


I haven't touched the sieve layer at all. Just return values from sql
calls setting the is_active field.
  
Uwe Kiewel wrote:
  

Paul J Stevens wrote:


fixed now.

  

Sure. Your fixed the command line utility. But maybe you broke the sieve
functionality?


No warranties, remember?
But: git-log -p is your friend.
  


This script won't work:


Plz include some logs.
  

lmtp is selecting the sieve script, but thats it about sieve. Loglevel
was 511.



selecting yes, but not running. Fixed now.

Thanks.

  



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] git head no sieve?

[Jonathan Feally]

Same problem for me. Haven't looked into it myself yet, but my Inbox is 
getting full of mailling list items.


-Jon

Uwe Kiewel wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Paul J Stevens schrieb:
  

I haven't touched the sieve layer at all. Just return values from sql
calls setting the is_active field.

Uwe Kiewel wrote:


Paul J Stevens wrote:
  

fixed now.



Sure. Your fixed the command line utility. But maybe you broke the sieve
functionality?
  

No warranties, remember?
But: git-log -p is your friend.




This script won't work:
  

Plz include some logs.




lmtp is selecting the sieve script, but thats it about sieve. Loglevel
was 511.

Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_stmt_prepare(+391): [0x19600b0] [SELECT name FROM dbmail_sievescripts
WHERE owner_idnr = ? AND active = 1]
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_stmt_set_u64(+407): [0x19a2790] 1:[5]
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_con_close(+275): [0x19600b0] connection to pool
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Info:[message]
sort_deliver_to_mailbox(+2004): useridnr [5] mailbox [INBOX]
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Debug:[db]
db_findmailbox(+2033): looking for mailbox with FQN [INBOX].
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Debug:[misc]
mailbox_remove_namespace(+193): [INBOX]
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_con_get(+257): [0x19600b0] connection from pool
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_stmt_prepare(+391): [0x19600b0] [SELECT mailbox_idnr FROM
dbmail_mailboxes WHERE owner_idnr= ?  AND name LIKE ? ]
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_stmt_set_u64(+407): [0x19c8b00] 1:[5]
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_stmt_set_str(+397): [0x19c8b00] 2:[INBOX]
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_con_close(+275): [0x19600b0] connection to pool
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Debug:[db]
db_find_create_mailbox(+2623): mailbox [INBOX] found
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Debug:[message]
sort_deliver_to_mailbox(+2019): Checking if we have the right to post
incoming messages
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_con_get(+257): [0x19600b0] connection from pool
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_query(+340): [0x19600b0] [SELECT name,seq FROM dbmail_mailboxes WHERE
mailbox_idnr=2]
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
log_query_time(+290): last query took [0.001] seconds
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_con_close(+275): [0x19600b0] connection to pool
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910]
Debug:[MailboxState] db_getmailbox_seq(+455): id: [2] name: [INBOX] seq
[1102]
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_con_get(+257): [0x19600b0] connection from pool
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_query(+340): [0x19600b0] [SELECT permission FROM dbmail_mailboxes
WHERE mailbox_idnr = 2]
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
log_query_time(+290): last query took [0.001] seconds
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_con_close(+275): [0x19600b0] connection to pool
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_con_get(+257): [0x19600b0] connection from pool
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_query(+340): [0x19600b0] [SELECT 'a',COUNT(*) FROM dbmail_messages
WHERE mailbox_idnr=2 AND (status < 2) UNION SELECT 'b',COUNT(*) FROM
dbmail_messages WHERE mailbox_idnr=2 AND (status < 2) AND seen_flag=1
UNION SELECT 'c',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=2 AND
(status < 2) AND recent_flag=1]
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
log_query_time(+290): last query took [0.002] seconds
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_con_clear(+282): [0x19600b0] connection cleared
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_con_close(+275): [0x19600b0] connection to pool
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910]
Debug:[MailboxState] db_getmailbox_count(+370): exists [23] unseen [23]
recent [1]
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_con_get(+257): [0x19600b0] connection from pool
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
db_query(+340): [0x19600b0] [SELECT MAX(message_idnr)+1 FROM
dbmail_messages WHERE mailbox_idnr=2]
Dec 15 22:12:28 server dbmail-lmtpd[6267]: [0x1949910] Database:[db]
log_query_time(+290): last query took [0.001] seconds
Dec

Re: [Dbmail] Message filtering.

[Jonathan Feally]

You would need to setup sieve scripts for your mailboxes. See 
http://www.dbmail.org/dokuwiki/doku.php?id=sieve for how to write a 
script. This script will then do things with messages as they are 
delivered to dbmail via the dbmail-deliver (pipe method) or dbmail-lmtpd 
(LMTP delivery). Messages inserted via IMAP would not be processed, thus 
if you move a message from a folder to the inbox that was filtered in 
the first place, it wouldn't disappear on you back to the sorted folder.


-Jon

Ken D'Ambrosio wrote:

Hi, all.  I'm seriously thinking of trying out DBMail, but I don't
understand one particular thing: message filtering.  Right now, I'm using
MailScanner to flag messages, and then I use procmail to filter my
messages based on header; ones flagged as spam go into the spam folder,
and everything else gets filtered based on where it's from (e.g., mail
from the Postfix mailing list goes into my Postfix directory).  Since
that's done with Maildir, it's a pretty straightforward mechanism.  How do
you go about that with DBMail?

Thanks!

Ken D'Ambrosio

P.S.  Sorry if this is a duplicate; I'd tried sending it before, but I
guess I wasn't properly subscribed.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

  



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] About virtual domain routing

[Jonathan Feally]

DBMail currently runs on only one database. Thus all email for your 3 
domains would be in one database on one server. The number of front-end 
servers (dbmail-imapd) can be as many as you like - all accessing the 
MySQL/PostGres db over the network. Now you could run 3 databases, 3 
front-ends, each separated out to handle each domain, but you will only 
be replacing your current IMAP with dbmail.


This leaves you with 2 options besides the all on one server.

Option 1
Put each domain on a different port number and map those port numbers to 
each of the boxes on your firewall. Clients would then need to be 
configured to use a hostname and port number, where the hostname on the 
inside looks up to the IP of the server they need, and the hostname on 
the outside looks up to your public IP (thus all outside hostnames would 
have the same IP).


Option 2
Run an imap proxy server that can understand from a small database or by 
domain name which users are on what imap servers. 
http://www.imapproxy.org or http://www.vergenet.net/linux/perdition/ 
would be a good place to start. This will let you use your current IMAP 
servers as if they were all one from the outside.  You could also move 
to dbmail as one instance per domain, but the imap proxy part is really 
what you need to run multiple backends merged as one front-end.


-Jon

Stephen Liu wrote:

Hi folks,


Can dbmail help me.?


I'm searching around for a solution to solve my problem as mentioned
hereinbelow;


I have a Xen box (a virtualizaion box) with following setup:-


MTA - Postfix 2.3.8
Database - MySQL 5.0.32
Couier IMAP 4.1.1
etc.


Host - Debian Etch Workstation
Guest01 - mail server for routing running postfix virtual
Guest02 - mail server, hostname01. localIP_01, domain01
Guest03 - mail server, hostname02, localIP_02, domain02
Guest04 - mail server, hostname03, localIP_03, domain03
etc.

all mail servers running postfix on Debian Etch.

single (one) external IP with all ports forwarded to Guest01 except 
port 53 which is forwarded to the DNS server, also running on a guest

of the Xen box.


Guest01 routes all incoming mails according to their domains to 
respective mail servers.  All outgoing mails are sent via the routing

mail server, Guest01, to Internet.


The system is working nicely on Intranet.  Mail client, Evolution, on
workstations connected to local network, the Intranet, can send/receive

mails on mail servers.  Server settings on mail client work on 
either local IP or on hostname.  



My problem is if the workstation is on remote site, not on local
network, with one external IP how can the remote mail client find the 
mail server which it needs to connect.  



Can dbmail help me out?  If YES Please shed me some light how to start?
 Where can I find relevant document on its setup to solve my problem. 


TIA


B.R.
Stephen L

Send instant messages to your online friends http://uk.messenger.yahoo.com 
___

DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

  



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] DBMail 2.3.4 released

[Jonathan Feally]

Have a look at 
http://git.dbmail.eu/?p=paul/dbmail;a=tree;f=sql;h=db840570d289ebbe1d77101724ec35a37ae03cf7;hb=master


From there click on your database platform, then click on history next 
to the create_tables file.


2.3.2 came out on 2008-02-09, so any commits past date you should click 
on the commitdiff to see what tables are touched.


The latest commit "misc fixes" is only for code that is after the 
tarball - so don't do that change.


For mysql is looks like the dbmail_mimeparts.hash column was changed 
from char(64) to char(128). That is the only change to the schema for 
that platform.


Simply do a:
ALTER TABLE dbmail_mimeparts MODIFY COLUMN `hash` char(128) NOT NULL;

That should be safe to do.

Don't drop this table as all of your mail is in it.

a mysqldump of the database would be a good idea before fooling with it 
though, just in case.


-Jon

dan wrote:

On Fri, 14 Nov 2008 14:48:01 +0100, Paul J Stevens <[EMAIL PROTECTED]> wrote:

  

Hi all,

It is with great pleasure that I'm announcing the availability of DBMail
version 2.3.4, the latest in the 'unstable' development series.



Cool. Will update soon. Any caveats moving ( home server ... no I'm not
using in production ) from 2.3.2 to 2.3.4 ... ie are there any rolling
changes I need to apply to the DB from 2.3.3?

Dan

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

  



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] imapd error

[Jonathan Feally]

It looks like you have either a db schema issue, or database server problem.

Please do a show create table dbmail_messages;
You should have a recent_flag field.
If you are missing that field (unlikely) do a alter table 
dbmail_messages add column `recent_flag` tinyint(1) NOT NULL DEFAULT '0';


You should also check the output for other fields that are missing 
against the create_tables.mysql file in the dist.


Also, what version of MySQL are you running?

-Jon

Jon Duggan wrote:

I have had a few occasions where users say they have lost email, are these 
errors possibly related?
  

That would be the first time, afaik. I can't tell if there's a relation.



This possibly isn’t dbmail related (could be our spam filtering)

  

Next time you see this error, try find out what query triggers the error.



Full log level 5 for a session with such an error can be found here;

http://pure.noffle.net/~jduggan/debug5.imapd.out

If you need further information do let me know.

TIA

Jon

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

  



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] Re: which tables

[Jonathan Feally]

I thought I suggested that??? If you want all the messages in the old 
db, then the auto_increment will be much faster and more reliable. If 
you are wanting to filter out some messages such as only transfer mail 
that is <1 year old, then imapsync would be a better choice for that. If 
you already have mail in your new db, you can use the mysql update to 
move those new messages to a new number. You will also want to update 
you phsmessage_id to a number bigger than the largest on the old db.


-Jon

Paul Stevens wrote:

Curtis Maurand wrote:

Hello,

the migration is complete. I moved the following tables and 
everything worked out just fine.  If I need to get the messages back 
in, I'll load up dbmail on a test server and use IMAPsync to transfer 
the messages.  


don't use imapsync for that! If you setup the other tables with the 
correct auto_increment values like I suggested, you can probably 
simply transfer and load dumps of the other tables. Much more 
efficient than imapsync.



I've got a dump of the original database.






--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] Which tables

[Jonathan Feally]

One option to migrate to a new server and still retain the old messages, 
but with a delay, is to start your new database with an auto_increment 
number for the dbmail_messages table that is greater than the 
max(message_idnr) on your existing dbmail_messages table. Then you can 
import  a dump (use dbmail-util -d -p -y twice to avoid coping deleted 
messages) with out the table drop/create for the tables Paul mentioned 
that could be skipped. Once you have imported all of the old data, even 
after new messages have been delivered, simply do an update of 
dbmail_messages to change the message_idnr to start an increment from a 
number higher than the current max(message_idnr) on the new database. 
Finally run a dbmail-util -ay twice to make sure all the cache tables 
are golden. IMAP clients should see all new headers for downloading 
(even messages that were seen during the data move process). Pop3 
clients using leave a copy on the server may or may not have issues with 
exiting messages that were already downloaded. If nothing else, all the 
messages would be seen as new and needed to be downloaded.


The update process for dbmail_messages
mysql dbmail #connect to db
mysql> SELECT MAX(message_idnr) FROM dbmail_messages;
+---+
| MAX(message_idnr) |
+---+
| 31252 |
+---+
1 row in set (0.00 sec)

mysql> set @x:=10;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE dbmail_messages SET [EMAIL PROTECTED]:[EMAIL PROTECTED];
Query OK, 11252 rows affected (3.33 sec)
Rows matched: 11252  Changed: 11252  Warnings: 0

mysql> SELECT MAX(message_idnr) FROM dbmail_messages;
+---+
| MAX(message_idnr) |
+---+
|111252 |
+---+
1 row in set (0.00 sec)

-Jon

Paul J Stevens wrote:

Curtis Maurand wrote:
  

I'm trying to move user accounts and mailbox structure without moving
the messages.



understood. Users will suddenly start seeing empty folders.

very tricky.

If you carefully setup all tables and dump/reload all tables *except*
dbmail_messageblks you will avoid transferring the bulk of your data
while retaining user experience.

After setting up the new host users will start getting errors if the try
to retrieve an /old/ message until you transfer the messageblks table as
well.

You must take special care to setup the auto-increment values correctly
for the empty messageblks table. So use 'mysqldump -h oldhost -d
dbmail|mysql -h newhost dbmail' if you ever mean to transfer the old
email later on.

I've never actually done this myself, but these migration scenarios are
perfectly valid and deserve some exploratory experimentation, so please
keep us informed :-)

Of course if you don't mind if users suddenly start seeing empty folders
you can also skip:

| dbmail_ccfield|
| dbmail_datefield  |
| dbmail_envelope   |
| dbmail_fromfield  |
| dbmail_headername |
| dbmail_headervalue|
| dbmail_mailboxes  |
| dbmail_messages   |
| dbmail_physmessage|
| dbmail_referencesfield|
| dbmail_replytofield   |
| dbmail_subjectfield   |
| dbmail_tofield|






  

 I have bandwidth limits not the least of which is that
the current system is residing on a T1, is several GB in size (which
means hours to transfer) and I have bandwidth and diskspace limits on
the new host.

Thanks,
Curtis

Paul J Stevens wrote:


Curtis Maurand wrote:
  
  

Hello all,

I find this list to be incredibly helpful.  Thank you all in advance of
my question.

I need to move my installation from the current server to another.  That
could be accomplished by simply doing a mysqldump -u  -p
--database dbmail |mysql -u -p -h  dmail

That's known and been done and that's great, but I don't want to move
several GB of mail.  I only want to transfer the user accounts, mailbox
structure and sieve scripts.  Which tables do I need to copy to the new
server?  I'm running 2.2.9 on the older machine and 2.2.10 on the newer
unit.



Why? It's not obvious what you're trying to achieve here.


  
  



___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail




  



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] Change Default Folder Names

[Jonathan Feally]

As far as I know, only INBOX is created. The other folders are created 
by the client when logging in with IMAP. INBOX will always be INBOX. You 
will have to experiment with other language releases of the email client 
on a fresh user account to see if there is any difference.


-Jon

Wilfredo Pachón wrote:

Hello friends

Some one knows how to "translate" default folder names when the user
is created or recieve is firts mail.

All the users are created with 6 Folders:
-INBOX
-Drafts
-Junk
-Sent
-Templates
-Trash

And i need that the names appears in other language or with other names.

Thanks a lot.

  




--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] feature for dbmail-util

[Jonathan Feally]

I was looking at the code for trunk/2.3.x and it looks like the vacuum 
switch is calling a function to do nothing. From the descriptions of 
what the -c flag should do, i think this is the function that used to do 
this and should be populated with the optimization of the tables code. 
Or make a new switch like -o to do the optimize table calls. It may also 
be advantageous to break the optimization into 2 parts - the caching and 
message link tables, and then the partlists and mimeparts. The time it 
takes to do all the tables on a larger set of data may make it easier to 
clean up the caching and message tables to keep operations running 
faster with a daily or 2 day interval run on those tables, then a 
cleaning of the mimeparts and partlists tables every 2 weeks.


-Jon

Uwe Kiewel wrote:

Jorge Bastos schrieb:

Is it an option to include the "OPTIMIZE TABLE" parts into the code of
dbmail-util? Let's discuss about advantages and disadvantages!


I think it can be an idea, but only with plus one switch for it.



Of course. Only with an additional switch - not per default.

Uwe
___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail





--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] Db growing size

[Jonathan Feally]

Just the partlists and mimeparts weren't being cleaned out. The normal 
tables cleaned in 2.2 were being cleaned, just not those new storage 
system tables. Do make sure you backup all your data using mysqldump 
before running it. I think it is fine to run but make no guarantee.

Also - the patch does not do the optimize table - just the delete's.

-Jon

Jorge Bastos wrote:

Hum... that must be it then, dbmail-util not deleting marked to be deleted
data!
Going to do my tests and let you know.

Paul, did you forgot dbmail-util to clean the new dbmail_mimeparts? :P
(I think Paul is on vacation, and he deserves!)



  

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Jonathan Feally
Sent: terça-feira, 12 de Agosto de 2008 21:39
To: DBMail mailinglist
Subject: Re: [Dbmail] Db growing size

I looked at the code for dbmail-util and the cleanup of the partlists
and mimeparts tables are not there. Thus your mimeparts will never be
cleared out of parts that are no longer in ues.

Run the normal dbmail-util -dy; dbmail-util -py; dbmail-util -cy to
clean out any messages pending db deletion and shrink the db files as
much as possible.

Then get a total byte count for the mail that is stored, and current
disk usage for the db.

Then run these queries in this order to remove part listings for
messages that have been purged, then to remove the parts that are no
longer referenced by the partlists.

DELETE dbmail_partlists.* FROM dbmail_partlists LEFT JOIN
dbmail_messages USING (physmessage_id) WHERE
dbmail_messages.message_idnr IS NULL;

DELETE dbmail_mimeparts.* FROM dbmail_mimeparts LEFT JOIN
dbmail_partlists ON (dbmail_mimeparts.id=dbmail_partlists.part_id)
WHERE dbmail_partlists.physmessage_id IS NULL;

OPTIMIZE TABLE dbmail_partlists;

OPTIMIZE TABLE dbmail_mimeparts;

Then report the current disk usage for the db after running these. It
should shrink quite a lot

I think that this will solve your issue. Attached is a hacked patch to
do these for you on a -py run. I have tested it with my 5.1 mysql
server, but not with sqlite or postgres.

Enjoy,
-Jon

Jorge Bastos wrote:


You're running into my major complaint about innodb.  It never gives
space back.  the file continues to grow until it uses all available
resources.  I have heard about altering the storage engine to MyISAM
and then changing it back to get your disk space back, but I've never
tried it.  running mysqloptimize will only make the file grow larger.


That cannot be done, if I do it I'll lose foreign keys.(on
dbmail_mimeparts I could do it since it doesn't have any foreign
  

keys)


But yes, doing that will free space.
InnoDB table space, never goes back on file size, true, but, if I
  

only


have about 9GB of email, why the 53GB of size?

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail


  

--
This message has been scanned for viruses and dangerous content by
MailScanner, and is believed to be clean.




___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

  




--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] Db growing size

[Jonathan Feally]

I looked at the code for dbmail-util and the cleanup of the partlists 
and mimeparts tables are not there. Thus your mimeparts will never be 
cleared out of parts that are no longer in ues.


Run the normal dbmail-util -dy; dbmail-util -py; dbmail-util -cy to 
clean out any messages pending db deletion and shrink the db files as 
much as possible.


Then get a total byte count for the mail that is stored, and current 
disk usage for the db.


Then run these queries in this order to remove part listings for 
messages that have been purged, then to remove the parts that are no 
longer referenced by the partlists.


DELETE dbmail_partlists.* FROM dbmail_partlists LEFT JOIN 
dbmail_messages USING (physmessage_id) WHERE 
dbmail_messages.message_idnr IS NULL;


DELETE dbmail_mimeparts.* FROM dbmail_mimeparts LEFT JOIN 
dbmail_partlists ON (dbmail_mimeparts.id=dbmail_partlists.part_id) WHERE 
dbmail_partlists.physmessage_id IS NULL;


OPTIMIZE TABLE dbmail_partlists;

OPTIMIZE TABLE dbmail_mimeparts;

Then report the current disk usage for the db after running these. It 
should shrink quite a lot


I think that this will solve your issue. Attached is a hacked patch to 
do these for you on a -py run. I have tested it with my 5.1 mysql 
server, but not with sqlite or postgres.


Enjoy,
-Jon

Jorge Bastos wrote:

You're running into my major complaint about innodb.  It never gives space
back.  the file continues to grow until it uses all available resources.  I
have heard about altering the storage engine to MyISAM and then changing it
back to get your disk space back, but I've never tried it.  running
mysqloptimize will only make the file grow larger.


That cannot be done, if I do it I'll lose foreign keys.(on dbmail_mimeparts
I could do it since it doesn't have any foreign keys)
But yes, doing that will free space.
InnoDB table space, never goes back on file size, true, but, if I only have
about 9GB of email, why the 53GB of size?

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

  



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Index: maintenance.c
===
--- maintenance.c   (revision 3052)
+++ maintenance.c   (working copy)
@@ -402,6 +402,16 @@
return db_update("DELETE FROM %smessages WHERE status=%d", DBPFX, 
MESSAGE_STATUS_PURGE);
 }
 
+static int db_deleted_partlists(void)
+{
+   return db_update("DELETE %spartlists.* FROM %spartlists LEFT JOIN 
%smessages USING (physmessage_id) WHERE %smessages.message_idnr IS NULL", 
DBPFX, DBPFX, DBPFX, DBPFX);
+}
+
+static int db_deleted_mimeparts(void)
+{
+   return db_update("DELETE %smimeparts.* FROM %smimeparts LEFT JOIN 
%spartlists ON (%smimeparts.id=%spartlists.part_id) WHERE 
%spartlists.physmessage_id IS NULL", DBPFX, DBPFX, DBPFX, DBPFX, DBPFX, DBPFX);
+}
+
 static int db_deleted_count(u64_t * rows)
 {
C c; R r; int t = FALSE;
@@ -444,8 +454,22 @@
return -1;
}
qprintf("Ok. Messages deleted.\n");
-   }
-   return 0;
+   qprintf("\nDeleting orphaned partlists...\n");
+   if (! db_deleted_partlists()) {
+   qerrorf ("Failed. An error occured. Please check 
log.\n");
+   serious_errors = 1;
+   return -1;
+   }
+   qprintf("Ok. Partlists deleted.\n");
+   qprintf("\nDeleting unused mimeparts...\n");
+   if (! db_deleted_mimeparts()) {
+   qerrorf ("Failed. An error occured. Please check 
log.\n");
+   serious_errors = 1;
+   return -1;
+   }
+   qprintf("Ok. Mimeparts deleted.\n");
+   }
+   return 0;
 }
 
 int do_set_deleted(void)
___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] DBMAIL grinds to a craw in production

[Jonathan Feally]

Also make sure that your MySQL is not 5.1.23 from packages or ports. I 
had an issue with the sorting of large result sets losing rows. I had 
the issue on both i386 and amd64. DBMail did all kinds a weird things 
such as messages not showing up at all when headers were downloaded.


I'm glad that the new kernel is working better.

-Jon

[EMAIL PROTECTED] wrote:

Thanks Jon and all others for your great suggestions.

As Jon said, I'm drawing towards the conclusion that the issue was with some 
bug or incompatibility between FreeBSD 7 and MySql 5.1. FreeBSD 7 has a new 
scheduler but by default it still uses the older one. Things seemed to be much 
better after we recompiled the kernel with the new scheduler.

  

Also - My custom kernel config has to following non-standard options



  

options HZ=2000
options SCHED_ULE   # ULE scheduler
options PREEMPTION  # Enable kernel thread preemption
options FULL_PREEMPTION # Enable kernel thread preemption
options IPI_PREEMPTION  # Enable kernel thread preemption
options SMP



Khanh
___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

  




--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] DBMAIL grinds to a craw in production

[Jonathan Feally]

Do Not use Mysql 5.1.23-rc on FreeBSD from ports/packages!!! Use 5.1.22. 
I ran into an issue where order by's were causing data from the result 
sets to be dropped. This was causing a lot of issues with dbmail imapd 
clients. There was a bug open on an issue with this with MySQL team. 
Essentially something in the ports build process for 5.1.23 was 
corrupting the sorting.


I always compile from source and I think that I had that issue with a 
manual compile as well on 5.1.23.


I am running 7-STABLE with MySQL 5.1.22-rc built from ports.

To build the older version from ports modify the version number in the 
Makefile to 5.1.22 and use the following for the distinfo file


MD5 (mysql-5.1.22-rc.tar.gz) = 87be813d1b2d76b1c756de0a16fa29fb
SHA256 (mysql-5.1.22-rc.tar.gz) = 
1bce64c7e8fe476828644cbc05157fc84fe02e9c13379d1611fbd7d27c0721ec

SHA1 (mysql-5.1.22-rc.tar.gz) = 97c1396e0f0561485df120cce2092192aecd12a0

My Mysql cconfig is as follows and is probably pretty bad, but access 
times are pretty fast for my few users. I have 8 GB of ram and quad 
opterons, so you may need to drop some of the numbers down


-BEGIN-
[client]
#password   = your_password
port= 3306
socket  = /tmp/mysql.sock

[mysqld]
port=3306
socket=/tmp/mysql.sock
myisam_recover
tmpdir=/usr/mysql.tmp
server-id=1
log-bin=mysql-bin
log-slow-queries=/var/log/mysqld-slow.log
log-output=FILE
character-set-server=utf8
innodb_force_recovery=0
innodb_file_per_table
skip-locking
default_table_type=INNODB
thread_concurrency=64
max_connections=512
max_allowed_packet=16M
#table_cache=1024
key_buffer_size=2G
innodb_buffer_pool_size=4G
innodb_additional_mem_pool_size=256M
innodb_autoinc_lock_mode=2
innodb_commit_concurrency=0
innodb_concurrency_tickets=32
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=32
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=2
table_cache=1024
thread_cache=32
query_cache_size=256M
sort_buffer_size=256M
read_buffer_size=256M
read_rnd_buffer_size=256M
myisam_sort_buffer_size=256M

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 256M
write_buffer = 256M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 256M
write_buffer = 256M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
open-files-limit = 3072
-END-

Also - My custom kernel config has to following non-standard options

options HZ=2000
options SCHED_ULE   # ULE scheduler
options PREEMPTION  # Enable kernel thread preemption
options FULL_PREEMPTION # Enable kernel thread preemption
options IPI_PREEMPTION  # Enable kernel thread preemption
options SMP


last pid: 77763;  load averages:  0.12,  0.19,  
0.15
up 1+05:13:58  22:38:57

187 processes: 1 running, 186 sleeping
CPU states:  0.2% user,  0.0% nice,  0.0% system,  0.2% interrupt, 99.6% 
idle

Mem: 5365M Active, 805M Inact, 450M Wired, 293M Cache, 214M Buf, 216M Free
Swap: 8192M Total, 388M Used, 7804M Free, 4% Inuse

 PID USERNAME  THR PRI NICE   SIZERES STATE  C   TIME   WCPU COMMAND
30517 nobody  1   40 55548K 16612K sbwait 0   1:23  0.29% 
dbmail-imapd

 965 mysql  41   40  7216M  4605M sbwait 2  37:53  0.00% mysqld

Just a little bit of ram in use on mysql!

Hope that helps.

-Jon


UEMURA (fka. MAENAKA) Tetsuya wrote:

Hi Khanh

I don't know the exact scenario how your MySQL halts though, it may be
caused by the bug in the MySQL's query optimizer.

If you see a query "SELECT messageblk, is_header FROM dbmail_messageblks
WHERE physmessage_id = NNN ORDER BY messageblk_idnr" doing a full table
scan, the bug is your cause, and the attached patch (against 2.2 branch)
should fix.

Note that how the bug affecting can be seen in the attached mysql
session log. In which, the 2nd "EXPLAIN SELECT ..." chose the incorrect
key requiring a full table scan.

FreeBSD 7.0-BETA2 as of Nov. 3 2007
MySQL 5.1.23-rc as of Dec. 14 2007
DBMail 2.2 as of Dec. 13 2007

  



___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
  



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] Re: DBMail 2.3.0 released

[Jonathan Feally]

I think that we should also add a column that specifies what hashing 
method(s) the hash value is on the row of the part. This would allow 2 
things.


1) Re-Calculation of the hash value for a change in hashing methods 
could be interrupted and resumed later,


and
2) Message insertion can still occur where as a simple check to see what 
hashing methods are use in the table, a new part to be inserted (or not) 
can be checked against all hashing methods used (old and new) to ensure 
that a duplicate doesn't take place during this migration period.


-Jon

Michael Monnerie wrote:

On Montag, 7. Januar 2008 Paul J Stevens wrote:
  

Nono, the hash is no-longer the primary key. The primary is now an
auto-increment int.



INT8 (aka BIGINT), I hope.

mfg zmi
  



___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
  



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] Re: DBMail 2.3.0 released

[Jonathan Feally]

While there has been quite a bit of discussion already on this topic, 
and I know the purpose of this was to avoid collisions, has there been 
any thought in a configuration option to skip the blob comparison and 
assume they are the same? Perhaps a size threshold that has to be 
exceeded to skip the check. My only thought is a large attachment - say 
100-200 MB - has to be read from the db server and into memory. This 
would be very taxing on the network and memory of the whole system. For 
small shops with the db on the same box, its not that big of an issue, 
but for larger shops with a lot of email traffic, it could become an 
issue. A value of "0" should mean check every part, while of value of 
"16777216" would mean check all parts less than 16 MB. As file sizes 
increase, the likely hood of a size and hash collision is going to 
decrease, especially since larger attachments are rare compared to the 
1.5 MB jpeg.


-Jon


Paul J Stevens wrote:

Matija Grabnar wrote:
  

I re-iterate: regardless of which digest algorithm is chosen, the code
MUST be able to
detect and correctly handle collisions. Collisions WILL occur,
regardless of the algorithm
chosen. It is a mathematically provable fact.



For those of you who have been following this discussion: I've done this
thing.

- we now use the cryptographic hash only to quickly locate possibly
duplicate mime-parts, If the hash doesn't occur yet, a new mimepart is
stored using the hash, but generating an auto-increment bigint as it's
primary key. If the hash does occur, the insertion code compares the
blobs to make sure no hash collision occurs on different blobs.

- I've added support for a whole dumpload of hashes: we now support md5,
sha1, sha256, sha512, tiger and whirlpool. Since I'm relying on mhash
for this, it would be trivial to add other hashes like ghost, but I'm
currently restricting things to the ones documenten on the nessie (EU)
pages. Looking back, adding all these was probably not really necessary
for single-instance storage, but libmhash is rock-solid and widely
available, and I have a hunch they might come in handy along the road.


  




--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

[Dbmail] Feature request for 2.4.0

[Jonathan Feally]

Hello All,
While I know the Dev Team has plenty on their plate already, I would 
like to propose 2 new features to be included in the release of 2.4.0 
(whenever that comes out).


Feature 1: Database stored configuration leaving only the vital database 
connection information in dbmail.conf


CREATE TABLE `dbmail_config` (
 `config_idnr` bigint(21) NOT NULL AUTO_INCREMENT,
 `section` varchar(255) NOT NULL DEFAULT '',
 `option` varchar(255) NOT NULL DEFAULT '',
 `value` varchar(255) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

dbmail.conf:
   [DBMAIL]
   driver=mysql
   host=localhost
   sqlport=3306
   sqlsocket=/tmp/mysql.sock
   user=dbmail
   pass=dbmail
   db=dbmail
   table_prefix=dbmail_

Feature 2: A Database structure information tag stored in a good place, 
such as the configuration table from Feature 1 to allow for an upgrade 
script/program to correctly upgrade the database structure based upon 
the dbmail version to be run, the old dbmail version, and the db 
platform. While this is a big task, I think this feature will simplify 
upgrades so that the Dev Team does not have to worry that someones db 
structure is not correct when troubleshooting problems.



-Jon


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] POP/IMAP logging via syslog

[Jonathan Feally]

You can change the logged in /logging out messages in pop.c, 
dbmail-imapsession.c and imapcommands.c from a TRACE_MESSAGE to a 
TRACE_WARNING so you can use a TRACE_LEVEL=2. This will probably solve 
your problem. But you would need to change these everytime you compile a 
new version. I do see that some of the logout messages user the id 
number and not the name, but that should be better than nothing.


-Jon

Michael Monnerie wrote:

On Donnerstag, 2. August 2007 Anne wrote:
  

Michael Monnerie wrote:


Is there a way to log which users logged in via POP/IMAP? I don't
see that info, and raising trace doesn't help either.
  

I recently raised the trace level to 3 to have this info available.
Aug  2 14:26:53 server dbmail/pop3d[26391]: Message:[pop3]
pop3.c,pop3(+440): user  logged in [messages=0, octets=0]



Yes, but I do not want trace level 3 information for normal operation. 
There should be, however, a simple log just as the normal pop/imap 
daemons do:


Aug  7 13:28:32 power2u imapd[14078]: Login user=xx 
host=h081217116113.dyn.cm.kabsi.at [81.217.116.113]


Aug  7 13:29:03 power2u popper[14124]: Stats: xx 0 0 0 0 
62.178.35.112 62.178.35.112 [pop_updt.c:296]


I want to see which users log in how often, and if it was successful. 
ATM, I cannot even see when there's a dictionary attack on the server.


mfg zmi
  



___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
  




___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] New DBMail Admin web tool released!

[Jonathan Feally]

This is what will show on the interface. Set to whatever you want 
to call your seperation of users.

$group_tag="Client/Group/Department";

// This tool works only in the database. LDAP modification is not 
done. Changing quotas and group id's may not do anything at all.
// Set the next line to true to allow you to continue past the login 
screen.

$skip_ldap_auth_warn=false;



Jonathan Feally wrote:

Greeting All,

I am pleased to announce the release of my own DBMail Admin tool for 
DBMail 2.2.x. Yes - Its Free!


You can download it at my site 
<http://www.netvulture.com/nvcapps.php> 
http://www.netvulture.com/nvcapps.php - I have also added a link on 
the Wiki so you can easily find it again later to get updates. Just 
follow the INSTALL file inside the archive to get started. The rest 
should be child's play.


While you are there, take my DBMail usage survey to become a 
statistic and help DBMail developers better understand what the 
people are doing with DBMail and what you would like to see.


DBMail Admin is currently for users of SQL based authentication, but 
full LDAP support is being investigated.


All 3 database platforms are supported and should work provided your 
PHP 5 has the appropriate DB support.


Current Features:
Store a real name against your userid's
Change mailbox passwords
View mailbox usage and set quotas
Manage local and external aliases
Divide users into groups, clients, departments, whatever you want 
to call them as you set what is displayed on the screen

Create, Modify and Delete the groups, clients, etc.

Upcoming features (hopefully)
Manage folders for any user including #Public
Manage ACL's on folders
Work with LDAP attributes for group and quota settings.
Do dbmail-util runs on the fly

Possible features way down the road
Allow users to manage their sieve scripts
An integrated sieve script builder?


Questions, comments, suggestions, or what have you can be sent to me 
at [EMAIL PROTECTED] - Patches to get things working in your 
setup are always welcome.


While I have built this tool to be simple and use the dbmail-users 
binary for any complex operations, use this tool at your own risk. I 
am not responsible for anything that goes wrong.


Thanks and Enjoy

-Jon a.k.a. The NetVulture


___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
  






___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

RE: [Dbmail] DBMail usage poll

[Jonathan Feally]

Sorry about that. The field is meant to capture the number of logins, not the 
count of all folders. Some people refer to a login as a mailbox. I'll change 
the text to be more specific. Thank you for participating in the poll.

-Jon

-Original Message-
From: Aleksander <[EMAIL PROTECTED]>
Sent: Friday, July 27, 2007 2:36 AM
To: DBMail mailinglist 
Subject: Re: [Dbmail] DBMail usage poll

Jonathan Fealy wrote:
> The poll is hosted on my site at 
> http://www.netvulture.com/dbmail_usage_poll.php

What do you mean by users/mailboxes?

I entered in the format users/mailboxes like 765/2228, only the first 
number is displayed in stats.

-- 
Aleksander Kamenik
system administrator
+372 6659 649
[EMAIL PROTECTED]

Krediidiinfo AS
http://www.krediidiinfo.ee/
___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail





___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

[Dbmail] New DBMail Admin web tool released!

[Jonathan Feally]

Greeting All,

I am pleased to announce the release of my own DBMail Admin tool for 
DBMail 2.2.x. Yes - Its Free!


You can download it at my site  
http://www.netvulture.com/nvcapps.php - I have also added a link on the 
Wiki so you can easily find it again later to get updates. Just follow 
the INSTALL file inside the archive to get started. The rest should be 
child's play.


While you are there, take my DBMail usage survey to become a statistic 
and help DBMail developers better understand what the people are doing 
with DBMail and what you would like to see.


DBMail Admin is currently for users of SQL based authentication, but 
full LDAP support is being investigated.


All 3 database platforms are supported and should work provided your PHP 
5 has the appropriate DB support.


Current Features:
   Store a real name against your userid's
   Change mailbox passwords
   View mailbox usage and set quotas
   Manage local and external aliases
   Divide users into groups, clients, departments, whatever you want to 
call them as you set what is displayed on the screen

   Create, Modify and Delete the groups, clients, etc.

Upcoming features (hopefully)
   Manage folders for any user including #Public
   Manage ACL's on folders
   Work with LDAP attributes for group and quota settings.
   Do dbmail-util runs on the fly

Possible features way down the road
   Allow users to manage their sieve scripts
   An integrated sieve script builder?


Questions, comments, suggestions, or what have you can be sent to me at 
[EMAIL PROTECTED] - Patches to get things working in your setup are 
always welcome.


While I have built this tool to be simple and use the dbmail-users 
binary for any complex operations, use this tool at your own risk. I am 
not responsible for anything that goes wrong.


Thanks and Enjoy

-Jon a.k.a. The NetVulture


___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] users and alias forwards together

[Jonathan Feally]

Yes, that is how it works. Not quite as one would expect, but it can be 
worked around.


Dbmail will checkthe aliases first. If one or more aliases exist for 
[EMAIL PROTECTED], then the mail is sent to those destinations, either a local 
mailbox, or sent to another email server. If no aliases are found, then 
the users are checked and delivered if found.


The best way to get the store and forward you are looking for is to have 
a local alias of [EMAIL PROTECTED] that points to the mailbox and an external alias 
that points to the other domain. This tricks Dbmail into delivering the 
mail to the local mailbox because it is delivering it to the 2 aliases, 
one by the uid of the mailbox, and the other the external address.


dbmail-users -a [EMAIL PROTECTED] -w the_pass -p crypt   # Create the 
local mailbox
dbmail-users -c [EMAIL PROTECTED] -s [EMAIL PROTECTED]   # Create a 
local alias - "store"
dbmail-users -x [EMAIL PROTECTED] -t [EMAIL PROTECTED]   # Create the 
external alias - "forward"


-Jon

Michael Monnerie wrote:

I have questions regarding users and aliases:

- when I have a user [EMAIL PROTECTED], and an alias that says
[EMAIL PROTECTED] -> [EMAIL PROTECTED]
is it correct behaviour that ONLY [EMAIL PROTECTED] receives the mail? This seems 
to be the case currently.


- what is the correct way to reach what I wanted to have (the user 
should receive the e-mail, PLUS it should go to the alias target)


- for vacation: when a user is on vacation, I install the sieve script 
and do a "vacation" there. Is it possible to use the vacation script, 
use "redirect" to copy the e-mail to another user and use "stop;" then 
to prevent the e-mail being stored locally? This is useful when a user 
in a sales shop is 4 weeks on vacation, they don't want to keep the 
holiday mails they receive during that time. 
I could of course test this myself, but as I already had to ask other 
questions, it saves some time *g*


mfg zmi
  



___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
  




___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] Bug #579

[Jonathan Feally]

Your problem is not with DBMAIL, but rather either a slow database 
server or really big mailbox. I have hacked my dbmail-imap to sleep for 
120 seconds when doing a search and it caused this same error on SM 
1.4.9a. The default timeout on the socket is 60 seconds and of course a 
query on a sql server on a huge mailbox such as looking for aa in all of 
the message bodies is going to take a long time, once it exceeds 60 
seconds, PHP fgets() will return because the socket says it timed out 
reading data. As suspected, it was a timeout issue, not a bug in DBMAIL.


This patch below shows you where to add a line to change this timeout to 
help with these types of operations in SM, but if the mailbox is too 
big, the user will just complain that the email server stops working - 
copy/paste patch to file:


--- functions/imap_general.phpWed Oct  4 12:35:42 2006
+++ functions/imap_general.php   Wed Jul  4 19:01:36 2007
@@ -449,6 +449,9 @@

$imap_stream = @fsockopen($imap_server_address, $imap_port, 
$error_number, $error_string, 15);


+// Extend normal timeout from 60 seconds to 150 seconds for slow 
servers doing complex operations such as body searching

+@stream_set_timeout($imap_stream, 150);
+
/* Do some error correction */
if (!$imap_stream) {
if (!$hide) {


-Jon


Jorge Bastos wrote:

PHP 5.2.1
SM 1.4.9
Apache 2.2.4

The error it's from dbmail, doesn't seams to be an error from a timeout.

ERROR:ERROR: Connection dropped by IMAP server.
 Query: SEARCH CHARSET ISO-8859-1 ALL BODY {2} aa



Paul,
Do you help me debuging this with gdb?




- Original Message ----- From: "Jonathan Feally" 
<[EMAIL PROTECTED]>

To: "DBMail mailinglist" 
Sent: Wednesday, July 04, 2007 11:46 PM
Subject: Re: [Dbmail] Bug #579


Well - all page requests in SM do a login, the work, then log out. 
Thus each transaction is a separate login session. Looking at the SM 
code for this error - It is displayed when the fgets on the 
$imap_stream fails. So aside from the version numbers I've already 
asked for, what version apache, PHP, and SM are you running? This 
error doesn't actually mean that the TCP connection was closed to the 
server, but rather that reading from the connection failed, perhaps 
due to a time out on reading from the stream.


-Jon

Jorge Bastos wrote:

Hum,
But that i don't know, but like i said in the previous email, after 
i received the error, i pushed the logout button on squirrelmail.



- Original Message - From: "Paul J Stevens" <[EMAIL PROTECTED]>
To: "DBMail mailinglist" 
Sent: Wednesday, July 04, 2007 8:14 AM
Subject: Re: [Dbmail] Bug #579



Jonathan Feally wrote:
What OS, kernel, glib, and db versions are you running? I tested 
the search command by hand using the literal method and did not 
have any problems at all. Also - your log file doesn't seem to 
indicate anything about the child process crashing out, but rather 
that the main parent process was told to shutdown down.


Damn, I didn't even see that! You're right; the process is shutdown 
clean.


Jul  2 23:23:15 lira dbmail/imap4d[6018]: Debug:[sql] 
dbmysql.c,db_query(+291): query [SELECT m.message_idnr,k.messageblk 
FROM dbmail_messageblks k JOIN dbmail_physmessage p ON k
.physmessage_id = p.id JOIN dbmail_messages m ON p.id = 
m.physmessage_id WHERE mailbox_idnr = 507 AND status IN (0,1 ) AND 
k.is_header = '0' GROUP BY m.message_idnr,k.messageblk

HAVING k.messageblk LIKE '%aa%']

Jul  2 23:24:30 lira dbmail/imap4d[6000]: Debug:[server] 
server.c,server_run(+291): MainSigHandler(): got signal [15]
Jul  2 23:24:30 lira dbmail/imap4d[6001]: Message:[server] 
pool.c,manage_stop_children(+470): General stop requested. Killing 
children...



So that query is taking rather long, after which something/someone 
requested a general shutdown of the dbmail-imapd processes.


There is no indication of a crash, but something is not right.

--
  
  Paul Stevens  paul at nfg.nl
  NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
  The Netherlandshttp://www.nfg.nl
___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail



___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail






___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail


___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail






___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] Bug #579

[Jonathan Feally]

Well - all page requests in SM do a login, the work, then log out. Thus 
each transaction is a separate login session. Looking at the SM code for 
this error - It is displayed when the fgets on the $imap_stream fails. 
So aside from the version numbers I've already asked for, what version 
apache, PHP, and SM are you running? This error doesn't actually mean 
that the TCP connection was closed to the server, but rather that 
reading from the connection failed, perhaps due to a time out on reading 
from the stream.


-Jon

Jorge Bastos wrote:

Hum,
But that i don't know, but like i said in the previous email, after i 
received the error, i pushed the logout button on squirrelmail.



- Original Message - From: "Paul J Stevens" <[EMAIL PROTECTED]>
To: "DBMail mailinglist" 
Sent: Wednesday, July 04, 2007 8:14 AM
Subject: Re: [Dbmail] Bug #579



Jonathan Feally wrote:
What OS, kernel, glib, and db versions are you running? I tested the 
search command by hand using the literal method and did not have any 
problems at all. Also - your log file doesn't seem to indicate 
anything about the child process crashing out, but rather that the 
main parent process was told to shutdown down.


Damn, I didn't even see that! You're right; the process is shutdown 
clean.


Jul  2 23:23:15 lira dbmail/imap4d[6018]: Debug:[sql] 
dbmysql.c,db_query(+291): query [SELECT m.message_idnr,k.messageblk 
FROM dbmail_messageblks k JOIN dbmail_physmessage p ON k
.physmessage_id = p.id JOIN dbmail_messages m ON p.id = 
m.physmessage_id WHERE mailbox_idnr = 507 AND status IN (0,1 ) AND 
k.is_header = '0' GROUP BY m.message_idnr,k.messageblk

HAVING k.messageblk LIKE '%aa%']

Jul  2 23:24:30 lira dbmail/imap4d[6000]: Debug:[server] 
server.c,server_run(+291): MainSigHandler(): got signal [15]
Jul  2 23:24:30 lira dbmail/imap4d[6001]: Message:[server] 
pool.c,manage_stop_children(+470): General stop requested. Killing 
children...



So that query is taking rather long, after which something/someone 
requested a general shutdown of the dbmail-imapd processes.


There is no indication of a crash, but something is not right.

--
  
  Paul Stevens  paul at nfg.nl
  NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
  The Netherlandshttp://www.nfg.nl
___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail



___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail






___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] Bug #579

[Jonathan Feally]

What OS, kernel, glib, and db versions are you running? I tested the 
search command by hand using the literal method and did not have any 
problems at all. Also - your log file doesn't seem to indicate anything 
about the child process crashing out, but rather that the main parent 
process was told to shutdown down.


-Jon

Jorge Bastos wrote:

hum...

and if create an account for you in my imap server it helps?



- Original Message - From: "Paul J Stevens" <[EMAIL PROTECTED]>
To: "DBMail mailinglist" 
Sent: Tuesday, July 03, 2007 10:21 PM
Subject: Re: [Dbmail] Bug #579



Jorge Bastos wrote:

Paul saw this email?


Yes. But I can't reproduce this. And I have really tried: replaying your
commands against the same dbmail-imapd process over and over ...



--
 
 Paul Stevens  paul at nfg.nl
 NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
 The Netherlandshttp://www.nfg.nl
___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail


___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail






___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] DBmail webmail app

[Jonathan Feally]

I don't think DBMAIL should try to impose any limits on an individual 
piece of mail size. Administrator's should limit the size of messages 
from the SMTP side, an wrist slap users putting messages in the system 
that are huge. Also keep in mind that most users are not going to have a 
100 Mbit/sec connection to the server and are probably going to have 1.5 
Mbit/sec or less for most residential ISP's, making it a real pain for 
that user to upload these huge attachments with out them being stuck 
online for a half an hour just to save it to the drafts or sent items 
folder, not to mention the time it takes to push it using SMTP to the 
outgoing server as well, which if the outgoing server burps at the 
outgoing mail size, the client would never copy it to the sent items folder.


As for which hash type to use. We need to keep in mind what the OS can 
do for us, and what we can include with the distribution. Not all OS's 
may be able to do sha256, but most can do an MD5 or SHA1. All the hash 
has to do is be able to create a fingerprint that is unique enough that 
along with the total length keep the data stored only once in the database.


-Jon

Aaron Stone wrote:


On Wed, Jun 6, 2007, Tom Allison <[EMAIL PROTECTED]> said:

[snip context]
 


Please don't turn dbmail into the mother-of-all-kitchen-sink-applications.
   



On the flip side, we will never tells users what to do with themselves if
they don't want to set up their email systems in exactly one way we think
is best (if we even had or wanted such a one true way!).

 


just benchmark your various hashing schemas and pick the fastest one.
   



We'll pick something that makes sense. It might not be your favorite, it
might not be the hottest new hash, but it'll be something we can live
with.

 


And keep the spam filtering to the spam filtering people.
   



Well there need to be hooks for spam filtering. Generally this is just
means setting up protocol level connections between SMTP and LMTP speaking
filters, but where other hooks make sense we will consider them.

 


You could just as easily make a pitch that dbmail become a usenet news
server so we can run mailing lists and port between mailing lists and
newsgroups seemlessly.
   



I like the idea of being a news server, but I've looked into mailing list
management, and that's a nightmare!

 


And while were at it, can't we make dbmail and blog tool?
   



It already is, you just aren't using an IMAP backend for your blog. This
would be almost trivial to set up and would probably work very well. Be
careful what you wish for:
http://www.oreilly.com/news/parrotstory_0401.html ;-)

Aaron
___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail


 

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] single instance mime storage

[Jonathan Feally]

Just to be clear - are you taking the headers of the mime part and 
keeping them seperate of the body of said part? My original post to keep 
them seperate was to allow for the attached file be renamed but not 
create duplicates, and also some mail clients may create the headers of 
the mime part slightly different when taking the file and re-attaching 
it into a new message.


One mail client may attach a file with headers like:

--090708070905030201070605
Content-Type: image/jpeg;
name="picture.jpg"
Content-Transfer-Encoding: base64
Content-Disposition: inline;
filename="picture.jpg"

While a different client with the same file like:

--090708070905030201070606
Content-Type: image/jpeg; name="picture.jpg"
Content-Transfer-Encoding: base64
Content-Disposition: inline; filename="picture.jpg"

If the headers weren't seperate from the body - we would end up with 2 
copies of this file=2 parts - which would take up more space than 2 
parts of the unique headers and only 1 part for the file.


Dropping of the boundaries - Multiple boundaries in a single piece of 
mail could present a problem - such as an attached message that was 
already mime. Complete retreival of the message would require decoding 
of the attached message headers on-the-fly. I propose that any time a 
single mime part is to be retrieved that the boundry is stripped off 
on-the-fly when the client is not expecting it to be in the returned 
data. This allows the entire message to still be retrieved with out 
magic and less computing overhead. I personally have a script that reads 
messages put into public spam/not spam folders out of the database for 
auto spam learning. Taking the boundaries out would require me to change 
the script from pure sh and sql to something like php and imap functions 
to get the same data.


Hashing - hashing of the entire data chunk is probably the safest way to 
go, but allowing multiple hash types to be used - such as taging the 
hash as {sha1}thehashdatahere would not help to keep the unique parts 
stored uniquly as one hash would not match a different method.


-Jon

Paul J Stevens wrote:


I think this discussion should move to dbmail-dev.

I've already finished most of the singleton mimechunk storage :-)

A bit raw around the edges, and only for sqlite atm. Stay tuned.

Fully backward compatible: just add two tables like I stated. Using sha1 over
the whole of the file, fall back to old-style storage if new-style not
available. I guess the one doing the work gets to make the decisions...

In this new setup; simple rfc2822 messages are stored in a single block. Yes: no
more chopping off of the headers. For multi-part messages, the first block
contains the rfc headers plus the mime-preamble. Following blocks contain the
mime-parts as-is.

Boundaries between parts are reconstructed at retrieval based on the boundary
used in the original message and stored in the first block.

I'm using the sha1 code from the mozilla project (dual licence). It's what Linus
uses in the GIT code, so it *must* be fast.

Are we having fun yet?

 



___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] DBmail webmail app

[Jonathan Feally]

Consider the following tables:

CREATE TABLE `dbmail_messageblks` (
 `messageblk_idnr` bigint(21) NOT NULL auto_increment,
 `physmessage_id` bigint(21) NOT NULL default '0',
 `messagepart_order` int NOT NULL,
 `messagepart_idnr` bigint(21) NOT NULL,
 `mime_header` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY  (`messageblk_idnr`),
 UNIQUE KEY `messageblk_idnr_message_part` 
(`messageblk_idnr`,`messagepart_idnr`),

 KEY `physmessage_id_index` (`physmessage_id`),
 KEY `messagepart_id_index` (`messagepart_idnr`),
 CONSTRAINT `dbmail_messageblks_ibfk_1` FOREIGN KEY (`physmessage_id`) 
REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `dbmail_messageparts` (
 `messagepart_idnr` bigint(20) NOT NULL auto_increment,
 `message` longblob,
 `size` bigint(20) NOT NULL,
 `hash` varchar(64) NOT NULL,
 PRIMARY KEY  (`messagepart_idnr`),
 UNIQUE KEY `hash_size` (`hash`,`size`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and a message constructed like follows

->Message
   -Non Mime Message or Mime Warning "This is a multi-part message in 
MIME format."

   ->Plain Text
   ->HTML
   ->JPEG
   ->Attached Message 1
  -Non Mime Message or Mime Warning "This is a multi-part message 
in MIME format."

  ->Plain Text
  ->HTML
  ->MPEG
   ->Attached Message 2
  -Non Mime Message or Mime Warning "This is a multi-part message 
in MIME format."

  ->Plain Text
  ->HTML
  ->GIF

Would be come these message parts

begin
message_part=0   Root Message Headers
message_part=1   Root Non-Mime Message
message_part=2   Boundry and headers ("Plain Text")
message_part=3   Body ("The Plain Text")
message_part=4   Boundry and headers ("HTML")
message_part=5   Body ("The HTML")
message_part=6   Boundry and headers ("JPEG Attachment")
message_part=7   Body ("Base64 of the JPEG")
message_part=8   Boundry and headers ("Attached Message 1")
message_part=9   Attached message 1 Headers
message_part=10  Attached message 1 Non-Mime
message_part=11  Attached message 1 Boundry and headers ("Plain Text")
message_part=12  Attached message 1 Body ("Plain Text")
message_part=13  Attached message 1 Boundry and headers ("HTML")
message_part=14  Attached message 1 Body ("HTML")
message_part=15  Attached message 1 Boundry and headers ("MPEG")
message_part=16  Attached message 1 Body ("MPEG")
message_part=17  Attached message 1 Closing boundry
message_part=18  Boundry and headers ("Attached Message 2")
message_part=19  Attached message 2 Headers
message_part=20  Attached message 2 Non-Mime
message_part=21  Attached message 2 Boundry and headers ("Plain Text")
message_part=22  Attached message 2 Body ("Plain Text")
message_part=23  Attached message 2 Boundry and headers ("HTML")
message_part=24  Attached message 2 Body ("HTML")
message_part=25  Attached message 2 Boundry and headers ("GIF")
message_part=26  Attached message 2 Body ("GIF")
message_part=27  Attached message 2 Closing Boundry
message_part=28  Root Message Closing Boundry
end

While a simple non-mime message would be
begin
message_part=0   Message Headers
message_part=1   Message Body
end


I propose using sha256 as it will give a more unique key at 64 chars 
than sha1 at 40 chars and only doing the sha256 on the first 8 or 16 MB. 
I can't think of any real data files that could result in the same size 
and sha256 of the first 8 MB. This could also be configurable in the 
dbmail.conf to what ever size the admin choses, and could be changed 
later, but would require dbmail to be shut down and the dbmail-util run 
to update every hash in the database to use the new value.


Features:
All parts of the messages can be stored uniquely.

Duplicate attachments/parts - even if they were renamed - can't occur - 
while the original attached file name remains intact.


I purposly left the boundry's in there so that original message could be 
re-built in SQL into its original complete data stream for external use 
or mail export.


The is_header field can be removed as message_part=0 will always contain 
the headers of the message.


The mime_header field can be used to find an attachment when looking to 
download only certain parts.


Rebuiling of headers can be done quickly with message_part=0

The messagepart_order allows the inserting of multiple message parts to 
be done by threads
   Message above would have a total of 29 threads spawned, though they 
may not all be spawned at the same due to thread/memory limits.
   1. The thread is told the messagepart_order number and passed the 
data to be inserted.

   2. The size is calculated and the first 8 MB of the data are sha256'ed.
   Shorter data would be ok as we would then have a sha256 on 
the enitre part.
   3. Do a quick select on the db to check if the hash and size is 
already there.

   4. If the data is not there - insert the data
   5. Get the messagepart_idnr for the size and hash.
   6. Insert the messageparts row with the matching order 

Re: [Dbmail] FreeBSD 6.x - 2.2.0 rc2 - mysql 5.0.21 - libsieve 2.2.1 - goes BOOM! (temp workaround found)

[Jonathan Feally]

How does it not work? Doesn't compile? Doesn't run? Doesn't run right? 
Did you make sure you installed the deps? Why not use 6-STABLE? I am 
having no issues with 6-STABLE with dbmail. This box actually runs 
dbmail inside a jail and has the database accessed via IP to another 
jail along with 8 other jails running their own web servers.


-Jon

Igor Olemskoi wrote:


Hello,

dbmail-2.2-release works ok on freebsd 6.2-PRERELEASE, but doesn't work
on FreeBSD 5.5-STABLE. How can I fix this problem?

Thanks,
Igor.

___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] Patch for LSUB/LIST "" "%/%" commands.

[Jonathan Feally]

Thanks.
I did test it some more and it seemed to work for everything I could 
throw at it.


-Jon

Paul J Stevens wrote:


Jonathan,

I've verified the validity of your patch and have merged it in my tree.
Thanks a lot.

regards,

Jonathan Feally wrote:
 


While playing around with 2.2.0 rc2 - this is broken in 2.1.x as well -
i found out that my netscape mail 7.2 will not show the folders as being
able to be subscribed to.

So - how to duplicate
Create a folder structure that is 3 levels deep like Lists/DBMAIL/Boring
with no other folders in DBMAIL
Unsubscribe from both Boring and its parent DBMAIL - Hit ok
Go back to add them both back in and you can't becuase DBMAIL just
doesn't show up.
  Netscape is doing: 666 LIST "" "%/%"
  DBMAIL returns nothing - the listex_match function never moves on
past the first % thus it fails as their is still more to be looked at on
either the pattern or the string being examined. In this case we have
both % in the pattern and DBMAIL in the string. This simple 1 line patch
says that if we still have both more string to look at and more pattern
to look at then call the whole thing again from where we left off.
Pretty simple and something that should be tested by others and put into
2.2.0

Attached is a diff -u for misc.c

-Jon





--- ../1/dbmail-2.2.0rc2/misc.c Fri Nov 10 01:32:18 2006
+++ misc.c  Tue Nov 14 15:33:14 2006
@@ -664,6 +664,7 @@
/* %. */
for (i = 0; x[i] && x[i] == p[i]; i++);
if (! x[i]) p += i;
+   if (*s && *p) return listex_match(p,s,x,flags); // We 
have more to look at - lets look again.
if (*s || *p) return 0;
return 1;





___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
   




 

Re: [Dbmail] delivery methods - POSTFIX SMTP AUTH / VALIDATION HOW TO

[Jonathan Feally]

I haven't tried to get MD5 going yet - I have all users connect over 
SSL/STARTTLS anyways so i'm not too worried about sniffers.
As far as the crypt - that is just what I had to work with. sasl may be 
able to do the md5 with the passwords stored differently in the 
database, but the smtp auth all comes from postifx and sasl2 more than 
the format the password is in. You can experiment with it and let me 
know! :)
When starting with dbmail I already had users in the master.passwd file 
using crypt and imap-uw -  so I didn't want to change their passwords 
when the change took place.


-Jon

Tom Allison wrote:


Jonathan Feally wrote:

I use postfix with smtp auth and receipient validation against dbmail 
with sasl.

All user passwords must be in format crypt!!




Can't do md5-digest eh?

Good to know before I get too far along.

Your write up looks great!  Thanks.
___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] delivery methods - POSTFIX SMTP AUTH / VALIDATION HOW TO

[Jonathan Feally]

I use postfix with smtp auth and receipient validation against dbmail 
with sasl.

All user passwords must be in format crypt!!

1. Recieve mail for only those that have a userid or alias
2. SMTP AUTH with either his userid ([EMAIL PROTECTED]) or any of his 
aliases ([EMAIL PROTECTED])

3. User can only send email as himself or one of his aliases.
4. Other users can not send email with a source of one of my domain 
names (sender_access map) - [EMAIL PROTECTED] to [EMAIL PROTECTED] is ok 
once authenticated. This helps stop those pesky spams of yourself 
sending yourself the spam.


Here's a quick how to:

/etc/postfix/main.cf:
broken_sasl_auth_clients = yes
smtpd_sasl_auth_enable = yes
smtpd_sasl_security_options = noanonymous
smtpd_recipient_restrictions =
   permit_mynetworks,
   reject_invalid_hostname,
   reject_non_fqdn_sender,
   reject_non_fqdn_recipient,
   reject_unknown_recipient_domain,
   reject_unauth_pipelining,
   reject_unknown_sender_domain,
   permit_sasl_authenticated,
   check_helo_access hash:/etc/postfix/helo_access,
   reject_unauth_destination,
smtpd_sender_restrictions =
   permit_mynetworks,
   check_helo_access hash:/etc/postfix/helo_access,
   reject_authenticated_sender_login_mismatch,
   permit_sasl_authenticated,
   reject_sender_login_mismatch,
   reject_unknown_sender_domain,
   check_sender_access 
hash:/etc/postfix/sender_access

# FOR SMTP AUTH CHECKS - NEED BOTH SO WE CAN CHECK BOTH TABLES
smtpd_sender_login_maps = mysql:/etc/postfix/dbmail_sender_login.cf, 
mysql:/etc/postfix/dbmail_sender_login_alias.cf

# RECIPIENT CHECKS - NEED BOTH SO WE CAN CHECK BOTH TABLES
local_recipient_maps = mysql:/etc/postfix/dbmail_rcpt.cf, 
mysql:/etc/postfix/dbmail_rcpt_alias.cf



cat /etc/postfix/sender_access
mydomainname.com   553 Not logged in. Use smtp auth to send email.
* OK

(don't forget to run postmap sender_access)


cat /etc/postfix/dbmail_rcpt.cf
# Look for a user that matches to receipient of the email
user = dbmail
password = dbmail
dbname = dbmail
hosts = localhost

#postfix 2.2.X version only
query = SELECT userid FROM dbmail_users WHERE userid='%s'


cat /etc/postfix/dbmail_rcpt_alias.cf
# Look for an alias that matches to receipient of the email
user = dbmail
password = dbmail
dbname = dbmail
hosts = localhost

#postfix 2.2.X version only
query = SELECT alias FROM dbmail_aliases WHERE alias='%s'


cat /etc/postfix/dbmail_sender_login.cf
#SMTP AUTH - check if the user exists
user = dbmail
password = dbmail
dbname = dbmail
hosts = localhost

#postfix 2.2.X version only
query = SELECT userid FROM dbmail_users WHERE userid='%s'


cat /etc/postfix/dbmail_sender_login_alias.cf
# SMTP AUTH - check if the alias exists
user = dbmail
password = dbmail
dbname = dbmail
hosts = localhost

#postfix 2.2.X version only
query = SELECT userid FROM dbmail_users JOIN dbmail_aliases ON 
(dbmail_aliases.deliver_to=dbmail_users.user_idnr) WHERE 
dbmail_aliases.alias='%s'



cat /usr/local/lib/sasl2/smtpd.conf
pwcheck_method: auxprop
auxprop_plugin: sql
allowanonymouslogin: no
allowplaintext: yes
mech_list: PLAIN LOGIN
srp_mda: md5
password_format: crypt
sql_engine: mysql
sql_hostnames: localhost
sql_user: dbmail
sql_passwd: dbmail
sql_database: dbmail
sql_verbose: yes
sql_select: SELECT passwd FROM dbmail_users WHERE userid = '[EMAIL PROTECTED]'


Remeber to follow all of the instructions on getting postfix working 
with dbmail first along with sasl2- then apply the changes above. Not 
everything may be applicable to you.


-Jon


Tom Allison wrote:


On 11/14/2006, "Leander Koornneef" <[EMAIL PROTECTED]> wrote:

 


Hi Tom,

   



 


You should realise that "dbmail user" != "email address"


It's not ugly, it's just the (perfectly sane) way dbmail works :-)
Doing this "your way" would severely limit the flexibility of dbmail.



Well, one thing you could do is use Postfix' recipient_canonical_maps
to rewrite [EMAIL PROTECTED] to [EMAIL PROTECTED]:

   



Thanks to all who replied.  I think it's this canonical maps that makes
the most sense to me.  Also, I should probably change my usernames from
fred to [EMAIL PROTECTED]  If I understand things correctly -- with this
then allow me to use recipient validation in the postfix database tables?

And is this the right direction to go in for AUTH-SMTP?

This is actually pretty good because I'm really forcing myself to learn
more about email and databases -- both of which seem pretty useful...
___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/l

[Dbmail] Patch for LSUB/LIST "" "%/%" commands.

[Jonathan Feally]

While playing around with 2.2.0 rc2 - this is broken in 2.1.x as well - 
i found out that my netscape mail 7.2 will not show the folders as being 
able to be subscribed to.


So - how to duplicate
Create a folder structure that is 3 levels deep like Lists/DBMAIL/Boring 
with no other folders in DBMAIL

Unsubscribe from both Boring and its parent DBMAIL - Hit ok
Go back to add them both back in and you can't becuase DBMAIL just 
doesn't show up.

   Netscape is doing: 666 LIST "" "%/%"
   DBMAIL returns nothing - the listex_match function never moves on 
past the first % thus it fails as their is still more to be looked at on 
either the pattern or the string being examined. In this case we have 
both % in the pattern and DBMAIL in the string. This simple 1 line patch 
says that if we still have both more string to look at and more pattern 
to look at then call the whole thing again from where we left off. 
Pretty simple and something that should be tested by others and put into 
2.2.0


Attached is a diff -u for misc.c

-Jon


--- ../1/dbmail-2.2.0rc2/misc.c Fri Nov 10 01:32:18 2006
+++ misc.c  Tue Nov 14 15:33:14 2006
@@ -664,6 +664,7 @@
/* %. */
for (i = 0; x[i] && x[i] == p[i]; i++);
if (! x[i]) p += i;
+   if (*s && *p) return listex_match(p,s,x,flags); // We 
have more to look at - lets look again.
if (*s || *p) return 0;
return 1;
 

Re: [Dbmail] FreeBSD 6.x - 2.2.0 rc2 - mysql 5.0.21 - libsieve 2.2.1 - goes BOOM! (temp workaround found)

[Jonathan Feally]

I was able to compile and run dbmail-imapd just fine with the fix.

-Jon

Paul J Stevens wrote:


Oleg,

try the attached patch.


UEMURA (fka. MAENAKA) Tetsuya wrote:
 


Posted on Mon, 13 Nov 2006 11:37:56 +0300
by author Oleg Albegov <[EMAIL PROTECTED]>
   


I think, i have the latest..

[EMAIL PROTECTED] /usr/ports/devel/autoconf259]# pkg_info | grep auto
autoconf-2.59_2 Automatically configure source code on many Un*x 
platforms

automake-1.9.6  GNU Standards-compliant Makefile generator (1.9)
 


If you have installed autoconf259, automake19 and libtool, all from the
ports, then the following will generate the configure.

cd dbmail_2_2_branch
cat /usr/local/share/aclocal/libtool.m4 >> acinclude.m4
ACLOCAL=aclocal19 AUTOCONF=autoconf259 AUTOHEADER=autoheader259 \
AUTOM4TE=autom4te259 AUTOMAKE=automake19 AUTORECONF=autoreconf259 \
AUTOSCAN=autoscan259 AUTOUPDATE=autoupdate259 LIBTOOL=libtool \
LIBTOOLIZE=libtoolize autoreconf259 -f -i

   



 




Index: serverchild.c
===
--- serverchild.c   (revision 2366)
+++ serverchild.c   (working copy)
@@ -270,14 +270,15 @@
// accept will block forever unless it is set non-blocking with fcntl,
// so we have to do this dance to make it temporarily non-blocking.
*clientSocket = accept(info->listenSockets[active], saClient, &len);
-	if (*clientSocket < 0) 
-		serr = errno;

+   // on some systems (freebsd) the non-blocking state is inherited by
+   // the new socket, so we want to reset both the new socket and the 
listening
+   // handle.
+	if (*clientSocket > 0) 
+		fcntl(*clientSocket, F_SETFL, flags);

fcntl(info->listenSockets[active], F_SETFL, flags);

-   if (*clientSocket < 0) {
-   TRACE(TRACE_ERROR, "accept failed: [%s]", strerror(serr));
+   if (*clientSocket < 0)
return -1;
-   }

TRACE(TRACE_INFO, "connection accepted");
return 0;
 




___
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
 

Re: [Dbmail] FreeBSD 6.x - 2.2.0 rc2 - mysql 5.0.21 - libsieve 2.2.1 - goes BOOM! (temp workaround found)

[Jonathan Feally]

I have tried playing with the settings, but setting the flag at all 
seems to also effect the blocking on the rx, tx streams. It does work 
fine with out touching the flags at all, so there must be something that 
is being missed here.


So the fix for now is to comment out
the 2 fcntl lines in select_and_accept or i guess you could run 
dbmail-imap through inetd with the -n option.


-Jon

Jonathan Feally wrote:

I have narrowed it down to the select_and_accept function in 
serverchild.c
I can get the daemon to work just fine when I comment out the setting 
and the unsetting of the O_NONBLOCK flags on the connection.
I'll see If I can figure out what the problem is. man 2 accept show 
that the O_NONBLOCK flag will be inheirited so perhaps its already set 
and we are unsetting it and resetting it (backwards). 2.1.6 did not 
even have this function - it just accept()'ed the connection and moved 
on.


If I am reading this right - the reason we are playing with the flag 
is to avoid having a child getting stuck on an accept block when 
someone connects and then disconnects the tcp connection before a 
child has accpeted the conn. So this is to prevent DOS for the most part.


-Jon

Jonathan Feally wrote:


Hello,

I have a FreeBSD box that is already running dbmail 2.1.x against a 
mysql db - client and server is 5.0.21.
I am trying to get it up to 2.2.0 rc2 with libSieve - and I can 
compile it just fine.

The daemon starts. And all looks good in the logs.

When I try to connect to the imap server it goes boom.

Launching daemon

Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+236): server will create  [1] children
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+249): children will make max. 
[1] connections
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+262): timeout [4500] seconds
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+267): no value for SOCKET in config 
file
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+270): socket []
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+282): binding to PORT [143]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+298): binding to IP [x.x.x.x]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+305): no value for BACKLOG in config 
file. Using default value [16]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+319): resolving client IP
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+333): Disabling IMAP-before-SMTP
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+345): effective user shall be [nobody]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+357): effective group shall be 
[nogroup]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+369): will maintain minimum of [1] 
spare children in reserve
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+381): will maintain maximum of [1] 
spare children in reserve
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+393): will allow maximum of [1] 
children
Nov 11 14:16:53 hammer01 dbmail/imap4d[52327]: Debug:[server] 
server.c,server_daemonize(+198): sid: [52326]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52327]: Debug:[server] 
pool.c,statefile_create(+589): Creating scoreboard at 
[/var/run/dbmail-imapd.state].
Nov 11 14:16:53 hammer01 dbmail/imap4d[52327]: Debug:[server] 
server.c,dm_socket(+334): done
Nov 11 14:16:53 hammer01 dbmail/imap4d[52327]: Debug:[server] 
server.c,create_inet_socket(+404): creating socket on [x.x.x.x:143] 
with backlog [16]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52327]: Debug:[server] 
server.c,dm_bind_and_listen(+354): done
Nov 11 14:16:53 hammer01 dbmail/imap4d[52328]: Debug:[db] 
dbmodule.c,db_load_driver(+67): looking for mysql as 
modules/.libs/libmysql.so
Nov 11 14:16:53 hammer01 dbmail/imap4d[52328]: Info:[db] 
dbmodule.c,db_load_driver(+71): cannot load Cannot open 
"modules/.libs/libmysql.so"
Nov 11 14:16:54 hammer01 dbmail/imap4d[52328]: Debug:[db] 
dbmodule.c,db_load_driver(+67): looking for mysql as 
/usr/local/lib/dbmail/libmysql.so
Nov 11 14:16:54 hammer01 dbmail/imap4d[52328]: Debug:[sql] 
dbmysql.c,db_query(+286): query [SHOW VARIABLES LIKE 'collation_%']
Nov 11 14:16:54 hammer01 dbmail/imap4d[52328]: Debug:[sql] 
dbmysql.c,db_mysq

Re: [Dbmail] FreeBSD 6.x - 2.2.0 rc2 - mysql 5.0.21 - libsieve 2.2.1 - goes BOOM! (temp workaround found)

[Jonathan Feally]

I have narrowed it down to the select_and_accept function in serverchild.c
I can get the daemon to work just fine when I comment out the setting 
and the unsetting of the O_NONBLOCK flags on the connection.
I'll see If I can figure out what the problem is. man 2 accept show that 
the O_NONBLOCK flag will be inheirited so perhaps its already set and we 
are unsetting it and resetting it (backwards). 2.1.6 did not even have 
this function - it just accept()'ed the connection and moved on.


If I am reading this right - the reason we are playing with the flag is 
to avoid having a child getting stuck on an accept block when someone 
connects and then disconnects the tcp connection before a child has 
accpeted the conn. So this is to prevent DOS for the most part.


-Jon

Jonathan Feally wrote:


Hello,

I have a FreeBSD box that is already running dbmail 2.1.x against a 
mysql db - client and server is 5.0.21.
I am trying to get it up to 2.2.0 rc2 with libSieve - and I can 
compile it just fine.

The daemon starts. And all looks good in the logs.

When I try to connect to the imap server it goes boom.

Launching daemon

Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+236): server will create  [1] children
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+249): children will make max. [1] 
connections
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+262): timeout [4500] seconds
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+267): no value for SOCKET in config file
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+270): socket []
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+282): binding to PORT [143]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+298): binding to IP [x.x.x.x]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+305): no value for BACKLOG in config 
file. Using default value [16]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+319): resolving client IP
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+333): Disabling IMAP-before-SMTP
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+345): effective user shall be [nobody]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+357): effective group shall be [nogroup]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+369): will maintain minimum of [1] 
spare children in reserve
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+381): will maintain maximum of [1] 
spare children in reserve
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+393): will allow maximum of [1] children
Nov 11 14:16:53 hammer01 dbmail/imap4d[52327]: Debug:[server] 
server.c,server_daemonize(+198): sid: [52326]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52327]: Debug:[server] 
pool.c,statefile_create(+589): Creating scoreboard at 
[/var/run/dbmail-imapd.state].
Nov 11 14:16:53 hammer01 dbmail/imap4d[52327]: Debug:[server] 
server.c,dm_socket(+334): done
Nov 11 14:16:53 hammer01 dbmail/imap4d[52327]: Debug:[server] 
server.c,create_inet_socket(+404): creating socket on [x.x.x.x:143] 
with backlog [16]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52327]: Debug:[server] 
server.c,dm_bind_and_listen(+354): done
Nov 11 14:16:53 hammer01 dbmail/imap4d[52328]: Debug:[db] 
dbmodule.c,db_load_driver(+67): looking for mysql as 
modules/.libs/libmysql.so
Nov 11 14:16:53 hammer01 dbmail/imap4d[52328]: Info:[db] 
dbmodule.c,db_load_driver(+71): cannot load Cannot open 
"modules/.libs/libmysql.so"
Nov 11 14:16:54 hammer01 dbmail/imap4d[52328]: Debug:[db] 
dbmodule.c,db_load_driver(+67): looking for mysql as 
/usr/local/lib/dbmail/libmysql.so
Nov 11 14:16:54 hammer01 dbmail/imap4d[52328]: Debug:[sql] 
dbmysql.c,db_query(+286): query [SHOW VARIABLES LIKE 'collation_%']
Nov 11 14:16:54 hammer01 dbmail/imap4d[52328]: Debug:[sql] 
dbmysql.c,db_mysql_check_collations(+116): does 
[collation_database:latin1_swedish_ci] match 
[collation_connection:latin1_swedish_ci]?
Nov 11 14:16:54 hammer01 dbmail/imap4d[52328]: Debug:[sql] 
dbmysql.c,db_query(+286): query [SELECT 1=1 FROM dbmail_physmessage 
LIMIT 1 OFFSET 0]
Nov 11 14:16:54 hammer01 dbmail/imap4d[52328]: Debug:[sql] 
dbmysql.c,db_query(+286): query [SELECT 1=1 FROM dbmail_headervalue 
LIMIT 1 OFFSET 0]
Nov 11 14:16:54 hammer01 dbmail/imap4d[

[Dbmail] FreeBSD 6.x - 2.2.0 rc2 - mysql 5.0.21 - libsieve 2.2.1 - goes BOOM!

[Jonathan Feally]

Hello,

I have a FreeBSD box that is already running dbmail 2.1.x against a 
mysql db - client and server is 5.0.21.
I am trying to get it up to 2.2.0 rc2 with libSieve - and I can compile 
it just fine.

The daemon starts. And all looks good in the logs.

When I try to connect to the imap server it goes boom.

Launching daemon

Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+236): server will create  [1] children
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+249): children will make max. [1] 
connections
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+262): timeout [4500] seconds
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+267): no value for SOCKET in config file
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+270): socket []
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+282): binding to PORT [143]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+298): binding to IP [x.x.x.x]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+305): no value for BACKLOG in config 
file. Using default value [16]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+319): resolving client IP
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+333): Disabling IMAP-before-SMTP
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+345): effective user shall be [nobody]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+357): effective group shall be [nogroup]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+369): will maintain minimum of [1] 
spare children in reserve
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+381): will maintain maximum of [1] 
spare children in reserve
Nov 11 14:16:53 hammer01 dbmail/imap4d[52325]: Debug:[serverparent] 
serverparent.c,LoadServerConfig(+393): will allow maximum of [1] children
Nov 11 14:16:53 hammer01 dbmail/imap4d[52327]: Debug:[server] 
server.c,server_daemonize(+198): sid: [52326]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52327]: Debug:[server] 
pool.c,statefile_create(+589): Creating scoreboard at 
[/var/run/dbmail-imapd.state].
Nov 11 14:16:53 hammer01 dbmail/imap4d[52327]: Debug:[server] 
server.c,dm_socket(+334): done
Nov 11 14:16:53 hammer01 dbmail/imap4d[52327]: Debug:[server] 
server.c,create_inet_socket(+404): creating socket on [x.x.x.x:143] with 
backlog [16]
Nov 11 14:16:53 hammer01 dbmail/imap4d[52327]: Debug:[server] 
server.c,dm_bind_and_listen(+354): done
Nov 11 14:16:53 hammer01 dbmail/imap4d[52328]: Debug:[db] 
dbmodule.c,db_load_driver(+67): looking for mysql as 
modules/.libs/libmysql.so
Nov 11 14:16:53 hammer01 dbmail/imap4d[52328]: Info:[db] 
dbmodule.c,db_load_driver(+71): cannot load Cannot open 
"modules/.libs/libmysql.so"
Nov 11 14:16:54 hammer01 dbmail/imap4d[52328]: Debug:[db] 
dbmodule.c,db_load_driver(+67): looking for mysql as 
/usr/local/lib/dbmail/libmysql.so
Nov 11 14:16:54 hammer01 dbmail/imap4d[52328]: Debug:[sql] 
dbmysql.c,db_query(+286): query [SHOW VARIABLES LIKE 'collation_%']
Nov 11 14:16:54 hammer01 dbmail/imap4d[52328]: Debug:[sql] 
dbmysql.c,db_mysql_check_collations(+116): does 
[collation_database:latin1_swedish_ci] match 
[collation_connection:latin1_swedish_ci]?
Nov 11 14:16:54 hammer01 dbmail/imap4d[52328]: Debug:[sql] 
dbmysql.c,db_query(+286): query [SELECT 1=1 FROM dbmail_physmessage 
LIMIT 1 OFFSET 0]
Nov 11 14:16:54 hammer01 dbmail/imap4d[52328]: Debug:[sql] 
dbmysql.c,db_query(+286): query [SELECT 1=1 FROM dbmail_headervalue 
LIMIT 1 OFFSET 0]
Nov 11 14:16:54 hammer01 dbmail/imap4d[52328]: Debug:[sql] 
dbmysql.c,db_query(+286): query [SELECT 1=1 FROM dbmail_envelope LIMIT 1 
OFFSET 0]
Nov 11 14:16:54 hammer01 dbmail/imap4d[52329]: Message:[server] 
pool.c,child_register(+294): register child [52329]
Nov 11 14:16:54 hammer01 dbmail/imap4d[52329]: Info:[server] 
pool.c,child_register(+319): initializing child_state [52329] using slot [0]
Nov 11 14:16:54 hammer01 dbmail/imap4d[52329]: Info:[serverchild] 
serverchild.c,CreateChild(+194): signal handler placed, going to perform 
task now
Nov 11 14:16:54 hammer01 dbmail/imap4d[52329]: Debug:[db] 
dbmodule.c,db_load_driver(+67): looking for mysql as 
modules/.libs/libmysql.so
Nov 11 14:16:54 hammer01 dbmail/imap4d[52329]: Info:[db] 
dbmodule.c,db_load_driver(+71): cannot load Cannot open 
"modules/.libs/libmysql.so"
Nov 11 14:16:54 hammer01 dbmail/imap4d[52329]

[Dbmail] Bug Cleanup

[Jonathan Feally]

Hello,
Being a new user of dbmail, but a longtime user of many other opensource 
projects, I have tried to get a handle on current issues and progress of 
DBMAIL's development. However, the bug system appears to be mostly 
ignored in terms of bug status and response to bugs in general. Feature 
requests just sit there with no feedback. Problems reported in version 
2.0.7 appear to have just been dropped with no response as to stating 
the problem is fixed.


I know my wishes will go mostly to the bit bucket, but on the chance 
that the bug marshalls (do we have any) are listening, perhaps mantis 
will get a good look through and bugs from 2004 that are still open will 
go away.


For any opensource project maintained by multiple people to move 
forward, there has to be some clear way of knowing what is busted and 
what should be added.


My 2 Cents
-Jon

Re: [Dbmail] Feature Request: trigger in dbmail

[Jonathan Feally]

Now why would a user delete mail from the spam folder if the script is 
going to do it for you?


My entire setup requires very little training of users. They move spam 
into folder. If it is a message that was marked as posible spam they 
copy it into the notspam folder. Thats it. They continue with their 
work. If they look in the spam folder they will see at most 29 minutes 
of spam that has been put in the folder, otherwise it's gone.


Now consider this. Lets say you wake up in the morning and move 100 
messages all at once into the spam folder. A trigger is going to launch 
your external script/application 100 times and probably at a rate in 
which your mail server is going to get load from the application being 
run without it actually doing anything. A cron script will let you run a 
batch of spams through and potential reduce the call to the application 
doing the work down to once or twice. Each instance of a program is 
going to suck up memory and other system resources. Do you really want 
100 concurrent instances of dspamc running? Or would it make more sense 
to keep the mail server happy and run the dspamc's serially, keeping 
system resources free for the database server or dbmail-imapd.


This is just for things like spam training. Imagine if somebody setups 
up a script to do some other inspection of the email. Perhaps a simple 
thing such as a support mailbox that recieves emails in a certain format 
that wants to mark a paticular item as responed to in another database. 
Then the midnight tech support guy responds to 30 emails all at once 
because his DVD just finished and them moves them off in bulk. This 
script then is going to read the message, extract the ticket #, open a 
connection to the database and update the record. Now all of a sudden 
you have 30 queries hitting up your db at the same time. Not a big deal, 
but for scalabillity this isn't going to work well.


Not to mention that each trigger would have to be defined somewhere and 
know what script/application to execute. For a spam thing to work with 
each user having his own spam folder, thats one trigger per user. Thus 
creating a lot of overhead for the mail administrator when he wants to 
set it up and change it.


This is also going to give dbmail-imap more work to do on every move 
checking if the message was in a folder that has a trigger that runs 
when a message is moved out and checking if the dst folder has a trigger 
for a message moved in.


Triggers may work good for an installation where you have a small number 
of users, but on a large scale in a corperate enviroment, programs 
running on the machine where dbmail-imapd sits is going to cause 
problems. It would be much simpler to have a timestamp field on the 
mailbox it self as to when the last write activity took place in the 
mailbox. This would allow for an external machine to access the database 
periodically to check whether it needs to open the mailbox and perform 
tasks. The only draw back to this timestamp is that all activity in the 
mailbox must be done with dbmail daemons or other triggers may not take 
place. This method will result in an additional query to the db to 
update the mailbox's last modified time on each transaction performed on 
a message in it. I guess each dbmail daemon could have a tuneable as to 
whether it is going to update this field and on what events.


-Jon


Magnus Sundberg wrote:


Aleksander wrote:


Jonathan Feally wrote:




As far as getting what your want done, I would suggest using a 
script driven via cron every half hour or so.




Yes, I know of such a possibility but there's always the chance, that 
the user deletes the mails before the cron shedule. And you would 
need another special folder for false positives. Cron+scripts is an 
ugly workaround, IMHO.





How do you know that the content of the deleted mail was spam?

I beleive you need the user to claim that it really is spam, or false 
positive, you can not jump to any conclusions. cron is in my opinion 
the simplest solution and it does not complicate dbmail either.


Magnus
___
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Re: [Dbmail] Feature Request: trigger in dbmail

[Jonathan Feally]

Triggers do sound interesting, however i'd rather see the effort put 
into server-side filters for sorting purposes then special execution of 
other applications.
As far as getting what your want done, I would suggest using a script 
driven via cron every half hour or so. I have SpamAssassin's sa-learn 
doing much the same thing that you wish to accomplish.
Keep in mind here that this setup is based upon multiple users sharing 
the same public folder, but that shouldn't matter much as you could have 
it read the messages out of any mailbox as long as you specify the 
correct ID. This script marks the message as deleted and will get purged 
by a dbmail-util purge run.


Here is my train.sh:

#!/bin/sh
export PATH=$PATH:/usr/local/bin

MYSQL_CMD="/usr/local/bin/mysql -h database_server_ip_here -udbmail 
-pdbma1l dbmail -B -N -r"

#Grab Messages
HAM_MSG_UIDS=`$MYSQL_CMD -e "SELECT dbmail_messages.message_idnr FROM 
dbmail_messages WHERE dbmail_messages.mailbox_idnr='59' and status='0'"`
SPAM_MSG_UIDS=`$MYSQL_CMD -e "SELECT dbmail_messages.message_idnr FROM 
dbmail_messages WHERE dbmail_messages.mailbox_idnr='58' and status='0'"`


cd /usr/sa_train/mail/ham
for MSG_UID in $HAM_MSG_UIDS
do
{
   $MYSQL_CMD -e "SELECT GROUP_CONCAT(dbmail_messageblks.messageblk 
ORDER BY dbmail_messageblks.is_header DESC SEPARATOR '') FROM 
dbmail_messages JOIN dbmail_messageblks ON 
(dbmail_messages.physmessage_id=dbmail_messageblks.physmessage_id) WHERE 
dbmail_messages.message_idnr='${MSG_UID}' GROUP BY 
dbmail_messages.message_idnr" > $MSG_UID
   $MYSQL_CMD -e "UPDATE dbmail_messages SET deleted_flag='1', 
status='2' WHERE message_idnr='${MSG_UID}'";

}
done

cd /usr/sa_train/mail/spam
for MSG_UID in $SPAM_MSG_UIDS
do
{
   $MYSQL_CMD -e "SELECT GROUP_CONCAT(dbmail_messageblks.messageblk 
ORDER BY dbmail_messageblks.is_header DESC SEPARATOR '') FROM 
dbmail_messages JOIN dbmail_messageblks ON 
(dbmail_messages.physmessage_id=dbmail_messageblks.physmessage_id) WHERE 
dbmail_messages.message_idnr='${MSG_UID}' GROUP BY 
dbmail_messages.message_idnr" > $MSG_UID
   $MYSQL_CMD -e "UPDATE dbmail_messages SET deleted_flag='1', 
status='2' WHERE message_idnr='${MSG_UID}'";

}
done

#Train SPAM
if [ ! -z "`ls -1 /usr/sa_train/mail/spam`" ]; then
   echo -n "SPAM: "
   /usr/local/bin/sa-learn -p 
/usr/local/etc/MailScanner/spam.assassin.prefs.conf --spam 
/usr/sa_train/mail/spam

   rm /usr/sa_train/mail/spam/*
fi

#Train HAM
if [ ! -z "`ls -1 /usr/sa_train/mail/ham`" ]; then
   echo -n "HAM: "
   /usr/local/bin/sa-learn -p 
/usr/local/etc/MailScanner/spam.assassin.prefs.conf --ham 
/usr/sa_train/mail/ham

   rm /usr/sa_train/mail/ham/*
fi

/usr/local/bin/sa-learn -p 
/usr/local/MailScanner/spam.assassin.prefs.conf --sync



It basically reads the headers and body of each message into its own 
file, then sa-learm runs on the spam or ham folder. When its done, the 
temporary copies of the messages are rm'ed from the filesystem and the 
messages in the db are marked deleted as if somebody deleted them via IMAP.

Don't forget to get the correct mailbox_idnr for each folder!
You could probably easily modify this script to pipe the message to 
dspamc and probably even skip making the temporary files.
If you don't want the email deleted then you do an update on 
dbmail_messages.mailbox_idnr and changed it to another folder instead of 
setting the status as deleted. I think this solution will probably work 
just fine and will accomplish what you want. I would not have cron run 
this at any less than a 5 minute interval. I have no issues with 50 
users using a #Public/spam mailbox running the script every half hour.


-Jon

Aleksander wrote:


I'm interested in the following feature.

A trigger mechanism for IMAP folders. If a message is moved to folder 
X, specified (external) command is executed. Likewise when message is 
moved from folder X. If required, dbmail-imap would give the whole msg 
to the program executed too.



Example.

Using DSpam antispam software. DSpam learns what is spam and what's 
not spam from the individual user (and is probably the best antispam 
solution!). In my implementation, postfix delivers mail to dspam, and 
dspam to dbmail-smtp. If dspam thinks a message is spam, it delivers 
directly to a user's spam folder. Usually the user forwards a spam 
message that passed the filter to a specified email address. Or a 
message that was categorised incorretly as spam to another email 
aadress. This, is not user friendly.


An alternative could be a triggering mechanism in dbmail-imap. When an 
email is moved to folder spam, then the command " | dspamc --user 
[EMAIL PROTECTED] --class=spam --source error" is executed (the whole 
email is piped to it). That way, DSpam is told the email is spam. 
(dspamc is a dspam client, that communicates with the dspam daemon.)


When a message is moved from the spam folder, the same command but 
with --cla