Amavisd-new 2.6.x upgrade headaches

Ok, so I was using amavisd to help me out with scanning for viruses and spam. I wanted MySQL tables for everything, but i was configuring everything so I could prefix those tables to fit into my control panel scheme. (by default their table names are lame, and as far as I know, un-prefix-able). So to use my scheme, I override the default sql used to query for various things, then I can use my own table names.

The problem I encountered was when I upgraded amavisd to the newest from the repository. Suddenly all of my mail was getting deferred and infinitely queued.

I was getting a lot of these types of errors:
sql-enter FAILED: sql exec: err=16, S1000, DBD::mysql::st > bind_param failed: Illegal parameter number at…

After some googling, I came across this page: http://www.mail-archive.com/amavis-user@lists.sourceforge.net/msg12088.html

Ok, so apparently they added the partition_tag column to the database schema, which would be nice if they did some kind of a check, to let you know that you need to fix this based on an upgrade, rather than just let your amavisd fail. I suppose you SHOULD read through the README’s, but when you’re doing ‘yum update’ on your server, are you really going to do that for EVERY piece of software that gets upgraded?

So in my case, the way to fix everything was to go through the README: http://www.ijs.si/software/amavisd/README.sql-pg.txt

Find all mentions of the new partition_tag column, and update my database to include it, and all CONSTRAINT or FOREIGN KEYS, etc.

Which of course, didn’t work by itself. You now have to change the amavisd.conf to update the %sql_clause you overrode to include this new column in the check. I first added it at the end thinking that they matched what you were selecting on the backend to see which order the data should be passed to the ?,?,?,? you set up inside those queries. But no, that’s not true either. You have to have them in the exact order in which they expect to send them to you.

But how do you know that? I couldn’t seem to pull any of that up on google. So since amavisd happens to be just a perl script, I figured I could read through that searching for queries to see how it builds out the defaults.

Sure enough, they are right there, exactly in the same shape with which you would override them in %sql_clause, so really all that had to be done was to copy those out of that file, paste them into amavisd.conf over the old ones, and re-do the table prefixes I am using.

I can only hope that someone finds this and doesn’t have to spend the time I did figuring it out.

/rant