I recently decided to move my enormous mail archive from my trusty Courier-imap/Maildir setup to DBmail. The reason is simple.  I have several mail folders with 100,000+ mails in.  That means several directories with upwards of 100,000 files in.   And that means bad performance.    There is another reason:  I can execute far more powerful searches with an SQL query, than any mail client can allow me to do.

DBmail is a POP3/IMAP server that uses a regular database server (currently MySQL, PostgreSQL or SQLite) for its mail store.  Given the obvious advantages, I’m surprised this isn’t more popular.

There is one gotcha to the setup.  MySQL, being Swedish, has a default collation setting of ‘latin1_swedish_ci’ while DBmail assumes ‘utf8_general_ci’ will be set.  But the DBmail docs, and even the MySQL notes page, does not mention this* at all, and the included create_tables.mysql script does not set the correct collation either.   This results in the following error showing up in the logs:

Sep 14 03:00:01 hermes dbmail/maintenance[16708]: 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.

This is easily fixed.  Assuming your database is called ‘dbmail’ do:

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from SCHEMATA where SCHEMA_NAME = ‘dbmail’;

The ‘DEFAULT_COLLATION_NAME’ column will most likely show ‘latin1_swedish_ci’ – this is the problem.  Run the following:
mysql> alter database `dbmail` collate `utf8_general_ci`;
Query OK, 1 row affected (0.02 sec)

Run the select query again and check if the right collation is showing.  After doing this, dbmail should connect to MySQL without any problems.