[Spce-user] CDR Export not working after update to mr12.3.1

[Alexander Schwarz]

Hello,


we did an update from version mr12.2.1 to version mr12.3.1.

Now cdr export is not working:



As far as we could narrow down the problem:

  *   If there is no cdr to export, file is generated in folder /home/jail/homes/resellers/%Reseller-Name%/202404/22/sipwise...cdr:

Content:
007,0000
9b8bd11538a55b017aab6b2ce9d7374f



  *   If there are data to export file is not generated, inside database the record is marked as exported
  *   SQL Statement is cut off inside the logs or is this the problem?
left outer join billing.voi ( at peast "p" is missing at the end )



So our questions:

  *   Are there any known issues regarding to cdr export in mr12.3.1?
  *   How to fix this?



Best regards,



Alex





Content /ngcp-data/logs/cdr-exporter.log:
Apr 22 09:00:01 spce (debug) ngcp-event-exporter[252347]: +++ Start event export with DB exporter@ to /home/jail/home/cdrexport
Apr 22 09:00:02 spce (debug) ngcp-event-exporter[252347]: select base_table.id, base_table.subscriber_id, base_table.reseller_id, base_table.type, base_table.old_status, base_table.new_status, base_table.id, base_table.type, billing.contracts.external_id, billing.contacts.company, billing.voip_subscribers.external_id, CONCAT(IFNULL((SELECT data.val FROM accounting.events_tag_data AS data INNER JOIN accounting.events_tag AS dict ON data.tag_id = dict.id WHERE data.event_id = base_table.id AND dict.type="primary_number_cc"),""),IFNULL((SELECT data.val FROM accounting.events_tag_data AS data INNER JOIN accounting.events_tag AS dict ON data.tag_id = dict.id WHERE data.event_id = base_table.id AND dict.type="primary_number_ac"),""),IFNULL((SELECT data.val FROM accounting.events_tag_data AS data INNER JOIN accounting.events_tag AS dict ON data.tag_id = dict.id WHERE data.event_id = base_table.id AND dict.type="primary_number_sn"),"")), old_profile.name, new_profile.name, from_unixtime(base_table.timestamp), base_table.reseller_id from accounting.events base_table left outer join billing.voip_subscribers on billing.voip_subscribers.id = base_table.subscriber_id left outer join billing.contracts on billing.contracts.id = billing.voip_subscribers.contract_id left outer join billing.contacts on billing.contacts.id = billing.contracts.contact_id left outer join provisioning.voip_subscribers on provisioning.voip_subscribers.uuid = billing.voip_subscribers.uuid left outer join provisioning.voip_subscriber_profiles old_profile on old_profile.id = base_table.old_status left outer join provisioning.voip_subscriber_profiles new_profile on new_profile.id = base_table.new_status where base_table.export_status = "unexported" order by base_table.id limit 3000
Apr 22 09:00:02 spce (info) ngcp-event-exporter[252347]: Started execution
Apr 22 09:00:02 spce (info) ngcp-event-exporter[252347]: Finished processing records
Apr 22 09:00:02 spce (info) ngcp-event-exporter[252347]: Finalizing output files
Apr 22 09:00:02 spce (info) ngcp-event-exporter[252347]: Committing changes to database
Apr 22 09:00:02 spce (info) ngcp-event-exporter[252347]: All done
Apr 22 09:25:02 spce (debug) ngcp-cdr-exporter[254207]: +++ Start cdr export stream 'default' with DB exporter at accounting to /home/jail/home/cdrexport
Apr 22 09:25:02 spce (debug) ngcp-cdr-exporter[254207]: select base_table.id, base_table.source_user_id, base_table.destination_user_id, base_table.source_provider_id, base_table.destination_provider_id, base_table.id, base_table.update_time, base_table.source_user_id, base_table.source_provider_id, base_table.source_external_subscriber_id, source_voip_subscribers.id, base_table.source_external_contract_id, base_table.source_account_id, base_table.source_user, base_table.source_domain, base_table.source_cli, base_table.source_clir, base_table.source_ip, base_table.destination_user_id, base_table.destination_provider_id, base_table.destination_external_subscriber_id, destination_voip_subscribers.id, base_table.destination_external_contract_id, base_table.destination_account_id, base_table.destination_user, base_table.destination_domain, base_table.destination_user_in, base_table.destination_domain_in, base_table.destination_user_dialed, base_table.peer_auth_user, base_table.peer_auth_realm, base_table.call_type, base_table.call_status, base_table.call_code, CONCAT(FROM_UNIXTIME(FLOOR(base_table.init_time)), ".", SUBSTRING_INDEX(base_table.init_time, ".", -1)) as cdr_init_time, CONCAT(FROM_UNIXTIME(FLOOR(base_table.start_time)), ".", SUBSTRING_INDEX(base_table.start_time, ".", -1)) as cdr_start_time, base_table.duration, base_table.call_id, base_table.rating_status, base_table.rated_at, base_table.source_carrier_cost, base_table.source_customer_cost, COALESCE(source_carrier_bbz.zone, "onnet"), source_customer_bbz.zone, COALESCE(source_carrier_bbz.detail, "platform internal"), source_customer_bbz.detail, base_table.source_carrier_free_time, base_table.source_customer_free_time, base_table.destination_carrier_cost, base_table.destination_customer_cost, destination_carrier_bbz.zone, destination_customer_bbz.zone, destination_carrier_bbz.detail, destination_customer_bbz.detail, base_table.destination_carrier_free_time, base_table.destination_customer_free_time, base_table.source_reseller_cost, source_reseller_bbz.zone, source_reseller_bbz.detail, base_table.source_reseller_free_time, base_table.destination_reseller_cost, destination_reseller_bbz.zone, destination_reseller_bbz.detail, base_table.destination_reseller_free_time, COALESCE((SELECT data.val FROM accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type = "source" AND prov.type="customer" AND dict.type="concurrent_calls_count"),"") as source_concurrent_calls_count, COALESCE((SELECT data.val FROM accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type = "source" AND prov.type="customer" AND dict.type="concurrent_calls_count_customer"),"") as source_concurrent_calls_count_customer, COALESCE((SELECT data.val FROM accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type = "destination" AND prov.type="customer" AND dict.type="concurrent_calls_count"),"") as destination_concurrent_calls_count, COALESCE((SELECT data.val FROM accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type = "destination" AND prov.type="customer" AND dict.type="concurrent_calls_count_customer"),"") as destination_concurrent_calls_count_customer from accounting.cdr base_table left outer join billing.voi
Apr 22 09:25:02 spce (info) ngcp-cdr-exporter[254207]: Started execution
Apr 22 09:25:02 spce (info) ngcp-cdr-exporter[254207]: Finished processing records
Apr 22 09:25:02 spce (info) ngcp-cdr-exporter[254207]: Finalizing output files
Apr 22 09:25:02 spce (info) ngcp-cdr-exporter[254207]: Committing changes to database
Apr 22 09:25:02 spce (info) ngcp-cdr-exporter[254207]: All done
Apr 22 09:55:01 spce (debug) ngcp-cdr-exporter[256398]: +++ Start cdr export stream 'default' with DB exporter at accounting to /home/jail/home/cdrexport
Apr 22 09:55:01 spce (debug) ngcp-cdr-exporter[256398]: select base_table.id, base_table.source_user_id, base_table.destination_user_id, base_table.source_provider_id, base_table.destination_provider_id, base_table.id, base_table.update_time, base_table.source_user_id, base_table.source_provider_id, base_table.source_external_subscriber_id, source_voip_subscribers.id, base_table.source_external_contract_id, base_table.source_account_id, base_table.source_user, base_table.source_domain, base_table.source_cli, base_table.source_clir, base_table.source_ip, base_table.destination_user_id, base_table.destination_provider_id, base_table.destination_external_subscriber_id, destination_voip_subscribers.id, base_table.destination_external_contract_id, base_table.destination_account_id, base_table.destination_user, base_table.destination_domain, base_table.destination_user_in, base_table.destination_domain_in, base_table.destination_user_dialed, base_table.peer_auth_user, base_table.peer_auth_realm, base_table.call_type, base_table.call_status, base_table.call_code, CONCAT(FROM_UNIXTIME(FLOOR(base_table.init_time)), ".", SUBSTRING_INDEX(base_table.init_time, ".", -1)) as cdr_init_time, CONCAT(FROM_UNIXTIME(FLOOR(base_table.start_time)), ".", SUBSTRING_INDEX(base_table.start_time, ".", -1)) as cdr_start_time, base_table.duration, base_table.call_id, base_table.rating_status, base_table.rated_at, base_table.source_carrier_cost, base_table.source_customer_cost, COALESCE(source_carrier_bbz.zone, "onnet"), source_customer_bbz.zone, COALESCE(source_carrier_bbz.detail, "platform internal"), source_customer_bbz.detail, base_table.source_carrier_free_time, base_table.source_customer_free_time, base_table.destination_carrier_cost, base_table.destination_customer_cost, destination_carrier_bbz.zone, destination_customer_bbz.zone, destination_carrier_bbz.detail, destination_customer_bbz.detail, base_table.destination_carrier_free_time, base_table.destination_customer_free_time, base_table.source_reseller_cost, source_reseller_bbz.zone, source_reseller_bbz.detail, base_table.source_reseller_free_time, base_table.destination_reseller_cost, destination_reseller_bbz.zone, destination_reseller_bbz.detail, base_table.destination_reseller_free_time, COALESCE((SELECT data.val FROM accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type = "source" AND prov.type="customer" AND dict.type="concurrent_calls_count"),"") as source_concurrent_calls_count, COALESCE((SELECT data.val FROM accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type = "source" AND prov.type="customer" AND dict.type="concurrent_calls_count_customer"),"") as source_concurrent_calls_count_customer, COALESCE((SELECT data.val FROM accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type = "destination" AND prov.type="customer" AND dict.type="concurrent_calls_count"),"") as destination_concurrent_calls_count, COALESCE((SELECT data.val FROM accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type = "destination" AND prov.type="customer" AND dict.type="concurrent_calls_count_customer"),"") as destination_concurrent_calls_count_customer from accounting.cdr base_table left outer join billing.voi
Apr 22 09:55:01 spce (info) ngcp-cdr-exporter[256398]: Started execution
Apr 22 09:55:02 spce (info) ngcp-cdr-exporter[256398]: Finished processing records
Apr 22 09:55:02 spce (info) ngcp-cdr-exporter[256398]: Finalizing output files
Apr 22 09:55:02 spce (info) ngcp-cdr-exporter[256398]: Committing changes to database
Apr 22 09:55:02 spce (info) ngcp-cdr-exporter[256398]: All done
Apr 22 10:00:01 spce (debug) ngcp-event-exporter[256759]: +++ Start event export with DB exporter@ to /home/jail/home/cdrexport
Apr 22 10:00:01 spce (debug) ngcp-event-exporter[256759]: select base_table.id, base_table.subscriber_id, base_table.reseller_id, base_table.type, base_table.old_status, base_table.new_status, base_table.id, base_table.type, billing.contracts.external_id, billing.contacts.company, billing.voip_subscribers.external_id, CONCAT(IFNULL((SELECT data.val FROM accounting.events_tag_data AS data INNER JOIN accounting.events_tag AS dict ON data.tag_id = dict.id WHERE data.event_id = base_table.id AND dict.type="primary_number_cc"),""),IFNULL((SELECT data.val FROM accounting.events_tag_data AS data INNER JOIN accounting.events_tag AS dict ON data.tag_id = dict.id WHERE data.event_id = base_table.id AND dict.type="primary_number_ac"),""),IFNULL((SELECT data.val FROM accounting.events_tag_data AS data INNER JOIN accounting.events_tag AS dict ON data.tag_id = dict.id WHERE data.event_id = base_table.id AND dict.type="primary_number_sn"),"")), old_profile.name, new_profile.name, from_unixtime(base_table.timestamp), base_table.reseller_id from accounting.events base_table left outer join billing.voip_subscribers on billing.voip_subscribers.id = base_table.subscriber_id left outer join billing.contracts on billing.contracts.id = billing.voip_subscribers.contract_id left outer join billing.contacts on billing.contacts.id = billing.contracts.contact_id left outer join provisioning.voip_subscribers on provisioning.voip_subscribers.uuid = billing.voip_subscribers.uuid left outer join provisioning.voip_subscriber_profiles old_profile on old_profile.id = base_table.old_status left outer join provisioning.voip_subscriber_profiles new_profile on new_profile.id = base_table.new_status where base_table.export_status = "unexported" order by base_table.id limit 3000
Apr 22 10:00:01 spce (info) ngcp-event-exporter[256759]: Started execution
Apr 22 10:00:01 spce (info) ngcp-event-exporter[256759]: Finished processing records
Apr 22 10:00:01 spce (info) ngcp-event-exporter[256759]: Finalizing output files
Apr 22 10:00:01 spce (info) ngcp-event-exporter[256759]: Committing changes to database
Apr 22 10:00:01 spce (info) ngcp-event-exporter[256759]: All done
Apr 22 10:25:01 spce (debug) ngcp-cdr-exporter[259917]: +++ Start cdr export stream 'default' with DB exporter at accounting to /home/jail/home/cdrexport
Apr 22 10:25:01 spce (debug) ngcp-cdr-exporter[259917]: select base_table.id, base_table.source_user_id, base_table.destination_user_id, base_table.source_provider_id, base_table.destination_provider_id, base_table.id, base_table.update_time, base_table.source_user_id, base_table.source_provider_id, base_table.source_external_subscriber_id, source_voip_subscribers.id, base_table.source_external_contract_id, base_table.source_account_id, base_table.source_user, base_table.source_domain, base_table.source_cli, base_table.source_clir, base_table.source_ip, base_table.destination_user_id, base_table.destination_provider_id, base_table.destination_external_subscriber_id, destination_voip_subscribers.id, base_table.destination_external_contract_id, base_table.destination_account_id, base_table.destination_user, base_table.destination_domain, base_table.destination_user_in, base_table.destination_domain_in, base_table.destination_user_dialed, base_table.peer_auth_user, base_table.peer_auth_realm, base_table.call_type, base_table.call_status, base_table.call_code, CONCAT(FROM_UNIXTIME(FLOOR(base_table.init_time)), ".", SUBSTRING_INDEX(base_table.init_time, ".", -1)) as cdr_init_time, CONCAT(FROM_UNIXTIME(FLOOR(base_table.start_time)), ".", SUBSTRING_INDEX(base_table.start_time, ".", -1)) as cdr_start_time, base_table.duration, base_table.call_id, base_table.rating_status, base_table.rated_at, base_table.source_carrier_cost, base_table.source_customer_cost, COALESCE(source_carrier_bbz.zone, "onnet"), source_customer_bbz.zone, COALESCE(source_carrier_bbz.detail, "platform internal"), source_customer_bbz.detail, base_table.source_carrier_free_time, base_table.source_customer_free_time, base_table.destination_carrier_cost, base_table.destination_customer_cost, destination_carrier_bbz.zone, destination_customer_bbz.zone, destination_carrier_bbz.detail, destination_customer_bbz.detail, base_table.destination_carrier_free_time, base_table.destination_customer_free_time, base_table.source_reseller_cost, source_reseller_bbz.zone, source_reseller_bbz.detail, base_table.source_reseller_free_time, base_table.destination_reseller_cost, destination_reseller_bbz.zone, destination_reseller_bbz.detail, base_table.destination_reseller_free_time, COALESCE((SELECT data.val FROM accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type = "source" AND prov.type="customer" AND dict.type="concurrent_calls_count"),"") as source_concurrent_calls_count, COALESCE((SELECT data.val FROM accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type = "source" AND prov.type="customer" AND dict.type="concurrent_calls_count_customer"),"") as source_concurrent_calls_count_customer, COALESCE((SELECT data.val FROM accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type = "destination" AND prov.type="customer" AND dict.type="concurrent_calls_count"),"") as destination_concurrent_calls_count, COALESCE((SELECT data.val FROM accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type = "destination" AND prov.type="customer" AND dict.type="concurrent_calls_count_customer"),"") as destination_concurrent_calls_count_customer from accounting.cdr base_table left outer join billing.voi
Apr 22 10:25:01 spce (info) ngcp-cdr-exporter[259917]: Started execution
Apr 22 10:25:01 spce (info) ngcp-cdr-exporter[259917]: Finished processing records
Apr 22 10:25:01 spce (info) ngcp-cdr-exporter[259917]: Finalizing output files
Apr 22 10:25:02 spce (info) ngcp-cdr-exporter[259917]: Committing changes to database

Apr 22 10:25:02 spce (info) ngcp-cdr-exporter[259917]: All done




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20240422/dc32a436/attachment-0001.htm>