<html><body><div style="font-family: arial,helvetica,sans-serif; font-size: 10pt; color: #000000"><div style="font-size: 13.63636302948px; background-color: #fdfdfd;" data-mce-style="font-size: 13.63636302948px; background-color: #fdfdfd;">Hi Jeremy, </div><div style="font-size: 13.63636302948px; background-color: #fdfdfd;" data-mce-style="font-size: 13.63636302948px; background-color: #fdfdfd;"><br></div><div style="font-size: 13.63636302948px; background-color: #fdfdfd;" data-mce-style="font-size: 13.63636302948px; background-color: #fdfdfd;">This is the query that cdr-exporter runs:</div><div style="font-size: 13.63636302948px; background-color: #fdfdfd;" data-mce-style="font-size: 13.63636302948px; background-color: #fdfdfd;"><br></div><div style="font-size: 13.63636302948px; background-color: #fdfdfd;" data-mce-style="font-size: 13.63636302948px; background-color: #fdfdfd;"><p style="margin: 0px;" data-mce-style="margin: 0px;">select cdr.id, update_time,<br>source_user_id, source_provider_id,<br>source_external_subscriber_id, source_bvs.id AS source_subscriber_id,<br>source_external_contract_id, source_account_id,<br>source_user, source_domain,<br>source_cli, source_clir, source_ip,<br>destination_user_id, destination_provider_id,<br>destination_external_subscriber_id, destination_bvs.id AS destination_subscriber_id,<br>destination_external_contract_id, destination_account_id,<br>destination_user, destination_domain,<br>destination_user_in, destination_domain_in, destination_user_dialed,<br>peer_auth_user, peer_auth_realm,<br>call_type, call_status,<br>call_code, CONCAT(FROM_UNIXTIME(start_time), '.', SUBSTRING_INDE<span title="X( - angry" style="height: 18px; width: 34px; padding: 9px 34px 9px 0px; background: url('https://zcs.vozelia.com/service/zimlet/com_zimbra_ymemoticons/img/14.gif') 0px 50% no-repeat;" data-mce-style="height: 18px; width: 34px; padding: 9px 34px 9px 0px; background: url('https://zcs.vozelia.com/service/zimlet/com_zimbra_ymemoticons/img/14.gif') 0px 50% no-repeat;"><span style="visibility: hidden;" data-mce-style="visibility: hidden;"></span></span>start_time, '.', -1)) AS start_time,<br>CONCAT(FROM_UNIXTIME(init_time), '.', SUBSTRING_INDE<span title="X( - angry" style="height: 18px; width: 34px; padding: 9px 34px 9px 0px; background: url('https://zcs.vozelia.com/service/zimlet/com_zimbra_ymemoticons/img/14.gif') 0px 50% no-repeat;" data-mce-style="height: 18px; width: 34px; padding: 9px 34px 9px 0px; background: url('https://zcs.vozelia.com/service/zimlet/com_zimbra_ymemoticons/img/14.gif') 0px 50% no-repeat;"><span style="visibility: hidden;" data-mce-style="visibility: hidden;"></span></span>init_time, '.', -1)) AS init_time,<br>duration, call_id,<br>rating_status, rated_at,<br>source_carrier_cost, source_reseller_cost, source_customer_cost,<br>source_carrier_free_time, source_reseller_free_time, source_customer_free_time,<br>source_carrier_bbz.zone AS source_carrier_zone, source_reseller_bbz.zone AS source_reseller_zone,<br>source_customer_bbz.zone AS source_customer_zone, source_carrier_bbz.detail AS source_carrier_destination,<br>source_reseller_bbz.detail AS source_reseller_destination, source_customer_bbz.detail AS source_customer_destination,<br>destination_carrier_cost, destination_reseller_cost, destination_customer_cost,<br>destination_carrier_free_time, destination_reseller_free_time, destination_customer_free_time,<br>destination_carrier_bbz.zone AS destination_carrier_zone, destination_reseller_bbz.zone AS destination_reseller_zone,<br>destination_customer_bbz.zone AS destination_customer_zone, destination_carrier_bbz.detail AS destination_carrier_destination,<br>destination_reseller_bbz.detail AS destination_reseller_destination, destination_customer_bbz.detail AS destination_customer_destination<br>from accounting.cdr<br>LEFT JOIN billing.billing_zones_history source_carrier_bbz ON cdr.source_carrier_billing_zone_id = source_carrier_bbz.id<br>LEFT JOIN billing.billing_zones_history source_reseller_bbz ON cdr.source_reseller_billing_zone_id = source_reseller_bbz.id<br>LEFT JOIN billing.billing_zones_history source_customer_bbz ON cdr.source_customer_billing_zone_id = source_customer_bbz.id<br>LEFT JOIN billing.billing_zones_history destination_carrier_bbz ON cdr.destination_carrier_billing_zone_id = destination_carrier_bbz.id<br>LEFT JOIN billing.billing_zones_history destination_reseller_bbz ON cdr.destination_reseller_billing_zone_id = destination_reseller_bbz.id<br>LEFT JOIN billing.billing_zones_history destination_customer_bbz ON cdr.destination_customer_billing_zone_id = destination_customer_bbz.id<br>LEFT JOIN billing.voip_subscribers source_bvs ON cdr.source_user_id = source_bvs.uuid<br>LEFT JOIN billing.voip_subscribers destination_bvs ON cdr.destination_user_id = destination_bvs.uuid<br>where cdr.export_status = 'unexported' AND cdr.id > ?<br>". ($EXPORT_INCOMING eq 'yes' ? '' : "and source_user_id != '0'") ."<br>". ($EXPORT_FAILED eq 'yes' ? '' : "and call_status = 'ok'") ."<br>order by<br>cdr.id<br>limit 5000;</p><p style="margin: 0px;" data-mce-style="margin: 0px;"><br></p><p style="margin: 0px;" data-mce-style="margin: 0px;"><br></p><p style="margin: 0px;" data-mce-style="margin: 0px;"><br></p><p style="margin: 0px;" data-mce-style="margin: 0px;">I checked the table you mentioned earlier but no clues:</p><p style="margin: 0px;" data-mce-style="margin: 0px;"><br></p><p style="margin: 0px;" data-mce-style="margin: 0px;">mysql> select * from billing.billing_zones_history;<br>+----+-------+--------------------+-------------------+------------------+<br>| id | bz_id | billing_profile_id | zone | detail |<br>+----+-------+--------------------+-------------------+------------------+<br>| 1 | 1 | 1 | Free Default Zone | All Destinations |<br>+----+-------+--------------------+-------------------+------------------+<br>1 row in set (0.00 sec)</p><p style="margin: 0px;" data-mce-style="margin: 0px;">mysql></p><p style="margin: 0px;" data-mce-style="margin: 0px;"><br></p><p style="margin: 0px;" data-mce-style="margin: 0px;"><br></p><p style="margin: 0px;" data-mce-style="margin: 0px;">Thank you for your help!</p><p style="margin: 0px;" data-mce-style="margin: 0px;"><br></p><p style="margin: 0px;" data-mce-style="margin: 0px;">Best regards, </p><p style="margin: 0px;" data-mce-style="margin: 0px;">Joel.</p></div><div><br></div><hr id="zwchr"><blockquote style="border-left:2px solid #1010FF;margin-left:5px;padding-left:5px;color:#000;font-weight:normal;font-style:normal;text-decoration:none;font-family:Helvetica,Arial,sans-serif;font-size:12pt;"><b>From: </b>"Jeremy Ward" <jward01@gmail.com><br><b>To: </b>"Joel S. | VOZELIA" <joel@vozelia.com><br><b>Cc: </b>"Spce-user" <spce-user@lists.sipwise.com><br><b>Sent: </b>Friday, September 12, 2014 10:29:24 PM<br><b>Subject: </b>Re: [Spce-user] Cdr-exporter is not writting CDRs to files<br><div><br></div><p dir="ltr">Joel,</p>
<p dir="ltr">Can you post the entire loop-join query that you have at the end of your last message?</p>
<p dir="ltr">Maybe I can help.</p>
<p dir="ltr">Thanks.</p>
<p dir="ltr">Jeremy D. Ward, CWNE<br>
(954) 661-4965</p>
<div class="gmail_quote">On Sep 12, 2014 3:59 PM, "Joel S. | VOZELIA" <<a href="mailto:joel@vozelia.com" target="_blank">joel@vozelia.com</a>> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div style="font-family:arial,helvetica,sans-serif;font-size:10pt;color:#000000"><div><span style="font-size:10pt">Hi everybody, </span></div><div><br></div><div>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<br></div><div><br></div><div>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:<br></div><div><br></div><div>Before:<br></div><div><br></div><div>my $limit = 5000;<br></div><div><br></div><div>After:<br></div><div><br></div><div>my $limit = 10000;<br></div><div><br></div><div><br>Apparently it worked ok (notice the size and length of the last two files):<br></div><div><br></div><div>-rw-r--r-- 1 cdrexport cdrexport 2502925 Sep 12 00:25 sipwise_007_20140912002501_0000007321.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 2503036 Sep 12 00:25 sipwise_007_20140912002501_0000007322.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 1414621 Sep 12 00:25 sipwise_007_20140912002501_0000007323.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 2502650 Sep 12 00:55 sipwise_007_20140912005501_0000007324.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 2503513 Sep 12 00:55 sipwise_007_20140912005501_0000007325.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 1651536 Sep 12 00:55 sipwise_007_20140912005501_0000007326.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 2504604 Sep 12 01:25 sipwise_007_20140912012501_0000007327.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 2505834 Sep 12 01:25 sipwise_007_20140912012501_0000007328.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 2333383 Sep 12 01:25 sipwise_007_20140912012501_0000007329.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 2505978 Sep 12 01:55 sipwise_007_20140912015501_0000007330.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 2504757 Sep 12 01:55 sipwise_007_20140912015501_0000007331.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 2400073 Sep 12 01:55 sipwise_007_20140912015501_0000007332.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 2505388 Sep 12 02:25 sipwise_007_20140912022501_0000007333.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 2504018 Sep 12 02:25 sipwise_007_20140912022501_0000007334.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 2361539 Sep 12 02:25 sipwise_007_20140912022501_0000007335.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 2504660 Sep 12 02:55 sipwise_007_20140912025501_0000007336.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 2503450 Sep 12 02:55 sipwise_007_20140912025501_0000007337.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 2380460 Sep 12 02:55 sipwise_007_20140912025501_0000007338.cdr<br><span style="color:rgb(255,0,0)">-rw-r--r-- 1 cdrexport cdrexport 5006355 Sep 12 03:25 sipwise_007_20140912032501_0000007339.cdr</span><br><span style="color:rgb(255,0,0)">-rw-r--r-- 1 cdrexport cdrexport 1853260 Sep 12 03:25 sipwise_007_20140912032501_0000007340.cdr</span><br></div><div><br></div><div><p style="margin:0px"> 5002 sipwise_007_20140912002501_0000007321.cdr<br> 5002 sipwise_007_20140912002501_0000007322.cdr<br> 2828 sipwise_007_20140912002501_0000007323.cdr<br> 5002 sipwise_007_20140912005501_0000007324.cdr<br> 5002 sipwise_007_20140912005501_0000007325.cdr<br> 3298 sipwise_007_20140912005501_0000007326.cdr<br> 5002 sipwise_007_20140912012501_0000007327.cdr<br> 5002 sipwise_007_20140912012501_0000007328.cdr<br> 4658 sipwise_007_20140912012501_0000007329.cdr<br> 5002 sipwise_007_20140912015501_0000007330.cdr<br> 5002 sipwise_007_20140912015501_0000007331.cdr<br> 4797 sipwise_007_20140912015501_0000007332.cdr<br> 5002 sipwise_007_20140912022501_0000007333.cdr<br> 5002 sipwise_007_20140912022501_0000007334.cdr<br> 4720 sipwise_007_20140912022501_0000007335.cdr<br> 5002 sipwise_007_20140912025501_0000007336.cdr<br> 5002 sipwise_007_20140912025501_0000007337.cdr<br> 4757 sipwise_007_20140912025501_0000007338.cdr<br> <span style="color:rgb(255,0,0)">10002 sipwise_007_20140912032501_0000007339.cdr</span><br><span style="color:rgb(255,0,0)"> 3702 sipwise_007_20140912032501_0000007340.cdr</span></p></div><div><br></div><div>After that one run, I set it back to 5000 and since then, every file is empty: (notice the size):</div><div><br></div><div><p style="margin:0px">-rw-r--r-- 1 cdrexport cdrexport 42 Sep 12 03:55 sipwise_007_20140912035501_0000007341.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 42 Sep 12 04:25 sipwise_007_20140912042502_0000007342.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 42 Sep 12 04:55 sipwise_007_20140912045501_0000007343.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 42 Sep 12 05:25 sipwise_007_20140912052501_0000007344.cdr<br>-rw-r--r-- 1 cdrexport cdrexport 42 Sep 12 05:55 sipwise_007_20140912055501_0000007345.cdr</p></div><div><br></div><div><p style="margin:0px">2 sipwise_007_20140912035501_0000007341.cdr<br> 2 sipwise_007_20140912042502_0000007342.cdr<br> 2 sipwise_007_20140912045501_0000007343.cdr<br> 2 sipwise_007_20140912052501_0000007344.cdr<br> 2 sipwise_007_20140912055501_0000007345.cdr</p><p style="margin:0px"><br></p><p style="margin:0px"><span style="font-size:10pt">I don't know how but I've messed things up :(</span></p></div><div><br></div><div><br></div><div>I can't get cdr-exporter to actually export any CDRs, it always writes 0 lines:</div><div><br></div><div><p style="margin:0px"><span style="font-family:'courier new',courier,monaco,monospace,sans-serif"># cdr-exporter</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">+++ Start run with DB exporter@accounting to sipwise</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">--- Starting CDR export</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">### 0 data lines written to /home/jail/home/cdrexport/system/201409/12/sipwise_007_20140912211517_0000007382.cdr.24010, checksum is 9b8bd11538a55b017aab6b2ce9d7374f</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">### 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</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">::: Updated DB marks, all done.</span></p><p style="margin:0px"><br></p><p style="margin:0px">Database:</p><p style="margin:0px"><br></p></div><div><p style="margin:0px"><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">mysql> select count(*) from accounting.cdr where export_status = "unexported";</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">+----------+</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">| count(*) |</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">+----------+</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">| 4333587 |</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">+----------+</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">1 row in set (1.22 sec)</span></p><p style="margin:0px"><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">mysql></span></p><p style="margin:0px"><br></p><p style="margin:0px"><br></p><p style="margin:0px"><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">mysql> select count(*) from accounting.cdr where rating_status = "ok" and export_status = "unexported" ;</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">+----------+</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">| count(*) |</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">+----------+</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">| 1222721 |</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">+----------+</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">1 row in set (2.53 sec)</span></p><p style="margin:0px"><br></p><p style="margin:0px"><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">mysql></span></p><p style="margin:0px"><br></p><p style="margin:0px"><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">mysql> select max(id) from cdr;</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">+---------+</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">| max(id) |</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">+---------+</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">| 8939578 |</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">+---------+</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">1 row in set (0.00 sec)</span></p><p style="margin:0px"><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">mysql></span></p><p style="margin:0px"><br></p><p style="margin:0px">My last correctly exported CDR id is: 5560563</p><p style="margin:0px"><br></p><p style="margin:0px"><br></p><p style="margin:0px"><br></p><p style="margin:0px">Since that one last correct execution (where I modified the limit from 5000 to 10000), every single CDR file is empty.</p><p style="margin:0px"><br></p><p style="margin:0px">I am again lost, I've tried to understand how cdr-exporter works but I don't know perl.</p><p style="margin:0px"><br></p><p style="margin:0px"><br></p><p style="margin:0px">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 <a href="http://cdr.id" target="_blank">cdr.id</a> it filters with:</p><p style="margin:0px"><br></p><p style="margin:0px"><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">[...]</span></p><p style="margin:0px"><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">LEFT JOIN billing.billing_zones_history destination_customer_bbz ON cdr.destination_customer_billing_zone_id = <a href="http://destination_customer_bbz.id" target="_blank">destination_customer_bbz.id</a></span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif"> LEFT JOIN billing.voip_subscribers source_bvs ON cdr.source_user_id = source_bvs.uuid</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif"> LEFT JOIN billing.voip_subscribers destination_bvs ON cdr.destination_user_id = destination_bvs.uuid</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif"> where cdr.export_status = 'unexported' AND <span style="color:rgb(255,0,0)"><a href="http://cdr.id" target="_blank">cdr.id</a> > ?</span></span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif"> ". ($EXPORT_INCOMING eq 'yes' ? '' : "and source_user_id != '0'") ."</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif"> ". ($EXPORT_FAILED eq 'yes' ? '' : "and call_status = 'ok'") ."</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif"> order by</span><br><span style="font-family:'courier new',courier,monaco,monospace,sans-serif"> <a href="http://cdr.id" target="_blank">cdr.id</a></span></p><p style="margin:0px"><span style="font-family:'courier new',courier,monaco,monospace,sans-serif">[...]</span></p><p style="margin:0px"><br></p><p style="margin:0px"><br></p><p style="margin:0px"><span style="font-size:13.63636302948px">How can I start troubleshooting this?</span></p><p style="margin:0px"><br></p><p style="margin:0px"><br></p><p style="margin:0px">Thanks in advanced.</p><p style="margin:0px"><br></p><p style="margin:0px">Best regards, </p><p style="margin:0px">Joel.</p><p style="margin:0px"><br></p><p style="margin:0px"><br></p></div></div></div><br>_______________________________________________<br>
Spce-user mailing list<br>
<a href="mailto:Spce-user@lists.sipwise.com" target="_blank">Spce-user@lists.sipwise.com</a><br>
<a href="https://lists.sipwise.com/listinfo/spce-user" target="_blank">https://lists.sipwise.com/listinfo/spce-user</a><br>
<br></blockquote></div>
</blockquote><div><br></div></div></body></html>