[Spce-user] Cdr-exporter is not writting CDRs to files

[Joel S. | VOZELIA]

Hi Jeremy, 

This is the query that cdr-exporter runs: 

select cdr.id, update_time, 
source_user_id, source_provider_id, 
source_external_subscriber_id, source_bvs.id AS source_subscriber_id, 
source_external_contract_id, source_account_id, 
source_user, source_domain, 
source_cli, source_clir, source_ip, 
destination_user_id, destination_provider_id, 
destination_external_subscriber_id, destination_bvs.id AS destination_subscriber_id, 
destination_external_contract_id, destination_account_id, 
destination_user, destination_domain, 
destination_user_in, destination_domain_in, destination_user_dialed, 
peer_auth_user, peer_auth_realm, 
call_type, call_status, 
call_code, CONCAT(FROM_UNIXTIME(start_time), '.', SUBSTRING_INDE start_time, '.', -1)) AS start_time, 
CONCAT(FROM_UNIXTIME(init_time), '.', SUBSTRING_INDE init_time, '.', -1)) AS init_time, 
duration, call_id, 
rating_status, rated_at, 
source_carrier_cost, source_reseller_cost, source_customer_cost, 
source_carrier_free_time, source_reseller_free_time, source_customer_free_time, 
source_carrier_bbz.zone AS source_carrier_zone, source_reseller_bbz.zone AS source_reseller_zone, 
source_customer_bbz.zone AS source_customer_zone, source_carrier_bbz.detail AS source_carrier_destination, 
source_reseller_bbz.detail AS source_reseller_destination, source_customer_bbz.detail AS source_customer_destination, 
destination_carrier_cost, destination_reseller_cost, destination_customer_cost, 
destination_carrier_free_time, destination_reseller_free_time, destination_customer_free_time, 
destination_carrier_bbz.zone AS destination_carrier_zone, destination_reseller_bbz.zone AS destination_reseller_zone, 
destination_customer_bbz.zone AS destination_customer_zone, destination_carrier_bbz.detail AS destination_carrier_destination, 
destination_reseller_bbz.detail AS destination_reseller_destination, destination_customer_bbz.detail AS destination_customer_destination 
from accounting.cdr 
LEFT JOIN billing.billing_zones_history source_carrier_bbz ON cdr.source_carrier_billing_zone_id = source_carrier_bbz.id 
LEFT JOIN billing.billing_zones_history source_reseller_bbz ON cdr.source_reseller_billing_zone_id = source_reseller_bbz.id 
LEFT JOIN billing.billing_zones_history source_customer_bbz ON cdr.source_customer_billing_zone_id = source_customer_bbz.id 
LEFT JOIN billing.billing_zones_history destination_carrier_bbz ON cdr.destination_carrier_billing_zone_id = destination_carrier_bbz.id 
LEFT JOIN billing.billing_zones_history destination_reseller_bbz ON cdr.destination_reseller_billing_zone_id = destination_reseller_bbz.id 
LEFT JOIN billing.billing_zones_history destination_customer_bbz ON cdr.destination_customer_billing_zone_id = destination_customer_bbz.id 
LEFT JOIN billing.voip_subscribers source_bvs ON cdr.source_user_id = source_bvs.uuid 
LEFT JOIN billing.voip_subscribers destination_bvs ON cdr.destination_user_id = destination_bvs.uuid 
where cdr.export_status = 'unexported' AND cdr.id > ? 
". ($EXPORT_INCOMING eq 'yes' ? '' : "and source_user_id != '0'") ." 
". ($EXPORT_FAILED eq 'yes' ? '' : "and call_status = 'ok'") ." 
order by 
cdr.id 
limit 5000; 

I checked the table you mentioned earlier but no clues: 

mysql> select * from billing.billing_zones_history; 
+----+-------+--------------------+-------------------+------------------+ 
| id | bz_id | billing_profile_id | zone | detail | 
+----+-------+--------------------+-------------------+------------------+ 
| 1 | 1 | 1 | Free Default Zone | All Destinations | 
+----+-------+--------------------+-------------------+------------------+ 
1 row in set (0.00 sec) 

mysql> 

Thank you for your help! 

Best regards, 

Joel. 

----- Original Message -----

> From: "Jeremy Ward" <jward01 at gmail.com>
> To: "Joel S. | VOZELIA" <joel at vozelia.com>
> Cc: "Spce-user" <spce-user at lists.sipwise.com>
> Sent: Friday, September 12, 2014 10:29:24 PM
> Subject: Re: [Spce-user] Cdr-exporter is not writting CDRs to files

> Joel,

> Can you post the entire loop-join query that you have at the end of your last
> message?

> Maybe I can help.

> Thanks.

> Jeremy D. Ward, CWNE
> (954) 661-4965
> On Sep 12, 2014 3:59 PM, "Joel S. | VOZELIA" < joel at vozelia.com > wrote:

> > Hi everybody,
> 

> > First of all, this is a followup on my previous post: [Spce-user]
> > Rate-o-mat
> > can't rate CDRs after terminating subscriber on SPCE mr3.2.1
> 

> > I have 4000k CDRs to rate and export. To speed things up, last night I
> > tried
> > to modify the 5000 limit set in the query cdr-exporter script uses to
> > obtain
> > its results:
> 

> > Before:
> 

> > my $limit = 5000;
> 

> > After:
> 

> > my $limit = 10000;
> 

> > Apparently it worked ok (notice the size and length of the last two files):
> 

> > -rw-r--r-- 1 cdrexport cdrexport 2502925 Sep 12 00:25
> > sipwise_007_20140912002501_0000007321.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 2503036 Sep 12 00:25
> > sipwise_007_20140912002501_0000007322.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 1414621 Sep 12 00:25
> > sipwise_007_20140912002501_0000007323.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 2502650 Sep 12 00:55
> > sipwise_007_20140912005501_0000007324.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 2503513 Sep 12 00:55
> > sipwise_007_20140912005501_0000007325.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 1651536 Sep 12 00:55
> > sipwise_007_20140912005501_0000007326.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 2504604 Sep 12 01:25
> > sipwise_007_20140912012501_0000007327.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 2505834 Sep 12 01:25
> > sipwise_007_20140912012501_0000007328.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 2333383 Sep 12 01:25
> > sipwise_007_20140912012501_0000007329.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 2505978 Sep 12 01:55
> > sipwise_007_20140912015501_0000007330.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 2504757 Sep 12 01:55
> > sipwise_007_20140912015501_0000007331.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 2400073 Sep 12 01:55
> > sipwise_007_20140912015501_0000007332.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 2505388 Sep 12 02:25
> > sipwise_007_20140912022501_0000007333.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 2504018 Sep 12 02:25
> > sipwise_007_20140912022501_0000007334.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 2361539 Sep 12 02:25
> > sipwise_007_20140912022501_0000007335.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 2504660 Sep 12 02:55
> > sipwise_007_20140912025501_0000007336.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 2503450 Sep 12 02:55
> > sipwise_007_20140912025501_0000007337.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 2380460 Sep 12 02:55
> > sipwise_007_20140912025501_0000007338.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 5006355 Sep 12 03:25
> > sipwise_007_20140912032501_0000007339.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 1853260 Sep 12 03:25
> > sipwise_007_20140912032501_0000007340.cdr
> 

> > 5002 sipwise_007_20140912002501_0000007321.cdr
> 
> > 5002 sipwise_007_20140912002501_0000007322.cdr
> 
> > 2828 sipwise_007_20140912002501_0000007323.cdr
> 
> > 5002 sipwise_007_20140912005501_0000007324.cdr
> 
> > 5002 sipwise_007_20140912005501_0000007325.cdr
> 
> > 3298 sipwise_007_20140912005501_0000007326.cdr
> 
> > 5002 sipwise_007_20140912012501_0000007327.cdr
> 
> > 5002 sipwise_007_20140912012501_0000007328.cdr
> 
> > 4658 sipwise_007_20140912012501_0000007329.cdr
> 
> > 5002 sipwise_007_20140912015501_0000007330.cdr
> 
> > 5002 sipwise_007_20140912015501_0000007331.cdr
> 
> > 4797 sipwise_007_20140912015501_0000007332.cdr
> 
> > 5002 sipwise_007_20140912022501_0000007333.cdr
> 
> > 5002 sipwise_007_20140912022501_0000007334.cdr
> 
> > 4720 sipwise_007_20140912022501_0000007335.cdr
> 
> > 5002 sipwise_007_20140912025501_0000007336.cdr
> 
> > 5002 sipwise_007_20140912025501_0000007337.cdr
> 
> > 4757 sipwise_007_20140912025501_0000007338.cdr
> 
> > 10002 sipwise_007_20140912032501_0000007339.cdr
> 
> > 3702 sipwise_007_20140912032501_0000007340.cdr
> 

> > After that one run, I set it back to 5000 and since then, every file is
> > empty: (notice the size):
> 

> > -rw-r--r-- 1 cdrexport cdrexport 42 Sep 12 03:55
> > sipwise_007_20140912035501_0000007341.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 42 Sep 12 04:25
> > sipwise_007_20140912042502_0000007342.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 42 Sep 12 04:55
> > sipwise_007_20140912045501_0000007343.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 42 Sep 12 05:25
> > sipwise_007_20140912052501_0000007344.cdr
> 
> > -rw-r--r-- 1 cdrexport cdrexport 42 Sep 12 05:55
> > sipwise_007_20140912055501_0000007345.cdr
> 

> > 2 sipwise_007_20140912035501_0000007341.cdr
> 
> > 2 sipwise_007_20140912042502_0000007342.cdr
> 
> > 2 sipwise_007_20140912045501_0000007343.cdr
> 
> > 2 sipwise_007_20140912052501_0000007344.cdr
> 
> > 2 sipwise_007_20140912055501_0000007345.cdr
> 

> > I don't know how but I've messed things up :(
> 

> > I can't get cdr-exporter to actually export any CDRs, it always writes 0
> > lines:
> 

> > # cdr-exporter
> 
> > +++ Start run with DB exporter at accounting to sipwise
> 
> > --- Starting CDR export
> 
> > ### 0 data lines written to
> > /home/jail/home/cdrexport/system/201409/12/sipwise_007_20140912211517_0000007382.cdr.24010,
> > checksum is 9b8bd11538a55b017aab6b2ce9d7374f
> 
> > ### successfully moved
> > /home/jail/home/cdrexport/system/201409/12/sipwise_007_20140912211517_0000007382.cdr.24010
> > to
> > /home/jail/home/cdrexport/system/201409/12/sipwise_007_20140912211517_0000007382.cdr
> 
> > ::: Updated DB marks, all done.
> 

> > Database:
> 

> > mysql> select count(*) from accounting.cdr where export_status =
> > "unexported";
> 
> > +----------+
> 
> > | count(*) |
> 
> > +----------+
> 
> > | 4333587 |
> 
> > +----------+
> 
> > 1 row in set (1.22 sec)
> 

> > mysql>
> 

> > mysql> select count(*) from accounting.cdr where rating_status = "ok" and
> > export_status = "unexported" ;
> 
> > +----------+
> 
> > | count(*) |
> 
> > +----------+
> 
> > | 1222721 |
> 
> > +----------+
> 
> > 1 row in set (2.53 sec)
> 

> > mysql>
> 

> > mysql> select max(id) from cdr;
> 
> > +---------+
> 
> > | max(id) |
> 
> > +---------+
> 
> > | 8939578 |
> 
> > +---------+
> 
> > 1 row in set (0.00 sec)
> 

> > mysql>
> 

> > My last correctly exported CDR id is: 5560563
> 

> > Since that one last correct execution (where I modified the limit from 5000
> > to 10000), every single CDR file is empty.
> 

> > I am again lost, I've tried to understand how cdr-exporter works but I
> > don't
> > know perl.
> 

> > The test I thought I could start with is try run the query that
> > cdr-exporter
> > runs, but I don't know from where It gets the cdr.id it filters with:
> 

> > [...]
> 

> > LEFT JOIN billing.billing_zones_history destination_customer_bbz ON
> > cdr.destination_customer_billing_zone_id = destination_customer_bbz.id
> 
> > LEFT JOIN billing.voip_subscribers source_bvs ON cdr.source_user_id =
> > source_bvs.uuid
> 
> > LEFT JOIN billing.voip_subscribers destination_bvs ON
> > cdr.destination_user_id
> > = destination_bvs.uuid
> 
> > where cdr.export_status = 'unexported' AND cdr.id > ?
> 
> > ". ($EXPORT_INCOMING eq 'yes' ? '' : "and source_user_id != '0'") ."
> 
> > ". ($EXPORT_FAILED eq 'yes' ? '' : "and call_status = 'ok'") ."
> 
> > order by
> 
> > cdr.id
> 

> > [...]
> 

> > How can I start troubleshooting this?
> 

> > Thanks in advanced.
> 

> > Best regards,
> 

> > Joel.
> 

> > _______________________________________________
> 
> > Spce-user mailing list
> 
> > Spce-user at lists.sipwise.com
> 
> > https://lists.sipwise.com/listinfo/spce-user
> 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20140913/1ff7effc/attachment-0001.html>