Ajcody-Mysql-Topics: Difference between revisions
Line 1,424: | Line 1,424: | ||
<pre>$ mysql -e 'SHOW tables FROM zimbra'</pre> | <pre>$ mysql -e 'SHOW tables FROM zimbra'</pre> | ||
===Misc Issues Related To Mysql=== | |||
====Error - inconsistent state: unread==== | |||
You might see a error like the following: | |||
<pre> | |||
"com.zimbra.common.service.ServiceException: system failure: | |||
inconsistent state: unread < 0 for item X" | |||
(X is the id of the folder in question.) | |||
</pre> | |||
Please see the following on the issue and the work around and/or later fix. | |||
* "Add RecalculateMailboxCounts admin command and CLI" | |||
** http://bugzilla.zimbra.com/show_bug.cgi?id=29637 | |||
===Third Party Tools And References=== | ===Third Party Tools And References=== |
Revision as of 19:02, 3 September 2010
- 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:
- http://www.zimbra.com/forums/administrators/21519-memory-usage-slowness-backup-error.html#post107080
So other background info:
- http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
- http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
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.
- We recommend this setting that might be causing issue:
- IO_DIRECT For MySQL Can Be Detrimental
- Mysql Bug - "innodb_flush_method = O_DIRECT may degrade performance of InnoDB on SAN":
- Comment about O_Direct from Linus:
- EXT3 not RAID-5 may be the cause of performance issues
- Numerous Items On This Blog Page:
- http://mysqldba.blogspot.com/2008_01_01_archive.html
- Most relevant are these sections:
- http://mysqldba.blogspot.com/2008_01_01_archive.html
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
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
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_metadata might not as there are changes exclusively in the db.
- 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.
- mod_content is updated, for example, when saving a draft, editing a briefcase document, etc.
- mod_content should always be reflected in the filename on the system
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 :
- http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
- Please see the note as well under the following, note 100% - so you might want to fudge the time/numbers you use when searching 'dates/times'
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 ### ###
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'
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.
- "Add RecalculateMailboxCounts admin command and CLI"
Third Party Tools And References
Recovery Issues
- "InnoDB Recovery Tool" - is a tool to recover corrupted or deleted InnoDB tables