Upgrading
Upgrading is automatic in version 3 and covers the majority of users. If you have a large or complex system then this guide will help you upgrade manually. If you are upgrading from an earlier version please see upgrading from version 2.
The database schema is stable and hasn't changed materially since version 3. Nevertheless changes happen and if you have a large database then it's essential to check if any changes are material and if so apply them manually.
If you have a large and active database then there are advantages to upgrading with, for example, setting a timeout to avoid locking a table and possibly causing a deadlock - effectively bringing the system to a halt.
The upgrade system is simple and straightforward. It's integrated into each version of DBMail ensuring the database schema is up to date. PostgreSQL, MySQL and SQLite plus compatible databases are supported thanks to libzdb the small, easy to use Open Source Database Connection Pool Library.
Automatic upgrades only work with the default table prefix, if you have changed the prefix you will need to apply any updates manually.
You can find the create table and upgrade scripts in the sql/database/upgrade folder for each database, for example the Postgres scripts can be found here.
The numbering system is straightforward, for example 35001 is the first upgrade for dbmail version 3.5. The first two digits represent the dbmail version then the next three numbers represent the incremental upgrade for that version.
Schema upgrades are identified in dbmail_upgrade_steps:
| Column | Datatype | Null | Default |
|---|---|---|---|
| from_version | integer | not null | |
| to_version | integer | not null | |
| applied | timestamp without time zone | now() |
select * from dbmail_upgrade_steps might look like the following:
| from_version | to_version | applied |
|---|---|---|
| 0 | 32001 | 2014-08-24 12:57:25 |
| 32001 | 32002 | 2014-08-24 12:57:26 |
| 32001 | 32003 | 2014-08-24 12:57:26 |
| 32001 | 32004 | 2014-08-24 12:57:44 |
| 32001 | 32005 | 2021-04-26 12:38:05 |
| 32001 | 32006 | 2022-02-06 20:28:02 |
| 32006 | 35001 | 2024-05-20 18:37:55 |
For consistency, there will always be a script even if there is nothing to do. For example version 35001 fixed an issue where the normal mysql text format utf8mb3 was insufficient to store the latest utf-8 emojis so all the tables were updated to utf8mb4. The Postgres, SQLite and Oracle upgrades note this is a mysql upgrade only and just inserts a record into the dbmail_upgrade_steps table.
Safe and happy upgrading.
Upgrading from version 2
DBMail Version 3 introduced improved storage from dbmail version 2 by separating out mime parts. These schema changes are material and all emails that were in dbmail_messageblks need to be transitioned to mime parts, you can see the current schema at /architecture/email-store and /architecture/email-cache.
The upgrade process is not reversible so please back-up your database and test before proceeding.
All the server daemons such as dbmail-imapd will run successfully before the upgrade is complete and all emails will be retrieved as normal, but information that hasn't been cached or upgraded may not be available.
To upgrade version 2 data to version 3 run dbmail-util -M, the default number of messages processed at a time is 10000, you can optionally specify the number of messages updated with -m, they're documented at /man-pages/dbmail-util. The upgrade needs to be run as many times as necessary.
dbmail-util -M -y
You can check the progress of how many message remain to be converted to the new format with the following SQL:
SELECT count(*) FROM dbmail_messageblks;
Although the upgrade should go smoothly, if the conversion barfs at invalid messages, there is a dbmail.conf variable you can set allow_invalid_messages=yes to add a text/plain header allowing an invalid message to be upgraded.
Happy upgrading
