[asterisk-dev] CDR on multiple database servers - getting rid of duplicates

[Leandro Dardini]

I agree with you. Usually if you have database in multimaster replication
mode, writing to one of the server is enough to make the info be written in
both. Unfortunately the cdr_adaptive_odbc driver cannot use a failover
configuration as working for example in the func_odbc driver. I had no
choice other than to write the CDR info to both databases. I am not sure
which one will be available. At the end I get two records for every call
and I have to filter out the duplicates. Obviously I have no primary/unique
index on the cdr table.

Maybe I was not clear, I was proposing a patch to add a parameter to
cdr_adaptive_odbc.conf to allow the usage of INSERT IGNORE. This way I can
configure a primary/unique index on the uniqueid of the cdr table and avoid
having to deal with duplicates.

The patch was trivial, plus it seems not to be of any interest... sorry for
bothering :-)

Leandro


2013/8/20 Gaston Draque <gaston.draque at gmail.com>

> Hi Leandro,
>    First, Im not sure why the solution was layed out like that, but as a
> rule of thumb, a single unique event should not be writen to 2 different
> mysql servers when they are running on a master-master replication scheme.
> This in itself is wrong and should be avoided.
>    If for topology reasons, you have 2 groups of Asterisk servers, each
> one writing to one specific mysql server then to do so, use the mysql
> increment and offset parameters to prevent collisions.
>    On a second note, I believe the asterisk-users list is better suited
> for this kind of questions and you will get more answers.
>
> Gaston//
>
>
>
> On Tue, Aug 20, 2013 at 7:24 AM, Leandro Dardini <ldardini at gmail.com>wrote:
>
>> Hello,
>> I am running a cluster of asterisk servers and each one is writing the
>> CDR to 2 mysql servers configured with multi master replication using the
>> cdr_adaptive_odbc driver.
>>
>> cdr_adaptive_odbc.conf has the following configuration:
>>
>> [cdr1]
>> connection=asteriskcdrdb1
>> table=cdrraw
>>
>> [cdr2]
>> connection=asteriskcdrdb2
>> table=cdrraw
>>
>> As you can imagine, if both mysql servers are available, every CDR is
>> written twice.
>>
>> To get rid of duplicates, every query against the cdr database need to
>> use the "DISTINCT" using an intermediate table and that is slow, really
>> slow.
>>
>> Nothing is better if I use a view with the SELECT DISTINCT, it is slower
>> than before.
>>
>> The only solution I see to add an option in cdr_adaptive_odbc.conf and if
>> set to change the INSERT in the cdr_adaptive_odbc.conf adding a "ON
>> DUPLICATE KEY UPDATE..." or better a "INSERT IGNORE"
>>
>> What do you think about it?
>>
>> Leandro
>>
>> --
>> _____________________________________________________________________
>> -- Bandwidth and Colocation Provided by http://www.api-digital.com --
>>
>> asterisk-dev mailing list
>> To UNSUBSCRIBE or update options visit:
>>    http://lists.digium.com/mailman/listinfo/asterisk-dev
>>
>
>
>
> --
> {
>   "fullName" : "Gaston Draque"
>   "email"    : "gaston.draque at gmail.com"
>   "twitter"  : "@gdraque"
>   "google+"  : "gplus.to/gdraque"
>   "linkedIn" : "http://www.linkedin.com/in/draque"
>   "tags"     : ["uc communications","telecom integrator","voip","software
> development"]
> }
>
>
> --
> _____________________________________________________________________
> -- Bandwidth and Colocation Provided by http://www.api-digital.com --
>
> asterisk-dev mailing list
> To UNSUBSCRIBE or update options visit:
>    http://lists.digium.com/mailman/listinfo/asterisk-dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.digium.com/pipermail/asterisk-dev/attachments/20130820/bbb956c7/attachment-0001.htm>