Ajcody-Mysql-Topics

From Zimbra :: Wiki

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

Contents

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. </pre>

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 Backup And Restore

Please see the MySQL_Backup_and_Restore page.

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
Show all accounts on mailstore

Show all accounts on mailstore.

$ mysql -e 'SELECT * FROM zimbra.mailbox WHERE comment LIKE "%"\G' | grep comment
           comment: admin@zimbra.DOMAIN.com
           comment: wiki@zimbra.DOMAIN.com
           comment: spam.1c4mokie@zimbra.DOMAIN.com
           comment: ham.iudu0ic_mz@zimbra.DOMAIN.com
           comment: shared-account@zimbra.DOMAIN.com
           comment: testuser@zimbra.DOMAIN.com
           comment: large-share@zimbra.DOMAIN.com
           comment: cos-user@zimbra.DOMAIN.com
           comment: arch-prod@zimbra.DOMAIN.com
Query All Users In A mboxgroup##

This is very useful, for example, if you experience corruption in one of your mboxgroup## db files when you want to see who is effected. Remember, these are unique to mailstore (group_id = mboxgroup)(id = userid). The account_id is a global variable, the comment field should reflect the account_id's email address - another global variable.

Note - I'm on a small test server, hence why only one account is showing. zimbra_mailbox_groups = 100 [default] determines the 'round-robin' behavior of assigning users to mboxgroup's

$ mysql -e 'select id, comment, account_id, group_id from zimbra.mailbox where group_id=5'
+----+-------------------------------------------+--------------------------------------+----------+
| id | comment                                   | account_id                           | group_id |
+----+-------------------------------------------+--------------------------------------+----------+
|  5 | ham.1msrt1ugis@mail37.DOMAIN.com          | 9ded4fa5-62fe-4ae4-ac14-7a8928386ea8 |        5 | 
+----+-------------------------------------------+--------------------------------------+----------+

You can also, double check, information the account_id and comment with zmprov then:

$ zmprov gmi 9ded4fa5-62fe-4ae4-ac14-7a8928386ea8
mailboxId: 5
quotaUsed: 0

$ zmprov gmi ham.1msrt1ugis@mail37.DOMAIN.com
mailboxId: 5
quotaUsed: 0

Just to be complete, below I'll list the location of the various db's in question based upon the filesystem.

$ pwd
/opt/zimbra/db/data/zimbra

$ ls mailbox*
mailbox.frm  mailbox.ibd  mailbox_metadata.frm  mailbox_metadata.ibd

$ cd ../mboxgroup5/

$ pwd
/opt/zimbra/db/data/mboxgroup5

$ ls
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

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#Getting_User_Variables_We_Need_To_Query_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#Getting_User_Variables_We_Need_To_Query_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

Useful Mysql Query Examples

First - Get 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
Content Of Message Example Above As On Filesystem
How To Locate Users Mailstore and Message Store Directory

The basics:

  • $ zmprov ga USER@DOMAIN zimbraMailHost
  • $ zmprov gmi USER@DOMAIN
    • Note the mailboxId for the user.
  • Then ssh to the zimbraMailHost the user is on.
  • $ zmvolume -l
    • Confirm the path for the primaryMessage volume, default is /opt/zimbra/store .
  • cd to the primaryMessage volume path, example uses the default path.
    • $ cd /opt/zimbra/store
    • find . -maxdepth 2 -name [replace with the mailboxId of the user] -print
    • or do perl -e 'print mailboxId# >> 12 ; print "\n"'
  • You can now cd to the users msg directory.

Working example:

 $ zmprov ga userA@DOMAIN zimbraMailHost
    # name userA@mail71.DOMAIN.com
    zimbraMailHost: mail71.DOMAIN.com

[I'm already on the zimbraMailHost for this user, no need to ssh to it]

 $ zmprov gmi userA@DOMAIN
    mailboxId: 17
   quotaUsed: 2032

 $ zmvolume -l
   Volume id: 1
        name: message1
        type: primaryMessage
        path: /opt/zimbra/store
  compressed: false
     current: true

   Volume id: 2
        name: index1
        type: index
        path: /opt/zimbra/index
  compressed: false
     current: true

 $ find . -maxdepth 2 -name 17 -print
   ./0/17

or to find the top directory the user directory is in:

$ perl -e 'print 17 >> 12 ; print "\n"'                                                                                                   
0                                                

 $ cd 0/17/msg/

 $ find . -name \*.msg -print        
   ./0/268-751.msg
   ./0/269-756.msg
   ./0/306-2119.msg
The Message On The Filesystem - id index_id imap_id

Change directory to the users root directory under your mailstores 'store' path. Default is /opt/zimbra/store , this example user is under /opt/zimbra/store/0/3 . Everything under that is "theirs".

[zimbra@mail39 0]$ pwd
/opt/zimbra/store/0/3/msg/0

[zimbra@mail39 0]$ ls
267-9778.msg  280-13700.msg  287-13743.msg  291-13777.msg  295-13763.msg  300-13771.msg  305-13784.msg
268-9780.msg  283-13715.msg  288-13747.msg  292-13758.msg  296-13764.msg  301-13774.msg  307-13791.msg
269-9782.msg  284-13740.msg  289-13752.msg  293-13759.msg  297-13765.msg  302-13775.msg  320-21392.msg
270-9854.msg  285-13741.msg  290-13755.msg  294-13760.msg  299-13769.msg  304-13782.msg  321-21394.msg

In our example above, we used "WHERE id=321". Remember, "id=321" is correlated to the 321 in the filename, 321-21394.msg. Also, you'll notice there's actually 3 matches :

  • id: 321
  • index_id: 321
  • imap_id: 321
mod_metadata And mod_content

From the output above, you see two matches for 21394 - remember, filename is 321-21394.msg :

  • mod_metadata: 21394
    • mod_metadata might not as there are changes exclusively in the db.
      • For example - marking read/unread, tagging, flagging, moving to a new folder, etc..
  • mod_content: 21394
    • mod_content should always be reflected in the filename on the system
      • mod_content is updated, for example, when saving a draft, editing a briefcase document, etc.
        • Note : "Edit As New" on a message actually creates a new file on the filesystem and uses a different id .
        • In ZCS6+ , using the "Remove Attachments" option on an email with attachments would most likely alter the mod_content number as well.

Notice that the main difference between the two is where changes are only exclusively made in the db vs. a change that would necessary alter the "file".

If I then "tag" the message in ZWC for this message, you'll notice the mod_metadata is altered to 23826 but mod_content and the filename on the fs still uses 21394 . Notice as well, "tags: 0" was now changed to "tags: 1" .

[zimbra@mail39 data]$ 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: 1
        tags: 1
      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: 23826
 change_date: 1282864623
 mod_content: 21394

$ cd /opt/zimbra/store/0/3/msg/0

$ ls -la 321*
-rw-r----- 1 zimbra zimbra 448 Aug 16 14:40 321-21394.msg
Mysql Query For date And change_date For ALL id's For A Specific User

Another item to point out in this example is the change_date and date fields. Notice that change_date has changed compared to the first time we ran the query. The change_date use to match the date variable number - date: 1281984004 . This field, change_date, is updated when the item has 'changed', not necessarily a mod_metadata type change -- that might be reflected in the time stamp of the file on the filesystem.

date: 1281984004
change_date: 1281984004
vs. after we applies a 'tag' to message
date: 1281984004
change_date: 1282864623

To convert that into a 'normal' readable time format, using perl:

# perl -e 'print localtime(1281984004). "\n"'
   Mon Aug 16 14:40:04 2010
 *** Which matches the ls -la time above. ***

vs.

# perl -e 'print localtime(1282864623). "\n"'
   Thu Aug 26 19:17:03 2010  
 *** Which is the time I tagged the message in ZWC. ***

To translate Thursday Aug 26 2010 19:17:03 to epoch seconds and back again, for example [Replace '-d' with '-ud' for GMT/UTC time]:

# date +%s -d "08/26/2010 19:17:03"
   1282864623

or

# date +%s -ud "Thu Aug 26 19:17:03 EDT 2010"
   1282864623

And to prove the conversion goes back again with the date command:

# date -d @1282864623
   Thu Aug 26 19:17:03 EDT 2010

Another reference is, the from_unixtime() and unix_timestamp() mysql functions - see :

Examples within mysql:

$ mysql -e 'SELECT UNIX_TIMESTAMP("2010-08-26 19:17:03")'
  +---------------------------------------+
  | UNIX_TIMESTAMP("2010-08-26 19:17:03") |
  +---------------------------------------+
  |                            1282864623 | 
  +---------------------------------------+

And back again:

$ mysql -e 'SELECT FROM_UNIXTIME(1282864623)'
  +---------------------------+
  | FROM_UNIXTIME(1282864623) |
  +---------------------------+
  | 2010-08-26 19:17:03       | 
  +---------------------------+

An example mysql search query using this format:

$ mysql -e 'SELECT * FROM mboxgroup3.mail_item WHERE mailbox_id=3 AND change_date=(SELECT UNIX_TIMESTAMP("2010-08-26 19:17:03"))\G'

This might be useful if you find yourself wanting to query against the change_date for a particular user and see what messages were altered at that time or time range. Example might be, where the user is reporting their "problem" occurring at a certain time or during a time range.

A search against the change_date field and mailbox_id we've been discussing here, shows:

$ mysql -e 'SELECT * FROM mboxgroup3.mail_item WHERE mailbox_id=3 AND change_date=1282864623\G'
*************************** 1. row ***************************
  mailbox_id: 3
          id: 64
        type: 3
   parent_id: NULL
   folder_id: 8
    index_id: NULL
     imap_id: NULL
        date: 1282864623
        size: 0
   volume_id: NULL
 blob_digest: NULL
      unread: NULL
       flags: 0
        tags: 0
      sender: NULL
     subject: Test-Tag
        name: Test-Tag
    metadata: d1:vi10ee
mod_metadata: 23825
 change_date: 1282864623
 mod_content: 23825
*************************** 2. 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: 1
        tags: 1
      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: 23826
 change_date: 1282864623
 mod_content: 21394

See how we also identified the 'tag' entry, id: 64 type: 3 , that was made as the id=321 type=5 was altered.

To search by a range of the change_date variable. Notice the syntax of :

  • change_date>=1282864600  ; meaning greater than or equal to
    • we could also use this instead, change_date>=(SELECT UNIX_TIMESTAMP("2010-08-26 19:17:03")
  • change_date<=1282865366  ; meaning less than or equal to
    • we could also use this instead, change_date<=(SELECT UNIX_TIMESTAMP("2010-08-26 19:29:26")
$ mysql -e 'SELECT * FROM mboxgroup3.mail_item WHERE mailbox_id=3 AND change_date>=1282864600 AND change_date<=1282865366\G'
*************************** 1. row ***************************
  mailbox_id: 3
          id: 64
        type: 3
   parent_id: NULL
   folder_id: 8
    index_id: NULL
     imap_id: NULL
        date: 1282864623
        size: 0
   volume_id: NULL
 blob_digest: NULL
      unread: NULL
       flags: 0
        tags: 0
      sender: NULL
     subject: Test-Tag
        name: Test-Tag
    metadata: d1:vi10ee
mod_metadata: 23825
 change_date: 1282864623
 mod_content: 23825
*************************** 2. 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: 1
        tags: 1
      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: 23826
 change_date: 1282864623
 mod_content: 21394
*************************** 3. row ***************************
  mailbox_id: 3
          id: 323
        type: 5
   parent_id: NULL
   folder_id: 6
    index_id: 323
     imap_id: 323
        date: 1282865366
        size: 444
   volume_id: 1
 blob_digest: l5E3l5rbE5XUzHcp+hRNkN1E7os=
      unread: 0
       flags: 65
        tags: 0
      sender: Adam Cody
     subject: test w mobile sending - edit as new
        name: NULL
    metadata: d1:dd2:do3:3214:idnt36:db166bd3-2405-49d2-aa38-91159a3c9302e1:f4:test1:s45:Adam Cody <ajcody@mail39.zimbra.DOMAIN.com>1:t32:admin@mail38.zimbra.DOMAIN.com1:vi10ee
mod_metadata: 23829
 change_date: 1282865366
 mod_content: 23829
Confirming subject Matches Subject Line In Message

You'll see that the message 321-21394.msg on the filesystem also matches the output we had with our mysql query - compare the Subject line.

[zimbra@mail39 0]$ head 321-21394.msg
Date: Mon, 16 Aug 2010 14:40:04 -0400 (EDT)
From: Adam Cody <ajcody@mail39.zimbra.DOMAIN.com>
To: admin@mail38.zimbra.DOMAIN.com
Message-ID: <27950055.531281984004882.JavaMail.root@mail39.zimbra.DOMAIN.com>
Subject: test w mobile sending
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
X-Originating-IP: [192.168.0.13]
X-Mailer: Zimbra 5.0.23_GA_3242.RHEL5 (zclient/5.0.23_GA_3242.RHEL5)

This is a good field to query against also. See Mysql Query For Subject Line In Message Of A Specific User.

Mysql Query For Subject Line In Message Of A Specific User

This is a good field to query against, depending on your circumstances. User's have very little information from their end, ZWC, that correlates into the mysql fields we've been discussing [generally speaking]. They can't see the message id as needed for id= in mboxgroup#.mail_item. Let's say you only have the mailbox_id of the user, therefor the mboxgroup#, and the Subject line of the message in question. You would do something like the following:

$ mysql -e 'SELECT * FROM mboxgroup3.mail_item WHERE mailbox_id=3 AND subject="test w mobile sending"\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: 1
        tags: 1
      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: 23826
 change_date: 1282864623
 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.DOMAIN.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

The Query For ALL Appointments For A Particular User

$ mysql -e 'SELECT * FROM mboxgroup3.appointment WHERE mailbox_id=3\G'
*************************** 1. row ***************************
mailbox_id: 3
       uid: 3252c10b-b889-41ab-bbea-e07eafba0d76
   item_id: 325
start_time: 2010-08-27 18:55:00
  end_time: 2010-08-27 20:00:00
*************************** 2. row ***************************
mailbox_id: 3
       uid: 87fa4fc8-70c6-4459-9d3d-e139c0f6e64a
   item_id: 266
start_time: 2010-06-01 17:40:00
  end_time: 2010-06-01 18:00:00

The only relationship I find so far at this point with the above data to 'other' areas within ZCS in regards to mysql or the filesystem is the UID [ 3252c10b-b889-41ab-bbea-e07eafba0d76 ] above will be the UID as well in the ics format that will be in the message that is sent. And that messages will be on the filesystem under the user's store directory path. The message filename was 326-24060.msg - which doesn't match the item_id [325] .

$ pwd
/opt/zimbra/store/0/3/msg/0

$ grep 3252c10b-b889-41ab-bbea-e07eafba0d76 *
326-24060.msg:UID:3252c10b-b889-41ab-bbea-e07eafba0d76

$ ls -la 326-24060.msg 
-rw-r----- 1 zimbra zimbra 4619 Aug 27 18:04 326-24060.msg

$ cat 326-24060.msg

## CUT ###
BEGIN:VEVENT
UID:3252c10b-b889-41ab-bbea-e07eafba0d76
SUMMARY:Test for Mysql Query
LOCATION:My office
ATTENDEE;ROLE=REQ-PARTICIPANT;PARTSTAT=NEEDS-ACTION;RSVP=TRUE:mailto:admin@m
 ail39.zimbra.DOMAIN.com
ORGANIZER;CN=Adam Cody:mailto:ajcody@mail39.zimbra.DOMAIN.com
DTSTART;TZID="(GMT-06.00) Central Time (US & Canada)":20100827T180000
DTEND;TZID="(GMT-06.00) Central Time (US & Canada)":20100827T190000
### ###

Mail Items Received Per Day

Actually saw this query from a customer in a case, thought it might be useful for others. Example uses mboxgroup3 and mailbox_id3 , you would adjust that for your own purposes.

$ mysql

mysql> connect mboxgroup3;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Connection id:    18414
Current database: mboxgroup3

mysql> select FROM_UNIXTIME( date, '%d %m %Y'), count(*) from mail_item where mailbox_id=3 group by  FROM_UNIXTIME( date, '%d %m %Y') order by  date;

+----------------------------------+----------+
| FROM_UNIXTIME( date, '%d %m %Y') | count(*) |
+----------------------------------+----------+
| 13 09 2010                       |       17 | 
| 14 09 2010                       |        5 | 
| 15 09 2010                       |        4 | 
| 16 09 2010                       |        4 | 
| 17 09 2010                       |        4 | 
+----------------------------------+----------+
5 rows in set (0.00 sec)

mysql>

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;
DESCRIBE mboxgroup[#].appointment Database Table

Description of the appointment table in the mboxgroup# database.

$ mysql -e 'DESCRIBE mboxgroup3.appointment'
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| mailbox_id | int(10) unsigned | NO   | PRI | NULL    |       | 
| uid        | varchar(255)     | NO   | PRI | NULL    |       | 
| item_id    | int(10) unsigned | NO   |     | NULL    |       | 
| start_time | datetime         | NO   |     | NULL    |       | 
| end_time   | datetime         | YES  |     | NULL    |       | 
+------------+------------------+------+-----+---------+-------+

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'

Getting The Size Of The Mbox Tables In MBs

Here is what you would run.

$ su - zimbra

## Note : I added \'s in the command below so it would format ok in wiki ##

$ mysql -e "SELECT table_schema AS 'Mbox table', \
Round( Sum( data_length + index_length ) / 1024 / 1024, 3)\
AS 'Mbox Size (MB)' FROM information_schema.tables \
GROUP BY table_schema ;"
+--------------------+----------------+
| Mbox table         | Mbox Size (MB) |
+--------------------+----------------+
| information_schema |          0.004 | 
| mboxgroup1         |          0.453 | 
| mboxgroup2         |          0.453 | 
| mboxgroup3         |          0.516 | 
| mboxgroup4         |          0.453 | 
| mboxgroup5         |          0.453 | 
| mboxgroup6         |          0.453 | 
| mysql              |          0.520 | 
| zimbra             |          0.319 | 
+--------------------+----------------+

This was a response from the developers on a question a customer had concerning the sizes of their mboxgroup*ibd files and why they were of varying sizes and if the larger ones could be "shrunk".

The innodb tablespace can physically grow, but won't shrink. We set innodb_file_per_table to store an idb file per table instead of one large idb file for all tables. If a table has a lot of activity, the idb file will grow. The rows can be deleted but the idb file will still be at the high-water mark. That doesn't mean there isn't a bunch of free space that can be used, just that it will remain large on the filesystem. You can find out the actual size of the space being used by querying the information_schema dictionary view.
Note - see syntax above that I did. Adam
SELECT table_schema AS 'Mbox table',
Round( Sum( data_length + index_length ) / 1024 / 1024, 3) AS Mbox Size (MB)',
FROM information_schema.tables
GROUP BY table_schema ;
As you can see, you can include both the data and index lengths, or you could exclude one and run separately to see exactly how large each is. That will get you the actual size which you can compare to the physical size of the idb on disk.If you want to reclaim the disk space by data, I think you are going to have to export the data, drop the table (I don't think a truncate will do it), then import the data again. As far as checking for the percentage of fragmentation, you can't really determine if the difference between physical size and data size is due to many records being deleted or due to fragmentation waste. You can STILL take the same administrative action of rebuilding the tables to help avoid the problem though.

Concerning the dropping of data/tables, see the Mysql_Crash_Recovery on the basic concepts for that. Zimbra Support uses the steps on the Mysql_Crash_Recovery for extreme or DR sitautions, falling back to a full DR recovery process using the zmrestore if the Mysql Crash Recovery steps fail. One should not pursue the Mysql_Crash_Recovery steps unless your willingly to accept the possible need of doing a full DR restore if things don't work out.

To see or understand more about the data in the mboxgroup* mail_item tables, see:

Also, for future references since 608 is at 5.0.90 - this query might prove useful in regards to the fragmentation question:

Misc Issues Related To Mysql

Error - inconsistent state: unread

You might see a error like the following:

"com.zimbra.common.service.ServiceException: system failure:
 inconsistent state: unread < 0 for item X"
 (X is the id of the folder in question.)

Please see the following on the issue and the work around and/or later fix.

Third Party Tools And References

Recovery Issues

Personal tools