Zimbra

chandu · #1 (**permalink**) 03-31-2010, 05:06 AM

Hi Guys,

I am not expert in mysql so would like to understand zimbra mysql architecure.

As per forum and wiki, zimbra mysql is getting used for below mapping :

- What folder is the message in
- What tags, conversations, and read/unread status is associated with the message
- Calendar schedule
- Personal contacts

I would like to understand what exactly changes happened in mysql database while creating and deleting any mailbox ?

How mysql is connected to LDAP / store and how its getting sync ?
I checked the details for abc@lab1.com as below :

################################################## ####################################

[zimbra@mail1 ~]$ zmprov gmi abc@lab1.com
mailboxId: 47
quotaUsed: 2
[zimbra@mail1 ~]$ zmprov ga abc@lab1.com zimbraMailHost
# name abc@lab1.com
zimbraMailHost: mail1.test.com

[zimbra@mail1 ~]$ mysql -e 'SELECT * FROM zimbra.mailbox WHERE id=47\G'
*************************** 1. row ***************************
id: 47
group_id: 47
account_id: 3d69cad5-bf16-490d-adf5-645fb660cc88
index_volume_id: 2
item_id_checkpoint: 279
contact_count: 0
size_checkpoint: 2
change_checkpoint: 1100
tracking_sync: 0
tracking_imap: 1
last_backup_at: NULL
comment: abc@lab1.com
last_soap_access: 0
new_messages: 5
idx_deferred_count: 5
highest_indexed: 0
[zimbra@mail1 ~]$

################################################## #################################

Here ID is 47. IS it realted to mboxgroup47 database ??

Is there link where i can get zimbra mysql architecture details ?

I have refered below link :

Ajcody-Mysql-Topics - Zimbra :: Wiki

Please help.

Thanks

uxbod · #2 (**permalink**) 03-31-2010, 05:32 AM

Yes that would be mboxgroup47.

chandu · #3 (**permalink**) 03-31-2010, 05:46 AM

Thanks for the reply Uxbod...

Then i deleted abc@lab1.com but still mboxgroup47 is there in mysql.
Why its not doing houskeeping in mysql if we delete any account from admin gui ?

And I found we have more than 2000 accounts in production but number of
mboxgroup databases seems less than the actual count of mailbox ID. it should be same na ?

Please help me to understand.

Thanks

uxbod · #4 (**permalink**) 03-31-2010, 06:30 AM

The mboxgroup<id> will be created when the user first connects to their account. When a account is deleted the contents of the tables within the mboxgroup<id> are removed; but the database/tables are not dropped. As the ID can be used again at a later day my assumption this approach has been adopted to reduce server over-head in the deletion/creation of databases.

chandu · #5 (**permalink**) 03-31-2010, 07:01 AM

Thanks for the details.

One more query ...

You meantioned "The mboxgroup<id> will be created when the user first connects to their account. "

It means when user actually login into his mailbox then only it will get create ...is it so ?

We have some of the emal IDs which are not getting used by users ...those are only getting used to send application notification. no one access these IDs. It means if such account recived any mails and if no one will login into this then mboxgroup<id> wont get create for these IDs...am i correct ?

uxbod · #6 (**permalink**) 03-31-2010, 07:04 AM

AFAIK if that account were to receive a email then the necessary mboxgroup<ID> and tables would be created.

chandu · #7 (**permalink**) 03-31-2010, 10:48 PM

Hi,

I created abc@lab1.com and found the ID is 230 but mboxgroup ID not got created under mysql for this ID.
Please have a look on below output :

################################################## #

[zimbra@mail1 log]$ zmprov gmi abc@lab1.com
mailboxId: 230
quotaUsed: 2234
[zimbra@mail1 log]$

mysql> select * from mailbox where id=230;
+-----+----------+--------------------------------------+-----------------+--------------------+---------------+-----------------+-------------------+---------------+---------------+----------------+---------------+------------------+--------------+--------------------+-----------------+
| id | group_id | account_id | index_volume_id | item_id_checkpoint | contact_count | size_checkpoint | change_checkpoint | tracking_sync | tracking_imap | last_backup_at | comment | last_soap_access | new_messages | idx_deferred_count | highest_indexed |
+-----+----------+--------------------------------------+-----------------+--------------------+---------------+-----------------+-------------------+---------------+---------------+----------------+---------------+------------------+--------------+--------------------+-----------------+
| 230 | 30 | a4af2cf5-c903-4787-aa04-c94743ab2c12 | 2 | 259 | 1 | 1754 | 16 | 0 | 0 | NULL | abc@lab1.com | 1270099444 | 0 | 1 | 14-258 |
+-----+----------+--------------------------------------+-----------------+--------------------+---------------+-----------------+-------------------+---------------+---------------+----------------+---------------+------------------+--------------+--------------------+-----------------+
1 row in set (0.00 sec)

mysql> desc new_messages;
ERROR 1146 (42S02): Table 'zimbra.new_messages' doesn't exist
mysql> desc mailbox_metadata;
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| mailbox_id | int(10) unsigned | NO | PRI | NULL | |
| section | varchar(64) | NO | PRI | NULL | |
| metadata | mediumtext | YES | | NULL | |
+------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from mailbox_metadata;
+------------+------------------+--------------------------------------------------------------------+
| mailbox_id | section | metadata |
+------------+------------------+--------------------------------------------------------------------+
| 1 | ver | d1:vi10e4:vmaji1e4:vmini8ee |
| 2 | ver | d1:vi10e4:vmaji1e4:vmini8ee |
| 4 | ver | d1:vi10e4:vmaji1e4:vmini8ee |
| 6 | ver | d1:vi10e4:vmaji1e4:vmini8ee |
| 9 | ver | d1:vi10e4:vmaji1e4:vmini8ee |
| 16 | ver | d1:vi10e4:vmaji1e4:vmini8ee |
| 17 | ver | d1:vi10e4:vmaji1e4:vmini8ee |
| 52 | ver | d1:vi10e4:vmaji1e4:vmini8ee |
| 86 | ver | d1:vi10e4:vmaji1e4:vmini8ee |
| 197 | CONTACT_RANKINGS | d16:kanchan@lab1.comd1:n0:1

i-1e1:ri1e1:ti1270041426789ee1:vi10ee |
| 197 | ver | d1:vi10e4:vmaji1e4:vmini8ee |
| 229 | ver | d1:vi10e4:vmaji1e4:vmini8ee |
| 230 | CONTACT_RANKINGS | d15:common@lab1.comd1:n0:1

uxbod · #8 (**permalink**) 04-01-2010, 12:50 AM

It is because when I said ID I meant group ID so mboxgroup30 has been created.

chandu · #9 (**permalink**) 05-10-2010, 07:33 AM

Hi ,

I am getting bellow listed Error while sending mail from test@abc.com to test@def.com

An object with that name already exists.
method: SendMsgRequest
msg: object with that id already exists: 4820
code: mail.ALREADY_EXISTS
detail: soap:Sender
trace: btpool0-9://10.11.24.4:8100/service/soap/SendMsgRequest:1273500850585:9c32045cbe15380f
request:

Body: {
SendMsgRequest: {
_jsns: "urn:zimbraMail",
m: {
e: [
0: {
a: "test@def.com",
t: "t"
},
1: {
a: "test@abc.com",
t: "f"
}
],
idnt: "433cc955-4f6f-42e4-b887-89dc6c432295",
mp: [
0: {
content: {
_content: ""
},
ct: "text/plain"
}
],
su: {
_content: "test@7:36"
}
},
suid: 1273500421875
}
},
Header: {
context: {
_jsns: "urn:zimbra",
account: {
_content: "test@abc.com",
by: "name"
},
authToken: "(removed)",
session: {
_content: 151,
id: 151
},
userAgent: {
name: "ZimbraWebClient - FF3.0 (Win)",
version: "6.0.6_GA_2324.RHEL5_64"
}
}
}

But i found the id 4820 is belongs to some other mail user test@example.com ( as per Mailboxd table in Mysql) .
Could Please tell me how the mails are getting stored in zimbra store (/opt/zimbra/store) and i can able to see lot of directories starting from 1 at zimbra store . how zimbra is naming those dictories ? Is it based on Mail id ?

Zimbra

Downloads

Product Information

Toolbox

Why Join?