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

Tue Sep 16 15:33:39 EDT 2014

Hi 

Never mind this, the problem was actually still related to rate-o-mat having 3000k CDRs pending to rate.

In case anyone else runs into a similar problem:

As today, rate-o-mat "only" has 1200k CDRs pending to rate and cdr-exporter has been writing CDRs again at intervals. (Check my previous post "[Spce-user] Rate-o-mat can't rate CDRs after terminating subscriber on SPCE mr3.2.1" to understand where this comes from)

Looking into the code of rate-o-mat and cdr-exporter (although I have no idea of perl) the thing is that rate-o-mat uses "start_time" in the SQL query to decide what to rate and cdr-exporter uses "id" in the SQL query to decide what to export.

After understanding that I was relieved cause it is now a matter of time until rate-o-mat catches up and cdr-exporter does its job.

Thanks 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: Saturday, September 13, 2014 5:13:16 AM
> Subject: Re: [Spce-user] Cdr-exporter is not writting CDRs to files

> Based upon the query, I do believe accounting.cdr would be where you want to
> look.

> Try:
> select * from accounting.cdr limit 10;

> I tried this on my SPCE test environment, and the table does have a column
> called cdr.id
> Let me know if this is what you're looking for.

> On Fri, Sep 12, 2014 at 6:21 PM, Joel S. | VOZELIA < joel at vozelia.com >
> wrote:

> > 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.
> 

> > > 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
> > > 
> > 
> 

> --
> Jeremy D. Ward
> Consulting Network Engineer
> (954) 661-4965