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

[Leandro Dardini]

2013/8/20 Kaloyan Kovachev <kkovachev at varna.net>

> Hi,
>
> On 2013-08-20 18:22, Leandro Dardini wrote:
>
>> 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.
>>
>>
> You may always use the call's uniquieid for the key and you need a key in
> order to find a duplicate and make use of the INSERT IGNORE or ON DUPLICATE
> KEY
>
>
>  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.
>>
>
> Unfortunately it won't fix your problem and in fact may cause even bigger
> problems ... consider the second server was offline for few hours and it is
> currently synchronizing with the other master - you insert a row which is
> not yet replicated, so there is no duplication, but then some time later
> you try to insert a duplicate, which have succeeded on the master, but
> fails on the slave - this will stop the replication with an error message
> and this will keep repeating for a long time until both masters are in full
> sync


I have think about this possible problem and I don't think it will never
happen. The master is storing the exact command sent by the client, so the
INSERT IGNORE will be sent to the secondary. If there was some sort of
"split brain", then the INSERT with duplicate keys will be ignored.


>
>
>
>> The patch was trivial, plus it seems not to be of any interest...
>> sorry for bothering :-)
>>
>>
> Do not patch - store duplicate records and then filter them out by INSERT
> IGNORE to another table or SELECT with GROUP BY 'uniueid'


That was my first idea, but it fails miserably when I start to build
statistics over the cdr. It is 20x slower having to "select distinct" and
then to apply sums functions.


>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.digium.com/pipermail/asterisk-dev/attachments/20130820/1357eb37/attachment.htm>