Deleting appointments from command-line

From Zimbra :: Wiki

Jump to: navigation, search

PART 1: DELETING APPOINTMENTS

It is best to delete appointments/meetings from the GUI, using Zimbra Web Client, Zimbra Connector for Outlook, Entourage, etc. However, in cases when you are unable to do so for whatsoever the reason, you will need to use the command-line to delete appointments.

This document explains how you can delete appointments through the command-line interface. Typically, you need to do this when you are hitting some bug or need to automate something using a script. If you are hitting a bug, it is also a good idea to grab the blob of the appointment that you are deleting by hand.

An appointment or meeting in Zimbra is a “mailItem”. (All emails are “mailItem”, too)

So, deleting appointments through command-line is simple. All you need is zmmailbox.

The complex part is deriving the itemNumber. Here is an example which explains this.

In the below given example: user1@example.com is the user facing this issue. He has an appointment with Subject "Test Appointment1" and Body "This is sample appointment. For testing purposes only." The appointment is scheduled for 5th Sep 2007, 12:30 PM upto 5th Sep 2007, 1:30 PM.

I have embedded comments in-line by using "#" at the start of the line.

Before proceeding with this exercise, please ensure that you have the Subject, Body, Start and End Times of the appointments accurately. If possible, please keep a screenshot handy.

Log-in to the system.

# Fetch the user's mailbox Id

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

# Fetch the user's mboxgroup Id

$ expr 11 % 100
11

# We concatenate the group ID with the term "mboxgroup" to get the the user's database. In this case, it is “mboxgroup11”.

[zimbra@supp01 ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 205
Server version: 5.0.33-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

Database changed

# From the information that we already have we can now create the following query. In the following query please do not forget to put % as I have done so. 

mysql> select * from mail_item where mailbox_id="11" and subject like "%Sample Appointment1%" and metadata like "%This is sample appointment%";
+------------+-----+------+-----------+-----------+----------+---------+------------+------+-----------+------------------------------+--------+-------+------+-------------+---------------------+------+-----------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+-------------+
| mailbox_id | id  | type | parent_id | folder_id | index_id | imap_id | date       | size | volume_id | blob_digest                  | unread | flags | tags | sender      | subject             | name | metadata                                                                                                                          | mod_metadata | change_date | mod_content |
+------------+-----+------+-----------+-----------+----------+---------+------------+------+-----------+------------------------------+--------+-------+------+-------------+---------------------+------+-----------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+-------------+
|         11 | 263 |    5 |       266 |         3 |      263 |     263 | 1191243364 | 4300 |         1 | Wnl6PwOTDf0DeuHjOuu4BmFHP38= |      0 |     4 |    0 | Fname Lname | Sample Appointment1 | NULL | d3:aisld1:ai264e1:ci0eee1:f54:This is sample appointment. For testing purposes only.1:s31:Fname Lname <user2@example.com>1:vi10ee |           13 |  1191243402 |           7 |
+------------+-----+------+-----------+-----------+----------+---------+------------+------+-----------+------------------------------+--------+-------+------+-------------+---------------------+------+-----------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+-------------+
1 row in set (0.00 sec)

# To get the itemId concatenate the “id” and “mod_content” with a “-” (hyphen). You
# can do it manually or do it with this SQL statement (in our case it will be "263-7"):

mysql> select CONCAT(id, '-', mod_content) as itemId from mail_item where mailbox_id = 11 and subject like "%Sample Appointment1%" and metadata like "%This is sample appointment%";
+--------+
| itemId |
+--------+
| 263-7  |
+--------+

mysql> \q
Bye
[zimbra@supp01 ~]$

So, to delete this appointment, you need to run the command:

[zimbra@supp01 ~]$ zmmailbox -z                 

mbox> sm user1@example.com

mailbox: user1@example.com, size: 35.36 KB, messages: 9, unread: 2

mbox user1@example.com> deleteItem 263-7 

mbox user1@example.com> quit

[zimbra@supp01 ~]$

Using the zmmailbox deleteItem command deletes the blob on disk as well. If you for some reason decided to delete it via SQL then you'll need to go back and delete the blob off the filesystem yourself, too. For this, please see Bobby's article http://wiki.zimbra.com/index.php?title=Account_mailbox_database_structure

PART 2: DELETING DUPLICATE APPOINTMENTS

(Please read PART 1 in order to understand the following text)

There have been some instances of users reporting that they have duplicate appointments. Deleting these is the same process as the above one. However, individually tracing out each itemId of duplicate mail items only is very cumbersome. For this, here's what you could do.

1. Define the criteria for "duplicate". It can be as simple as having the same subject, or you may need to look at more of the data. - More data may include description/body of the appointment, start and end times, etc.

2. Whatever the criteria, you can apply them to the REST-dump of each mailbox's calendar. That will give you the list of appointment UIDs to delete. - Here's a blog entry (http://www.zimbra.com/blog/archives/2006/01/rest_update.html) that talks about how you can download the ICS using REST. Grab the UIDs from this. You need to do some scripting here or just manually lookup. You may want to try this tool: http://www.the-word-is-not-enough.com/dedupe.php

3. The item id for the UIDs can be looked up from the appointment table. - E.g. select * from mboxgroup11.appointment;

Use “zmmailbox” for deleting. Please see PART 1.

How to find duplicates via SQL

Using the instructions in PART 1 above to locate the user's mailbox ID and which database they were in, I used the following SQL to get a list of item IDs to delete:

SELECT DISTINCT q.r_item_id
FROM   (SELECT l.item_id    l_item_id,
              r.item_id    r_item_id,
              l.start_time l_start_time,
              r.start_time r_start_time,
              l.end_time   l_end_time,
              r.end_time   r_end_time,
              l.subject    l_subject,
              r.subject    r_subject
       FROM   (SELECT a.item_id,
                      a.start_time,
                      a.end_time,
                      m.subject
               FROM   appointment a
                      LEFT JOIN mail_item m
                        ON a.item_id = m.id
               WHERE  m.mailbox_id = $mailbox_id) AS l            /* <- put mailbox ID here */
              INNER JOIN (SELECT a.item_id,
                                 a.start_time,
                                 a.end_time,
                                 m.subject
                          FROM   appointment a
                                 LEFT JOIN mail_item m
                                   ON a.item_id = m.id
                          WHERE  m.mailbox_id = $mailbox_id) AS r  /* <- put mailbox ID here */
                ON l.subject = r.subject                 /* <- this is your criteria */
                   AND l.start_time = r.start_time       /* <- for what consitutes   */
                   AND l.end_time = r.end_time           /* <- a duplicate           */
                   AND l.item_id < r.item_id    /* <- this is not, leave this alone */
       ORDER  BY l.start_time,
                 l.item_id) AS q
ORDER  BY r_item_id;

To visually inspect the results to be sure before deleting, you can run the outer-most subquery by itself (remove everything outside of and including the outermost pair of parentheses).

Once you're satisfied with your list of item IDs, you can use the deleteItem command in zmmailbox as described in PART 1 to delete them. You don't want to delete them via SQL because that will leave the content blob behind on disk.

Verified Against: Date Created: 9/5/2007
Article ID: http://wiki.zimbra.com/index.php?title=Deleting_appointments_from_command-line Date Modified: 05/18/2012
Personal tools