Results 1 to 9 of 9

Thread: Zimbra mysql query

  1. #1
    Join Date
    Dec 2007
    Posts
    445
    Rep Power
    7

    Default Zimbra mysql query

    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

  2. #2
    Join Date
    Nov 2006
    Location
    UK
    Posts
    8,017
    Rep Power
    25

    Default

    Yes that would be mboxgroup47.

  3. #3
    Join Date
    Dec 2007
    Posts
    445
    Rep Power
    7

    Default

    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

  4. #4
    Join Date
    Nov 2006
    Location
    UK
    Posts
    8,017
    Rep Power
    25

    Default

    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.

  5. #5
    Join Date
    Dec 2007
    Posts
    445
    Rep Power
    7

    Default

    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 ?

  6. #6
    Join Date
    Nov 2006
    Location
    UK
    Posts
    8,017
    Rep Power
    25

    Default

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

  7. #7
    Join Date
    Dec 2007
    Posts
    445
    Rep Power
    7

    Default

    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:1i-1e1:ri1e1:ti1270041426789ee1:vi10ee |
    | 197 | ver | d1:vi10e4:vmaji1e4:vmini8ee |
    | 229 | ver | d1:vi10e4:vmaji1e4:vmini8ee |
    | 230 | CONTACT_RANKINGS | d15:common@lab1.comd1:n0:1i-1e1:ri1e1:ti1270099792899ee1:vi10ee |
    | 230 | ver | d1:vi10e4:vmaji1e4:vmini8ee |
    +------------+------------------+--------------------------------------------------------------------+
    14 rows in set (0.00 sec)



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


    Have a look on show databases :

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

    mysql> show databases;
    +--------------------+
    | Database |
    +--------------------+
    | information_schema |
    | master1 |
    | master2 |
    | mboxgroup1 |
    | mboxgroup10 |
    | mboxgroup100 |
    | mboxgroup11 |
    | mboxgroup12 |
    | mboxgroup13 |
    | mboxgroup14 |
    | mboxgroup15 |
    | mboxgroup16 |
    | mboxgroup17 |
    | mboxgroup18 |
    | mboxgroup19 |
    | mboxgroup2 |
    | mboxgroup20 |
    | mboxgroup21 |
    | mboxgroup22 |
    | mboxgroup23 |
    | mboxgroup24 |
    | mboxgroup25 |
    | mboxgroup26 |
    | mboxgroup27 |
    | mboxgroup28 |
    | mboxgroup29 |
    | mboxgroup3 |
    | mboxgroup30 |
    | mboxgroup31 |
    | mboxgroup32 |
    | mboxgroup33 |
    | mboxgroup34 |
    | mboxgroup35 |
    | mboxgroup36 |
    | mboxgroup37 |
    | mboxgroup38 |
    | mboxgroup39 |
    | mboxgroup4 |
    | mboxgroup40 |
    | mboxgroup41 |
    | mboxgroup42 |
    | mboxgroup43 |
    | mboxgroup44 |
    | mboxgroup45 |
    | mboxgroup46 |
    | mboxgroup47 |
    | mboxgroup48 |
    | mboxgroup49 |
    | mboxgroup5 |
    | mboxgroup50 |
    | mboxgroup51 |
    | mboxgroup52 |
    | mboxgroup53 |
    | mboxgroup54 |
    | mboxgroup55 |
    | mboxgroup56 |
    | mboxgroup57 |
    | mboxgroup58 |
    | mboxgroup59 |
    | mboxgroup6 |
    | mboxgroup60 |
    | mboxgroup61 |
    | mboxgroup62 |
    | mboxgroup63 |
    | mboxgroup64 |
    | mboxgroup65 |
    | mboxgroup66 |
    | mboxgroup67 |
    | mboxgroup68 |
    | mboxgroup69 |
    | mboxgroup7 |
    | mboxgroup70 |
    | mboxgroup71 |
    | mboxgroup72 |
    | mboxgroup73 |
    | mboxgroup74 |
    | mboxgroup75 |
    | mboxgroup76 |
    | mboxgroup77 |
    | mboxgroup78 |
    | mboxgroup79 |
    | mboxgroup8 |
    | mboxgroup80 |
    | mboxgroup81 |
    | mboxgroup82 |
    | mboxgroup83 |
    | mboxgroup84 |
    | mboxgroup85 |
    | mboxgroup86 |
    | mboxgroup87 |
    | mboxgroup88 |
    | mboxgroup89 |
    | mboxgroup9 |
    | mboxgroup90 |
    | mboxgroup91 |
    | mboxgroup92 |
    | mboxgroup93 |
    | mboxgroup94 |
    | mboxgroup95 |
    | mboxgroup96 |
    | mboxgroup97 |
    | mboxgroup98 |
    | mboxgroup99 |
    | mysql |
    | t4 |
    | test |
    | zimbra |
    +--------------------+
    107 rows in set (0.00 sec)

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


    Can anyone please tell me why mboxgroup230 not got created ?

    Thanks

  8. #8
    Join Date
    Nov 2006
    Location
    UK
    Posts
    8,017
    Rep Power
    25

    Default

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

  9. #9
    Join Date
    Dec 2007
    Posts
    445
    Rep Power
    7

    Default

    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 ?

Similar Threads

  1. Upgrade to ZCS 5.10
    By blozancic in forum Installation
    Replies: 0
    Last Post: 10-21-2008, 09:03 AM
  2. Zimbra spam system
    By rajahd in forum Administrators
    Replies: 9
    Last Post: 04-16-2008, 08:25 PM
  3. Big Fubar on 5 FOSS GA Upgrade
    By uxbod in forum Administrators
    Replies: 24
    Last Post: 01-21-2008, 03:37 AM
  4. dspam logrotate errors
    By michaeln in forum Users
    Replies: 7
    Last Post: 02-19-2007, 12:45 PM
  5. Zimbra server crashed
    By goetzi in forum Administrators
    Replies: 6
    Last Post: 03-25-2006, 01:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •