RE: [Dbmail] Large Sites
Hi Paul - We're running a reasonably large site on MySQL. Approx 400GB of mail storage, with 100k users. Currently Dbmail 1.x; although likely to move to 2.0 when I get back from holiday. Won't say it hasn't been without its challenges. We've tried both MyISAM and Innodb tables and due to either MySQL bugs or external glitches (power cut) had had the misfortune to need to check and repair our large database tables which has proven to be a nightmare. This isn't an issue with DBMAIL - more MySQL... Not sure if it's a really good choice for large installs, but I don't know Postgres well enough and it may or may not be any better. We tried 4.1.7 recently and in a master-slave scenario had some software crashes in MySQL, which caused some issues. I'd suggest waiting to 4.1.9 or 4.1.10 before using it; although the performance of 4.1 seems really good in comparison to 4.0. Of late we've just started using MERGE tables to help partition our data files up. So messageblks = UNION(messageblks_alpha, messageblks_beta, messageblks_charlie). This has worked fairly well (esp under 4.1), and ok under 4.0 -- and means that messageblks_alpha/beta effectivley become read-only except for when running the maintenance program to purge messages. I've found this a godsend as can then optimise the read-only tables on a slave and copy them back to the master, etc. It also means that your database tables can be kept at smaller sizes -- say 100Gb, which means checks/repairs/optimize are less of a burden; and more than likely a problem is likely only to affect the current 'insert' table => less data to have to check. All in all, Dbmail has been good -- provided you have a good database setup. We have separate servers for SMTP delivery and POP/IMAP; and separate DB server (and now a read-only slave for backups) - currently on Xeon 2.4G with 1TB SCSI array. Having the read-only slave is quite good; as you can do intensive queries without affecting users... Like 'show me all users with this text in their message'. I've even written custom maintenance programs which use the slave to lookup messages for purging, and just direct the DB writes to the master. This has avoided the table-lock issues that you get with MyISAM tables when doing big queries. Hope this info helps. Unfortunatley I'm on holiday from tomorrow morning for 6 weeks, so may not reply for a while if you've got any questions; although bound to be online every now and then while away. -- Regards, Mark Mackay -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Stewart Sent: Friday, 17 December 2004 2:28 a.m. To: dbmail@dbmail.org Subject: [Dbmail] Large Sites Hi there.. Just new to this mailing list but ran across dbmail recently at a client site... Looks pretty good..:) I'm wondering what sizes of "real-life" sites are running currently and what kind of hardware, how many users etc? Has anyone pushed the limits or is MySQL the limit in this system per say? Thanks for your time, Paul
RE: [Dbmail] Mirroring DBMail
> So, instead of 2 DBMail boxes, I will have one Cyrus box (which the users will connect to), and a DBMail box which will only be used for statistical analysis and archive purposes. DBMail is perfectly > suited for that purpose; it saves me a lot of coding. Unless you've got a particular need to run Cyrus, why not try the following: 1) use DBMail for your primary server which your users log into 2) set up one-way replication from that database server to another box 3) Direct all search queries/etc to your slave database, which means no load is incurred on your primary box We're just in the process of setting up something similar, where we scan through customer mailboxes for say people over quota limit, or inactive mailboxes, etc - and can't afford to have the load on the primary server. Then when we find a message, mailbox, user, etc or whatever we want to delete, we can direct the update query to the primary box. This moves all the searching load to the slave, and only puts minor update queries to the primary server. The bummer at the moment, (according to list popular consensus a few months back) is that you can't use either MySQL server as your 'primary' database, as the replication is not real-time enough, so you may end up with out-of-sync views of data. But treating the slave as read-only, should negate/solve this. -- Regards, Mark Mackay
RE: [Dbmail] dbmail-smtp slow
We've disabled the quota checks in a 1.x version and it has produced a major speed gain in the database. Previously our database was spiking to high loads doing the SUM() calcs, so I wouldn't underestimate the cost of this when you get to large message stores. If possible - it would be great to have a config item which could be enabled to disable quota checks on delivery... Would save patching each release. -- Regards, Mark Mackay -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Simon Gray Sent: Thursday, 28 October 2004 4:53 a.m. To: 'Ilja Booij'; 'DBMail mailinglist' Subject: RE: [Dbmail] dbmail-smtp slow > I don't think that disabling quota checks will get you any real speed > improvement. The real speed issues here are in the starting of the > dbmail-smtp process, setting up database connection etc. >
RE: [Dbmail] procmail and dbmail-smtp
I did this a while back by writing a simple perl script which basically
piped stdin; but killed the first line which procmail adds for mbox format.
#!/usr/bin/perl
# discard first line from procmail (comment this out if you continue to use
this script direcly as the LDA in sendmail)
$line = ;
open(PIPE, "|/path-to-dbmail");
while () {
print PIPE $_;
}
close PIPE;
Regards,
Mark
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of April Lorenzen
Sent: Friday, 15 October 2004 11:06 a.m.
To: DBMail mailinglist
Subject: [Dbmail] procmail and dbmail-smtp
Thanks, Paul.
Can anyone suggest a manual method of testing just the procmail to dbmail
successfully, like a command I could use to pipe a test message from the
command line or a prepared text file to procmail, and watch procmail
correctly deliver it into dbmail-smtp?
This would eliminate sendmail from the picture, allow me to verify the
procmail to dbmail workings, then proceed from there.
Thank you,
- April
> April,
>
> Somehow, sendmail isnt passing the correct arguments to procmail.
> Therefor the mailer definition must be incorrect.
>
> From the sendmail site I understand:
>
> - you don't want to override Mprocmail. Leave it at default. Of course
> you do want to enable this mailer so leave MAILER(procmail) in.
>
> - you simply set up a mailertable entry for the domain you want to
> deliver to dbmail via procmail:
>
> -/etc/mail/mailertable-
> codelock.com procmail:/etc/procmailrcs/codelock.com
> ---
> where /etc/procmailrcs/codelock.com contains:
>
> /etc/procmailrcs/codelock.com--
> VERBOSE=true
> LOGFILE=/tmp/procmail.log
>
> :0:
> * ^X-milter-siq-Report: pass=NO
> | /usr/sbin/dbmail-smtp -m Bulk -d "$2"
>
> :0:
> | /usr/sbin/dbmail-smtp -d "$2"
> ---
>
> This is really more a sendmail issue than dbmail. If the above doesn't
> help you should get support from a sendmail user, which I am not
> anymore.
>
>
>
>
>
> April Lorenzen wrote:
>> So far I haven't gotten procmail to deliver into dbmail - I get an
>> error about
>>
>> procmail: Couldn't determine implicit lockfile from
>> "/usr/sbin/dbmail-smtp"
>> procmail: Locking ".lock"
>> procmail: Executing "/usr/sbin/dbmail-smtp,-d,"
>> procmail: Non-zero exitcode (75) from "/usr/sbin/dbmail-smtp"
>> procmail: Assigning "LASTFOLDER=/usr/sbin/dbmail-smtp -d "
>> procmail: Assigning "PATH=/root/bin:/usr/local/bin:/usr/bin:/bin"
>>
>> I am wondering if the procmailrc line you suggested:
>>
>> | /usr/sbin/dbmail-smtp -m Bulk -d "$@"
>>
>> delivers to the Bulk folder under the user the message was addressed
>> to
>> -
>> or to a system-wide Bulk folder. I need it to go to the individual
>> addressee bulk folder.
>>
>>
>>
>>>April Lorenzen wrote:
>>>
Using dbmail-pgsql, yesterday's debian pkg :)
Sendmail inserts and anti-spam header into my inbound mail like
this:
X-milter-siq-Report: pass=YES
or
X-milter-siq-Report: pass=NO
I would like to use the simplest method to cause mail with a
pass=NO to be stored in the dbmail tables so that it will appear in
an IMAP folder named "bulk"
while the pass=YES mail goes as usual to the inbox folder.
>>>
>>>
>>>
3. add another program between sendmail and dbmail - not sure how
this would help unless LMTP makes it possible to insert to a
particular folder and SMTP doesn't. (sounds silly)
>>>
>>>By inserting procmail between sendmail and dbmail you can achieve
>>>this easily.
>>>You'll have to use dbmail-smtp for this though.
>>>
>>>in /etc/procmailrc
>>>
>>>
>>>VERBOSE=true
>>>LOGFILE=/tmp/procmail.log
>>>
>>>:0:
>>>* ^X-milter-siq-Report: pass=NO
>>>| /usr/sbin/dbmail-smtp -m Bulk -d "$@"
>>>
>>>:0:
>>>| /usr/sbin/dbmail-smtp -d "$@"
>>>
>>>
>>>
>>>
>>>This assumes a mailer setup along the lines of:
>>>
>>>Mprocmail, P=/usr/bin/procmail, F=mSDFMhun, S=11, R=21,
>>> A=procmail -m /etc/procmailrc $u
>>>
>>>
>>>
>>>--
>>>
>>> Paul Stevens [EMAIL PROTECTED]
>>> NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
>>> The Netherlands___www.nfg.nl
>>>
>>
>>
>>
>> ___
>> Dbmail mailing list
>> Dbmail@dbmail.org
>> https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>
>
> --
>
>Paul Stevens [EMAIL PROTECTED]
>NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
>The Netherlands___www.nfg.nl
> ___
> Dbmail mailing list
> Dbmail@dbmail.org
> https://mail
[Dbmail] MySQL MERGE tables with dbmail
Hi all - I was wondering whether anyone had played using MERGE tables for dbmail? I've currently got a MyISAM table that's about 171G for messageblks - and was looking at converting it back to Innodb -- but the size of course is horendous, so will take hours. I've also been wanting to setup a replicated copy for a while, but again size has been an issue (don't want 12+ hours downtime). So then I started thinking about MERGE tables: The messageblks table is the prime candidate for this - as it is effectively an insert-only table until you run the maintenance scripts, so by potentially creating multiple tables... E.g.MyISAM: messageblks_2004_08 MyISAM: messageblks_2004_07 MyISAM: messageblks_latest MERGE: messageblks ... the messageblks_latest table would be the only one changing. So any copying done of messageblks_2004_XX would be fine as the disk copy would not be changing; which would be great for me as I could copy say 170G of old mail over ot the slave server, and then just have a short bit of downtime to clone the messageblks_latest table over to the slave and then start the replication up again. It should be able to copy a live archived table (e.g. messageblks_2004_07) to a temporary name and then do things like OPTIMIZE TABLE and then rename it back to the correct name, with only minimal down time (just time to flush and lock tables and complete the table shuffle), etc. Things like table recovery, etc would probably be better too - as only the messageblks_latest would have changed. --- Does anyone have any experience with this in a dbmail installation? Or comments based on experiences elsewhere? There no doubt are potential issues with MERGE which I'm unaware of (e.g. more file descriptor usage, etc); but perhaps this is a good technique I can use to get the database copied over to another server and then just copy the messageblks_latest contents back into a central messageblk table... Anyone's thoughts, comments appreciated. Also - does anyone know if this sort of thing could be done with Innodb? -- Regards, Mark Mackay
RE: [Dbmail] Two MX servers updating/inserting into 1 dbmail database
> My question is: Can I have two MX hosts updating the same database? What if there is an insert at precisely the same time from both > MX hosts? Will each seperate message that is inserted get it's own message_idnr? Will it all stay consistent? We have had up to 9 MX hosts inserting into one database, without isssue (except for MySQL/Linux threading problems) -- so I don't think you'll have a problem. /Mark
RE: [Dbmail] Imap problem
Our outlook users have been having the same problem for quite a few months. Was hoping 2.0 would fix it, but haven't had a chance to upgrade to it yet. /Mark _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Schultz Sent: Tuesday, 20 April 2004 5:13 a.m. To: dbmail@dbmail.org Subject: [Dbmail] Imap problem We have been using dbmail 1.2.5 for several months with no major problems. In the past couple of days we have started getting client side errors from outlook as follows: "The UID of a message changed unexpectedly. This typically indicates a server bug. Your program may not function properly after this." All of our users connect via imap. Anyone else seen this problem and/or have any ideas on a suggestion? Thanks David Schultz
RE: [Dbmail] dbmail causing excessive server load?
Try disabling dns lookups in the config file /Mark > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Gabriel Ricard > Sent: Wednesday, 14 April 2004 11:53 a.m. > To: Dbmail@dbmail.org > Subject: [Dbmail] dbmail causing excessive server load? > > I've got dbmail running on Mac OS X 10.3.3 on a Quicksilver G4 > PowerMac. For some reason, the lookupd process was utilizing 180% CPU > according to top. I killed some processes off to see what might be > influencing that, and as soon as I killed the dbmail-imapd processes, > lookupd's load went down to nothing. lookupd is the directory service > daemon for Mac OS X. I can't figure out why dbmail would be causing > lookupd to suck up the CPU... Any ideas? > > - Gabriel > > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail >
RE: [Dbmail] Large sites using dbmail?
> Ever think about trying postgresql? It is my understanding that it > scales up better with lots of concurrent access. Anyone have > any good > experience comparing the two for dbmail? We have tried postgresql (not for dbmail), and I guess all I can say is MySQL 'feels nicer' to me, and it's the devil I know vs. the devil I don't. We run Innodb and it performs realy well in general. I just have some config setting wrong; or linux is not the best platform for high threads or something. > I think a config option should be added to dbmail to disable quota > calcs. It does considerably add to the load unnecessarily if not being > used, and adds an update statement to the user table ever > message insert. Agreed. I was thinking the same thing when I was hacking the code to disable it :) /Mark
RE: [Dbmail] Large sites using dbmail?
Hi Andrew - > email accounts: 6000 > email storage: 150G and growing > Are there any sites out there of similar size or larger using dbmail? Yep. We're up to about 150G now - just pricing up a fibre channel array now to plan for the future growth. We've got about 50,000 mail accounts loaded; but only about 10,000 are checking every regularly. > Please provide details on your setup as well as any gotchas you encountered. Database is on a dedicated linux box -- dual Xeon 2.4 with SCSI U160 HDs (at moment) and about 12G of memory. We run (currently) six dedicated smtp servers; and 3 pop/imap servers. Handles the load nicely in general -- although we do virus and spam filtering so sometimes the front-end servers get a it loaded. Biggest problem for me at the moment is MySQL tuning. We keep 'running out of thread memory' and I can't seem to find the right mysql config settings to solve it. I'm certain MySQL will handle more connections; but I suspect I'm going to have to pay for the mysql.com to 'optimize' my database so it hums along. We recently had a problem with quota calculations taking too long to calculate; although we are running an older version so this may be fixed. I simply disabled the quota calc (added 2 lines in the code) and substantially reduced the database CPU. Database load was about 50-100%; but with the quota calcs disabled this dropped to about 10-50%. MySQL rocks :) /Mark
RE: [Dbmail] Suggestion for a-minute-pop3-users
Expanding on this idea -- is there any way to do a 'status' message with POP? E.g. if doing server maintenance have the users download a message from a bastion server saying 'server undergoing maintanance or offline/etc' Problem I believe is the UIDL handling -- if you simply do a fake POP3 server without the UIDL list then it will reset everyone's keep on server list. But I wonder if something could be done where you 'download' the list of unique_id's from dbmail to a holding server and presend the UIDL list + the status message or something. And re: imap -- I think there is a status message that can be displayed. Maybe that could be built into dbmail. /Mark > I have a suggestion for users > that have robots check there mailbox every minute or so. > > Could a "suspend" option be added in dbmail-adduser in a > future release of dbmail. > > We could suspend users that do frequent pop/imap request from > getting there mail but they could still receive there mail until we unsuspend them.
RE: [Dbmail] What to do with POP3 spammers
> You should get them to switch to consistent imap. At least > then they wouldn't be downloading all of their mail at one time. > > -- Dan I find in our installation that the POP3 method works better, because once they initially download their mall the POP3 client is asking is 'is there new mail'; which it can do really quickly of an index lookup. The client then disconects. Whereas with IMAP -- the client stays connected to the daemon, using up a process and correspondingly a database connection. That would be a hit of 400 database connections if you did that -- and if people use multithreaded clients that can burst higher. In a shared application pool which reused database connections this may be a better approach. Don't know if dbmail is heading that way with their daemon installations. Looks like forked processes with persistent connections for a while. /Mark
RE: [Dbmail] What to do with POP3 spammers
While it's definitely annoying, that's one thing I love about dbmail: It's an indexed SQL lookup, rather than a mbox-style trudge-through-a-10Mb-file-to-fine-there-is-no-new-mail approach. With enough memory, and especially with the MySQL 4.0 query-cache; it barely causes a performance hit. /Mark > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Jacques Beaudoin > Sent: Thursday, 11 March 2004 3:16 p.m. > To: dbmail@dbmail.org > Subject: [Dbmail] What to do with POP3 spammers > > I have my dbmail+postfix+amavis_new+fprot server running > and im blocking 7000 spams+virus a day (2,555,000 per year !outch!). > > Well so much for spams comming from the internet. > > Yesterday i made a small script using a mysql querry > and the linux sort program to get a cumulative > readout of there last_login. > > And via cron i executed that script at every minutes > for 3 hours to get a summary for the 3 hours. > > Well i have more then 400 POP3 spammers (teachers with phd). > > Some have setup there pc to check for mail every minutes > others at every 2 minutes and others at every 5 minutes even > if they where ask not to do that. > > I plan to make a other script to send a automatic email to all > the pop3 spammers. > > Does anybody have a other solution ? > > 1 - Have dbmail 2.0 refuse rapid pop3 queries > but what the heck there getting to dbmail anyway > 2 - Quick there derriere > 3 - Fire them > 4 - Send them back to school so they no 60 x 24 = 1440 pop3 > querys a day > 5 - Send them back to school so they can help me write scripts > 6 - > 7 - > 8 - > 9 - > 10- > > > Jacques Beaudoin > Responsable des accès internet / Internet security manager > Commission scolaire de la Pointe de l'Ile / School board > Montréal, Québec, Canada > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail >
RE: [Dbmail] Date of mails without date
Re: procmail -- never mind :) /Mark > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Pablo León > Sent: Wednesday, 3 March 2004 7:33 a.m. > To: dbmail@dbmail.org > Subject: Re: [Dbmail] Date of mails without date > > I'm using qmail, don't remember then version (6 months old or so) > > Jesse Norell wrote: > > > > > > >>On Tue, Mar 02, 2004 at 05:48:47PM +0100, Pablo León wrote: > >> > >> > >>>Yes, these messages are copletely missing the Date: header. Par > >>> > >>> > >example, > > > > > >>>a printer reporting a paper-jam situatuion sends me a > message without > >>>date: field, just because the printer don't have an > internal clock! I > >>>think the cuestion is how to best deal with these > >>> > >>> > >non-rfc-compliant emails. > > > > > >> > >>You could have your mta add a date header if it doesn't exist. > >> > >> > > > > > > What mta are you using? Offhand I don't know of any that _don't_ > >add a Date: when it's missing. I suppose that could be checked > >by dbmail at message injection time too, and add one if missing, but > >that's really not dbmail's job. > > > > > > > >-- > >Jesse Norell > > > >[EMAIL PROTECTED] is not my email address; > >change "administrator" to my first name. > >-- > > > >___ > >Dbmail mailing list > >Dbmail@dbmail.org > >https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > >
RE: [Dbmail] Date of mails without date
Hi Pablo - Are you by chance using procmail to deliver your messages? If so, procmail adds another line to the start of each message which would be present when piped through to dbmail, and would either confuse the parser or email clients. We had a problem similar to what you're describing in the early days of our dbmail testing; and we wrote a simple perl script which killed the first line of the file in the pipe. Essentially: procmail | killfirstline.pl | dbmail-smtp /Mark > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Pablo León > Sent: Tuesday, 2 March 2004 10:41 p.m. > To: dbmail@dbmail.org > Subject: [Dbmail] Date of mails without date > > Hi! > > Some messages I receive are missing the 'date:' header field. Most of > them are messages automaticaly generated by adminitrative tools. When > using mozilla as mail client, I see 1/1/1970 as mail date, then this > email goes to the bottom of the list and I don't see it! > > Any Ideas? > > > Scanned by WinProxy > http://www.Ositis.com/ >
RE: [Dbmail] dbmail-smtp and imap folders
For mailbox delivery it must be a username, not an email alias - from memory. I don't know whether mailbox selection is enabled via alias expansion yet -- may need to wait for the Sieve work to be complete. /Mark > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of nerVo > Sent: Wednesday, 4 February 2004 9:43 a.m. > To: dbmail@dbmail.org > Subject: Re: [Dbmail] dbmail-smtp and imap folders > > Hm... > No more success... > > cat mail_file | dbmail-smtp -d [EMAIL PROTECTED] -m temp > ---> Recipient names must be specified > ---> Recipient names must be specified > (** yes, two times**) > > Nor using "temp" or -u instead of -d, or both. > > Perhaps my folder "temp" has an another syntax or something > like that, but it > appears as it in all the mail client i tried (balsa, kmail, sylpheed, > outlook, ...) > Is there a way to verify that ??? > > Thanks. > > > You'll hate this... The '-m' option has to appear *after* > the -d option: > > > > cat mail_file | dbmail-smtp -d [EMAIL PROTECTED] -m temp > > > > -fr. > > > > On Tue, 3 Feb 2004 21:04:12 +0100 > > > > nerVo <[EMAIL PROTECTED]> wrote: > > > Hello, > > > > > > I'm using DBMail 1.2.3 on FreeBSD 5.2. > > > Due to the lack of documentation, i can't find any > information about the > > > usage of th "-m" parameter to deliver a mail in a special > imap folder. > > > > > > if i just cat a mail, with a pipe on dbmail-smtp like that : > > > > > > cat mail_file | dbmail-smtp -d [EMAIL PROTECTED] > > > > > > its ok and the mail appear in the Inbox folder > > > But if i want it to appear in the "temp" folder of my > imap directory : > > > > > > cat mail_file | dbmail-smtp -m temp -d [EMAIL PROTECTED] > > > --> Error: When using the mailbox delivery option, you > should specify a > > > userna cat mail_file | dbmail-smtp -m "temp" -d [EMAIL PROTECTED] > > > --> Error: When using the mailbox delivery option, you > should specify a > > > userna cat mail_file | dbmail-smtp -m temp -u [EMAIL PROTECTED] > > > --> ** nothing happened ** > > > cat mail_file | dbmail-smtp -m "temp" -u [EMAIL PROTECTED] > > > --> ** nothing happened ** > > > > > > I need using folders in my aliases file of sendmail, and > "catting" mail > > > file is the simpliest way i found to test it. > > > > > > Regards. > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail >
RE: [Dbmail] 2.0 and header-cache
> A few questions, > - What should the table structure be? > - What about automatic rebuilding the table cache, if you get a > query for a new header? If you change the syntax, then as Jesse pointed out, dbmail-maintenance would be a logical place. Failing that, based on the lifetime of most email -- you could simply add another header, and for the first month or so the header would only show up for newly delivered email. > - What about this table structure > > CREATE TABLE cached_header_types ( > header_idnr int(??) NOT NULL AUTO_INCREMENT, > header_name text, > PRIMARY KEY (header_idnr), > INDEX (header_name) > ) TYPE=InnoDB; > This table holds the headers types we cache, > > CREATE TABLE header_cache ( > header_cache_idnr bigint(21) NOT NULL AUTO_INCREMENT, > header_type int(??), > header_val text, > message_id bigint(21), > UNIQUE(message_id, header_type), > FOREIGN KEY (`message_id`) > REFERENCES `messages` (`message_idnr`) > ON DELETE CASCADE, > ) TYPE=InnoDB; > > This way, we have a good index on the user/header combination, > This might not be the best approach, > I beleive the typical query is > Get headers A,B and C from mailbox x. > This would reguire something like > SELECT header_type, header_val FROM (header_cache, messages) > WHERE header_cache.message_id=messages.message_idnr AND > messages.mailbox_idnr= AND > (header_cache.header_type=head_id1 OR > header_cache.header_type=head_id2 OR ... > header_cache.header_type=head_idN) > > I beleive we would get a quite good index and quite fast replies. I'm not suggesting that a header cache is built up for every header, or for use in IMAP optimisations, etc (although others may want this). The issue I see with the above model is the one-to-many relationship. Typically in a webmail client you want to display a single summary row of info for each message (usually pulling stuff out of the messages table too, like message_idnr, etc. Thus a one-to-one join works well. My complex SQL is a bit rough so maybe there is a way to do this elegantly. A single sql transaction seems the best way for my purposes. "select messages.*, messageheaders.* from messages, messageheaders where messages.message_idnr=messageheaders.message_idnr order by " If someone can point out an easy way to get the same output from the above structure, then fine by me. Guessing the above model has merit for it's easy customization of header types. One other note -- I don't think everyone wants the header cache. A lot of people are using IMAP webmail clients, and unless dbmail itself can benefit from such a cache, I'd suggest it's a compile-time option. No processing overhead if you don't need it, and typically you're gonna choose the list of headers you want once or twice in the lifetime of a dbmail setup. If people want more headers, it should only affect DB size and processing time at delivery/rebuild. Selects from I should still be fast and only the desired headers can be returned. /Mark
RE: [Dbmail] 2.0 and header-cache
> One silly question, > How do we know which headers to cache? Ideally it would be customisable (even if needed to Define things at compile time, etc). Most people are after the header-cache-like-thing for webmail clients, or management reports/etc -- thus the ones I had below were the ones that came of interest to me. Even if the table were expanded to have 20 headers, only the people that wanted it would have to enable it - thus for the rest, there would be no overhead (especially if compile-time). > The headers are stored in the first messageblock in for the message. > > Suppose we stored the headers in a separate table and used the > database indexing facilities. > We could then do without a separate header cache, by just using > well formulated queries. I did that previously, and the speed hit was quite profound each time someone did a search. I ended up writing a perl-script to batch-process and generate my own table cache every five minutes and when someone logged into webmail. Worked better, but still was icky. It all comes down to database load (related to my scalability Q's). You want to move complicated functions off the database in my opinion and let the database be a really fast, indexed filesystem. That way the SQL server can be run on a dual processor box/etc and you don't need to update to a much more expensive 4x processor box or icky pay-for databases to get clustering. As soon as you ask the database to do more than just searches (e.g. string analysis), it has to think about it. A table join is pretty efficient (with the right keys). Just my humble opinion :) /Mark > /Magnus > > Mark Mackay - Orcon wrote: > > Hi guys - > > > > Another quick question (first time I've really looked at > 2.0 other than > > listening to the list). > > > > Is 2.0 going to have any type of header cache (something > that's suited to > > the native-webmail clients everyone's no-doubt hacking away at? We > > implemented some patches to 1.x and posted them to a list a > while back which > > cached the: > > > > - size > > - attachement (y/n) > > - to > > - from > > - subject > > - priority > > - sent-date > > - received-date > > > > from message when they came in... I notice the 2.0 schema > doesn't seem to > > have anything in it for this (may have overlooked it, > hopefully not going > > blind). Is this planned or can it be? ;) ? > > > > Cheers, > > Mark. > > > > ___ > > 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] 2.0 and header-cache
The separated fields are really useful as it means you can show a message listing using a simple join in SQL. Just having the headers flagged or in a separate msgblk means you need to process and interate every visible message, every time you show a mail listing -- very innefficient, unless you use complicated caching schemes/etc. On the other hand - simply parsing the headers into your native presentation format, means this is only done once and pulling up a mailbox listing takes milliseconds of CPU time rather than ... . I appreciate every doesn't necessarily want this, so maybe having it as compile-time included code would mean that those of us using webmail interfaces can simply turn it on. /Mark > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Ricardo Andere de Mello > Sent: Thursday, 4 December 2003 2:11 p.m. > To: dbmail@dbmail.org > Subject: Re: [Dbmail] 2.0 and header-cache > > Em Quarta 03 Dezembro 2003 20:27, Mark Mackay - Orcon escreveu: > > > Is 2.0 going to have any type of header cache (something > that's suited to > > the native-webmail clients everyone's no-doubt hacking away at? We > > implemented some patches to 1.x and posted them to a list a > while back > > which cached the: > > I would apreciate a kind of mime reference table too (not > necessarily separate > the mimes, just a reference table with mime boundaries and > mime headers). > I don't like to scan all the message to decode the mime, > since it was scanned > to enter at the system at the first place. > > []s, gandhi > > -- > Ricardo Andere de Mello > Quilombo Digital - Presidente > [EMAIL PROTECTED] - 55 11 32717928 > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail >
[Dbmail] 2.0 and header-cache
Hi guys - Another quick question (first time I've really looked at 2.0 other than listening to the list). Is 2.0 going to have any type of header cache (something that's suited to the native-webmail clients everyone's no-doubt hacking away at? We implemented some patches to 1.x and posted them to a list a while back which cached the: - size - attachement (y/n) - to - from - subject - priority - sent-date - received-date from message when they came in... I notice the 2.0 schema doesn't seem to have anything in it for this (may have overlooked it, hopefully not going blind). Is this planned or can it be? ;) ? Cheers, Mark.
RE: [Dbmail] trackrecord + (Scalability REPOST)
> - you need a clusterable database (For 2.0 you should be able > to write a oracle driver in about 4 hours). >(real clustered databases partition automatically). Heard scary things about Oracle from an admin point-of-view, but expect unfortuantely I'll need to look at that in the future. Figured it would be relatively trivial to do (write driver).. Keep hoping MySQL will have two-way replication soon with write locks and then it's all solved :o) Just done a bit more about database partitioning (oracle, db2) and looks quite good. Does anyone know of an easy-to-manage (like MySQL) and cheap database that does two-way replication and partioning? > - you'll need the LMTP daemon, not dbmail-smtp (increase in speed and handling is about 1000%). I've just checked 1.x cvs and 2.0 cvs and can't find anything (except in the TODO list) about LMTP. Can you advise when this is planned for? > - Dbmail 2.0 will have a lot of imap optimalization which > will increase performance a lot. My main stress for scalability is the database load -- I don't care how many front-end servers I have, so if the front-end servers have to grind away that's fine. > - use a native webmail like webDBmail. Yep, we've written one already which works nicely -- I think that's what's helping us scale at the moment - as we don't have heaps of imap webmail clients hammering away at the database. I'd love to use one-way replication with MySQL and point the webmail at that, but I got told about the issues with writes being delayed, etc so gave up on that -- but otherwise it would be perfect. > We currently have a db2 licence laying around. We might write > a driver for that and test for speed. Anyone know what the cost is for DB2 (real cost) for a 2-4 server system? Ideally 2 partitioned main servers which are replicated for backup to second two servers? > You could also hire us to tune a monsterous dbmail system for you. > We're already doing some very big dbmail setups. ;) > I once started dbmail with the thought of a multi-million > based e-mail system with super ease of use and maintenance. That would actually be great, as I've been thinking that my current setup is not tuned very well. Been looking around for a local (New Zealand) MySQL expert, that would come in a cheaper than the MySQL.com's $3,500+ offering :( Get in touch and let me know your rates, estimated cost, etc. /Mark
RE: [Dbmail] trackrecord + (Scalability REPOST)
>From a recent post I made for the trackrecord list: Status: O > We're currently running dbmail with: > - Dual Xeon 2.4G processors / SCSI Disks > - MySQL/InnoDB > - roughly 90k mailboxes > - 100G of mail data > - alias selection offloaded to smtp servers (we don't use aliases table we deliver straight to userid) > - Load average between 0.4 and 2.5 on a dual processor machine, averaging at around the .8 mark (so say 50% load) > > We have 4 machines handling SMTP/Virus/Spam filtering and then handing it to dbmail-smtp for delviery, and 3 machines > doing POP/IMAP -- all of which are light-moderate loaded. - I'd be *really keen* to hear from people with larger installations (even if privately and off list), who have ideas on how dbmail can scale. I need to work out what the next evolution for my cluster will be for the question below, and also just for general organic growth, etc. > I've been posed the question by management lately -- how would dbmail scale if we wanted to offer say 3 Million > mailboxes, rather than just the current limit I see of 100-200k mailboxes using my current setup? > > > Ideas that spring to mind are: > > Clustered databases > --- > Oracle or some database with multiple write masters and > cluser-wide locking for writes springs to mind. I understand > there is problems with MySQL one-way replication and > directing all writes to a master server, but reads from > multiple slaves - due to write-locking not being supported > thus the slave getting out of sync with the master, etc. I > scanned the list again recently but couldn't find any obvious > posts with solutions for this. > > Last minute addition >> I notice SAP DB is now MySQL MaxDB > and is available. Anyone looked at this as a viable backend > for dbmail? Can't for the life of me find the details on > replication support/etc on the site... > > > Partitioned databases > - > The old customea A-E on database server 1, F-L on database > server 2, etc. This would seem to do the trick, although with > dbmail it may be user_idnr 1-50,000 on DB1, 50,001-100,000 on > DB2 I guess, with some sort of central mailbox -> store index > table. I haven't looked at the code for 2.0 recently, but > from memory 1.x maintained a persistent connection with the > mailstore, thus this could be quite bad if there were say 100 > database servers. I'm not sure what the overhead would be for > connection setup/teardown if doing dynamic connections to > different servers. > > > Any other ideas? (even if not likely to be implemented > officially by dbmail -- may code our own variation if needed). ADDITIONAL QUESTIONS: - Has anyone ported dbmail to Oracle or something that has two-way clustering? - Cheers, Mark.
[Dbmail] Scalability
Hi all - I'm just looking ahead now and trying to see how well dbmail will scale with larger deployments. We're currently running dbmail with: - Dual Xeon 2.4G processors / SCSI Disks - MySQL/InnoDB - roughly 90k mailboxes - 100G of mail data - alias selection offloaded to smtp servers (we don't use aliases table we deliver straight to userid) - Load average between 0.4 and 2.5 on a dual processor machine, averaging at around the .8 mark (so say 50% load) We have 4 machines handling SMTP/Virus/Spam filtering and then handing it to dbmail-smtp for delviery, and 3 machines doing POP/IMAP -- all of which are light-moderate loaded. I've been posed the question by management lately -- how would dbmail scale if we wanted to offer say 3 Million mailboxes, rather than just the current limit I see of 100-200k mailboxes using my current setup? Ideas that spring to mind are: Clustered databases --- Oracle or some database with multiple write masters and cluser-wide locking for writes springs to mind. I understand there is problems with MySQL one-way replication and directing all writes to a master server, but reads from multiple slaves - due to write-locking not being supported thus the slave getting out of sync with the master, etc. I scanned the list again recently but couldn't find any obvious posts with solutions for this. Last minute addition >> I notice SAP DB is now MySQL MaxDB and is available. Anyone looked at this as a viable backend for dbmail? Can't for the life of me find the details on replication support/etc on the site... Partitioned databases - The old customea A-E on database server 1, F-L on database server 2, etc. This would seem to do the trick, although with dbmail it may be user_idnr 1-50,000 on DB1, 50,001-100,000 on DB2 I guess, with some sort of central mailbox -> store index table. I haven't looked at the code for 2.0 recently, but from memory 1.x maintained a persistent connection with the mailstore, thus this could be quite bad if there were say 100 database servers. I'm not sure what the overhead would be for connection setup/teardown if doing dynamic connections to different servers. Any other ideas? (even if not likely to be implemented officially by dbmail -- may code our own variation if needed). Cheers, Mark Mackay.
RE: [Dbmail] messagblks is full...
Hi Robert - If you're not, you should probably switch to using InnoDB tables; as from memory on linux the maximum MyISAM table size is 4G (FS limatation - can get around this by using XFS/etc). Then just go "alter table messageblks type=Innodb;" Keep an eye on "show table status like 'messageblks' \G " The comment section tells you how much InnoDB space you have left. Add more InnoDB data files as needed as the table fills up. /Mark > > > > I get a messageblks is full error in dbmail. > > How do I increase the messageblks size in mysql? > > Thanks, > > > > robert > > > > ___ > > 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: Re[2]: [Dbmail] Usage question...
We do something similar - 24 hour grace period. I'd like to make it 7 days for any message from deletion, not just say every 7 days. With the size of our database, I need to run the purge every day unfortunately - so the deleted timestamp would be more helpful. It would also allow us to differentiate services - so say corporate customers had a 30 day grace period but standard mailboxes had a 48 hour one. /Mark > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Jeff Brenton > Sent: Tuesday, 14 October 2003 8:43 a.m. > To: Jamieson Becker > Subject: Re[2]: [Dbmail] Usage question... > > > Also on deletions - it would be great to have a 'deleted timestamp' > > (forgive me if this is in the upcoming 2.0 release, I haven't > > checked the code). That way you can purge messages from the database > > once they've been *marked as deleted* for more than > configurable> days. A grace-period if you will where helpdesks can > > 'save the day' for unsavvy users. > > Um, this is already possible. > > On our sites, we run the dbmail-maint program each morning, deleting > messages that the system has tagged as deleted (status 003), then > marking user-deleted messages (status 002) as system deleted. This > gives at least 24 hours for a customer to decide that they need to > have a message undeleted. > > On a client's site, that task is only run on the first of the month, > giving 28+ days. Putting it on a weekly cron job would do the same, > with a 7 day minimum grace period. > > Implementing a by-timestamp would be simple enough, though. Just add a > timestamp field to the table. The last user-induced change would be > the deleting the message. You could then run a query to make changes > based upon status=002 and timestamp older than X days to change the > status to 003, then let the normal maintenance program take over at > that point for the final deletion. > > -- > Jeff Brenton > President, > Engineered Software Products, Inc > http://espi.com > Questionable web page: http://dididahdahdidit.com > > Liberalism grants you the freedom to advocate any idea*. > * Please see http://www.dididahdahdidit.com/except.php for a >current list of exceptions > > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail >
RE: [Dbmail] Usage question...
I totally second this idea! Also on deletions - it would be great to have a 'deleted timestamp' (forgive me if this is in the upcoming 2.0 release, I haven't checked the code). That way you can purge messages from the database once they've been *marked as deleted* for more than days. A grace-period if you will where helpdesks can 'save the day' for unsavvy users. /Mark > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Jamieson Becker > Sent: Monday, 13 October 2003 3:05 p.m. > To: dbmail@dbmail.org > Subject: Re: [Dbmail] Usage question... > > Doug, > > Perhaps the maintenance script could also be modified to allow > user-modifiable SQL statements. For example, > > DELETE FROM sometable WHERE deleted='1' AND TIMESTAMP<"%s" > > That way, it'd only delete mail that was older than a certain > date AND > deleted... > > (excuse my SQL, it's a second language..) > > Doug Stanley wrote: > > > Are any of you using the dbmail system at a larger > university? Or even > > a small university? I suggested using it here for our > computer science > > deptartment, but the big thing stopping us was that > professors often > > "accidentally" delete mail they didn't want to so we have > to go in and > > get their mail file from tape backups... > > > > I think dbmail would actually be better in this case cause > we could just > > change the mail deleted flag in the db...as long as we only run the > > maintanence script pretty rarely...anyways...I'm just > curious if anyone > > out there is actually using this at a university setting > where people > > do stupid things to their mail... > > > > Doug > > > > ___ > > 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] feature solicitation
N! Having the config info in the main database was the first method - but unless you provide a per-client identifier (so that when the pop/imap/smtp daemon connects you can give it a particular config); you can't differentiate services, etc. For example on some lower-spec'd servers I run fewer processes, others I run test cvs versions on alternate ports, etc -- this would be a real pain to provide for using database configurations. Maybe optional... If people really want it -- but please make sure either command line overrides and/or local on-disk configs remain! /Mark > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Doug Stanley > Sent: Saturday, 11 October 2003 12:32 p.m. > To: dbmail@dbmail.org > Subject: Re: [Dbmail] feature solicitation > > I agree! > Doug > > Matthew T. O'Connor wrote: > > Personally, I think all dbmail configuration information > should be stored in > > the database, the only exception being, bootstrap info > required to connect > > to the database. > > > > - Original Message - > > From: "Jesse Norell" <[EMAIL PROTECTED]> > > To: > > Sent: Friday, October 10, 2003 2:55 PM > > Subject: [Dbmail] feature solicitation > > > > > > > >>Hello, > >> > >> Would anyone be interested in customizable error messages for > >>dbmail bounces, etc.? Eg. have a directory /etc/dbmail/ with > >>some message templates that are used to build the actual errors > >>(mailbox full, no such user, etc.). Or maybe would be nicer to > >>have them in a table in the database (for sites that use multiple > >>dbmail boxes for the same message store). I'll log a feature > >>request as a reminder if it seems like a good idea. > >> > >>jn > >> > >>-- > >>Jesse Norell > >>jesse (at) kci.net > >> > >> > >>___ > >>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] Hierarctical Mail Folders
You can have many, many levels of nested folders using dbmail -- up to the limit of the column constraints for mailbox name (which you can alter as needed). Eg. INBOX INBOX/subfolderofinbox INBOX/subfolderofinbox/deeperfolder /Mark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Magnus Sundberg Sent: Thursday, 3 April 2003 7:54 p.m. To: dbmail@dbmail.org Subject: [Dbmail] Hierarctical Mail Folders Dear sirs, Is it possible to have hiearctical mail folders in dbmail? It does not look like it is possible by studying the database structure. The UW-imap daemon follows the file system layout carefully. It is therefore not possible to store mail in a folder containing subfolders. You can do this in Exchange. /Magnus ___ Dbmail mailing list Dbmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] Multiple copies of every email
Check the unique_id column of the database. We had the same problem and it was because the UIDLs were stuffed up.From memory i think we fixed it by doing the following query: update messages set unique_id=concat(message_idnr,'A',unix_timestamp()); i don't think it matters what the values are tho. It will mean anyone who has keep mail on server ticked will have to redownload all their messages; but i think it will fix the problem. It does beg the question - why isn't the message_idnr simply used as the UIDL reference anyway? Hope this helps though. /Mark - Original Message - From: "Jason Burfield" <> To: Sent: Friday, March 07, 2003 7:05 AM Subject: [Dbmail] Multiple copies of every email > I found a few things that were close to this in the archives...but no real > answers. > > I've got DBMail set up and running with Postfix. I am having a few glitches, > but the most annoying one at the moment is that mail is not deleted from the > tables in DBMail. > > Each time I check mail I get the same exact mail I already retrieved. > > I have the dbmail-maintenanace set up to run each night, however that does > not seem to delete any mail. > > I have also manually run dbmail-maintenance a few times with no luck there > either. > > Can anyone point me in a good direction here? > > Thanks much. > > -- Jason > > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail >
Re: [Dbmail] dbmail-maintenance segfaulting
I had similar problems, but I think it may have related to the number of
messages returned. I had something like 40,000 to purge, and I don't think
the query was returning.
>From memory, if I changed all but 100 customers to state=002 and then ran
Status: O
purge, it worked. But this was a real pain so I ended up writing a perl
program to do it.
#!/usr/bin/perl
use DBI;
$db_dbmailauth = DBI->connect($DBMAILAUTH_DBHOST, $DBMAILAUTH_DBUSER,
$DBMAILAUTH_DBPASS);
$db_dbmailstore = DBI->connect($DBMAILSTORE_DBHOST, $DBMAILSTORE_DBUSER,
$DBMAILSTORE_DBPASS);
print "Getting message list (status=003) \n";
my $query = $db_dbmailstore->prepare("select message_idnr from messages
where status=003 order by message_idnr limit 5");
$query->execute || print "error was ".$db_dbmailstore->errstr;
$query->bind_columns( undef, \($message_idnr) );
print "DELETING THE FOLLOWING MESSAGES\n";
while ($query->fetch) {
print "+ $message_idnr \n";
#print "- messageblks \n";
my $query2 = $db_dbmailstore->prepare("delete from messageblks where
message_idnr='$message_idnr' ");
$query2->execute || die "error was ".$db_dbmailstore->errstr;
#print "- message \n";
my $query2 = $db_dbmailstore->prepare("delete from messages where
message_idnr='$message_idnr' ");
$query2->execute || die "error was ".$db_dbmailstore->errstr;
##print "- message_headers \n";
#
#my $query2 = $db_dbmailstore->prepare("delete from message_headers
where message_idnr='$message_idnr' ");
# $query2->execute || die "error was ".$db_dbmailstore->errstr;
}
Crude but effective. Ignore mail headers, that¹s part of our webmail
interface we're working on.
/Mark
On 27/12/02 4:25 AM, "Eli Stair" <[EMAIL PROTECTED]> wrote:
> Starting today, dbmail-maintenance has started segfaulting when run. I
> manually
> run maintenance so I can check status and see output. Below is the command
> stdout as well as the info log from the maintenance run. This is the second
> segfault, first time I was only logging dbmail-maintenance at TRACE=2, this is
> now at 5.
>
> Im guessing this is somehow related to the problems I'm having with pop3d
> sig11ing
> when checking POP, but that's only a guess. The system is running MySQL
> 4.0.5b
> with INNODB tables, database is _extremely small_, > 1MB with only 3 accounts
> receiving mail.
>
> /eli
>
>
> # dbmail-maintenance -c -i -p -d
> *** dbmail-maintenance ***
> Opening connection to the database... Ok. Connected
> Deleting messages with DELETE status... Ok. [1] messages deleted.
> Setting DELETE status for deleted messages... Ok. [0] messages set for
> deletion.
> Now checking DBMAIL messageblocks integrity.. Segmentation fault
>
> ---/var/mail/info
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: ReadConfig(): starting
> procedure
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: ReadConfig(): found [DBMAIL]
> tag
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: ReadConfig(): no value
> specified for service item [].
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: ReadConfig(): item [host]
> value [localhost] added
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: ReadConfig(): item [user]
> value [dbmail] added
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: ReadConfig(): item [pass]
> value [{*}] added
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: ReadConfig(): item [db]
> value [dbmail] added
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: ReadConfig(): no value
> specified for service item [].
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: ReadConfig(): item
> [TRACE_LEVEL] value [5] added
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: ReadConfig(): service
> [DBMAIL] added
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: ReadConfig(): config for
> [DBMAIL] read, found [5] items
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: GetConfigValue(): searching
> value for config item [trace_level]
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: GetConfigValue(): found
> value [5]
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: GetConfigValue(): searching
> value for config item [host]
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: GetConfigValue(): found
> value [localhost]
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: GetConfigValue(): searching
> value for config item [db]
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: GetConfigValue(): found
> value [dbmail]
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: GetConfigValue(): searching
> value for config item [user]
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: GetConfigValue(): found
> value [dbmail]
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: GetConfigValue(): searching
> value for config item [pass]
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: GetConfigValue(): found
> value [mL$jX3Q7]
> Dec 26 11:14:45 tardis dbmail/maintenance[15496]: db_deleted_purge():
> executing query [SELECT message_idnr
Re: [Dbmail] Request for a modified dbmail-smtp
I'll see if Klas can update it for the current CVS version and post it later on today. Also take a look at mbox2dbmail-fast.c (I posted it to the list a while ago). It does what formail does I guess, but I did have a few problems with the unique ID numbers. I ended up after importing simply doing an sql update on the entire table. Something like: update messages set unique_id=concat(message_idnr,'A',''); Good for first import, but can screw up the UIDL entries for existing users. On 11/12/02 4:12 AM, "Jacques-Beaudoin" <[EMAIL PROTECTED]> wrote: > Thanks Mark > > Messages do go to mailbox 0. > > Your patch did not make it to the CVS(1.0) because thats what i use to > install > dbmail. > > One of the major task in installing DBmail is converting UW-imap users and > messages to DBMAIL. > > Im almost finish writing a simple 5 sections script on how to transfert > a UW-imap server to DBmail (With detail instruction for everyone) > > It would be nice if your dbmail-smtp patch make it to the CVS > so i could complete my script and postit on this board. >
Re: [Dbmail] Can i create imap folders with dbmail-smtp
Check to see if the folder exists first. I had a similar problem (but as I recall they all went to mailbox 0, not the inbox. Check the syslog). I wrote a patch at one point (not sure whether it made it into the CVS) that auto-created the mailbox if not present. I didn't know for sure if it would make it, so for my imports I wrote a wrapper in perl that looked up the user_idnr, then added a mailbox named '$FOLDER' for that user_idnr, then ran dbmail-smtp. /Mark On 10/12/02 12:20 PM, "Jacques-Beaudoin" <[EMAIL PROTECTED]> wrote: > I migrated all my inbox from uw-imap to dbmail with a script > and a line like > > cat $USER | /usr/bin/formail -s dbmail-smtp -m "INBOX" -u $USER > > Now i want to transfert all my imap folders with a line like > > cat $FOLDER | /usr/bin/formail -s dbmail-smtp -m "$FOLDER" -u $USER > > I figure that $FOLDER would be created by dbmail-smtp and all mail > would be sent into that $FOLDER. > > But mail are being sent to the INBOX. > > Is that normal > > Thanks > > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail > >
Re: [Dbmail] Sub-Folders
Mmm. One of our testing clients is Outlook and it seems to work fine. I know with Courier IMAP (which I played with about a year ago), everything was a subfolder of "INBOX." They reserved the root tree for Shared folders and stuff. The problem was Outlook express for one didn't every try to call "capabilities" so it didn't know this and always used to barf when trying to create folders. I made some hacks to change the namespace which solved that problem for me. Moving back to dbmail -- I think the way dbmail does it *is* compatible with outlook/express. I do seem to recall that there were some characters that were needed in the AcceptedMailboxChars array -- which may be especially relevant if you're trying to use any funny character sets or using punctuation, etc. Try with a few basic names and setting the debug level to 5. It may give you more of a clue. /Mark On 24/11/02 7:17 AM, "John Ruff" <[EMAIL PROTECTED]> wrote: > Thanks for clearing that up. I can create sub-folders in Outlook, but I > use Ximian Evolution (1.2.0) on Linux and cannot seem to figure out how > to have Evolution create sub-folders. Does know how to do this, or what > configuration to make to Evolution. > > -John > > On Sat, 2002-11-23 at 06:00, [EMAIL PROTECTED] wrote: > > Date: Sat, 23 Nov 2002 12:17:43 +1300 > Subject: Re: [Dbmail] Sub-Folders > From: Mark Mackay <[EMAIL PROTECTED]> > To: John Ruff <[EMAIL PROTECTED]>, > Dbmail Mailing list > Reply-To: dbmail@dbmail.org > > Check your mail client's mailbox delimiter settings. I think dbmail uses > the > following syntax: > > INBOX > INBOX/Sub folder of Inbox > Folder in root level > > Quickly looking through the source, it looks like dbmail doesn't support > the > "namespace" directive, which tells the mail client (from memory) what > the > root folder is and what the delimiter is. > > It probably defaults to "." or something. > > /Mark > > On 22/11/02 7:02 PM, "John Ruff" <[EMAIL PROTECTED]> wrote: > >> Will DBMail support sub-folder creation through IMAP? Or did I not >> configure something correctly, b/c I'm only able to create folders >> directly at the root (same level as INBOX). >> >> -Thanks > > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail > >
Re: [Dbmail] Sub-Folders
Check your mail client's mailbox delimiter settings. I think dbmail uses the following syntax: INBOX INBOX/Sub folder of Inbox Folder in root level Quickly looking through the source, it looks like dbmail doesn't support the "namespace" directive, which tells the mail client (from memory) what the root folder is and what the delimiter is. It probably defaults to "." or something. /Mark On 22/11/02 7:02 PM, "John Ruff" <[EMAIL PROTECTED]> wrote: > Will DBMail support sub-folder creation through IMAP? Or did I not > configure something correctly, b/c I'm only able to create folders > directly at the root (same level as INBOX). > > -Thanks
[Dbmail] FW: Config and master/slave db patch
One of our new programmers Klas has been working with us on some improvements to dbmail, but can't seem to post to the list yet (are new signups working?). Here's his post from a few days ago and the attachments: /Mark --- Hi, My name is Klas Handel and has just started working at Orcon Internet in New Zealand. Together with Mark Mackay I've done some updates to dbmail during the last week. 1. Added support for master/slave (write/read) db servers, only for MySQL though. 2. New configuration utility that reads parameters either from the Command line or a new configuration file (dbmail.config). Example: ./dbmail-pop3d --POP3D_BIND_PORT=109 I've merged the changes with the dbmail cvs version from October 30 (i.e. today). The attached files are the new files and a patch file (diff -u -r dbmail dbmail.patch > patch.file). Have a try and tell me what you think! I've only worked with dbmail for a couple of days so be nice... :) Cheers /Klas Handel [EMAIL PROTECTED] new-files.tar.gz Description: Binary data patch-dbmail.cvs-20021030.gz Description: Binary data
Re: [Dbmail] database connection parameters in dbmail.conf ?
We're just working with a CVS version from 2-3 days ago to apply two major changes (which may possibly need to be re-done if Eelco/Roel have made some major layout changes within) 1) Separate read and write databases - Send updates/inserts to a master MySQL host - All selects from a slave MySQL host (or the master if you're not running replication) 2) Config file and command line options - focussing primarily on db host assignment via a config file (so if the slave/master servers go down you don't have to recompile the source to change the hosts); and probably also the bind IP configs. I hired someone to take care of this (I'm too busy) so I'm not sure what shape it will take. The contract ends today, so hopefully will have something that we can post to the list Mon/Tue. The first point I think would be a really good thing to get into the 1.0 release (and primarily it's just a few find and replaces to call the relevant db read/write function); as this means that using MySQL replication you have a hot backup of your entire database server (I'm getting a bit nervous now that we have 15G in InnoDB tables). And also means you can load balance busy sites with multiple slave servers. I'll keep you posted. /Mark On 18/10/02 6:50 AM, "Chris Hilts" <[EMAIL PROTECTED]> wrote: >> Before I start a duplicate effort, has anyone been planning or >> implementing an external configuration mechanism for connecting to the >> database ? Perhaps expanding dbmail.conf to include these parameters >> would by feasable. > > I admit, it's been a LONG time since I had to change the dbmail > configuration. As my memory serves, the dbmail configuration is stored in > the database. Support to read a config file at run-time for the database > parameters will have to be done from scratch, I believe. > > Chris Hilts > [EMAIL PROTECTED] > > (Of course, all this will have changed since the last time I grabbed a > copy of CVS, and I'm probably completely wrong now) > > > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail > >
[Dbmail] Bug with Incredimail
I'm running an old cvs version so I hope this hasn't been fixed already: Incredimail has a bug? with its handling of POP3, which dbmail barfs at. It effectively goes RETR 1 DELE 1 RETR 2 DELE 2 RETR 3 DELE 3 RETR 4 DELE 4 DELE 1 - ERR No such message DELE 2 - ERR No such message DELE 3 - ERR too many failures go away Incredimail does a double-delete which dbmail doesn't like. I did a quick fix to handle this (updated MAX_ERRORS to 50) but this is obviously an issue for us. We're notifying incredimail, but the current version is already out there. This can probably be handled a few ways: 1) allow double deletion without an err 2) don't disconnect with err if already logged in (eg. Not in user/pass query state) 3) don't increment error count for this particular error message /Mark
[Dbmail] FW: POP3D Problems
Hi there -
There seems to be an error how the pop client handles the "AUTH" command.
The "AUTH" command is not in RFC1081 (as indicated at the top of the pop3.c
file) but added in RFC1734.
The dbmail source seems to use the results code from the IMAPD AUTHORISE
command.
To fix this change in pop3.c
from
-
case POP3_AUTH:
{
if (state!=AUTHORIZATION)
return pop3_error(stream,"-ERR wrong command mode, sir\r\n");
fprintf ((FILE *)stream, "+OK List of supported
mechanisms\r\n.\r\n");
return 1;
}
-
to
---
case POP3_AUTH:
{
if (state!=AUTHORIZATION)
return pop3_error(stream,"-ERR wrong command mode, sir\r\n");
fprintf ((FILE *)stream, "-ERR AUTH not supported\r\n");
return 1;
}
---
/Mark
Re: [Dbmail] Quick fix for the imap vs pop read state
Ah, no. different issue it seems. When reading via POP with keep mail on server, if you then viewed the mailbox with IMAP all the messages appeared unread. I used POP at my main PC and webmail/imap from remote -- to view new messages before I download them at my PC. This was a real pain to deal with so I produced the hack... Don't use Imp, so am not sure about your issue sorry. /Mark On 1/10/02 2:15 AM, "Kostas Georgakopoulos" <[EMAIL PROTECTED]> wrote: > I don't know if you are reffering to my post, but after your quick fix i still > have the same problem: > > (i read my mail with imp) > > A new mail arrives, i read it, i MARK it as read, and after refreshing i get > the same pop up that says: you have 1 new message! > > -∞Ú¯ÈÎfi ª‹Ó˘Ì·- > ∞fi: Mark Mackay <[EMAIL PROTECTED]> > £€Ì·: [Dbmail] Quick fix for the imap vs pop read state > ∏ÌÂÚÔÌËÓ›·: Sat, 28 Sep 2002 18:59:42 +1200 > > As mentioned in a previous post, I found that if you POPed a mailbox and > kept messages on server, any subsequent imap sessions showed the message > state as unread. > > Here's a quick fix (sorry not a patchfile): > > In dbmysql, function db_update_pop replace: > > snprintf (query,DEF_QUERYSIZE, > "UPDATE messages set status=%llu WHERE message_idnr=%llu AND > status<002", > ((struct message *)tmpelement->data)->virtual_messagestatus, > ((struct message *)tmpelement->data)->realmessageid); > > With > > snprintf (query,DEF_QUERYSIZE, > "UPDATE messages set seen_flag=if(%llu=1, 1, seen_flag), > status=%llu WHERE message_idnr=%llu AND status<002", > ((struct message *)tmpelement->data)->virtual_messagestatus, > ((struct message *)tmpelement->data)->virtual_messagestatus, > ((struct message *)tmpelement->data)->realmessageid); > > > Works for me and should keep the imap state intact if POP doesn't change it, > but I don't think that really should be an issue anyway. The 'better' way to > do it is probably to just use the state column to determine read/unread in > the Imap client, but I didn't want do go and hack away at that if the rfc > called for something else... > > /Mark > > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > __ > > http://mobile.pathfinder.gr - Pathfinder Mobile logos & Ringtones! > http://www.pathfinder.gr - ¢ˆÚÂ¿Ó mail ·fi ÙÔÓ Pathfinder! > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail > >
[Dbmail] Quick fix for the imap vs pop read state
As mentioned in a previous post, I found that if you POPed a mailbox and kept messages on server, any subsequent imap sessions showed the message state as unread. Here's a quick fix (sorry not a patchfile): In dbmysql, function db_update_pop replace: snprintf (query,DEF_QUERYSIZE, "UPDATE messages set status=%llu WHERE message_idnr=%llu AND status<002", ((struct message *)tmpelement->data)->virtual_messagestatus, ((struct message *)tmpelement->data)->realmessageid); With snprintf (query,DEF_QUERYSIZE, "UPDATE messages set seen_flag=if(%llu=1, 1, seen_flag), status=%llu WHERE message_idnr=%llu AND status<002", ((struct message *)tmpelement->data)->virtual_messagestatus, ((struct message *)tmpelement->data)->virtual_messagestatus, ((struct message *)tmpelement->data)->realmessageid); Works for me and should keep the imap state intact if POP doesn't change it, but I don't think that really should be an issue anyway. The 'better' way to do it is probably to just use the state column to determine read/unread in the Imap client, but I didn't want do go and hack away at that if the rfc called for something else... /Mark
Re: [Dbmail] Pop locks
Definitely AGAINST provided it doesn't cause problems... Can't see any to date. Doesn't seem to screw up the second session, so all good! Pop locks cause so many hassles for our helpdesk when people get cut off mid session or the like. Yay for not needing to worry about it with dbmail! /Mark
[Dbmail] Pop locks
Just checking so I can brief our helpdesk, I'm assuming dbmail does not lock the pop sessions to prohibit the same user from relogging in while another session is active. >From my tests, it looks like any destructive change (eg deleting a message Status: O is honoured immediately upon a client exiting...) Is this right? /Mark
[Dbmail] Additional wishlist
+ Purging deleted messages based on a time different: Currently status is set to 03 meaning deleted. This allows the dbmail-maintenance script to clear all deleted messages each night or whenever the script is run. If this field could be changed to a timestamp (or treated as a timestamp, where values > 1000 = delete time, 0-2 = normal flags). /Mark
Re: [Dbmail] Imapd is slow as hell
On 25/9/02 4:21 AM, "Roel Rozendaal" <[EMAIL PROTECTED]> wrote: > The basic layout for dbmail was based upon pop3 but we are interested in > better schemes for imap. One of the main goals right now is to have the > fastest imap server around. Better table layouts would mean a great > improvement; just don't know how much time will be involved in > implementing those. > Things to do right now in my opinion are to fix annoying bugs and update > the package with the several patches we have to release a STABLE 1.0 > version asap. One annoying bug I've notices is the mis-match between the status and flag_read fields. When you use POP and keep messages on server, all the messages are set to be read by setting status to 02. However, Imap only seems to look at the flag_read field, which is not updated by the POP session. Suggest either syncing the two fields, or at least getting rid of flag_read. /Mark
[Dbmail] Patch to fix IMAP ^M command issue
I encountered an issue where when moving messages between IMAP accounts (not
mailboxes), that I could not move more than 1 at a time, and would get an
error ("Bad tag") until I re-logged in. I finally traced through the code
and found that commands were being processed like:
COMMAND: [0018Z APPEND "INBOX" .]
COMMAND: []
Basically the [] was actually the result of clarify_data processing "^M".
The following hack seems to do the trick, but it looks like clarify_data
tried to do it (but obviously failed). The problem occurred on Outlook
Express for PC and Microsoft Entourage for Macintosh, so I'm guessing it's
global (at least for M$ software).
/Mark
--- dbmail/imap4.c 2002-06-05 20:46:13.0 +1200
+++ dbmail.mine/imap4.c 2002-09-13 12:41:12.0 +1200
@@ -122,6 +122,7 @@
char *tag = NULL,*cpy,**args,*command;
int i,done,result;
int nfaultyresponses;
+ int endidx;
imap_userdata_t *ud = ci->userData;
mailbox_t newmailbox;
@@ -168,6 +169,8 @@
{
/* we have had just about it with this user */
sleep(2); /* avoid DOS attacks */
+
+ trace(COMMAND_SHOW_LEVEL,"FATAL: To many fault responses\n");
fprintf(ci->tx,"* BYE [TRY RFC]\r\n");
done = 1;
break;
@@ -234,9 +237,18 @@
return 1;
}
+
+ /* Hack by Mark Mackay to get lines of '^M' only to be ignored...
*/
+ endidx = strlen(line)-1;
+ if (line >= 0 && endidx ==1 && (line[endidx] == '\n'
||line[endidx] == '\r'))
+ {
+ continue;
+ }
+
/* clarify data a little */
clarify_data(line);
+
trace(COMMAND_SHOW_LEVEL,"COMMAND: [%s]\n",line);
if (!(*line))
[Dbmail] mbox2dbmail-fast.c
This is a modification to uni-one-converter which process a single mailbox
using the database hooks directly, rather than via dbmail-smtp.
Usage: ./mbox2dbmail-fast /path/to/mboxfile useexisting|create
The "mboxfile" will be the value it references against column 'userid' in
the database. Useexisting bails if it can't find the user, create does the
same as uni-one-converter and adds a new user with passwd 'default' [not
tested btw].
Has a bit of a bug which means you can't just call as "mbox2dbmail-fast
mboxfile useexisting" : I'm not a c programmer, but the path resolve
function kills the first character when returning the username. Workaround
"mbox2-dbmail-fast ./mboxfile useexisting"
/Mark
mbox2dbmail-fast.c
Description: application/applefile
/*
* this program traverses a directory tree and executes
* dbmail conversion on each file.
*/
#include
#include
#include
#include
#include
#include
#include
#include "db.h"
#include "auth.h"
#include "dbmailtypes.h"
#include "debug.h"
#include
#define MAX_LINESIZE 1024
#define UID_SIZE 70
const char *mbox_delimiter_pattern = "^From .* ";
char blk[READ_BLOCK_SIZE + MAX_LINESIZE + 1];
/* syslog */
#define PNAME "dbmail/mbox2dbmail-fast"
char *getusername (char *path);
int processmbox (char *path, char *flags);
int process_mboxfile(char *file, u64_t userid);
int main (int argc, char* argv[])
{
int result;
if (argc < 3)
{
printf ("Usage: mbox2dbmail-fast [create|useexisting] \n");
return -1;
}
openlog(PNAME, LOG_PID, LOG_MAIL); /* open connection to syslog */
configure_debug(TRACE_ERROR, 1, 0);
/* open dbase connections */
if (db_connect() != 0 || auth_connect() != 0)
{
printf("Error opening dbase connections\n");
return -1;
}
result = processmbox(argv[1], argv[2]);
return result;
}
int processmbox (char *path, char *flags)
{
char *username;
int n;
u64_t userid;
printf ("file %s\n",path);
username = getusername(path);
printf ("username %s\n", username);
if ( strcmp(flags, "create") == 0) {
printf("creating user...");
userid = auth_adduser(username, "default", "", "0", "10M");
} else {
printf("looking up existing user");
userid = auth_user_exists(username);
}
if (userid != -1 && userid != 0)
{
printf("Ok id [%llu]\n", userid);
fflush(stdout);
n = process_mboxfile(path, userid);
if (n != 0)
printf("Warning: error converting mailbox\n");
else
printf ("\ndone :)\n");
} else {
printf("user already exists (create) or user not found (useexisting).
Skipping\n");
}
return 0;
}
char *getusername (char *path)
{
int i;
char *tmp;
i = strlen (path);
tmp = path+i;
while ( (tmp!=path) && (*tmp!='/'))
tmp--;
return tmp+1;
}
int process_mboxfile(char *file, u64_t userid)
{
regex_t preg;
int result;
FILE *infile;
int in_msg, header_passed=0;
char newunique[UID_SIZE];
unsigned cnt,len,newlines;
u64_t msgid=0, size;
char saved;
int msgcount = 0;
if ((result = regcomp(&preg, mbox_delimiter_pattern, REG_NOSUB)) != 0)
{
trace(TRACE_ERROR,"Regex compilation failed.");
return -1;
}
if ( (infile = fopen(file, "r")) == 0)
{
trace(TRACE_ERROR,"Could not open file [%s]", infile);
return -1;
}
in_msg = 0;
cnt = 0;
size = 0;
newlines = 0;
while (!feof(infile) && !ferror(infile))
{
if (fgets(&blk[cnt], MAX_LINESIZE, infile) == 0)
break;
/* check if this is an mbox delimiter */
if (regexec(&preg, &blk[cnt], 0, NULL, 0) == 0)
{
if (!in_msg)
in_msg = 1; /* ok start of a new msg */
else
{
/* update & end message */
msgcount++;
printf("\rConverting mailbox :: %d", msgcount);
db_insert_message_block(blk, cnt, msgid);
snprintf(newunique, UID_SIZE, "%lluA%lu", userid, time(NULL));
db_update_message(msgid, newunique, size+cnt, size+cnt+newlines);
trace(TRACE_ERROR, "message [%llu] inserted, [%u] bytes", msgid,
size+cnt);
}
/* start new message */
msgid = db_insert_message(userid, 0, 0);
header_passed = 0;
cnt = 0;
size = 0;
newlines = 0;
}
else
{
newlines++;
if (header_passed == 0)
{
/* we're still reading the header */
len = strlen(&blk[cnt]);
if (strcmp(&blk[cnt], "\n") == 0)
{
db_insert_message_block(blk, cnt+len, msgid);
header_passed = 1;
size += (cnt+len);
cnt = 0;
}
[Dbmail] Useful SQL Query
Bit of a nasty hack query which I thought may be of use as a single-query
way of getting the From: and Subject: lines out of a message list.
Thought I'd post it in case it was of use to members on the list. If there
are any better ways of doing this -- please let me know!
/Mark
--
select *,
left(substring(left(substring(messageblks.messageblk from locate('From',
messageblks.messageblk)), locate('\n', substring(messageblks.messageblk from
locate('From', messageblks.messageblk from 6),30) as mailfrom,
left(substring(left(substring(messageblks.messageblk from
locate('Subject', messageblks.messageblk)), locate('\n',
substring(messageblks.messageblk from locate('Subject',
messageblks.messageblk from 9),50) as subject,
from messages, messageblks
where
messages.message_idnr=messageblks.message_idnr
and messages.mailbox_idnr='-INSERT MAILBOX NUMBER HERE -'
group by messageblks.message_idnr
order by messages.internal_date desc
[Dbmail] Dbmail development flow
Have been doing some thinking over the last week about how dbmail is progressing. It's pretty obvious that Roel / Eelco are quite busy at the moment, which means that patches aren't being applied as wished and it's hard to get good feedback from them on plans/etc. Managing wishlists/bugs via the mailing list is also a bit difficult, which made me thing about setting up a web page to manage this (as was Roel's suggestion). However rather than reinventing the wheel: What about setting up a project at sourceforge.net? - feature/request lists - bug reports - patch repository - discussion forums - file downloads/cvs if wanted This would help consolidate important issues into one location, allow patches to be posted by one person (and then ported to the other database types by someone else), and then let Roel/Eelco integrate them into the CVS. Prior to this being done, the patches could be downloaded and applied one-by-one. Thoughts? I'm happy to set the project up, but I figured this is probably best for Roel/Eelco to do so they know all the passwords and the like. However if you're too busy, just shout out and I'll do it and pass you the details. /Mark PS. Roel/Eelco - I know you're busy, but I'm about to put 30,000 mailboxes onto this system because I think it's great! It would be really good - especially if the sf.net idea goes through - if you could at least post your thoughts from a project-owner perspective on the todo list ideas. I'm more than happy to spend time hacking code and coming up with ideas, but without knowing whether you guys are like the idea and are going to adopt it, it's really demoralizing just submitting code and having nothing happen. There seems to be a few people on the list who are really happy to do stuff for the project, and the system has so much potential. However there seem to be a number of requests (summary fields, duplicate username support, ...) which really need to be integrated into the core code concept. It would be much better if a fork never eventuated... Let us do the work, just keep us informed that we're working in the right direction and you're happy to integrate the code. :o)
[Dbmail] Status update please
Can we please get an update on the progress of features/wishlists for dbmail? From memory the following are outstanding: - header summary: ability to have common headers saved (probably in the messages table) so that webmail/management front-ends don't need to scan the messageblks table to get this info. >> Idea: If this isn't desired in the messages table, what about adding another table called messagesummary which stores messageid, from, subject, date, etc. This could be used filled when the message is delivered, and then only used by management and webmail utils. That would provide no extra storage needs in the message table, and only the maintenance and deliver code would need to be adjusted. A config item could even allow people to turn this off if they didn't use it... - indexes on the default sql structures :: a number have been submitted, but these don't seem to have made it to the CVS. Not good for newbies who then observe bad performance until they ask for help ... seem to have forgotten most of them :o) ... Let's fill in the blanks people, so we can tell what's important to people and finally get to 1.0! Add a few of mine: - Ability to deliver to a userid :: (rather than email address/username), so that duplicate "marks" can be referenced in the delivery agent. - Two-key lookups for the userid :: duplicate usernames again - date-deleted key :: allow the maintenance program to purge deleted messages from the database after a user-selectable period (eg. Allow messages to be 'restored' by helpdesk staff if within the 7 day non-purged period). - Import from user's mbox and IMAP folders :: I for example have 12 imap mailboxes, which I had to hack mbox2dbmail to support mbox delivery, write a wrapper scan my homedir. This worked but as it used dbmail-smtp it was really slow. Uni-one converter needs to be updated to support this functionality IMHO. - [low prio] Export to mbox format :: got to be a good selling point for newbies who want to try but need an exit clause [I'm happy to present patches for this functionality, but am getting a bit disheartened as so far none of my patches seem to be being adopted or even being discussed]. /Mark
Re: [Dbmail] multiple dbmail server setup
Thanks for the reply Roel - Having on-disk config files shouldn't be a problem for most people. You lose the option of updating the config via database, but how often do you need to change it.. If keeping with databased configs, Jeff's point about the clientid matches what I was thinking of, and sounds good. > Mark: i would opt for different tables for different service providers; > maybe we could implement the table to authenticate users to as a > config-option. I don't think it necessarily has to be separate tables, but don't have a problem with that. Separate tables would mean having to have multiple database connections open from all the dbmail daemons. I currently use sendmail/virtusertable to result in a delivery username which I use with dbmail-smtp -u X. There's nothing stopping me or anyone else returning a numeric userid from the virtusertable lookup, and then using dbmail-smtp -uid as the delivery mechanism. Provided the pop/imap daemons could be configured with an optional second key (such as realm/domain/etc), then the lookups could key of both the userid and this key and return the unique clientid value needed for the mailbox/messages tables. /Mark PS. I notice the patches I submitted to correct the problems if you tried to split the authentication and message databases off haven't been applied. Is this or another appropriate fix likely to happen sometime soon?
Re: [Dbmail] multiple dbmail server setup
I has similar problems when investigating this earlier on, and even more so
when I had hoped to have multiple pop3/imap deaemons on the same machines
listing for different ISPs.
My ultimate plan is to different pop3/imap daemons consult either a
different user database, or a single userdatabase but with 2 keys (eg.
Username and ISP/Company code => thereby allowing duplicate usernames. As
dbmail for the most part refers to mail messages and mailboxes by a numeric
ID, you could have a mark/ISPA and mark/ISPB which didn't need to log in by
realm ("[EMAIL PROTECTED]" / "[EMAIL PROTECTED]"). The differention for me
would be IP
address:
Therefore I need to be able to have POP3/IMAP listening on say
10.0.0.1 - doing a lookup from dbmail users with a userid of "mark" and say
"virtualcode" of "ISPA" ('virtualcode' being a new column in the dbmail
users table).
10.0.0.2 - could do a lookup from dbmail users with userid of "mark" and
virtualcode="ISPB"
No-one commented back on whether people liked this idea or not, so I'd
welcome comments now that dbmail is starting to be used in more production
environments and the list is getting bigger.
The current dbmail config database makes this a bit harder, and I'd been
thinking of two ways this could be implemented:
1) On-disk config (start up dbmail with dbmail-pop3d -f
/etc/dbmail/ispa.conf and dbmail-pop3d -f /etc/dbmail/ispb.conf).
2) Per-client database identifier:
dbmail-pop3d -clientid ISPA
dbmail-pop3d -clientid ISPB
The second would allow configs to be managed by database still, but make
them selectable. The config lookup query could be modified to simply do a
"select ... From config where clientid=''". If the clientid was not
specified in the command line of the client, let it default to "DEFAULT".
What do people think? (... *please* comment). I'd really love dbmail to
have good support for duplicate usernames without the requirement for @realm
logins stored in the database. It's inherent design makes this easy, but it
always seems to be hard to get feedback supporting this idea, even when I
offer to write patches. Eelco.. I'd love to hear from you regarding your
support/rejection of this concept...
/Mark
On 5/9/02 10:40 AM, "Jesse Norell" <[EMAIL PROTECTED]> wrote:
>
> Hello,
>
> We're working on a multi-server setup with a single
> database server, and multiple postfix/dbmail servers
> connecting to it. It seems the current (database-stored)
> configuration setup (ie. config table) does not handle this,
> as most of the settings may/will be server-specific. The
> most prominant example would be POP3D_BIND_IP and IMAPD_BIND_IP,
> which if not '*' will be different on every machine, but most
> all the variables really should be server-specific.
>
> I'm pretty sure I can get around this by setting up nearly
> identical machines (including user/group names, etc.), and
> bind to '*', so it's not a "showstopper" problem, but is
> there a way to handle this currently, or is this something
> that needs addressed? If the latter, a few thoughts on the
> design - there does need to be one central config source for
> the entire system, that each dbmail server will consult (ie.
> in the database), and then a way to have server-specific
> values. One way would be to have server-specific values
> saved in a local config file (eg. dbmail.conf) - alternately
> could add a field to the config table to specify which server
> the config setting applies to (global if empty, otherwise it
> matches the server's hostname or ip addr).
>
> Or, did I just miss something in how I have this setup?
>
> Thanks,
> Jesse
>
>
>
> --
> Jesse Norell
> [EMAIL PROTECTED]
> ___
> Dbmail mailing list
> Dbmail@dbmail.org
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] Getting messages back out of dbmail
Try mounting the dbmail and non-dbmail servers via iMap. Then just drag-and-drop them between each other. Works for smaller numbers, but I've had problems when the message count got above 1000 or so, as the buffer length for the commands in dbmail seemed to be to small and was truncating the message list. On 22/8/02 7:15 AM, "Chris Hilts" <[EMAIL PROTECTED]> wrote: > I've been trying to get some messages back out of dbmail for 1 account > which is moving to another machine. (Not running dbmail). I've been using > fetchmail to do it, but for some reason it's not working at all, and I > think I've probably generated about a billion bounce messages to the admin > of this list by now. > > Is there a quick n easy way to extract the messages from an imap folder in > dbmail, dumping it into an mbox-type file? > > Thanks in advance, > > Chris Hilts > [EMAIL PROTECTED] > > > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] Sendmail
>> I've installed dbmail from the latest CVS and it is running fine as an
>> IMAP-Server.
>>
>> But what I not understand is how to configure sendmail to work with dbmail.
The way we've did it for testing, was to use procmail and pipe it through
the dbmail-smtp program. However procmail seems to add an additional line at
the top, which screwed up date/header recognition -- so I had to write a
small wrapper which simply discarded the first line of the pipe.
/etc/procmailrc:
SHELL=/bin/sh
:0c
|/path/to/dbmail-wrapper.pl $LOGNAME
$LOGNAME got filled with the username being delivered to:
/path/to/dbmail-wrapper.pl:
(crude but did the job for testing...)
#!/usr/bin/perl
$mailbox=$ARGV[0];
open(PIPE, "|/path/to/dbmail-smtp -u $mailbox");
My $firstline=0;
While () {
if ($firstline eq 0) {
$firstline = 1;
next;
}
Print PIPE $_;
}
Close PIPE;
It's a bit ugly, but allowed me to test while keeping local delivery on my
main mailserver, and also using conditions in procmail you can selectively
do it for certain users... (match $LOGNAME to be the test username)
/Mark
Re: [Dbmail] dbmail-imap extremely slow??
I found that the index on the tables weren't set up efficiently for mysql (and I think postgresql too). I did the following, which made a *very* substantial speed gain: mysql> alter table messages add index(mailbox_idnr); mysql> alter table messages add index(unique_id); mysql> alter table messages add index(status); mysql> alter table messageblks add index(mailbox_idnr); mysql> alter table mailboxes add index(owner_idnr); mysql> alter table mailboxes add index(name); mysql> alter table mailboxes add index(permission); mysql> alter table users add index(userid); I got these indexes from looking at the queries in the source code. I'm no mysql guru, so there are probably more efficient indexes that could be applied. However prior to doing these queries, I was spiking to a load of about 4 on my dual PIII database with only about 4-5 concurrent lan-based imap reads on large mailboxes (admittedly with about 12G of messages...). Afterwards, the database server load dropped about .2. (The pop/imap server, similar spec still had a hernia, but that's due I think to the amount of concurrent rapid reads I was doing...) Hope this helps -- and if it does, perhaps post back to the list so these indexes (or similar) can be added to the CVS. /Mark On 16/8/02 5:18 AM, "Johan Ekenberg" <[EMAIL PROTECTED]> wrote: > Hi! > > I just installed dbmail. When using it through IMAP from a webmail application > it's extremely slow! It's much slower than UW-IMAPD which is also running on > the server (on a different port). I thought dbmail would speed things up? > > Could I be doing something wrong or is dbmail-imap really this slow? On a > larger mailbox ~400 mails it's almost completely useless. > > Thanks for your help, > /Johan Ekenberg > > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail
[Dbmail] Patches for separated database and auto inbox creation
I encountered a problem with the code when trying to split the
authentication and message store databases. Some of the functions used the
wrong database hook, as for the developers the tables all sit in one table.
The patch below seemed to address it for me, but I'd also made a patch which
auto-created an inbox for a user when a new mail message was received --
which is also inline. It's only in mysql, as I don't know the postgres
hooks.
It's from the CVS about 3 weeks ago, but I still hope it's useful. I'd
really appreciate it if the database fixup could be applied to the CVS as
it's preventing me upgrading my local version :-)
/Mark
diff -r -u dbmail.unmodified/db.h dbmail.mine/db.h
--- dbmail.unmodified/db.h Wed Jun 12 03:43:43 2002
+++ dbmail.mine/db.hFri Jul 12 19:58:49 2002
@@ -15,6 +15,7 @@
#include "memblock.h"
#define DEF_QUERYSIZE 1024
+#define AUTH_QUERY_SIZE 1024
#define DUMP_BUF_SIZE 1024
#define ICHECK_RESULTSETSIZE 100
@@ -101,6 +102,7 @@
int db_connect();
int db_disconnect();
int db_query (const char *thequery);
+int __auth_query (const char *thequery);
char *db_get_config_item (char *item, int type);
int db_clear_config();
diff -r -u dbmail.unmodified/maintenance.c dbmail.mine/maintenance.c
--- dbmail.unmodified/maintenance.c Wed Jun 12 03:43:43 2002
+++ dbmail.mine/maintenance.c Fri Jul 12 20:00:58 2002
@@ -117,9 +117,19 @@
return 0;
}
- printf ("Opening connection to the database... ");
+ printf ("Opening connection to the user database... ");
if (db_connect()==-1)
+{
+ printf ("Failed. An error occured. Please check log.\n");
+ return -1;
+}
+
+ printf ("Ok. Connected\n");
+
+ printf ("Opening connection to the message database... ");
+
+ if (auth_connect()==-1)
{
printf ("Failed. An error occured. Please check log.\n");
return -1;
diff -r -u dbmail.unmodified/mbox2dbmail.c dbmail.mine/mbox2dbmail.c
--- dbmail.unmodified/mbox2dbmail.c Tue May 28 23:18:12 2002
+++ dbmail.mine/mbox2dbmail.c Sat Jul 27 16:22:30 2002
@@ -15,7 +15,8 @@
#define MAX_LINESIZE 1024
#define SMTP_INJECTOR "./dbmail-smtp -u "
-const char *mbox_delimiter_pattern = "^From [EMAIL PROTECTED] ";
+//const char *mbox_delimiter_pattern = "^From [EMAIL PROTECTED] ";
+const char *mbox_delimiter_pattern = "^From .*";
int main(int argc, char *argv[])
{
diff -r -u dbmail.unmodified/mysql/dbauthmysql.c
dbmail.mine/mysql/dbauthmysql.c
--- dbmail.unmodified/mysql/dbauthmysql.c Wed Jun 5 04:04:53 2002
+++ dbmail.mine/mysql/dbauthmysql.c Fri Jul 12 19:58:40 2002
@@ -18,13 +18,13 @@
#include "../config.h"
#include
-#define AUTH_QUERY_SIZE 1024
-
/*
* var's for dbase connection/query
*/
char __auth_query_data[AUTH_QUERY_SIZE];
MYSQL __auth_conn;
+extern MYSQL conn;
+extern char *query;
MYSQL_RES *__auth_res;
MYSQL_ROW __auth_row;
@@ -511,13 +511,13 @@
useridnr = mysql_insert_id(&__auth_conn);
/* creating query for adding mailbox */
- snprintf (__auth_query_data, AUTH_QUERY_SIZE,"INSERT INTO mailboxes
(owner_idnr, name) VALUES (%llu,'INBOX')",
+ snprintf (query, AUTH_QUERY_SIZE,"INSERT INTO mailboxes (owner_idnr,
name) VALUES (%llu,'INBOX')",
useridnr);
trace (TRACE_DEBUG,"auth_adduser(): executing query for mailbox");
- if (__auth_query(__auth_query_data))
+ if (db_query(query))
{
trace (TRACE_ERROR,"auth_adduser(): query failed for adding
mailbox");
return -1;
diff -r -u dbmail.unmodified/mysql/dbmysql.c dbmail.mine/mysql/dbmysql.c
--- dbmail.unmodified/mysql/dbmysql.c Wed Jul 10 20:00:59 2002
+++ dbmail.mine/mysql/dbmysql.c Sat Jul 27 14:41:04 2002
@@ -20,6 +20,8 @@
#include "../auth.h"
+extern char __auth_query_data[AUTH_QUERY_SIZE];
+extern MYSQL __auth_conn;
MYSQL conn;
MYSQL_RES *res;
MYSQL_RES *checkres;
@@ -450,10 +452,38 @@
if (mysql_num_rows(res)<1)
{
- trace (TRACE_DEBUG,"db_get_mailboxid(): user has no mailbox named
[%s]", mailbox);
+ trace(TRACE_ERROR,"db_get_mailboxid(): user has no mailbox named [%s]
- Creating", mailbox);
mysql_free_result(res);
+
+ // create mailbox
+ db_createmailbox(mailbox, useridnr);
+
+ snprintf (query, DEF_QUERYSIZE,"SELECT mailbox_idnr FROM mailboxes
WHERE "
+ "name=\'%s\' AND owner_idnr=%llu",
+ mailbox, useridnr);
+
+ // double check to see if mailbox exists now
+ if (db_query(query)==-1)
+{
+ trace(TRACE_ERROR, "db_get_mailboxid(): query failed");
+ return 0;
+}
+
+if ((res = mysql_store_result(&conn)) == NULL)
+ {
+ trace(TRACE_ERROR,"db_get_mailboxid(): mysql_store_result
failed: %s",mysql_error(&conn));
+ return 0;
+ }
+
+if (mysql_num_rows(res)<1)
+ {
+ trace (TRACE_DEBUG,"db_get_mailboxid(): user has no mailbox
named [%s]. Couldn't create.
Re: [Dbmail] Postgresql remote connections
I just did a make dist-clean, then compiled it and it seemed to fix it. But I'd definitely did recompile it the before emailing the list (2-3 times), so dunno why it didn't work. Oh well -- working now. Thanks for the email replies. /Mark On 10/8/02 8:39 PM, "Eelco van Beek - IC&S" <[EMAIL PROTECTED]> wrote: > Hi Mark, > > Did you recompile after changeing the config.h? > It looks like your dbmail is trying to connect through unix sockets in > stead of connecting through TCP/IP. > > Eelco > > Mark Mackay heeft op zaterdag, 10 aug 2002 om 05:01 (Europe/Amsterdam) > het volgende geschreven: > >> Hi there. I just hit the table limit of mysql (4G) so thought I'd try >> postgresql first and see if it performs any better, etc. However I >> can't >> seem to get it going. >> >> I've got two servers. >>1) Dedicated postgresql >>2) POP/IMAP Toaster >> >> I can connect to the database using the psql command line utility from >> the >> toaster box, no problems. I've set up a simple trusted subnet, which >> doesn't >> require md5/ident authentication or anything. >> >>> pgsql -h dbmail-database -U dbmail dbmail >>> >> >> However when I try using dbmail, it simply says can't connect see log. >> >>> Aug 10 15:00:07 dbmail-toaster1 dbmail/adduser[7409]: auth_connect(): >> PQconnectdb failed: could not connect to server: No such file or >> directory ^IIs >> the server running locally and accepting ^Iconnections on Unix domain >> socket >> "/tmp/.s.PGSQL.5432"? >> >> I've changed the config.h to the relevant host, and can tcpdump and see >> connections going there. I >> >> I'm really new to postgres and don't know where to start looking >> (though it >> looks like a dbmail problem). I'd appreciate any help from anyone who >> can >> point me in the right direction. >> >> >> Thanks in advance, >> Mark. >> >> >> PS. I've also got dbmail running with Mysql/Innodb and thought I'd try >> postgres for comparison (debian ships with postgres, meaning I >> wouldn't have >> to worry about the upgrading the database software manually...). >> >> Is there anyone who's tried both who can give recommendations? >> >> >> >> >> ___ >> Dbmail mailing list >> Dbmail@dbmail.org >> https://mailman.fastxs.nl/mailman/listinfo/dbmail >> > __ > E.J.A. van Beek > ICT Manager > IC&S > T: +31 30 2322878 > F: +31 30 2322305 > > PGP-key: > www.ic-s.nl/keys/eelco.txt > > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail . Mark Mackay, Network Coordinator, Orcon Internet. --> DDI: (09) 480-9399 --> mobile: (021) 622 379 --> email: [EMAIL PROTECTED]
[Dbmail] Postgresql remove connections
Hi there. I just hit the table limit of mysql (4G) so thought I'd try postgresql first and see if it performs any better, etc. However I can't seem to get it going. I've got two servers. 1) Dedicated postgresql 2) POP/IMAP Toaster I can connect to the database using the psql command line utility from the toaster box, no problems. I've set up a simple trusted subnet, which doesn't require md5/ident authentication or anything. > pgsql -h dbmail-database -U dbmail dbmail > However when I try using dbmail, it simply says can't connect see log. > Aug 10 15:00:07 dbmail-toaster1 dbmail/adduser[7409]: auth_connect(): PQconnectdb failed: could not connect to server: No such file or directory ^IIs the server running locally and accepting ^Iconnections on Unix domain socket "/tmp/.s.PGSQL.5432"? I've changed the config.h to the relevant host, and can tcpdump and see connections going there. I I'm really new to postgres and don't know where to start looking (though it looks like a dbmail problem). I'd appreciate any help from anyone who can point me in the right direction. Thanks in advance, Mark. PS. I've also got dbmail running with Mysql/Innodb and thought I'd try postgres for comparison (debian ships with postgres, meaning I wouldn't have to worry about the upgrading the database software manually...). Is there anyone who's tried both who can give recommendations?
[Dbmail] Deleted_flag as a timestamp?
As an easy but extremely helpful feature, what about changing the deletedflag to be a unix timestamp (or similar). This would allow the maintenance script, etc to be modified in the future to delete messages that have been deleted after they've been flagged for deletion for over 72 hours, as an example. This would be a good feature for ISP helpdesks, as so many times users screw up and delete mail accidentally, but with POP there's nothing we can do about it. Currently we can restore it if they call up within 12-24 hours, but making it a timestamp would allow ISPs to set it to their own preferred 'grace period', probably as a function of database size and performance. (I for example would like to keep deleted messages for a week and allow a user to 'undelete' messages by accessing a webpage if they need to within that time). As far as syntax, the format of 0 for active, 1, etc... Could be retained -- and when the value is over 1000 (for example) it could be then treated as a timestamp... /Mark On 6/8/02 6:17 PM, "Philip Warner" <[EMAIL PROTECTED]> wrote: > > We have about 600 users and for some reason, some of them (less than 10) > have certain messages that are continuously sent to them via POP (never > deleted). Looking at the database for one such offending message: > > message_idnr | 3634 > mailbox_idnr | 1157 > messagesize | 6010 > seen_flag | 0 > answered_flag | 0 > deleted_flag | 1 > flagged_flag | 0 > recent_flag | 1 > draft_flag| 0 > unique_id | 1158A1028439277 > internal_date | 2002-08-04 15:34:37 > status| 1 > rfcsize | 6189 > > I notice that deleted_flag is 1 and seen_flag is 0, but this seems normal, > at least when mail is left on the server. What is odd is that no matter how > many times the user retrieves their mail, this message is always retrieved. > > As far as I can tell from the logs, they are only using POP. > > I'd be interested to know if anyone else has these problems, or has any > insight into likely causes. > > > > > Philip Warner| __---_ > Albatross Consulting Pty. Ltd. |/ - \ > (A.B.N. 75 008 659 498) | /(@) __---_ > Tel: (+61) 0500 83 82 81 | _ \ > Fax: (+61) 0500 83 82 82 | ___ | > Http://www.rhyme.com.au |/ \| > |---- > PGP key available upon request, | / > and from pgp5.ai.mit.edu:11371 |/ > > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] Bad From address information and folder question...
Are you using procmail to deliver the messages to dbmail? I found that procmail added an aditional line to the message which made the from address column be a time.. I wrote a simple wrapper which deletes the first line and then pipes it through to dbmail-smtp which seemed to fix it. /Mark On 7/8/02 5:14 AM, "Craig Coles" <[EMAIL PROTECTED]> wrote: > I am using IMP as my web-mail MUA and see that the From address is always > bad when displaying the list of mail. When the mail message is opened > everything seems to be fine. > > I've included a small pic of the problem. I've set the debug level to 5 and > included the section of syslog for the display of the messages. (see folder > question also at bottom) > > > Aug 6 10:36:24 raven dbmail/imap4[32105]: SS_WaitAndProcess(): client @ > socket 3 (IP: 127.0.0.1) accepted > Aug 6 10:36:24 raven dbmail/imap4[32105]: COMMAND: [ CAPABILITY] > Aug 6 10:36:24 raven dbmail/imap4[32105]: COMMAND: [0001 AUTHENTICATE > LOGIN] > Aug 6 10:36:24 raven dbmail/imap4[32105]: IMAPD [PID 32105]: user (id 3, > name soar) login accepted @ 2002-08-06 10:36:24^M > Aug 6 10:36:24 raven dbmail/imap4[32105]: COMMAND: [0002 CAPABILITY] > Aug 6 10:36:24 raven dbmail/imap4[32105]: COMMAND: [0003 SELECT INBOX] > Aug 6 10:36:25 raven dbmail/imap4[32105]: COMMAND: [0004 LIST "" > INBOX*] > Aug 6 10:36:25 raven dbmail/imap4[32105]: COMMAND: [0005 LIST "" > INBOX%] > Aug 6 10:36:25 raven dbmail/imap4[32105]: COMMAND: [0006 SEARCH ALL] > Aug 6 10:36:25 raven dbmail/imap4[32105]: COMMAND: [0007 FETCH 1:20 > (UID ENVELOPE BODY.PEEK[HEADER.FIELDS (Path Message-ID Newsgroups > Followup-To References)] INTERNALDATE RFC822.SIZE FLAGS)] > Aug 6 10:36:25 raven dbmail/imap4[32105]: COMMAND: [0008 FETCH 21:57 > (UID INTERNALDATE RFC822.SIZE FLAGS)] > Aug 6 10:36:25 raven dbmail/imap4[32105]: COMMAND: [0009 SEARCH ALL > UNSEEN] > Aug 6 10:36:26 raven dbmail/imap4[32105]: COMMAND: [000a FETCH 53:57 > (UID ENVELOPE BODY.PEEK[HEADER.FIELDS (Path Message-ID Newsgroups > Followup-To References)] INTERNALDATE RFC822.SIZE FLAGS)] > Aug 6 10:36:26 raven dbmail/imap4[32105]: COMMAND: [000b NOOP] > Aug 6 10:36:26 raven dbmail/imap4[32105]: COMMAND: [000c EXAMINE INBOX] > > Aug 6 10:36:26 raven dbmail/imap4[32105]: COMMAND: [000d UID FETCH 6825 > UID] > Aug 6 10:36:26 raven dbmail/imap4[32105]: COMMAND: [000e UID FETCH 6826 > UID] > Aug 6 10:36:26 raven dbmail/imap4[32105]: COMMAND: [000f UID FETCH 6828 > UID] > Aug 6 10:36:26 raven dbmail/imap4[32105]: COMMAND: [0010 UID FETCH 6830 > UID] > Aug 6 10:36:26 raven dbmail/imap4[32105]: COMMAND: [0011 UID FETCH 6831 > UID] > Aug 6 10:36:27 raven dbmail/imap4[32105]: COMMAND: [0012 FETCH 53,54:57 > (ENVELOPE BODY.PEEK[HEADER.FIELDS (Path Message-ID Newsgroups Followup-To > References)] INTERNALDATE RFC822.SIZE FLAGS)] > Aug 6 10:36:27 raven dbmail/imap4[32105]: COMMAND: [0013 LOGOUT] > Aug 6 10:36:27 raven dbmail/imap4[32105]: IMAPD [PID 32105]: user (id:3) > logging out @ (null)^M > Aug 6 10:36:27 raven dbmail/imap4[32105]: imap_process(): Closing > connection for client from IP [127.0.0.1] > Aug 6 10:36:27 raven dbmail/imap4[32105]: SS_WaitAndProcess(): client @ > socket 3 (IP: 127.0.0.1) logged out, connection closed > > > > Folder question... Also IMP is having problems displaying it's Folder > function. It will finally time out, but while doing so is displaying the > following in syslog.. can anyone tell me what is going on so I can forward > to the IMP group for a fix? (I have several thousand messages in the > database and the 'MESSAGES UNSEEN' line is repeating over and over) > > > Aug 6 11:00:09 raven dbmail/imap4[32100]: COMMAND: [0b9c STATUS INBOX > (MESSAGES UNSEEN)] > Aug 6 11:00:09 raven dbmail/imap4[32100]: COMMAND: [0b9d STATUS INBOX > (MESSAGES UNSEEN)] > Aug 6 11:00:09 raven dbmail/imap4[32100]: COMMAND: [0b9d] > Aug 6 11:00:09 raven dbmail/imap4[32100]: imap_process(): error [Broken > pipe] on read-stream > Aug 6 11:00:09 raven dbmail/imap4[32100]: SS_WaitAndProcess(): client @ > socket 3 (IP: 127.0.0.1) logged out, connection closed > > > Thanks for your time, > > > -Craig > >
[Dbmail] Quick suggestion - process names
Just a quickie -- sendmail renames its argv[0] variable to update the state of connections, which is really useful. Doing a ps ax with dbmail only shows lots of dbmail-pop3d processes. What about updating this based on states? Eg. dbmail-pop3: waiting dbmail-pop3: login dbmail-pop3: handling requests for Etc... /Mark
[Dbmail] Performance issue with large messages
I'm just testing some large mailboxes for performance handling, etc and noticed that checking for new mail seemed to pause for up to 5-10 seconds or so before continuing. When looking at the mysql query logs, the following seemed to be holding things up: > SELECT messageblk FROM messageblks WHERE message_idnr = 323 ORDER BY > messageblk_idnr This was using IMAP, and message 323 was a 10Mb file. The code doing this I believe is dbmsgbuf.c. It seems to load the entire message into memory or at least process it on the database before starting to send it to the user. I'm running a P4 1.8G with 1Gb memory as the database server and the same spec for the imap server; both with ata66 drives. All-in-all dbmail has been performing OK, only curbed by the noticeable pausing. Maybe this is unavoidable, in which case I'll just have to boost specs -- but maybe the message could be loaded into chunks from mysql (using a limit X,100 and repeating the query every 100 blocks or so). FYI. /Mark
[Dbmail] Creating users/mailboxes by database queries
Hi all -
I'm about to move on of our mailservers onto dbmail as part of our first
production environment; and was wondering the best way to propogate the
database, etc with information.
1) Porting mailboxes
Obviously I'll use the existing utilities here to handle this; but was
wondering if anyone had a utility already written which also checked the
users' home directory for IMAP mailboxes and imported them at the same time.
2) Adding new users on an ongoing system
The adduser script does the job for one-by-one additions, but we already
maintain a user database elsewhere. I don't want to have to call the script
each time a user is added. I'd rather simply execute an SQL query or run a
synchronisation script every 5 mins or something.
This works in principle, by simply calling:
insert into users (userid, password) values ('blah','blah')
Delivering mail using dbmail-smtp -u blah works fine, but reports an error
"db_check_sizelimit(): user has NO mailboxes" which make sense (I haven't
added any).
POP works fine and returns any test messages, but when looking at it via
IMAP I get a no such folder INBOX error.
I can obviously get around this by creating a mailbox using a similar SQL
query, but I'm hoping to leave this table alone... I'm using two databases
(dbmail_auth and dbmail_store); and only the users and aliases table are in
the dbmail_store database. My objective is to only edit these tables (and
later integrate them into my main authentication database when 1.0 is
released), so that the dbmail internals (messages, mailboxes, etc) are never
touched by me/my code (thus minimising the risk of corruption).
Thus is there any way to have this mailbox created automatically?
It seems to make sense to me, and would work for POP and IMAP. This I guess
would also be useful if people were using the dbmail -m mailbox -u username
syntax.
A patch like this (for mysql, easy to port if liked) - would be something
like:
--- mysql/dbmysql.c_origSat Jul 27 14:19:06 2002
+++ mysql/dbmysql.c Sat Jul 27 14:41:04 2002
@@ -452,10 +452,38 @@
if (mysql_num_rows(res)<1)
{
- trace (TRACE_DEBUG,"db_get_mailboxid(): user has no mailbox named
[%s]", mailbox);
+ trace(TRACE_ERROR,"db_get_mailboxid(): user has no mailbox named [%s]
-
Creating", mailbox);
mysql_free_result(res);
+
+ // create mailbox
+ db_createmailbox(mailbox, useridnr);
+
+ snprintf (query, DEF_QUERYSIZE,"SELECT mailbox_idnr FROM mailboxes
WHERE "
+ "name=\'%s\' AND owner_idnr=%llu",
+ mailbox, useridnr);
+
+ // double check to see if mailbox exists now
+ if (db_query(query)==-1)
+{
+ trace(TRACE_ERROR, "db_get_mailboxid(): query failed");
+ return 0;
+}
+
+if ((res = mysql_store_result(&conn)) == NULL)
+ {
+ trace(TRACE_ERROR,"db_get_mailboxid(): mysql_store_result
failed: %s",mysql_error(&conn));
+ return 0;
+ }
+
+if (mysql_num_rows(res)<1)
+ {
+ trace (TRACE_DEBUG,"db_get_mailboxid(): user has no mailbox
named [%s]. Couldn't create.", mailbox);
+ mysql_free_result(res);
+
+ return 0;
+ }
- return 0;
+ // if still here, the mailbox should have been created
If I'm barking up the wrong tree, can someone let me know the better way to
handle this please.
Cheers,
Mark.
Re: [Dbmail] Auth from a radius database
No support for this exists at the moment (to my knowledge) -- but it has recently been discussed on the list. Version 1.0 has not be finalised yet -- which I personally am waiting for. At that time I may hack around and see if I can make a working plugin; but can't promise anything [I'm not a C programmer]. As a more direct option -- as soon as 1.0 is release (or sooner if the team indicates they accept the patch) -- I'm going to provide a patch file which will allow the user of customer field names for the authentication database. That way if your Radius daemon uses an sql server -- you could get dbmail to use the same database; without needing alter the auth database much [eg. Probably will need to add a "mailboxid" column or something]. /Mark > From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> > Reply-To: dbmail@dbmail.org > Date: Thu, 11 Jul 2002 17:31:14 -0700 > To: > Subject: [Dbmail] Auth from a radius database > > First off I'd like to say, from just looking at dbmail I'm very excited. I > haven't ever seen a solution to store mail in a database...the possibilities > are limitless... > I'm part of an isp that is currently trying to decide on a new mail system. > Someone in the ISP recommended DBmail upon review I found it to be a great > solution except for one detail...we need to be able to authenticate directly > from a radius server. I'm hoping there is some solution for this already > because if that were the case this would be the absolute perfect solution > for this company. Any help is greatly appreciated, thanks in advance. > > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail
