Upgrading
Upgrading is automatic and covers the majority of users. If you have a large or complex system then this guide will help you upgrade manually.
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, Oracle 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.
