[Spce-user] Database Question (CDRs) v2.6

[Aníbal Cañada]

Hi , sorry for my late response:

- I put the archives in plain text:

File repetidas:

usr/bin/mysql -u sipwise --password=arsa < /root/limpieza/repetidas.sql2 >> /root/limpieza/registro.txt
/usr/bin/mysql -u sipwise --password=arsa < /root/limpieza/repetidas.sql > /root/limpieza/repetidas.txt
/bin/bash /root/limpieza/borrar_repetidas /root/limpieza/repetidas.txt
/usr/bin/mysql -u sipwise --password=arsa < /root/limpieza/borrar.sql
/bin/echo "$(($(date +%s)/3600%24))":"$(($(date +%s)/60%60))":"$(($(date +%s)%60))"" realizado"  >> /root/limpieza/registro.txt
/bin/touch /root/limpieza/repetidas.txt
cp -R /var/lib/my/ /var/db/

File 	repetidas.sql2:

select id,call_type,init_time,source_external_subscriber_id, destination_user_dialed,customer_cost,call_id ,update_time,count(call_id) as count from accounting.cdr where call_type='call' group by call_id having count(*) > 1 ORDER BY id;

File repetidas.sql:

select id from accounting.cdr where call_type='call' group by call_id having count(*) > 1 ORDER BY id;

File borrar_repetidas:

!/bin/bash
declare -a ARRAY
# leemos archivo
exec < $1
let count=0
while read LINE; do
    ARRAY[$count]=$LINE
    ((count++))
done
#echo Number of elements: ${#ARRAY[@]}
#borramos llamadas repetidas
max=${#ARRAY[@]}
for i in `seq 2 $max`
do
    echo "delete from accounting.cdr where id='"${ARRAY[$i-1]}"';" >> /root/limpieza/borrar.sql
done
# restore stdin from filedescriptor 10
# and close filedescriptor 10
#exec 0<&10 10<&-


That are the files than i used to clean the mysql tables.
I put all the files in /root , and i put file repetidas in cron every one hour. 
Put all the log in /root/limpieza/registro.txt, for control the deleted CDRs.

Cheers,
Anibal 

El 04/04/2013, a las 22:26, Andreas Granig <agranig at sipwise.com> escribió:

> Hi,
> 
> On 04/04/2013 07:43 PM, Kevin Masse wrote:
>> Thank you for the valuable input Mario, currently it is on a dedicated system and our programmers have just reworked what we pull for records making sure we have (distinct(start_time)) and this allowed us to move forward without the duplicates.
> 
> This will break correct billing for call forwards, as there are two records with the same start time, but different call types (one call, and one (or more) cfb or cft or similar).
> 
> We'll look into mediator to check whether we can reproduce this double-bye -> duplicate cdr issue.
> 
> Andreas
> 
> _______________________________________________
> Spce-user mailing list
> Spce-user at lists.sipwise.com
> http://lists.sipwise.com/listinfo/spce-user