Account mailbox database structure

Each zimbra account is associated with a mailbox group, which is based on the mailbox id number. (The mailboxId is specific to the store, the zimbraId on the other hand is system wide). You can determine the mailbox id for an account (this example account is named “b@test.test”) like this:

$ zmprov getMailboxInfo b@test.test
mailboxId: 5247
quotaUsed: 1951021

Mailbox users are members of mailbox groups on a rotation up to a maximum of 100 mailbox groups; you can determine the mailbox group by modulo division of the mailbox id by 100 (the remainder of dividing the mailbox id by 100, i.e only the last two digits). Note that if the result is zero, the mailbox group is 100, not 0.

$ expr 5247 % 100
47

Take a look at the mail_item table in the mailbox group database, particularly the date, size, sender, and subject columns.

$ mysql mboxgroup47
mysql> describe mail_item;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| mailbox_id   | int(10) unsigned    | NO   | PRI |         |       |
| id           | int(10) unsigned    | NO   | PRI |         |       |
| type         | tinyint(4)          | NO   |     |         |       |
| parent_id    | int(10) unsigned    | YES  |     | NULL    |       |
| folder_id    | int(10) unsigned    | YES  |     | NULL    |       |
| index_id     | int(10) unsigned    | YES  |     | NULL    |       |
| imap_id      | int(10) unsigned    | YES  |     | NULL    |       |
| date         | int(10) unsigned    | NO   |     |         |       |
| size         | int(10) unsigned    | NO   |     |         |       |
| volume_id    | tinyint(3) unsigned | YES  | MUL | NULL    |       |
| blob_digest  | varchar(28)         | YES  |     | NULL    |       |
| unread       | int(10) unsigned    | YES  |     | NULL    |       |
| flags        | int(11)             | NO   |     | 0       |       |
| tags         | bigint(20)          | NO   |     | 0       |       |
| sender       | varchar(128)        | YES  |     | NULL    |       |
| subject      | text                | YES  |     | NULL    |       |
| name         | varchar(128)        | YES  |     | NULL    |       |
| metadata     | text                | YES  |     | NULL    |       |
| mod_metadata | int(10) unsigned    | NO   |     |         |       |
| change_date  | int(10) unsigned    | YES  |     | NULL    |       |
| mod_content  | int(10) unsigned    | NO   |     |         |       |
+--------------+---------------------+------+-----+---------+-------+

Let’s examine a single mail item.

mysql> select * from mail_item where id>26000 and mailbox_id=5247 limit 1 \G
*************************** 1. row ***************************
  mailbox_id: 5247
          id: 26001
        type: 5
   parent_id: NULL
   folder_id: 2
    index_id: 26001
     imap_id: 26001
        date: 1182380903
        size: 95842
   volume_id: 1
 blob_digest: rbrw+fj0tvyvTPt2haxssued7,A=
      unread: 1
       flags: 2
        tags: 0
      sender: sender@domain.com
     subject: Message subject
        name: NULL
    metadata: d1:f147:This message...1:p8:Re: SF: 1:s21:sender@domain.com1:vi10ee
mod_metadata: 30102
 change_date: 1182380905
 mod_content: 30102
1 row in set (0.00 sec)

With the volume_id, mailbox_id, id, and mod_content fields, you can determine the location of the message “blob” file. Volume 1 is the default message store:

mysql> select * from zimbra.volume;
+----+------+----------+-------------------+-----------+-----------------+--------------+--------------------+----------------+-----------------------+
| id | type | name     | path              | file_bits | file_group_bits | mailbox_bits | mailbox_group_bits | compress_blobs | compression_threshold |
+----+------+----------+-------------------+-----------+-----------------+--------------+--------------------+----------------+-----------------------+
|  1 |    1 | message1 | /opt/zimbra/store |        12 |               8 |           12 |                  8 |              0 |                  4096 |
|  2 |   10 | index1   | /opt/zimbra/index |        12 |               8 |           12 |                  8 |              0 |                  4096 |
+----+------+----------+-------------------+-----------+-----------------+--------------+--------------------+----------------+-----------------------+

On the filesystem, the user directories and the message directories within each user directory are split so that there are a maximum of 4096 (that is, 2¹²) files in each directory. To determine the appropriate directory “hash” numbers, bitshift the mailbox_id and the mail item id to the right by 12 bits (i.e. divide by 2¹², rounding down). It is significant to note that if the id is less than 4096, the hash number will be 0.

$ perl -e 'print 5247 >> 12 ; print "\n"'
1
$ perl -e 'print 26001 >> 12 ; print "\n"'
6

The mod_content field is used to keep track of message blob file revisions. If a blob file is ever updated (for example, if a calendar appointment is modified), the filename and this field are updated. So, if the user with mailbox_id 5427 has a message on volume 1 with id 26001 and mod_content 30102, we’ll see it like this:

$ ls -l /opt/zimbra/store/1/5247/msg/6/26001-30102.msg
-rw-r-----  1 zimbra zimbra 95842 Jun 20 16:08 /opt/zimbra/store/1/5247/msg/6/26001-30102.msg

Assuming only the default message store is in use, the filename for an item may be derived with a query.

mysql> select id, 
    concat('/opt/zimbra/store/', (mailbox_id >> 12), '/', mailbox_id, '/msg/',
    (id >> 12), '/', id, '-', mod_metadata, '.msg') as file
    from mail_item where mailbox_id="723" limit 1;
+-----+-------------------------------------------+
| id  | file                                      |
+-----+-------------------------------------------+
| 261 | /opt/zimbra/store/0/723/msg/0/261-103.msg |
+-----+-------------------------------------------+