Funambol, BlackBerries and duplicate appointments

[uxbod]

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 ?

[axterics]

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?

[uxbod]

#!/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

[uxbod]

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 ?

[uxbod]

Funambol :: Duplicate Calendar Entries

[axterics]

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.

[uxbod]

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.

[thesubmitter]

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

[adrian.gibanel.btactic]

Originally Posted by uxbod

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!

#!/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;