Account mailbox database structure: Difference between revisions
No edit summary |
No edit summary |
||
Line 104: | Line 104: | ||
concat('/opt/zimbra/store/', (mailbox_id >> 12), '/', mailbox_id, '/msg/', | concat('/opt/zimbra/store/', (mailbox_id >> 12), '/', mailbox_id, '/msg/', | ||
(id >> 12), '/', id, '-', mod_content, '.msg') as file | (id >> 12), '/', id, '-', mod_content, '.msg') as file | ||
from mail_item where mailbox_id="723" limit 1; | from mail_item where mailbox_id="723" and id="261" limit 1; | ||
+-----+-------------------------------------------+ | +-----+-------------------------------------------+ | ||
| id | file | | | id | file | | ||
Line 111: | Line 111: | ||
+-----+-------------------------------------------+ | +-----+-------------------------------------------+ | ||
</pre> | </pre> | ||
--NOTICE---, you must specify the mailbox_id, id and mod_content (these are placed in concat function) by hand and the mailbox_id, id which are placed in where condition are the mailbox_id and id you want to search; | |||
--FIXME--, I had problems with ID's greater than 1048575 (which is 1024*1024 -1) for mail_items. It seems the calculation for the mail_item.id only allows upto 255 directories below the /msg/ directory. | --FIXME--, I had problems with ID's greater than 1048575 (which is 1024*1024 -1) for mail_items. It seems the calculation for the mail_item.id only allows upto 255 directories below the /msg/ directory. | ||
Line 137: | Line 138: | ||
{{Article Footer|unknown|7/12/2007}} | {{Article Footer|unknown|7/12/2007}} | ||
{{Dalmate|Modified|11/09/2007}} | |||
[[Category: Troubleshooting Mailbox]] | [[Category: Troubleshooting Mailbox]] | ||
[[Category: MySQL]] | [[Category: MySQL]] | ||
[[Category: Database]] | [[Category: Database]] |
Revision as of 04:25, 9 November 2010
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, 212) 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 212, 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_content, '.msg') as file from mail_item where mailbox_id="723" and id="261" limit 1; +-----+-------------------------------------------+ | id | file | +-----+-------------------------------------------+ | 261 | /opt/zimbra/store/0/723/msg/0/261-103.msg | +-----+-------------------------------------------+
--NOTICE---, you must specify the mailbox_id, id and mod_content (these are placed in concat function) by hand and the mailbox_id, id which are placed in where condition are the mailbox_id and id you want to search;
--FIXME--, I had problems with ID's greater than 1048575 (which is 1024*1024 -1) for mail_items. It seems the calculation for the mail_item.id only allows upto 255 directories below the /msg/ directory. I had to modify the query like this to get correct values for the greater id's:
mysql> select id, concat('/opt/zimbra/store/', (mailbox_id >> 12), '/', mailbox_id, '/msg/', (id - (floor(id / 1048575) * 1048575) >> 12), '/', id, '-', mod_content, '.msg') as file from mail_item where mailbox_id="723" limit 1;
The floor(id / 1048575) * 1048575 part is for:
if id < 1048575, this evaluates to 0, so nothing happens
if id > 1048575, 1048575 will be subtracted
if id > 2097150, 2097150 will be subtracted
and so on, so you will have always a value from 1 to at most 1048575 for the bitshifting, which will result in the correct directory, at least for me in my data.
If someone has more insight please correct if it's not correct. Best Ray