qmail/MySQL - history
qmail v1.1.15 and netqmail v1.1.15, 2006-03-29
This patch changes the way we deal with MySQL queries. It's strictly more correct although the functionality is unchanged. Stephen Hawkins again.
qmail v1.1.14 and netqmail v1.1.14, 2006-03-22
While I was chatting with Stephen Hawkins about the bad address patch he alerted me to an Old Bug in getpw_mysql() whereby we were checking the value of an uninitialised variable. The best part is the check was completely unnecessary anyway. Fixed.
qmail v1.1.13 and netqmail v1.1.13, 2006-03-22
Rolled in Stephen Hawkins's bad address patch. It was The Right Way to do things anyway, and I discovered that not applying the patch would cause qmail to fail to bounce messages to invalid virtual addresses where no catchall existed and a default relay is set in smtproutes.
qmail v1.1.12 and netqmail v1.1.12, 2006-03-08
DO NOT USE. Thanks to Stephen Hawkins for spotting that this patch was incorrectly built.
qmail v1.1.11 and netqmail v1.1.11, 2005-05-07
qmail-smtpd wasn't always calling disconnect_mysql(). While this didn't have any impact on qmail itself, it could cause the MySQL server log to fill up with warnings. Thanks to Brian for spotting this.
qmail v1.1.10 and netqmail v1.1.10, 2004-11-09
Another very subtle memory leak, this time in vdoms_mysql(). Not so subtle that it wasn't found, however. Found and eradicated.
qmail v1.1.9 and netqmail v1.1.9, 2003-03-20
Stupid stupid malloc() bug. Everyone makes mistakes, and I'm a better C programmer than when I started on this project, so I won't say "I can't believe I did that." I can't believe it's taken this long for someone to spot it. Gavin Hamill again...
checkpassword v2.0.1 prerelease 2 for checkpassword 0.90, 2001-07-26
Fixed a dumb bug that caused checkpassword to segfault and die if a port section was found in the sqlserver file.
checkpassword v1.1.7 for checkpassword 0.81, 2001-07-26
This silly bug was down to every C programmer's worst enemy, the rogue star. One *portnum where you mean portnum and kaboom!
qmail v1.1.8, 2001-07-26
Maybe one day I'll learn to test features that I don't personally use instead of assuming they'll be OK...
New scripts, 2001-06-20
I've finally updated the admin scripts to reflect the changes in the table structures. You can now specify the type of password on the command line, as well as having a global default. Thanks Sidnei (and others) for prodding me.
checkpassword v2.0.1 prerelease 1 for checkpassword 0.90, 2001-05-29
This patch for checkpassword 0.90 implements the same revised MySQL connection code as qmail-1.1.7. Still on prerelease 'cos I still haven't tidied up the Makefile. It's functionally complete though.
checkpassword v1.1.6 for checkpassword 0.81, 2001-05-29
This is the checkpassword 0.81 version of the above patch.
qmail v1.1.7, 2001-05-11
This release fixes a Potentially Very Serious Bug which was brought to my attention by Renato Lins from Brasil.
I now believe that this Potentially Very Serious Bug was the actual cause of the FreeBSD troubles some people had been experiencing (see later). Here's an email sent to me by Daniel Kerr:
From: Daniel Kerr To: <me@iain.cx> Subject: qmail-1.03+qmail-mysql-1.1.7 Date: Sat, 26 May 2001 21:23:46 -0500 (CDT) Sir, First let me thank you for your work on the above patch. I'd like to let you know that my installation on FreeBSD 4.3-RELEASE has happily delivered 2050 messages with no hint of the ~23 message erroneous behaviour. Thanks again, -- Daniel.
Patch 1.1.7 is also available as a diff from 1.1.6 for people downloading with a 9600 baud modem (or who had already applied other patches...)
The bug
The bug, which is present in all versions prior to 1.1.7, can cause qmail-send to crash or hang if and when it loses its connection to the MySQL database server. This may happen because mysqld dies or is configured to terminate idle connections. qmail-send is unable to reconnect to mysqld and will either get stuck waiting for its database connection to reappear (which of course it won't) or segfault and die.
How did such a Very Serious Bug go unnoticed for almost a year? Well I did say it was Potentially Very Serious. When was the last time your MySQL server crashed? Of those times, when was the last time svscan didn't start it up again straightaway? When was the last time your qmail-send was so lightly used that it timed out from your mysqld? For me, and obviously for everyone else apart from Renato, the answer to each of the above questions is "never!"
Of course qmail-local, qmail-getpw, qmail-smtpd and checkpassword don't live long enough to need to REconnect to the database server. Either they connect at startup or they don't. End of story. Thus even though they share the same connection code they were not affected by my mistake.
What the man himself had to say
Date: Fri, 11 May 2001 11:50:54 -0400
From: renato
Organization: i24horas.com.br
X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.3-20mdk i586)
To: Iain Patterson <me@iain.cx>
Subject: The Real Story
Hi,
I would like to tell you how I realy find the that little bug :
I had just patched the code and compiled. To test it a ran the
qmail-start from command line, and exactly when i was about to send the
first e-mail with it, The people here in the office went to lunch , and
I left with them, when we returned, I send the e-mail, and for my
surprise it segfault , I ran it again and it works, I looked the log and
there were 3 attemps to connect to database, and then I figured out
whats happened. As the matter of facts our main server has over 2000
e-mail accounts (thats why I would like to change to qmail using mysql)
and the db (mysql) server has a 375 (today) days of uptime and it never,
even once had down for any reason.
Thanks
Renato Lins
Other changes in v1.1.7
As well as rewriting the database connection code, I found time to tweak the configuration (sqlserver) file handling ever so slightly.
You'll be pleased to know that key/value pairs in the config file can be separated by <space>, =, or <tab> (tab is new!). You can now use "host blablabla" as a synonym for "server blablabla" and instead of having a hardcoded 3 attempts to connect to the database you can specify how many tries you want with "tries n" where n is a natural number (that's an integer greater than zero for you non-mathematicians). In other words you CANNOT say "tries 0" to mean unlimited or anything silly like that. Try to set the number of attempts to anything less than one and qmail will use the default 3.
All of this stuff has my debugging code left in (I always used to strip it out and then wish I hadn't when someone asked me for help :-) Uncomment -DEBUG_DEBUG_DEBUG in the Makefile for more information than you really want.
Finally, I changed a few strtol calls to scan_ulong so as to be more like Dan...
FreeBSD discussion
Pawel Garbowski over in Poland reported that patch 1.1.1 works fine on FreeBSD but that with all subsequent patches, qmail-send hangs after processing around 23 messages. I used to develop these patches primarily under FreeBSD but I since switched to Linux and Solaris, as these are the platforms I use in production, so the idea that FreeBSD itself has something to do with it may seem plausible. However if you take a look at the differences in code between subsequent releases you may struggle to see what that OS-specific problem is. Certainly I can't see it (although without a FreeBSD box to play with I'm at a disadvantage).
Another thing to bear in mind is that I've always found FreeBSD manages memory much better than Linux. Indeed, in many ways I find FreeBSD a far superior operating system to Linux. Unfortunately one side effect of its increased resilience that I've noticed is that bugs which can cause programs to segfault under Linux continue running on FreeBSD. This would certainly explain why qmail-send would hang and not crash.
So it may be that Pawel was just using MySQL in a different way to me and allowing his database connections to drop. Not an OS issue at all. Daniel's message would seem to confirm this theory and since the MySQL bug is a bit of a brown paper bag one, this patch is now recommended for all operating systems.
checkpassword v2.0.0 prerelease 1, 2001-01-18
For checkpassword 0.90 only. Get 1.1.5 for checkpassword 0.81.
This is the first version of the checkpassword patch that works with 0.90. I hadn't been keeping up and didn't realise Dan had put out 0.90 until I got complaints that my patches wouldn't apply.
This is only a prerelease because although it compiles and runs, it's very untidy. I've ruined Dan's Makefile and filled it up with redundant rules. I intend to clean it up for the actual 2.0.0 release.
There's one actual change to the code: I've now added a -DO_NOT_LOG_ERRORS #define so you can choose to ignore messages from checkpassword about failing to find users in the database (you'll already know this because of the failed query warnings...
qmail v1.1.6, 2000-11-24
Replaced tcplog with log3 in connect_mysql.c. Without this change, qmail-send tries to write error messages to a bad file descriptor. This means that you'll miss the "error: Access denied for user: 'qmail@localhost' (Using password: YES)" or similar reason for not being able to connect to the database. This should help people who complain of "alert: out of memory, sleeping..." or "temporary failure in qmail-lspawn" errors and who didn't read the appropriate section in the FAQ (or who read it but didn't take it seriously).
So why did I use tcplog in the first place? Because the other programs grab file descriptor 0 for their own devices, that's why. I needed a file descriptor that could be used by qmail-smtpd, qmail-local and qmail-getpw to report their errors.
If you're playing with regular expression queries, there's also an updated version of that patch.
Web administration, 2000-11-17
Download a tarball of PHP files that let you manage virtual domains and alias (sorry, not actual users although the code is their in qmail/functions so it wouldn't be hard for you to set up). You'll need to change a few things for it to work: see the README.html file in the tarball.
These pages are the backbone of the system I use at work but with stuff specific to my company stripped out (which is why there's no user creation page; we do that somewhere else...) So hack away all you want and send comments if you want but don't expect me to make any changes to the code :-)
I should mention (actually I should have mentioned it earlier) that these pages only work to their full potential if you use MAGIC FORWARDING and yes, I said earlier that MAGIC FORWARDING should be avoided. When I was in secondary school (ages 11-16 for those not familiar with the UK education system) I had a maths teacher whose catchphrase seems particularly appropriate now: "Do as Blewitt says, not as Blewitt does!"
Oh yeah, don't read the PHP, it's a mish-mash of horrible hacks...
qmail v1.2.0, 2000-11-14 - BETA RELEASE! UNSTABLE! EXPERIMENTAL!
I was chatting with Gavin about some virtual table entries and it occurred to me that it would be cool if as well as FUNKY QUERIES we could have REGULAR EXPRESSION QUERIES. A bit of hacking later and here it is. I just knocked it up for a laugh. Let me know what you think.
Basically here's the deal. Put an extended regular expression in the virtual_username column and (optionally) a string containing backreferences in the ext column. Like this:
| username | ext | virtual_username | virtual_host |
| iain | \1 | subscribe-([-a-z0-9]+)$ | mailinglists.com |
Then mail for subscribe-qmail-users@mailinglists.com would be delivered to iain-qmail-users.
You're limited to matching in the virtual_username column and backreferencing in ext but you can have up to 99 backreferences (yeah right) and pretty do anything that regular expressions let you do. You can even use & in your string like in sed...
I'm thinking about extending that by letting you put regular expressions in bother virtual_username and virtual_host and then have backrefs in both username and ext. I'd make it work so that if there was only one set of brackets in virtual_username and you had \3 then it would search for the second set of brackets in virtual_host. Yes, this sounds good. If I do any more work on regular expressions I'll certainly implement that.
Beware! This patch is just me hacking about and having some fun. It isn't tested AT ALL. Specifically there's no guarantee that the virtual table queries will return sensible results (ie an exact match before a regular expression match) as is the case with standard queries. Do NOT download this patch if this is your first visit to this site and you're planning on setting up a production server. Use 1.1.5 instead. As usual I'm open to suggestions. Uncomment -DVIRTUAL_REGEX and have fun...
Just to clear up any possible confusion: the only difference between this and version 1.1.5 is the regular expression hacks. If you don't want to play with regular expressions then there's no point getting this.
qmail v1.1.5 fixed, 2000-11-10
Fixed two stupid typos which broke qmail-getpw.
Important note: MAGIC FORWARDING
I have been informed that MAGIC FORWARDING will fail if the destination address is hosted on the same server because it always assumes that the destination is remote (after all there is no need to use this method for local addresses).
MAGIC FORWARDING is a hack which I implemented at the request of Gavin Hamill following a discussion of various methods of forwarding messages for virtual hosts to remote addresses. It is not the qmail way of doing things but it is a convenient fix for a certain problem.
It is one of my goals to write clean code that is as robust as DJB's own. I am not always able to do that and I certainly have not done that in this case. I had a look at how I could fix this problem and I have not come up with a proper solution. The best answer I've come up with in the short space of time that I have at my disposal resulted in a setup in which mail loops were possible.
To implement MAGIC FORWARDING correctly (that is the qmail way, ie 100% reliably) would take more work than I currently have time to do. Therefore I have taken the decision NOT to support MAGIC FORWARDING. You can use it if you want to, and as long as you only try to magically forward to actual remote addresses you should be fine but from now on I will recommend to anyone who asks me how to implement this functionality that they map the virtual domain to a local user and then use the alias table to forward the resulting local address to a remote location. This is the qmail way and the inconvenience of it being more of a hassle to set up is outweighed by the advantage that it absolutely will not go wrong.
qmail v1.1.4, 2000-11-09
Same atoi -> strtol fixes as for checkpassword (see previous entry).
checkpassword v1.1.5, 2000-11-04
Not compatible with checkpassword 0.90. Use version 2 for that.
My best friend Gavin Hamill, who isn't Irish, brought it to my attention that checkpassword dies on an Alpha box. I think this is due to a rogue mysql_free_result() that was over-zealously trying to free() a result that had already been freed. Oh dear. Brown paper bag time.
To make amends, I've changed the atoi calls in qmail-getpw.c to strtol with proper checking. Now if someone changes all the uids and gids in your database to strings checkpassword will exit gracefully! Nice to know isn't it?
mysqldump output fixed, 2000-11-03
The mysqldump output has been updated to reflect the new table formats as of checkpassword 1.1.3. Apologies to all those who got confused about the discrepancies between the dump and the source, and thanks to those who pointed it out. Then more apologies for not fixing it sooner...
qmail v1.1.3, 2000-10-25
After a request from the not-at-all-Irish Gavin Hamill who promised to be my best friend if I did it, I've implemented what I call MAGIC FORWARDING.
It's very simple. Uncomment the MAGIC #defines in the Makefile and run a query like the following:
insert into virtual values ('forward', 'somewhere@else', 'testing', 'iain.cx');
Now mail for testing@iain.cx will be automagically forwarded straight to somewhere@else and, if you uncomment -DMAGIC_LOGGING, you'll get a line like this in your logs:
magic forward: msg 280082 for testing@iain.cx to somewhere@else
checkpassword v1.1.4, 2000-10-23
Added an update to the manpage and made _authen know its name when explaining its usage.
Yes, that's it.
qmail v1.1.2, 2000-10-12
Not for FreeBSD. See news section.
No I don't know why 1.1.1 isn't listed here. It was and still is available for download!
Phew! This is a big one. Lots of time has passed since the last release but in fairness I didn't get a chance to work on it for most of that time. Nonetheless there are significant changes. But first:
A few harmless compilation warnings have been shut up thanks to advice from Ben at Zen. I've tidied up the logging. We no longer use syslog at all. All logging is done to stderr or some other filehandle. The bottom line is that it ends up being handled by multilog or splogger or whatever it is that you've got doing logging. Plus database errors are always logged regardless of whether queries and stuff are logged.
There are a few changes to the Makefile. The default MySQL install directory is now /opt/mysql and not /usr/local/mysql because that's where I install MySQL and I keep forgetting to change it back when I build the diffs. There are a few explanatory notes to help people decide which bits they need to uncomment when certain compilation errors crop up and now there's the promised Solaris fix for qmail-getpw.c. Just uncomment -DSOLARIS_STUPIDITY in OS_SPECIFIC.
The alias table querying routines in dotqmail_mysql.c have been tidied up. They're now compatible with the virtual queries in that a blank alias entry means no extension and the wildcard symbol is @. Then of course there are the long-awaited FUNKY QUERIES. These are far too scary to attempt to explain. Read the README.queries file and have a play! Your comments would be very much appreciated as I haven't had time to test these new queries as much as I would have liked. If in doubt, don't use them. If you leave the QUERY_STYLE bits commented out then the old behaviour will be used.
I say again: FUNKY QUERIES are not tested as thoroughly as the original queries. Do not use them in production without testing them first. But they are funky!
checkpassword v1.1.3, 2000-09-30
New password_type and suspended fields in the mailbox table. checkpassword will fail to authenticate any user whose suspended field is "Y" regardless of whether their password is correct or not. Furthermore, you no longer need to decide on plaintext, crypt() or MySQL scrambled passwords at compile time. Just set password_type to one of Password, Crypt-Password or MySQL-Password and checkpassword will automagically pick a password checking algorithm at runtime. This feature is partly there to allow you to migrate groups of users to different password types at a time and partly for compatibility with ICRadius (with my MySQL hashed passwords patch).
I've updated the mysqldump file so it creates the new format mailbox table. If you're upgrading from a previous release of my checkpassword patches you should run the following queries prior to patching and installing:
alter table mailbox add password_type enum("Password", "Crypt-Password", "MySQL-Password") not null default "Password";
alter table mailbox add suspended enum("N", "Y") not null default "N";
Don't worry: your old tables will continue to work between running these queries and the checkpassword upgrade.
The ptest utility, which lets you test a user's password on the command line, has been changed to _authen for compatibility with the USR (oh all right, 3Com) Total Control's undocumented (thanks boys) debugging command of the same name. To use it:
4>&1 _authen username password
If you don't see why the "4>&1" is there, try it with that line and/or use the source!
Finally, I also fixed a bug-let where MySQL errors were only logged if you had #undef-ed O_NOT_LOG_SQL. Errors should be logged in all cases.
checkpassword v1.1.2, 2000-09-07
Fixed a long-standing but non-serious bug where getpw_mysql() didn't close its database connections properly, leading to lots of "got an error reading communication packets" messages in the MySQL log.
Some additional library dependencies which may or may not be needed depending on your OS have been put in the Makefile. Uncomment as needed.
New scripts, 2000-08-04
I've updated the admin scripts. They now include a domain information script (alias info coming soon) and support for everyone's favourite auto_break.
checkpassword v1.1.1, 2000-07-28
All of that evil syslog stuff has been binned. checkpassword now outputs its diagnostics to file descriptor 4 (subject to you compiling it without -DO_NOT_LOG. Failed database connections are logged whether or not you enabled standard or SQL logging, because they're bad news.
Each message includes its parent's process ID (ie the pid of the tcpserver process) so you can match up (failed)? logins to tcp clients. Use a script like:
tcpserver -v 0 110 qmail-popup hostname \ checkpassword qmail-pop3d Maildir 4>&1 2>&1
in your supervise directory.
The Makefile was also cleaned up a bit.
checkpassword v1.1.0, 2000-07-28
Development only. Not released.
qmail v1.1.0, 2000-07-25
Fixed two silly database misconfiguration problems. Previously, if you had an entry in the virtual table where the username was blank, or an entry in the alias where the alias_userame was blank, qmail would blindly try (and fail, of course) to deliver to the user called "". Clearly that wasn't too clever.
Now, you'll get a warning message on standard error (which will make its way into the logs eventually) and vdoms_mysql() will return 0, making qmail-send continue trying to process the address as best it can - it will check ~qmail/virtualdomains and probably end up bouncing the message. I'm pretty sure this isn't the best way to handle the error (or even a good way to handle it). As for local deliveries, qmail-local will just ignore the bogus recipient. This is more likely to be a good idea... Comments appreciated please.
qmail v1.0.3, 2000-07-11
I don't get it. stralloc_cat(&sa, &sa2) never works for me, whereas stralloc_cats(&sa, sa2.s) is fine. vdoms_mysql.c was fixed (again) to reflect this, and an entirely unnecessary line was removed. You'll notice that the links on this page work now.
(Boring) discussion
There follows a better explanation of the differences between versions 1.0.2+ and 1.0.1.
As we know, qmail-send adds a Delivered-To: header when delivering messages. We also know that vdoms_mysql() parses addresses of the form user@host and selects rows from the database where virtual_username is user and virtual_host is host. However, previously it was forming the host part of the Delivered-To: line from the envnoathost control, so that me@iain.cx gets the line: Delivered-To: iain@babasse.com appended to it.
But what if iain.cx were in rcpthosts but not locals and the mail was forwarded to another SMTP server? And what if that SMTP server parsed the Delivered-To: line and decided to send the mail back to babasse.com? That would be bad.
MDaemon does this.
We receive mail for people that use MDaemon. (Oh well).
vdoms_mysql() now preserves the virtual_host part of the original address when adding the Delivered-To: header, just like vanilla qmail does.
qmail v1.0.2, 2000-07-03
vdoms_mysql.c was incorrectly putting envnoathost at the end of rwline. It has been fixed to put the virtual domain. This problem affects SMTP delivery to things like remote MDaemon servers where envnoathost would be interpreted as being off-site.
qmail v1.0.1, 2000-05-31
splog.c was still broken.
qmail v1.0.0, 2000-05-18
Replaced '-' with auto_break in MySQL queries.
qmail v0.9.9, 2000-05-18
After prompting by my colleague Ben Warham I have decided to implement a version numbering scheme. And since things appear to work at the moment, I'll start with version 1.0.0. Except that I won't, because I've got a change to make...