Ajcody-Mysql-Topics

Attention.png - This article is NOT official Zimbra documentation. It is a user contribution and may include unsupported customizations, references, suggestions, or information.

Mysql

Actual Mysql Topics Homepage

Please see Ajcody-Mysql-Topics

Mysql Issues And Memory Usuage

My Ajcody-General-Notes#Trouble_Shooting_Memory_Issues has some stuff on memory troubleshooting - generalities.

Our performance guide for Mysql:

http://wiki.zimbra.com/index.php?title=Performance_Tuning_Guidelines_for_Large_Deployments#MySQL

Variable Within MySQL & Zimbra

These percentages below are used against physical RAM - disregard swap in the percentage division.

From the above url, we get some details.

"By default, we try to reserve 30% of system memory for use by this process, and 40% for use by MySQL."
# ZCS 5.0 and later
$ zmlocalconfig -e mailboxd_java_heap_memory_percent=40
Java - mailboxd_java_heap_memory_percent=

The command to set the java percentage is:

 zmlocalconfig -e mailboxd_java_heap_memory_percent=30
Mysql - mysql_memory_percent & my.cnf w/ innodb_buffer_pool_size

Looks like there's an issue with the command/variable (zmlocalconfig -e mysql_memory_percent=##) in regards to MySQL though.

"There is a local config variable [mysql_memory_percent] for mysql memory percent, but today my.cnf doesn't get rewritten after install, so you have to edit my.cnf for this setting if you want to change it."

In summary, this variable [ mysql_memory_percent ] for MySQL is basically not working.

Do this to get memory in bytes for division numbers:

free -b

Figure out what your % in bytes using the physical memory (Mem:) output. Now edit the /opt/zimbra/conf/my.cnf file to change the following line with your memory percentage in bytes:

vi /opt/zimbra/conf/my.cnf
#Modify this var to adjust memory usage
innodb_buffer_pool_size = xxx

This will require a restart.

A GREAT forum post on this is here:

So other background info:

Mysql And Swap

See this article first before exploring the options below.

Should you have your swap file enabled while running MySQL ?

Set swappiness Kernel Var To 0

Source of this summary is here: [http://www.mysqlperformanceblog.com/2007/09/28/heikki-tuuri-to-answer-your-in-depth-innodb-questions/#comment-174442 Swappiness To 0"

Set it on running box:

echo 0 > /proc/sys/vm/swappiness

Set it to persist after reboot - vi /etc/sysctl.conf :

vm.swappiness = 0

The value is a percentage. By setting it to 100, Linux will always swap programs out and never shrink the buffer cache.

Swap Ram Disk Instead Of Disk

Source of this summary is here:

MySQL and the Linux swap problem

Make a (or some) swap disks:

mkdir /mnt/ram0
dd bs=1024 count=14634 if=/dev/zero of=/mnt/ram0/swapfile
mkswap /mnt/ram0/swapfile
swapon /mnt/ram0/swapfile

Will look like this:

[root@mail3 ~]# mkdir /mnt/ram0

[root@mail3 ~]# dd bs=1024 count=14634 if=/dev/zero of=/mnt/ram0/swapfile
14634+0 records in
14634+0 records out
14985216 bytes (15 MB) copied, 0.246329 seconds, 60.8 MB/s

[root@mail3 ~]# mkswap /mnt/ram0/swapfile 
Setting up swapspace version 1, size = 14979 kB

[root@mail3 ~]# free
             total       used       free     shared    buffers     cached
Mem:       1555472    1461200      94272          0     159400     344328
-/+ buffers/cache:     957472     598000
Swap:      2031608        104    2031504

[root@mail3 ~]# swapon /mnt/ram0/swapfile 

[root@mail3 ~]# free
             total       used       free     shared    buffers     cached
Mem:       1555472    1476252      79220          0     159404     344376
-/+ buffers/cache:     972472     583000
Swap:      2046232        104    2046128

[root@mail3 ~]# swapon -s
Filename				Type		Size	Used	Priority
/dev/mapper/VolGroup00-LogVol01         partition	2031608	104	-1
/mnt/ram0/swapfile 
                     file		14624	0	-2
[root@mail3 ~]# cat /proc/swaps 
Filename				Type		Size	Used	Priority
/dev/mapper/VolGroup00-LogVol01         partition	2031608	104	-1
/mnt/ram0/swapfile                      file		14624	0	-2


Just add those lines to your relevant startup file, like /etc/rc.d/rc.local, and it’ll persist after reboots.

O_DIRECT On Linux And INNODB To Fix Swapping

Source reference for this part is here:

Using O_DIRECT on Linux and INNODB to Fix Swap Insanity

See also: Ajcody-Mysql-Topics#IO_DIRECT_And_Ext3_On_Linux_As_Possible_Reason_For_Corruption

IO_DIRECT And Ext3 On Linux As Possible Reason For Corruption

This is being investigated. Below are my notes on the issue.

Mysql Database Corruption/Repair/Checks

Please see the Mysql_Crash_Recovery page rather than the normal search results that give mysql-logger details.

Mysql Table Checks

Mysql Table Types - InnoDB or MyISAM

Tables are either InnoDB or MyISAM. Putting this information here because if a table is in need of repair or something, the steps to fix it might be different depending on the tables ENGINE type.

Example for MyISAM:

$ mysql -e "show create table zimbra.jiveRoster"
 [cut]
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 
 [cut]

Example for InnoDB:

$ mysql -e "show create table zimbra.mailbox"
 [cut]
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
 [cut]
check zimbra.mailbox database tables
$ mysql -e "check table zimbra.mailbox"
+----------------+-------+----------+----------+
| Table          | Op    | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| zimbra.mailbox | check | status   | OK       | 
+----------------+-------+----------+----------+
check mboxgroup[#].mail_item table
$ mysql -e "check table mboxgroup3.mail_item"
+----------------------+-------+----------+----------+
| Table                | Op    | Msg_type | Msg_text |
+----------------------+-------+----------+----------+
| mboxgroup3.mail_item | check | status   | OK       | 
+----------------------+-------+----------+----------+
jive.Roster or zimbra.jiveUserProp Errors From Daily Report With mysqlcheck Errors
Please note, these tables are MyISAM. Don't use the below steps for other type of tables as each table might be treated differently or with extra caution. [InnoDB vs MyISAM]

Your report from /opt/zimbra/libexec/zmdbintegrityreport might give the following for example:


Database errors found.
/opt/zimbra/mysql/bin/mysqlcheck --defaults-file=/opt/zimbra/conf/my.cnf -S /opt/zimbra/db/mysql.sock
 -A -C -s -u root --password=[mysql password]
zimbra.jiveID
 warning  : 1 client is using or hasn't closed the table properly  zimbra.jiveRoster  warning  :
  1 client is using or hasn't closed the table properly  zimbra.jiveUserProp
 warning  : 2 clients are using or haven't closed the table properly

Attempt the following:

mysql -e "check table zimbra.jiveRoster" 
+-------------------+-------+----------+----------+ 
| Table             | Op    | Msg_type | Msg_text | 
+-------------------+-------+----------+----------+ 
| zimbra.jiveRoster | check | status   | OK       |  
+-------------------+-------+----------+----------+

To see if the data "looks" fine or works correctly against a select, do:

mysql -e "select * from zimbra.jiveRoster"

Note, if the above command might dump out a lot of data, try:

mysql -e "select * from zimbra.jiveRoster" > /tmp/zimbra.jiveRoster.out

Yours might show something different for Msg_text. If you need to repair, you would run:

mysql -e "repair table zimbra.jiveRoster"

You can also do the same above but replacing zimbra.jiveRoster with zimbra.jiveUserProp .

Manual Change Of Mysql Passwords

Please see:

http://wiki.zimbra.com/index.php?title=Issues_with_mysql_and_logmysql_passwords

Working Within Mysql For Accounts

Note, the mailboxId is specific to the mailstore the user resides on. The zimbraId is the users unique id system wide - zmprov ga user@domain.com zimbraId .

Other References:

Getting User Variables We Need To Query MySQL

Getting The Users mailboxId # - zmprov gmi user@domain

For Mysql, this # returned below will become apart of mailbox_id=### . Example used is mailbox_id=3 .

Note, the mailboxId is specific to the mailstore the user resides on. The zimbraId is the users unique id system wide - zmprov ga user@domain.com zimbraId .
[root@mail39 ~]# su - zimbra
[zimbra@mail39 ~]$ zmprov gmi ajcody@mail39.zimbra.DOMAIN.com
  mailboxId: 3
  quotaUsed: 169831
Getting The Users Mailstore That Their DB Resides On - zmprov ga user@domain zimbraMailHost

The mailboxId found above for the user is unique to their actual mailstore and the db that resides there. Again:

Note, the mailboxId is specific to the mailstore the user resides on. The zimbraId is the users unique id system wide - zmprov ga user@domain.com zimbraId .

To get their particular mailstore their database resides on:

$ zmprov ga ajcody@mail39.zimbra.DOMAIN.com zimbraMailHost
# name ajcody@mail39.zimbra.DOMAIN.com
zimbraMailHost: mail39.zimbra.DOMAIN.com

So you would now log onto that mailstore to do your mysql operations (searches).

Confirm zimbra_mailbox_groups Variable

Need to confirm zimbra_mailbox_groups variable to get the users mboxgroup# since we'll then need to run the expression below to determine it.

[zimbra@mail39 ~]$ zmlocalconfig zimbra_mailbox_groups
zimbra_mailbox_groups = 100
The Mysql group_id Value

The group_id is controlled by a local configuration value. It defaults to 100.

 $ zmlocalconfig zimbra_mailbox_groups
   zimbra_mailbox_groups = 100

You'll see group_id in the following:

 $ mysql -e 'SELECT * FROM zimbra.mailbox WHERE id=3\G'

 *************************** 1. row ***************************
                 id: 3
           group_id: 3

*** CUT REST OF OUTPUT ***
Get Users mboxgroup #

Finally confirming what the mboxgroup is for the user by running the following below. The mboxgroup is comprised of multiple users that are unique by way of their mailbox_id number. This is to minimize the number of db's that are needed on the mailstore, rather than doing one db per user.

3 ='s the users mailboxId
100 ='s the variable from zimbra_mailbox_groups

For Mysql, this # returned below will become apart of mboxgroup###.mail_item = mboxgroup3.mail_item

 
[zimbra@mail39 ~]$ expr 3 % 100
3

Queries Against Mysql For User Information In The zimbra DB And mailbox Table - zimbra.mailbox

By mailboxId

You'll need to know the mailboxId for the user for this. [ zmprov gmi USER@DOMAIN.com ]

Format:

$ mysql -e 'SELECT * FROM zimbra.mailbox WHERE id=<mailboxID of USER>\G'

Working example:

 $ mysql -e 'SELECT * FROM zimbra.mailbox WHERE id=3\G'

 *************************** 1. row ***************************
                 id: 3
           group_id: 3
         account_id: 3be48432-926c-4a54-bd66-3b16185a37a4
    index_volume_id: 2
 item_id_checkpoint: 339
      contact_count: 5
    size_checkpoint: 35912
  change_checkpoint: 7200
      tracking_sync: 0
      tracking_imap: 0
     last_backup_at: 1231567229
            comment: ajcody@zimbra.DOMAIN.com
   last_soap_access: 1229584283
       new_messages: 1
 idx_deferred_count: 0
By zimbraId

You'll need to know the zimbraId for the user for this.

Via zmprov:

$ zmprov ga ajcody@zimbra.DOMAIN.com zimbraId
  # name ajcody@zimbra.DOMAIN.com
  zimbraId: 3be48432-926c-4a54-bd66-3b16185a37a4

Or ldap:

$ su - zimbra
$ source ~/bin/zmshutil
$ zmsetvars
$ ldapsearch -LLL -x -H $ldap_master_url -D $zimbra_ldap_userdn -w $zimbra_ldap_password "mail=ajcody@zimbra.DOMAIN.com" zimbraId
   dn: uid=ajcody,ou=people,dc=zimbra,dc=DOMAIN,dc=com
   zimbraId: 3be48432-926c-4a54-bd66-3b16185a37a4

Format:

$ mysql -e 'SELECT * FROM zimbra.mailbox WHERE account_id=<zimbraID of USER>\G'

Working example:

 $ mysql -e 'SELECT * FROM zimbra.mailbox WHERE account_id="3be48432-926c-4a54-bd66-3b16185a37a4"\G'

 *************************** 1. row ***************************
                 id: 3
           group_id: 3
         account_id: 3be48432-926c-4a54-bd66-3b16185a37a4
    index_volume_id: 2
 item_id_checkpoint: 339
      contact_count: 5
    size_checkpoint: 35912
  change_checkpoint: 7200
      tracking_sync: 0
      tracking_imap: 0
     last_backup_at: 1231567229
            comment: ajcody@zimbra.DOMAIN.com
   last_soap_access: 1229584283
       new_messages: 1
 idx_deferred_count: 0
By Comment Field - I.E. Username

This way searches against the "comment field". The comment field is mostly correct, but it is deemed unreliable since there are some corner cases where it can be duplicated. Use it in a pinch.

Format:

$ mysql -e 'SELECT * FROM zimbra.mailbox WHERE comment LIKE "<USER>%"\G'

Working example:

 $ mysql -e 'SELECT * FROM zimbra.mailbox WHERE comment LIKE "ajcody%"\G'

 *************************** 1. row ***************************
                 id: 3
           group_id: 3
         account_id: 3be48432-926c-4a54-bd66-3b16185a37a4
    index_volume_id: 2
 item_id_checkpoint: 339
      contact_count: 5
    size_checkpoint: 35912
  change_checkpoint: 7200
      tracking_sync: 0
      tracking_imap: 0
     last_backup_at: 1231567229
            comment: ajcody@zimbra.DOMAIN.com
   last_soap_access: 1229584283
       new_messages: 1
  idx_deferred_count: 0

Deleting An Account In Mysql

This comes up in very rare circumstances, usually when an account rename was attempted and for some reason it fails -- leaving the account in an odd state, where ldap and mysql data no longer match up. Use with extreme caution and under guidance of support staff.

The Steps To Delete User In Mysql

Caution - This Should Almost Never Need To Be Done. Usually Only Needed When Ldap Doesn't Have Account Anymore And For Some Reason Account Still Exists In Mysql

Use the above section, Ajcody-Mysql-Topics#Basic_Query_Of_Information_For_Account_In_Mysql , to get mailboxId or other needed data. The below example will most likely fail with the zmprov command because of missing data in ldap. You still might need to do the expr example below when you do your mysql delete commands.

# Fetch the user's mailbox Id
# zmprov will most likely fail in the situation where you need to do this process
# Use Ajcody-Mysql-Topics#Basic_Query_Of_Information_For_Account_In_Mysql
# alternative methods to get this variable - mailboxId

[root@]# su - zimbra
[zimbra@]$ zmprov getMailboxInfo user1@example.com
mailboxId: 11
quotaUsed: 30620 

# Fetch the user's mboxgroup Id
# Default is 100 for zimbra_mailbox_groups
$ zmlocalconfig zimbra_mailbox_groups
  zimbra_mailbox_groups = 100
# expr [mailboxId] % [zimbra_mailbox_groups]
$ expr 11 % 100
11


Each user is referenced by a unique mailboxId within Mysql. Each mailstore has it's own Mysql database and therefore the user mailboxId's are only locally unique - i.e., the id isn't a unique global variable. Replace id=#####, mboxgroup## and mailbox_id=##### with relevant data. An example might have mboxgroup77 , id=18577 and mailbox_id=18577.

[root@]# su - zimbra
[zimbra@]# mysql
mysql> SET foreign_key_checks = 0;
mysql> use mboxgroup##;
mysql> delete from appointment where mailbox_id=#####;
mysql> delete from data_source_item where mailbox_id=#####;
mysql> delete from imap_folder where mailbox_id=#####;
mysql> delete from imap_message where mailbox_id=#####;
mysql> delete from mail_item where mailbox_id=#####;
mysql> delete from open_conversation where mailbox_id=#####;
mysql> delete from pop3_message where mailbox_id=#####;
mysql> delete from revision where mailbox_id=#####;
mysql> delete from tombstone where mailbox_id=#####;
mysql> use zimbra;
mysql> delete from mailbox where id=#####;
mysql> delete from mailbox_metadata where mailbox_id=#####;
mysql> SET foreign_key_checks = 1
mysql> quit
[zimbra@]# zmprov fc account

Mysql Queries To See What Is Shown For Message ID's

Getting User Variables we need to query MySQL

Please see Ajcody-Mysql-Topics#Getting_User_Variables_We_Need_To_Query_MySQL

Mysql Query For A Users Specific Blob-Data ID

Query of Mysql against users mboxgroup [mboxgroup#.mail_item] for a SPECIFIC message [id=#]

Note, in this case, the users mailbox_id isn't necessary
[zimbra@mail39 ~]$ mysql -e 'SELECT * FROM mboxgroup3.mail_item WHERE id=321\G'
*************************** 1. row ***************************
  mailbox_id: 3
          id: 321
        type: 5
   parent_id: NULL
   folder_id: 5
    index_id: 321
     imap_id: 321
        date: 1281984004
        size: 448
   volume_id: 1
 blob_digest: 0pe,M6lHRy4KBXbIyEeR7AJzfkA=
      unread: 0
       flags: 8193
        tags: 0
      sender: Adam Cody
     subject: test w mobile sending
        name: NULL
    metadata: d1:f0:1:s45:Adam Cody <ajcody@mail39.zimbra.DOMAIN.com>1:t32:admin@mail38.zimbra.DOMAIN.com1:vi10ee
mod_metadata: 21394
 change_date: 1281984004
 mod_content: 21394
Mysql Query For ALL Message ID's Of A User

As you'll see in the above query, it has type=5 . Description of mboxgroup[#].mail_item Type shows that type 5 is 'messages'.

[zimbra@mail39 ~]$ mysql -e 'SELECT * FROM mboxgroup3.mail_item WHERE mailbox_id=3 AND type=5\G'
*************************** 1. row ***************************
  mailbox_id: 3
          id: 261
        type: 5
   parent_id: 263
   folder_id: 5
    index_id: 261
     imap_id: 261
        date: 1273609779
        size: 458
   volume_id: 3
 blob_digest: baQatWGZSt5THO6tAGImpEOqVQY=
      unread: 0
       flags: 1
        tags: 0
      sender: Adam Cody
     subject: test test
        name: NULL
    metadata: d1:f9:test test1:s45:Adam Cody <ajcody@mail39.zimbra.DOMAIN.com>1:t45:Adam Cody <ajcody@mail39.zimbra.DOMAIN.com>1:vi10ee
mod_metadata: 6216
 change_date: 1273609779
 mod_content: 6215
*************************** 2. row ***************************
  mailbox_id: 3
          id: 262
        type: 5
   parent_id: 263
   folder_id: 2
    index_id: 262
     imap_id: 262
        date: 1273609779
        size: 1599
   volume_id: 3
 blob_digest: s2BgZHas3+fgRhBYpIHv7uAEuCY=
      unread: 0
       flags: 0
        tags: 0
      sender: Adam Cody
     subject: test test
        name: NULL
    metadata: d1:f9:test test1:s45:Adam Cody <ajcody@mail39.zimbra.DOMAIN.com>1:vi10ee
mod_metadata: 6217
 change_date: 1273609784
 mod_content: 6216
*************************** 3. row ***************************
  mailbox_id: 3
          id: 264
        type: 5
   parent_id: NULL
   folder_id: 2
    index_id: 264
     imap_id: 264
        date: 1273610021
        size: 3467
   volume_id: 3
 blob_digest: n,VKvXmfL,jiA6kC8zE2g3TkWuM=
      unread: 0
       flags: 0
        tags: 0
      sender: Test User
     subject: test html
        name: NULL
    metadata: d1:f14:BOLD Underline1:s43:Test User <test@mail39.zimbra.DOMAIN.com>1:vi10ee
mod_metadata: 6220
 change_date: 1273610084
 mod_content: 6219
*************************** 4. row ***************************
cut -- it then continues for all the messages that exist for this account
Mysql Query For ALL Message ID's In A Particular Folder Of A User

As you'll see in the above query, it has type=5 . Description of mboxgroup[#].mail_item Type shows that type 5 is 'messages'.

Get folder_id Number And Description

One way to get the folder_id is to use the zmmailbox against the user to find the id# that we want.

[zimbra@mail39 ~]$ zmmailbox -z -m ajcody@mail39.zimbra.DOMAIN.com gaf
        Id  View      Unread   Msg Count  Path
----------  ----  ----------  ----------  ----------
         1  conv           0           0  / ()
        16  docu           0           1  /Briefcase ()
        10  appo           0           1  /Calendar ()
        14  mess           0           6  /Chats ()
         7  cont           0           1  /Contacts ()
         6  mess           0           1  /Drafts ()
        13  cont           0           2  /Emailed Contacts ()
         2  mess          10          19  /Inbox ()
         4  mess           0           0  /Junk ()
        12  wiki           0           0  /Notebook ()
         5  mess           0           6  /Sent ()
        15  task           0           0  /Tasks ()
         3  conv           0           0  /Trash ()

Or we could use a mysql query to get the output of the descriptions as well. folder_type=1 if for "Folders" and instead of selecting * , we'll use "id,name".

[zimbra@mail39 ~]$ mysql -e 'SELECT id,name FROM mboxgroup3.mail_item WHERE mailbox_id=3 AND type=1\G'
*************************** 1. row ***************************
  id: 1
name: USER_ROOT
*************************** 2. row ***************************
  id: 2
name: Inbox
*************************** 3. row ***************************
  id: 3
name: Trash
*************************** 4. row ***************************
  id: 4
name: Junk
*************************** 5. row ***************************
  id: 5
name: Sent
*************************** 6. row ***************************
  id: 6
name: Drafts
*************************** 7. row ***************************
  id: 7
name: Contacts
*************************** 8. row ***************************
  id: 8
name: Tags
*************************** 9. row ***************************
  id: 9
name: Conversations
*************************** 10. row ***************************
  id: 10
name: Calendar
*************************** 11. row ***************************
  id: 11
name: ROOT
*************************** 12. row ***************************
  id: 12
name: Notebook
*************************** 13. row ***************************
  id: 13
name: Emailed Contacts
*************************** 14. row ***************************
  id: 14
name: Chats
*************************** 15. row ***************************
  id: 15
name: Tasks
*************************** 16. row ***************************
  id: 16
name: Briefcase
The Query For ALL Message ID's In A Particular Folder Of A User

Now we'll setup the query to also use the folder_id. In this example folder_id=5 for the users "Sent" folder.

[zimbra@mail39 ~]$ mysql -e 'SELECT * FROM mboxgroup3.mail_item WHERE mailbox_id=3 AND type=5 AND folder_id=5\G'
*************************** 1. row ***************************
  mailbox_id: 3
          id: 261
        type: 5
   parent_id: 263
   folder_id: 5
    index_id: 261
     imap_id: 261
        date: 1273609779
        size: 458
   volume_id: 3
 blob_digest: baQatWGZSt5THO6tAGImpEOqVQY=
      unread: 0
       flags: 1
        tags: 0
      sender: Adam Cody
     subject: test test
        name: NULL
    metadata: d1:f9:test test1:s45:Adam Cody <ajcody@mail39.zimbra.DOMAIN.com>1:t45:Adam Cody <ajcody@mail39.zimbra.DOMAIN.com>1:vi10ee
mod_metadata: 6216
 change_date: 1273609779
 mod_content: 6215
*************************** 2. row ***************************
  mailbox_id: 3
          id: 281
        type: 5
   parent_id: 286
   folder_id: 5
    index_id: 281
     imap_id: 281
        date: 1277122331
        size: 463
   volume_id: 3
 blob_digest: LifXXOMYCgjFsIj,+bsxi0cboOQ=
      unread: 0
       flags: 1
        tags: 0
      sender: Adam Cody
     subject: test mail39
        name: NULL
    metadata: d1:f0:1:s45:Adam Cody <ajcody@mail39.zimbra.DOMAIN.com>1:t33:ajcody@mail59.zimbra.DOMAIN.com1:vi10ee
mod_metadata: 13741
 change_date: 1278082563
 mod_content: 13701
*************************** 3. row ***************************
  mailbox_id: 3
          id: 297
        type: 5
   parent_id: 286
   folder_id: 5
    index_id: 297
     imap_id: 297
        date: 1278084134
        size: 14896
   volume_id: 1
 blob_digest: 66QUCgivALxwGk+p3hnQ4viVZXU=
      unread: 0
       flags: 8195
        tags: 0
      sender: Adam Cody
     subject: test mail39
        name: NULL
    metadata: d1:f22:-- Original Message --1:p4:Re: 1:s45:Adam Cody <ajcody@mail39.zimbra.DOMAIN.com>1:t45:Adam Cody <ajcody@mail59.zimbra.homeunix.com>1:vi10ee
mod_metadata: 13765
 change_date: 1278084134
 mod_content: 13765
*************************** 4. row ***************************


cut -- it then continues for all the messages that exist for this account

Mysql Tables

zimbra database

SHOW zimbra Database Tables

This output shows the tables of the zimbra database.

$ mysql -e 'SHOW tables FROM zimbra'
+----------------------+
| Tables_in_zimbra     |
+----------------------+
| config               | 
| current_volumes      | 
| deleted_account      | 
| jiveExtComponentConf | 
| jiveGroupProp        | 
| jiveGroupUser        | 
| jiveID               | 
| jiveOffline          | 
| jivePrivacyList      | 
| jivePrivate          | 
| jiveProperty         | 
| jiveRemoteServerConf | 
| jiveRoster           | 
| jiveRosterGroups     | 
| jiveSASLAuthorized   | 
| jiveUserProp         | 
| jiveVCard            | 
| jiveVersion          | 
| mailbox              | 
| mailbox_metadata     | 
| mucAffiliation       | 
| mucConversationLog   | 
| mucMember            | 
| mucRoom              | 
| mucRoomProp          | 
| out_of_office        | 
| scheduled_task       | 
| service_status       | 
| table_maintenance    | 
| volume               | 
+----------------------+
DESCRIBE zimbra.mailbox Database Table

Description of the zimbra.mailbox table in the zimbra database.

$ mysql -e 'DESCRIBE zimbra.mailbox'
+--------------------+---------------------+------+-----+---------+-------+
| Field              | Type                | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| id                 | int(10) unsigned    | NO   | PRI | NULL    |       | 
| group_id           | int(10) unsigned    | NO   |     | NULL    |       | 
| account_id         | varchar(127)        | NO   | UNI | NULL    |       | 
| index_volume_id    | tinyint(3) unsigned | NO   | MUL | NULL    |       | 
| item_id_checkpoint | int(10) unsigned    | NO   |     | 0       |       | 
| contact_count      | int(10) unsigned    | YES  |     | 0       |       | 
| size_checkpoint    | bigint(20) unsigned | NO   |     | 0       |       | 
| change_checkpoint  | int(10) unsigned    | NO   |     | 0       |       | 
| tracking_sync      | int(10) unsigned    | NO   |     | 0       |       | 
| tracking_imap      | tinyint(1)          | NO   |     | 0       |       | 
| last_backup_at     | int(10) unsigned    | YES  | MUL | NULL    |       | 
| comment            | varchar(255)        | YES  |     | NULL    |       | 
| last_soap_access   | int(10) unsigned    | NO   |     | 0       |       | 
| new_messages       | int(10) unsigned    | NO   |     | 0       |       | 
| idx_deferred_count | int(10) unsigned    | NO   |     | 0       |       | 
+--------------------+---------------------+------+-----+---------+-------+
Example Of User Information Of zimbra.mailbox

I use both the user's :

{ldap} mailboxId equals id {mysql}
remember, this is for the zimbra.mailbox and not the mboxgroup# db, for mboxgroup# you would use mailbox_id.
{ldap} zimbraId equals account_id {mysql}

below in this example.

[zimbra@mail39 ~]$ zmprov gmi ajcody@mail39.zimbra.DOMAIN.com
mailboxId: 3
quotaUsed: 169831

[zimbra@mail39 ~]$ mysql -e 'SELECT * FROM zimbra.mailbox WHERE id=3\G'
*************************** 1. row ***************************
                id: 3
          group_id: 3
        account_id: db166bd3-2405-49d2-aa38-91159a3c9302
   index_volume_id: 2
item_id_checkpoint: 322
     contact_count: 3
   size_checkpoint: 169831
 change_checkpoint: 23800
     tracking_sync: 0
     tracking_imap: 0
    last_backup_at: 1282366808
           comment: ajcody@mail39.zimbra.DOMAIN.com
  last_soap_access: 1281984004
      new_messages: 1
idx_deferred_count: 18


[zimbra@mail39 ~]$ zmprov ga ajcody@mail39.zimbra.DOMAIN.com zimbraId
# name ajcody@mail39.zimbra.DOMAIN.com
zimbraId: db166bd3-2405-49d2-aa38-91159a3c9302

[zimbra@mail39 ~]$ mysql -e 'SELECT * FROM zimbra.mailbox WHERE account_id="db166bd3-2405-49d2-aa38-91159a3c9302"\G'
*************************** 1. row ***************************
                id: 3
          group_id: 3
        account_id: db166bd3-2405-49d2-aa38-91159a3c9302
   index_volume_id: 2
item_id_checkpoint: 322
     contact_count: 3
   size_checkpoint: 169831
 change_checkpoint: 23800
     tracking_sync: 0
     tracking_imap: 0
    last_backup_at: 1282366808
           comment: ajcody@mail39.zimbra.DOMAIN.com
  last_soap_access: 1281984004
      new_messages: 1
idx_deferred_count: 18

mboxgroup[#] database

Users are associated with a mboxgroup database. Each mboxgroup database will be comprised of multiple users, the users and their data are unique by way for the mailbox_id of the user.

SHOW mboxgroup[#] Database Tables

This output shows the tables of the mboxgroup# database.

$ mysql -e 'SHOW tables FROM mboxgroup3'
+----------------------+
| Tables_in_mboxgroup3 |
+----------------------+
| appointment          | 
| data_source_item     | 
| imap_folder          | 
| imap_message         | 
| mail_item            | 
| open_conversation    | 
| pop3_message         | 
| revision             | 
| tombstone            | 
+----------------------+
DESCRIBE mboxgroup[#].mail_item Database Table

Description of the mail_item table in the mboxgroup# database.

$ mysql -e 'DESCRIBE mboxgroup3.mail_item'
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| mailbox_id   | int(10) unsigned    | NO   | PRI | NULL    |       | 
| id           | int(10) unsigned    | NO   | PRI | NULL    |       | 
| type         | tinyint(4)          | NO   |     | NULL    |       | 
| 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   |     | NULL    |       | 
| size         | bigint(20) unsigned | NO   |     | NULL    |       | 
| 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     | mediumtext          | YES  |     | NULL    |       | 
| mod_metadata | int(10) unsigned    | NO   |     | NULL    |       | 
| change_date  | int(10) unsigned    | YES  |     | NULL    |       | 
| mod_content  | int(10) unsigned    | NO   |     | NULL    |       | 
+--------------+---------------------+------+-----+---------+-------+
Description of mboxgroup[#].mail_item Type
    /** Item is a standard {@link Folder}. */
    public static final byte TYPE_FOLDER       = 1;
    /** Item is a saved search {@link SearchFolder}. */
    public static final byte TYPE_SEARCHFOLDER = 2;
    /** Item is a user-created {@link Tag}. */
    public static final byte TYPE_TAG          = 3;
    /** Item is a real, persisted {@link Conversation}. */
    public static final byte TYPE_CONVERSATION = 4;
    /** Item is a mail {@link Message}. */
    public static final byte TYPE_MESSAGE      = 5;
    /** Item is a {@link Contact}. */
    public static final byte TYPE_CONTACT      = 6;
    // /** Item is a {@link InviteMessage} with a <tt>text/calendar</tt> MIME part. */
    // public static final byte TYPE_INVITE       = 7;   // SKIP 7 FOR NOW!
    /** Item is a bare {@link Document}. */
    public static final byte TYPE_DOCUMENT     = 8;
    /** Item is a {@link Note}. */
    public static final byte TYPE_NOTE         = 9;
    /** Item is a memory-only system {@link Flag}. */
    public static final byte TYPE_FLAG         = 10;
    /** Item is a calendar {@link Appointment}. */
    public static final byte TYPE_APPOINTMENT  = 11;
    /** Item is a memory-only, 1-message {@link VirtualConversation}. */
    public static final byte TYPE_VIRTUAL_CONVERSATION = 12;
    /** Item is a {@link Mountpoint} pointing to a {@link Folder},
     *  possibly in another user's {@link Mailbox}. */
    public static final byte TYPE_MOUNTPOINT   = 13;
    /** Item is a {@link WikiItem} */
    public static final byte TYPE_WIKI         = 14;
    /** Item is a {@link Task} */
    public static final byte TYPE_TASK         = 15;
    /** Item is a {@link Chat} */
    public static final byte TYPE_CHAT         = 16;

Mysql Database Location On Filesystem

mboxgroup# Database Default Example for ZCS5

Using my examples above of the mboxgroup3 .

[zimbra@mail39 data]$ pwd
/opt/zimbra/db/data

[zimbra@mail39 data]$ ls -F
backup/  ib_logfile0  ib_logfile1  ibdata1  mboxgroup1/  mboxgroup2/  mboxgroup3/  mboxgroup4/  mboxgroup5/  mboxgroup6/  mysql/  test/  zimbra/

[zimbra@mail39 data]$ ls mboxgroup3/
appointment.frm       data_source_item.ibd  imap_folder.ibd   mail_item.frm          open_conversation.ibd  revision.frm   tombstone.ibd
appointment.ibd       db.opt                imap_message.frm  mail_item.ibd          pop3_message.frm       revision.ibd
data_source_item.frm  imap_folder.frm       imap_message.ibd  open_conversation.frm  pop3_message.ibd       tombstone.frm

You'll notice there's matching files on the system for the various tables reported with:

$ mysql -e 'SHOW tables FROM mboxgroup3'

zimbra Database Default Example for ZCS5

[zimbra@mail39 data]$ pwd
/opt/zimbra/db/data

[zimbra@mail39 data]$ ls -F
backup/  ib_logfile0  ib_logfile1  ibdata1  mboxgroup1/  mboxgroup2/  mboxgroup3/  mboxgroup4/  mboxgroup5/  mboxgroup6/  mysql/  test/  zimbra/

[zimbra@mail39 data]$ ls zimbra
config.frm                jiveGroupUser.MYI    jiveProperty.MYD          jiveSASLAuthorized.frm  mucAffiliation.MYD      mucRoomProp.frm
config.ibd                jiveGroupUser.frm    jiveProperty.MYI          jiveUserProp.MYD        mucAffiliation.MYI      out_of_office.frm
current_volumes.frm       jiveID.MYD           jiveProperty.frm          jiveUserProp.MYI        mucAffiliation.frm      out_of_office.ibd
current_volumes.ibd       jiveID.MYI           jiveRemoteServerConf.MYD  jiveUserProp.frm        mucConversationLog.MYD  scheduled_task.frm
db.opt                    jiveID.frm           jiveRemoteServerConf.MYI  jiveVCard.MYD           mucConversationLog.MYI  scheduled_task.ibd
deleted_account.frm       jiveOffline.MYD      jiveRemoteServerConf.frm  jiveVCard.MYI           mucConversationLog.frm  service_status.MYD
deleted_account.ibd       jiveOffline.MYI      jiveRoster.MYD            jiveVCard.frm           mucMember.MYD           service_status.MYI
jiveExtComponentConf.MYD  jiveOffline.frm      jiveRoster.MYI            jiveVersion.MYD         mucMember.MYI           service_status.frm
jiveExtComponentConf.MYI  jivePrivacyList.MYD  jiveRoster.frm            jiveVersion.MYI         mucMember.frm           table_maintenance.frm
jiveExtComponentConf.frm  jivePrivacyList.MYI  jiveRosterGroups.MYD      jiveVersion.frm         mucRoom.MYD             table_maintenance.ibd
jiveGroupProp.MYD         jivePrivacyList.frm  jiveRosterGroups.MYI      mailbox.frm             mucRoom.MYI             volume.frm
jiveGroupProp.MYI         jivePrivate.MYD      jiveRosterGroups.frm      mailbox.ibd             mucRoom.frm             volume.ibd
jiveGroupProp.frm         jivePrivate.MYI      jiveSASLAuthorized.MYD    mailbox_metadata.frm    mucRoomProp.MYD
jiveGroupUser.MYD         jivePrivate.frm      jiveSASLAuthorized.MYI    mailbox_metadata.ibd    mucRoomProp.MYI

You'll notice there's matching files on the system for the various tables reported with :

$ mysql -e 'SHOW tables FROM zimbra'
Jump to: navigation, search