Deleting appointments from command-line: Difference between revisions

(http://www.the-word-is-not-enough.com/dedupe.php)
No edit summary
 
(11 intermediate revisions by 7 users not shown)
Line 1: Line 1:
{{BC|Community Sandbox}}
__FORCETOC__
<div class="col-md-12 ibox-content">
=Deleting appointments from command-line=
{{KB|{{Unsupported}}|{{ZCS 7.0}}|{{ZCS 6.0}}|}}
{{Archive}}{{WIP}}
== PART 1: DELETING APPOINTMENTS ==
== PART 1: DELETING APPOINTMENTS ==


Line 56: Line 62:
+------------+-----+------+-----------+-----------+----------+---------+------------+------+-----------+------------------------------+--------+-------+------+-------------+---------------------+------+-----------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+-------------+
+------------+-----+------+-----------+-----------+----------+---------+------------+------+-----------+------------------------------+--------+-------+------+-------------+---------------------+------+-----------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+-------------+
1 row in set (0.00 sec)
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
mysql> \q
Line 61: Line 77:
[zimbra@supp01 ~]$
[zimbra@supp01 ~]$
</pre>
</pre>
'''From the last output, pick-up the “id” and “mod_content” and concatenate them with a “-” (hyphen). So in the above case, it will be “263-7”. This is our itemId.'''


So, to delete this appointment, you need to run the command:
So, to delete this appointment, you need to run the command:
Line 73: Line 87:
mailbox: user1@example.com, size: 35.36 KB, messages: 9, unread: 2
mailbox: user1@example.com, size: 35.36 KB, messages: 9, unread: 2


mbox user1@example.com> deleteItem 264-9
mbox user1@example.com> deleteItem 263-7


mbox user1@example.com> quit
mbox user1@example.com> quit
Line 80: Line 94:
</pre>
</pre>


If you also want to get the blob, please see Bobby's article http://wiki.zimbra.com/index.php?title=Account_mailbox_database_structure
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 ==
== PART 2: DELETING DUPLICATE APPOINTMENTS ==
Line 97: Line 111:


Use “zmmailbox” for deleting. Please see PART 1.
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.
{{Article_Footer|Zimbra Collaboration 7.0, 6.0|9/5/2007}}
[[Category:Command Line Interface]]
[[Category:Calendar]]

Latest revision as of 17:34, 11 July 2015

Deleting appointments from command-line

   KB 2226        Last updated on 2015-07-11  




0.00
(0 votes)

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: Zimbra Collaboration 7.0, 6.0 Date Created: 9/5/2007
Article ID: https://wiki.zimbra.com/index.php?title=Deleting_appointments_from_command-line Date Modified: 2015-07-11



Try Zimbra

Try Zimbra Collaboration with a 60-day free trial.
Get it now »

Want to get involved?

You can contribute in the Community, Wiki, Code, or development of Zimlets.
Find out more. »

Looking for a Video?

Visit our YouTube channel to get the latest webinars, technology news, product overviews, and so much more.
Go to the YouTube channel »

Jump to: navigation, search