Zimbra database structure for mailbox

In this article we are going to analyze how the zimbra database is structured for the management of mailbox accounts.

WARNING: In this article we are going to work directly on Database. Be sure to manage it carefully and to be aware of what you are doing

How it works

In Zimbra, mail header information, contacts, calendar items, and tasks are stored using mysql database. An account is automatically assigned to a mailbox group after its creation.

The mailbox group matches a database located in folder /opt/zimbra/db/data. There is a limit of 100 mailbox groups per server.

The mailbox group for the zimbra account is identified by the mailbox id number. It is specific to the store, while the ZimbraID is system level. To determine this id for a mail account, you can use the following command:

$ zmprov getMailboxInfo user@sampledomain.test
mailboxId: 1234
quotaUsed: 1234567

To establish the mailbox group, since the mailbox users are members of groups on a rotation up to a maximum of 100 mb groups, you can use modulo division of mailbox id by 100 (eg. the last two digits representing the remainder of dividing the mailbox id by 100). Here is an example:

$ expr 1234 % 100

34

Note that if the result is zero, the mailbox group is 100, not 0.

Now, to better understand the structure of mail_item table in the mailbox group database, let’s have a look at the table below from mysql:

$ mysql mboxgroup34
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   |     |         |       |
+--------------+---------------------+------+-----+---------+-------+

A Practical Example

Let’s see now a practical example, with a single mail item, to better understand how to interact with DB. We are going to start with a “select” query:

TIP: To find the “id” of a single mail in a very easy way, you can simply right click on the item in your webclient, and select “Show Original” form the pop up menu. In the window that opens, you will find the ID at the end of the url that appears

mysql> select * from mail_item where id>15000 and mailbox_id=1234 limit 1 \G
*************************** 1. row ***************************
  mailbox_id: 1234
          id: 15001
        type: 5
   parent_id: NULL
   folder_id: 2
    index_id: 15001
     imap_id: 15001
        date: 1282680702
        size: 65974
     locator: 1
 blob_digest: rbrw+fj0tvyvTPt2haxssued7,A=
      unread: 1
       flags: 2
        tags: 0
      sender: sender@sampledomain.com
     subject: Message subject
        name: NULL
    metadata: d1:f147:This message...1:p8:Re: SF: 1:s21:sender@sampledomain.com1:vi10ee
mod_metadata: 30102
 change_date: 1282680705
 mod_content: 20301
1 row in set (0.00 sec)

The location of the message Blob file can be determined with the locator (volume_id in versions older than 8.8), mailbox_id, id, and mod_content fields. 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 |
+----+------+----------+-------------------+-----------+-----------------+--------------+--------------------+----------------+-----------------------+

The user and the message directories within each user directory on the filesystem are split to have a limit of 4096 files in each one.

Note that if the id is less than 4096, the hash number will be 0.

To keep track of message blob file revisions, the mod_content field is used. When a blob file is updated, at the same time also the filename and this specific field are updated too. So for example, considering user with mailbox id 1234, that has a message on volume 1 with id 15001 and mod_content 20301, we will see something like this

$ ls -l /opt/zimbra/store/1/1234/msg/6/15001-20301.msg
-rw-r-----  1 zimbra zimbra 65974 Mar 03 15:58 /opt/zimbra/store/1/1234/msg/6/15001-20301.msg

Now, if we suppose that only the default message store is in use, we can get the filename for an item with a query:

mysql> select id, 
    concat('/opt/zimbra/store/', (mailbox_id >> 12), '/', mailbox_id, '/msg/',
    (id >> 12), '/', id, '-', mod_content, '.msg') as file
    from mail_item where mailbox_id="123" and id="456" limit 1;

With a result like this:

+-----+-------------------------------------------+
| id  | file                                      |
+-----+-------------------------------------------+
| 456 | /opt/zimbra/store/0/123/msg/0/456-305.msg |
+-----+-------------------------------------------+

In this case you have to specify manually the mailbox_id, the id and the mod_content to be placed in concat function. Likewise, you have to do that for the mailbox_id and id that you want to search.

Note:

It is know that you may experience problems with IDs greater than “1048575”. In this situation you can change the previous query as follows:

mysql> select id, 
    concat('/opt/zimbra/store/', (mailbox_id >> 12), '/', mailbox_id, '/msg/',
    (id % (1024*1024) >> 12), '/', id, '-', mod_content, '.msg') as file
    from mail_item where mailbox_id="123" limit 1;

Simply using the mod operator to avoid the problem.

Technical writer at Zextras. Linux and technology enthusiast. He describes himself as an atypical and polyhedric IT expert, thanks to a creative and open minded attitude, and to the experience gained over time as an IT technician, system engineer and developer.

Post your comment