dbmail and database default character set

I migrated my mailserver to another server recently. I installed dbmail from scratch and restored my database into new mysql server. When I started dbmail to connect to the database server, I found out in syslog (see below) that the dbmail-imapd couldn't connect to the server because of the different character set. It's a FATAL error!. I found out that my old database used latin1 as the default character set and dbmail used utf8 as I defaulted in /etc/dbmail.conf. So I just change the default character set of my dbmail database in MySQL to utf8 (see below). This solved the problem.

In syslog before changing the character set:
Mar 14 13:01:54 webmail.fakedomain.com.my dbmail-imapd[13986]: Error:[sql] dbmysql.c,db_mysql_check_collations(+138): collation mismatch, your MySQL configuration specifies a different charset than the data currently in your DBMail database.
Mar 14 13:01:54 webmail.fakedomain.com.my dbmail-imapd[13986]: FATAL:[server] server.c,StartServer(+129): Unable to connect to database.

To change default character set of a dbmail database:
ALTER DATABASE dbmail DEFAULT CHARACTER SET utf8;

In syslog after changing database default character set:
Mar 15 13:20:29 webmail dbmail/imap4d[14107]: Message:[server] pool.c,child_register(+299): register child [14107]
Mar 15 13:20:29 webmail dbmail/imap4d[14109]: Message:[server] pool.c,child_register(+299): register child [14109]
Mar 15 13:20:29 webmail dbmail/imap4d[14111]: Message:[server] pool.c,child_register(+299): register child [14111]
Mar 15 13:20:29 webmail dbmail/imap4d[14113]: Message:[server] pool.c,child_register(+299): register child [14113]
Mar 15 13:20:29 webmail dbmail/imap4d[14101]: Message:[server] pool.c,scoreboard_state(+590): Scoreboard state:children [4/10], spares [4 (2 - 4)]

dbmail 2.2.10 released

I have just upgraded my dbmail to 2.2.10. There are bugs fixed. So upgrade is highly recommended.

Changes since 2.2.9:

- - PostgreSQL 8.3 compatibility (# 676).
- - Duplicate key errors and other insertion problems ( #685, #686).

Download:
http://www.dbmail.org/download/2.2/dbmail-2.2.10.tar.gz

Debian etch backport:
deb http://debian.nfgd.net/debian stable main

Full changelog:
http://git.dbmail.eu/?p=paul/dbmail;a=shortlog;h=v2.2.10

Refer my previous post on how to upgrade dbmail (without database schema change).

dbmail 2.2.9 released

From dbmail mailing-list (Feb, 9 2008):

Hi all,

I'm pleased to announce the availability of DBMail version 2.2.9, the latest in the stable/production series.

THis is a bugfix maintenance release.

Bugs fixed since 2.2.8:

DBMail - 2.2.9
==============
- 0000668: [Command-Line programs (dbmail-users, dbmail-util)]
dbmail-export segmentation fault and mbox compatibility (paul)
- 0000663: [Authentication layer] Authldap module reloads when it try to recconect
- 0000662: [Authentication layer] Ability to bypass authentication on Active Directory
- 0000660: [IMAP daemon] imapd provides better inetd compatability (paul)

Changelog:

http://git.dbmail.eu/?p=paul/dbmail;a=shortlog;h=v2.2.9

Download:

http://www.dbmail.org/download/2.2/dbmail-2.2.9.tar.gz


kind regards,
Paul Stevens paul at nfg.nl

What is dbmail?
Read here

dbmail 2.2.6-rc1 released

I have just upgraded my dbmail to dbmail 2.2.6-rc1.

Announcement : http://www.gossamer-threads.com/lists/dbmail/users/22531
Changelog : http://nfg3.nfgs.net/cgi-bin/gitweb.cgi?p=dbmail.git;a=log;h=dbmail_2_2
Download : http://www.dbmail.org/download/2.2/dbmail-2.2.6-rc1.tar.gz

How to upgrade from previous release (2.2.5)

1. Stop postfix, dbmail-imapd, dbmail-lmtpd and dbmail-timsieved using service command.
2. Download the tarball and extract it.
3. Enter the installation folder and run :

./configure --prefix=/usr --sysconfdir=/etc --localstatedir=/var --with-mysql --with-sieve

4. make
5. make install
6. Restart all the stopped services.

That's all there is to it.

Upgrading dbmail

Last week, I upgraded my production imap server from dbmail 2.0.9 to the latest stable 2.2.1. I need to upgrade because there are features I need to use. They are:

1. server-side sorting (fixed after 2.1.7)
2. sieve server-side filter (using dbmail-timsieved daemon)

The upgrade was a risky one. It was a leapfrog from 2.0.x to 2.2.1 by skipping 2.1.x. Many changes since 2.0. Since I did not have a test server so it was important to backup all emails. After too much thought, I decided to give it a go.

Stopping the mail server

These stops all necessary mail-related services. You may not want to stop httpd services to make announcement to users on the website. You have to stop services in order to migrate your system smoothly without bothering emails coming in and out and slow down the whole process.

service postfix stop
service dbmail-imapd stop
service dbmail-lmtpd stop


Backing up all emails
It is important to backup all emails before upgrading process. At least you can revert to previous dbmail if something go wrong.

mysql -u dbmail -p dbmail > dbmail-2.0.9.sql

Source
Download the source from dbmail.org and untar it.

tar xzvf dbmail-2.2.1.tar.gz
./configure --prefix=/usr --sysconfdir=/etc --localstatedir=/var --with-mysql --with-sieve

Note: If you want to use sieve filter (--with-sieve), you have to download libsieve at http://libsieve.sourceforge.net/. Compile it and install.

make
make install

Migrate the database
Use the provided script in sql dir to migrate dbmail tables to make it suitable for 2.2.x

mysql -u dbmail -p dbmail <>

Fix the header

/usr/sbin/dbmail-util -by

If you do not run this command, you will get unknown (for sender) and (no subject) (for subject) for all mails in mail header. Please make sure it is the latest binary you're running by issuing dbmail-util -V.

Please watch any error coming out of the upgrade process. If any, you should consult dbmail mailing list.

That's all there is to it. Restart all the process (or you may want to restart your server). Make sure all the related services start during startup with chkconfig.

dbmail-timsieved init-script
This is a script I make for dbmail-timsieved by modifying dbmail-imapd script. Clever huh? :P

#!/bin/bash
#
# Startup script for the DBMail TimSieve Server
#
# chkconfig: - 81 19
# description: DBMail is a mail server with a database backend. Unlike \
#              WUimapd it does not run through inetd.
# processname: dbmail-timsieved
# pidfile: /var/run/dbmail-timsieved.pid
# config: /etc/dbmail.conf

# Source function library.
. /etc/rc.d/init.d/functions

if [ -f /etc/sysconfig/dbmail ]; then
     . /etc/sysconfig/dbmail
fi

# Path to the dbmail script.
dbmail=/usr/sbin/dbmail-timsieved
prog=dbmail-timsieved
RETVAL=0

start() {
     pid=`pidof $prog`
     if [ -n "$pid" ]
     then
             echo "dbmail timsieved daemon is already running, PID $pid"
     else
             echo -n $"Starting $prog: "
             daemon $dbmail
             RETVAL=$?
             echo
             [ $RETVAL = 0 ] && touch /var/lock/subsys/$prog
             return $RETVAL
     fi
}
stop() {
     echo -n $"Stopping $prog: "
     killproc $dbmail
     RETVAL=$?
     echo
     [ $RETVAL = 0 ] && rm -f /var/lock/subsys/$prog /var/run/$prog.pid
}
reload() {                                                       
     echo -n $"Reloading $prog: "
     killproc $dbmail -HUP
     RETVAL=$?
     echo
}

# See how we were called.
case "$1" in
start)
     start
     ;;
stop)
     stop
     ;;
status)
     status $dbmail
     RETVAL=$?
     ;;
restart)
     stop
     start
     ;;
condrestart)
     if [ -f /var/run/$prog.pid ] ; then
             stop
             start
     fi
     ;;
reload)
     reload
     ;;
*)
     echo $"Usage: $prog {start|stop|restart|condrestart|reload|status}"
     exit 1
esac

exit $RETVAL

Migrating mails from old server to new server

I have done migrating my users' mails to new server. dbmail saves all mails into database (in my case mysql), backing up and restoring should be trivial.

1. backup (using mysqldump)
~#mysqldump -u user -pPassword dbmail > dbmail.sql

2. restore
~#mysql -u user -pPassword dbmail < dbmail.sql

Voila. It's done!.

I have users' preferences and address books saved in database too. So the method of backing up and restoring to the new server should be the same.