Results 1 to 10 of 10

Thread: Funambol, BlackBerries and duplicate appointments

  1. #1
    uxbod's Avatar
    uxbod is offline Moderator
    Join Date
    Nov 2006
    Location
    UK
    Posts
    8,017
    Rep Power
    24

    Default Funambol, BlackBerries and duplicate appointments

    Hi,

    If anybody is running this configuration do you suffer from duplicated appointments ? One of our clients has just managed to get their BB to send out 300 calendar entries

    I have a script to de-dupe them but was wondering if anybody else has seen the same thing ?

  2. #2
    axterics is offline New Member
    Join Date
    Nov 2009
    Posts
    4
    Rep Power
    5

    Default

    I can confirm the issue. I don't analyze yet if all the appointments or which and why...
    Can you post the script? Have you done any analysis?

  3. #3
    uxbod's Avatar
    uxbod is offline Moderator
    Join Date
    Nov 2006
    Location
    UK
    Posts
    8,017
    Rep Power
    24

    Default

    Code:
    #!/usr/bin/perl
    
    use DBI;
    
    $account = $ARGV[0];
    $subject = $ARGV[1];
    open(PIPE,"zmlocalconfig -s zimbra_mysql_password|");  ($passwd) = <PIPE> =~ /zimbra_mysql_password = (.*)/; close(PIPE);
    
    $dsn = "DBI:mysql:zimbra;mysql_read_default_file=/opt/zimbra/conf/my.cnf;mysql_socket=/opt/zimbra/db/mysql.sock";
    $dbh = DBI->connect($dsn, 'zimbra', $passwd) || die "Could not connect to database: $DBI::errstr";
    
    $sql = "select id from mailbox where comment = '${account}'";
    $sth = $dbh->prepare($sql);
    $sth->execute();
    @result = $sth->fetchrow_array; $mailbox = $result[0];
    
    $sth->finish();
    $dbh->disconnect;
    
    my @dupes = (); my @dupes_subject = ();
    
    $dsn = "DBI:mysql:mboxgroup${mailbox};mysql_read_default_file=/opt/zimbra/conf/my.cnf;mysql_socket=/opt/zimbra/db/mysql.sock";
    $dbh = DBI->connect($dsn, 'zimbra', $passwd) || die "Could not connect to database: $DBI::errstr";
    
    $sql = "select a.start_time, a.end_time, b.subject, count(*) from appointment a, mail_item b ";
    $sql = $sql . "where a.item_id = b.id ";
    $sql = $sql . "group by b.subject, a.start_time, a.end_time having count(*) > 1 order by b.subject, a.start_time, a.end_time";
    
    $sth = $dbh->prepare($sql); $sth->execute();
    
    while (($start_time, $end_time, $subject, $count) = $sth->fetchrow_array) {
    
        $sql = "select a.item_id from appointment a, mail_item b ";
        $sql = $sql . "where a.start_time = '$start_time' and a.end_time = '$end_time' and b.subject = \"$subject\" and a.item_id = b.id ";
        $sql = $sql . "order by a.item_id";
    
        $dupe = 0;
        $sth2 = $dbh->prepare($sql); $sth2->execute();
        while ($id = $sth2->fetchrow_array) {
            if ($dupe > 0) { print "zmmailbox -z -m $account deleteitem $id\n"; }
            $dupe += 1;
        }
        $sth2->finish();
    }
    
    $sth->finish();
    $dbh->disconnect;
    The script is required to be run as the zimbra user and the only argument it expects is the email account name you wish to de-dupe. I recommend that you back up the account before running the script and probably should test it first.

    Unfortunately have not been able to track down why it is happening yet as I do not have access to a BB myself. Will keep hunting though

  4. #4
    uxbod's Avatar
    uxbod is offline Moderator
    Join Date
    Nov 2006
    Location
    UK
    Posts
    8,017
    Rep Power
    24

    Default

    Funambol is failing with a different Blackberry bug where every time the Blackberry receives a meeting request from someone else, it then reinterprets the meeting request as a new meeting it has created and sends its own meeting requests to every other recipient of the original meeting request Is this the same as what you are seeing ?

  5. #5
    uxbod's Avatar
    uxbod is offline Moderator
    Join Date
    Nov 2006
    Location
    UK
    Posts
    8,017
    Rep Power
    24

  6. #6
    axterics is offline New Member
    Join Date
    Nov 2009
    Posts
    4
    Rep Power
    5

    Default

    Can I ask you how works the perl script you posted?
    If I download the .ics file and check the number of duplicate elements it is very low instead the script number output is hight.
    I try it and it works but why if I deleted a duplicated appointment via webGUI and I count the delete $id lines before and after the result is the same?

    Thanks a lot again.

  7. #7
    uxbod's Avatar
    uxbod is offline Moderator
    Join Date
    Nov 2006
    Location
    UK
    Posts
    8,017
    Rep Power
    24

    Default

    I look at where the subject, start time and end time are the same. If the count is > 1 then I treated it as a duplicate.

  8. #8
    thesubmitter is offline Active Member
    Join Date
    Jun 2008
    Posts
    41
    Rep Power
    7

    Default

    I had this issue, what a mess....

  9. #9
    adrian.gibanel.btactic is offline Senior Member
    Join Date
    Nov 2010
    Posts
    54
    Rep Power
    4

    Post Improved script for detecting duplicated calendar appointments

    Quote Originally Posted by uxbod View Post
    The script is required to be run as the zimbra user and the only argument it expects is the email account name you wish to de-dupe. I recommend that you back up the account before running the script and probably should test it first.
    Thank you very much uxbod!

    I have updated the script because it did not reflect the mailbox group rotation that have mailbox users as described on Account_mailbox_database_structure.

    It would be nice if you could check my changes so that I have not made any mistake. Thank you again!

    Code:
    #!/usr/bin/perl
    
    use DBI;
    
    $account = $ARGV[0];
    $subject = $ARGV[1];
    open(PIPE,"zmlocalconfig -s zimbra_mysql_password|");  ($passwd) = <PIPE> =~ /zimbra_mysql_password = (.*)/; close(PIPE);
    
    $dsn = "DBI:mysql:zimbra;mysql_read_default_file=/opt/zimbra/conf/my.cnf;mysql_socket=/opt/zimbra/db/mysql.sock";
    $dbh = DBI->connect($dsn, 'zimbra', $passwd) || die "Could not connect to database: $DBI::errstr";
    
    
    $sql = "select id,group_id from mailbox where comment = '${account}'";
    $sth = $dbh->prepare($sql);
    $sth->execute();
    @result = $sth->fetchrow_array; 
      $mailbox = $result[0];
      $mailbox_group = $result[1];
    
    $sth->finish();
    $dbh->disconnect;
    
    my @dupes = (); my @dupes_subject = ();
    
    $dsn = "DBI:mysql:mboxgroup${mailbox_group};mysql_read_default_file=/opt/zimbra/conf/my.cnf;mysql_socket=/opt/zimbra/db/mysql.sock";
    $dbh = DBI->connect($dsn, 'zimbra', $passwd) || die "Could not connect to database: $DBI::errstr";
    
    $sql = "select a.start_time, a.end_time, b.subject, count(*) from appointment a, mail_item b ";
    $sql = $sql . "where (a.item_id = b.id) and (b.mailbox_id = " . $mailbox . ")";
    $sql = $sql . "group by b.subject, a.start_time, a.end_time having count(*) > 1 order by b.subject, a.start_time, a.end_time";
    
    $sth = $dbh->prepare($sql); $sth->execute();
    
    while (($start_time, $end_time, $subject, $count) = $sth->fetchrow_array) {
    
        $sql = "select a.item_id from appointment a, mail_item b ";
    	$sql = $sql . "where a.start_time = '$start_time' and a.end_time = '$end_time' and b.subject = \"$subject\" and a.item_id = b.id ";
    	$sql = $sql . "order by a.item_id";
    
    	$dupe = 0;
    	$sth2 = $dbh->prepare($sql); $sth2->execute();
    	while ($id = $sth2->fetchrow_array) {
    	    if ($dupe > 0) { print "zmmailbox -z -m $account deleteitem $id\n"; }
    	    $dupe += 1;
        }
        $sth2->finish();
    }
    
    $sth->finish();
    $dbh->disconnect;

  10. #10
    andyb is offline New Member
    Join Date
    Dec 2011
    Posts
    4
    Rep Power
    3

    Default Running your script from cron

    Quote Originally Posted by uxbod View Post
    The script is required to be run as the zimbra user and the only argument it expects is the email account name you wish to de-dupe. I recommend that you back up the account before running the script and probably should test it first.

    Unfortunately have not been able to track down why it is happening yet as I do not have access to a BB myself. Will keep hunting though
    I too have this problem. Script works for me, thanks. However can you point me to a reason as to why it might not work when running from cron? In the output I see that the connection to the database fails:

    .... Access denied for user 'zimbra'@'localhost' (using password:NO) ....

    thanks

    Andy

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •