Incorrect data in the table vicidial_campaign_stats

All installation and configuration problems and questions

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

Incorrect data in the table vicidial_campaign_stats

Postby frank83 » Fri Apr 09, 2021 4:40 am

Hello guys,
I noticed that some times the data in the table vicidial_campaign_stats are incorrect, for example in the campaign 1 yesterday I had the column calls_today to 1400 calls but after checking the vicidial_log table I noticed that the calls was 94, has anyone had a similar problem?
Vicidial VERSION: 2.14-807a BUILD: 210325-2220
frank83
 
Posts: 51
Joined: Thu Nov 15, 2018 10:59 am

Re: Incorrect data in the table vicidial_campaign_stats

Postby carpenox » Fri Apr 09, 2021 11:06 am

have you run a mysqlcheck --all-databases ?
Alma Linux 9.3 | Version: 2.14-911a | SVN Version: 3815 | DB Schema Version: 1710 | Asterisk 18.18.1
www.dialer.one -:- 1-833-DIALER-1 -:- https://linktr.ee/CyburDial -:- WhatsApp: +19549477572 -:- Skype: live:carpenox_3
carpenox
 
Posts: 2230
Joined: Wed Apr 08, 2020 2:02 am
Location: Coral Springs, FL

Re: Incorrect data in the table vicidial_campaign_stats

Postby frank83 » Fri Apr 09, 2021 11:57 am

yes but I haven't found any issues, the problem is random. Do you know the script that populate the table?

thanks
frank83
 
Posts: 51
Joined: Thu Nov 15, 2018 10:59 am

Re: Incorrect data in the table vicidial_campaign_stats

Postby mflorell » Fri Apr 09, 2021 1:40 pm

I have seen something like that before. It is random and it only appears to happen at the top of the hour. It's near impossible to replicate or troubleshoot and the records are reset less than a minute later. It is on my list of things to fix, but I haven't had the time or patience to work on it recently.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Incorrect data in the table vicidial_campaign_stats

Postby frank83 » Sat Apr 10, 2021 3:23 am

Thanks Matt, I will try to investigate.
frank83
 
Posts: 51
Joined: Thu Nov 15, 2018 10:59 am

Re: Incorrect data in the table vicidial_campaign_stats

Postby frank83 » Fri Apr 16, 2021 4:53 am

Hello guys sorry if I bother you again, from a first analysis I verified that the problem occurs when the time changes for example between 10.00 -10.02 or 11.00 11.02 is systematic every hour for inbound and outbound, I debugged also the script with the command perl AST_VDadapt.pl --debugX but it is really difficult to understand the problem, could be a workaround if I disable the cache in the script?

$VLhour_counts=0; # use cached hour counts for vicidial_log entries per campaign
$VCLhour_counts=0; # use cached hour counts for vicidial_closer_log entries per in-group



// Correct data
VCLHC CURRENT HOUR CALLS: |1|4|SELECT count(*) from vicidial_closer_log FORCE INDEX (call_date) where campaign_id='OFO_HOME_INB' and call_date >= '2021-04-15 15:00:00';|
VCLHC STATS INSERT/UPDATE TOTAL|2|INSERT IGNORE INTO vicidial_ingroup_hour_counts SET group_id='OFO_HOME_INB',date_hour='2021-04-15 15:00:00',type='CALLS',next_hour='2021-04-15 16:00:00',last_update=NOW(),calls='4',hr='15' ON DUPLICATE KEY UPDATE last_update=NOW(),calls='4';|
VCLHC CACHED HOUR CHECK: |1|15|SELECT count(*) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='CALLS' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 15:00:00' and last_update > next_hour;|
VCLHC CACHED HOUR SINGLE QUERY: |1|28(32)|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='CALLS' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 15:00:00' and last_update > next_hour;|
VCLHC CURRENT HOUR CALLS ANSWERS: |1|1|SELECT count(*) from vicidial_closer_log FORCE INDEX (call_date) where campaign_id='OFO_HOME_INB' and call_date >= '2021-04-15 15:00:00' and status NOT IN('DROP','XDROP','HXFER','QVMAIL','HOLDTO','LIVE','QUEUE','TIMEOT','AFTHRS','NANQUE','INBND','MAXCAL');|
VCLHC STATS INSERT/UPDATE TOTAL|2|INSERT IGNORE INTO vicidial_ingroup_hour_counts SET group_id='OFO_HOME_INB',date_hour='2021-04-15 15:00:00',type='ANSWERS',next_hour='2021-04-15 16:00:00',last_update=NOW(),calls='1',hr='15' ON DUPLICATE KEY UPDATE last_update=NOW(),calls='1';|
VCLHC CACHED HOUR CHECK: |1|15|SELECT count(*) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='ANSWERS' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 15:00:00' and last_update > next_hour;|
VCLHC CACHED HOUR SINGLE QUERY: |1|11(12)|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='ANSWERS' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 15:00:00' and last_update > next_hour;|
VCLHC CURRENT HOUR CALLS DROPS: |1|2|SELECT count(*) from vicidial_closer_log FORCE INDEX (call_date) where campaign_id='OFO_HOME_INB' and call_date >= '2021-04-15 15:00:00' and status IN('DROP','XDROP');|
VCLHC STATS INSERT/UPDATE TOTAL|2|INSERT IGNORE INTO vicidial_ingroup_hour_counts SET group_id='OFO_HOME_INB',date_hour='2021-04-15 15:00:00',type='DROPS',next_hour='2021-04-15 16:00:00',last_update=NOW(),calls='2',hr='15' ON DUPLICATE KEY UPDATE last_update=NOW(),calls='2';|
VCLHC CACHED HOUR CHECK: |1|15|SELECT count(*) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='DROPS' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 15:00:00' and last_update > next_hour;|
VCLHC CACHED HOUR SINGLE QUERY: |1|14(16)|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='DROPS' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 15:00:00' and last_update > next_hour;|
VCLHC CURRENT HOUR CALLS HOLD SECONDS 1: |1|0|SELECT count(*) from vicidial_closer_log FORCE INDEX (call_date) where campaign_id='OFO_HOME_INB' and call_date >= '2021-04-15 15:00:00' and queue_seconds <= 20 and status NOT IN('DROP','XDROP','HXFER','QVMAIL','HOLDTO','LIVE','QUEUE','TIMEOT','AFTHRS','NANQUE','INBND','MAXCAL');|
VCLHC STATS INSERT/UPDATE TOTAL|2|INSERT IGNORE INTO vicidial_ingroup_hour_counts SET group_id='OFO_HOME_INB',date_hour='2021-04-15 15:00:00',type='HOLDSEC1',next_hour='2021-04-15 16:00:00',last_update=NOW(),calls='0',hr='15' ON DUPLICATE KEY UPDATE last_update=NOW(),calls='0';|
VCLHC CACHED HOUR CHECK: |1|15|SELECT count(*) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='HOLDSEC1' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 15:00:00' and last_update > next_hour;|
VCLHC CACHED HOUR SINGLE QUERY: |1|7(7)|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='HOLDSEC1' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 15:00:00' and last_update > next_hour;|
VCLHC CURRENT HOUR CALLS HOLD SECONDS 2: |1|0|SELECT count(*) from vicidial_closer_log FORCE INDEX (call_date) where campaign_id='OFO_HOME_INB' and call_date >= '2021-04-15 15:00:00' and queue_seconds <= 30 and status NOT IN('DROP','XDROP','HXFER','QVMAIL','HOLDTO','LIVE','QUEUE','TIMEOT','AFTHRS','NANQUE','INBND','MAXCAL');|
VCLHC STATS INSERT/UPDATE TOTAL|2|INSERT IGNORE INTO vicidial_ingroup_hour_counts SET group_id='OFO_HOME_INB',date_hour='2021-04-15 15:00:00',type='HOLDSEC2',next_hour='2021-04-15 16:00:00',last_update=NOW(),calls='0',hr='15' ON DUPLICATE KEY UPDATE last_update=NOW(),calls='0';|
VCLHC CACHED HOUR CHECK: |1|15|SELECT count(*) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='HOLDSEC2' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 15:00:00' and last_update > next_hour;|
VCLHC CACHED HOUR SINGLE QUERY: |1|7(7)|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='HOLDSEC2' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 15:00:00' and last_update > next_hour;|
VCLHC CURRENT HOUR CALLS HOLD SECONDS OF ANSWERED CALLS: |1|126.00|SELECT sum(queue_seconds) from vicidial_closer_log FORCE INDEX (call_date) where campaign_id='OFO_HOME_INB' and call_date >= '2021-04-15 15:00:00' and status NOT IN('DROP','XDROP','HXFER','QVMAIL','HOLDTO','LIVE','QUEUE','TIMEOT','AFTHRS','NANQUE','INBND','MAXCAL');|
VCLHC STATS INSERT/UPDATE TOTAL|2|INSERT IGNORE INTO vicidial_ingroup_hour_counts SET group_id='OFO_HOME_INB',date_hour='2021-04-15 15:00:00',type='HDSECANS',next_hour='2021-04-15 16:00:00',last_update=NOW(),calls='126.00',hr='15' ON DUPLICATE KEY UPDATE last_update=NOW(),calls='126.00';|
VCLHC CACHED HOUR CHECK: |1|146|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='HDSECANS' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 15:00:00' and last_update > next_hour;|
VCLHC CACHED HOUR SINGLE QUERY: |1|146(272)|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='HDSECANS' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 15:00:00' and last_update > next_hour;|
VCLHC CURRENT HOUR CALLS HOLD SECONDS OF DROPPED CALLS: |1|112.00|SELECT sum(queue_seconds) from vicidial_closer_log FORCE INDEX (call_date) where campaign_id='OFO_HOME_INB' and call_date >= '2021-04-15 15:00:00' and status IN('DROP','XDROP');|
VCLHC STATS INSERT/UPDATE TOTAL|2|INSERT IGNORE INTO vicidial_ingroup_hour_counts SET group_id='OFO_HOME_INB',date_hour='2021-04-15 15:00:00',type='HDSECDRP',next_hour='2021-04-15 16:00:00',last_update=NOW(),calls='112.00',hr='15' ON DUPLICATE KEY UPDATE last_update=NOW(),calls='112.00';|
VCLHC CACHED HOUR CHECK: |1|139|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='HDSECDRP' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 15:00:00' and last_update > next_hour;|
VCLHC CACHED HOUR SINGLE QUERY: |1|139(251)|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='HDSECDRP' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 15:00:00' and last_update > next_hour;|
VCLHC CURRENT HOUR CALLS HOLD SECONDS OF ALL CALLS: |1|418.00|SELECT sum(queue_seconds) from vicidial_closer_log FORCE INDEX (call_date) where campaign_id='OFO_HOME_INB' and call_date >= '2021-04-15 15:00:00';|
VCLHC STATS INSERT/UPDATE TOTAL|2|INSERT IGNORE INTO vicidial_ingroup_hour_counts SET group_id='OFO_HOME_INB',date_hour='2021-04-15 15:00:00',type='HDSECALL',next_hour='2021-04-15 16:00:00',last_update=NOW(),calls='418.00',hr='15' ON DUPLICATE KEY UPDATE last_update=NOW(),calls='418.00';|
VCLHC CACHED HOUR CHECK: |1|285|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='HDSECALL' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 15:00:00' and last_update > next_hour;|
VCLHC CACHED HOUR SINGLE QUERY: |1|285(703)|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='HDSECALL' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 15:00:00' and last_update > next_hour;|
INBOUND OFO_HOME_INB|1|UPDATE vicidial_campaign_stats SET calls_today='32',answers_today='12',drops_today='16',drops_today_pct='50.00',drops_answers_today_pct='133.33',hold_sec_stat_one='7',hold_sec_stat_two='7',hold_sec_answer_calls='272',hold_sec_drop_calls='251',hold_sec_queue_calls='703',park_calls_today='4',park_sec_today='134',status_category_1='',status_category_count_1='0',status_category_2='',status_category_count_2='0',status_category_3='',status_category_count_3='0',status_category_4='',status_category_count_4='0' where campaign_id='OFO_HOME_INB';|
5 IN-GROUP: OFO_HOME_INB CALLS: 32 ANSWER: 12 DROPS: 16
Stat1: 7 Stat2: 7 Hold: 703|272|251


// Incorrect data

VCLHC CACHED HOUR QUERY: |1|0|SELECT count(*) from vicidial_closer_log FORCE INDEX (call_date) where campaign_id='OFO_HOME_INB' and call_date >= '2021-04-15 15:00:00' and call_date < '2021-04-15 16:00:00' and queue_seconds <= 30 and status NOT IN('DROP','XDROP','HXFER','QVMAIL','HOLDTO','LIVE','QUEUE','TIMEOT','AFTHRS','NANQUE','INBND','MAXCAL');|
VCLHC STATS INSERT/UPDATE HOUR|15|2|INSERT IGNORE INTO vicidial_ingroup_hour_counts SET group_id='OFO_HOME_INB',date_hour='2021-04-15 15:00:00',type='HOLDSEC2',next_hour='2021-04-15 16:00:00',last_update=NOW(),calls='0',hr='15' ON DUPLICATE KEY UPDATE last_update=NOW(),calls='0';|
VCLHC CURRENT HOUR CALLS HOLD SECONDS OF ANSWERED CALLS: |1||SELECT sum(queue_seconds) from vicidial_closer_log FORCE INDEX (call_date) where campaign_id='OFO_HOME_INB' and call_date >= '2021-04-15 16:00:00' and status NOT IN('DROP','XDROP','HXFER','QVMAIL','HOLDTO','LIVE','QUEUE','TIMEOT','AFTHRS','NANQUE','INBND','MAXCAL');|
VCLHC STATS INSERT/UPDATE TOTAL|1|INSERT IGNORE INTO vicidial_ingroup_hour_counts SET group_id='OFO_HOME_INB',date_hour='2021-04-15 16:00:00',type='HDSECANS',next_hour='2021-04-15 17:00:00',last_update=NOW(),calls='',hr='16' ON DUPLICATE KEY UPDATE last_update=NOW(),calls='';|
VCLHC CACHED HOUR CHECK: |1|146|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='HDSECANS' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 16:00:00' and last_update > next_hour;|
VCLHC CACHED HOUR SINGLE QUERY: |1|146(146)|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='HDSECANS' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 16:00:00' and last_update > next_hour;|
VCLHC CURRENT HOUR CALLS HOLD SECONDS OF DROPPED CALLS: |1||SELECT sum(queue_seconds) from vicidial_closer_log FORCE INDEX (call_date) where campaign_id='OFO_HOME_INB' and call_date >= '2021-04-15 16:00:00' and status IN('DROP','XDROP');|
VCLHC STATS INSERT/UPDATE TOTAL|1|INSERT IGNORE INTO vicidial_ingroup_hour_counts SET group_id='OFO_HOME_INB',date_hour='2021-04-15 16:00:00',type='HDSECDRP',next_hour='2021-04-15 17:00:00',last_update=NOW(),calls='',hr='16' ON DUPLICATE KEY UPDATE last_update=NOW(),calls='';|
VCLHC CACHED HOUR CHECK: |1|139|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='HDSECDRP' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 16:00:00' and last_update > next_hour;|
VCLHC CACHED HOUR SINGLE QUERY: |1|139(139)|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='HDSECDRP' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 16:00:00' and last_update > next_hour;|
VCLHC CURRENT HOUR CALLS HOLD SECONDS OF ALL CALLS: |1||SELECT sum(queue_seconds) from vicidial_closer_log FORCE INDEX (call_date) where campaign_id='OFO_HOME_INB' and call_date >= '2021-04-15 16:00:00';|
VCLHC STATS INSERT/UPDATE TOTAL|1|INSERT IGNORE INTO vicidial_ingroup_hour_counts SET group_id='OFO_HOME_INB',date_hour='2021-04-15 16:00:00',type='HDSECALL',next_hour='2021-04-15 17:00:00',last_update=NOW(),calls='',hr='16' ON DUPLICATE KEY UPDATE last_update=NOW(),calls='';|
VCLHC CACHED HOUR CHECK: |1|285|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='HDSECALL' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 16:00:00' and last_update > next_hour;|
VCLHC CACHED HOUR SINGLE QUERY: |1|285(285)|SELECT sum(calls) from vicidial_ingroup_hour_counts where group_id='OFO_HOME_INB' and type='HDSECALL' and date_hour >= '2021-04-15 00:00:00' and date_hour < '2021-04-15 16:00:00' and last_update > next_hour;|
INBOUND OFO_HOME_INB|1|UPDATE vicidial_campaign_stats SET calls_today='424',answers_today='166',drops_today='212',drops_today_pct='50.00',drops_answers_today_pct='127.71',hold_sec_stat_one='105',hold_sec_stat_two='105',hold_sec_answer_calls='146',hold_sec_drop_calls='139',hold_sec_queue_calls='285',park_calls_today='4',park_sec_today='134',status_category_1='',status_category_count_1='0',status_category_2='',status_category_count_2='0',status_category_3='',status_category_count_3='0',status_category_4='',status_category_count_4='0' where campaign_id='OFO_HOME_INB';|
5 IN-GROUP: OFO_HOME_INB CALLS: 424 ANSWER: 166 DROPS: 212
Stat1: 105 Stat2: 105 Hold: 285|146|139
frank83
 
Posts: 51
Joined: Thu Nov 15, 2018 10:59 am

Re: Incorrect data in the table vicidial_campaign_stats

Postby mflorell » Thu Jul 08, 2021 7:48 pm

We finally had some time(and patience) to work on this over the last week and we've committed updated AST_VDadapt.pl code to svn/trunk revision 3480.

Please upgrade and give it a try and let us know if you find any issues with it.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Incorrect data in the table vicidial_campaign_stats

Postby frank83 » Tue Sep 21, 2021 11:14 am

Sorry for the delay but I haven't receive the notification of the post, I tried and now it works!

Thanks Matt :)
frank83
 
Posts: 51
Joined: Thu Nov 15, 2018 10:59 am

Re: Incorrect data in the table vicidial_campaign_stats

Postby mflorell » Tue Sep 21, 2021 12:35 pm

Thanks for the confirmation, this one has been around for a long time, glad we've finally solved it :)
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: Incorrect data in the table vicidial_campaign_stats

Postby dinurajd » Wed Dec 15, 2021 12:26 pm

After the Security Upgrade, the issue with incorrect data has been resurfaced.
dinurajd
 
Posts: 3
Joined: Thu Nov 18, 2021 12:26 pm

Re: Incorrect data in the table vicidial_campaign_stats

Postby dinurajd » Mon Dec 27, 2021 12:08 pm

mflorell wrote:Thanks for the confirmation, this one has been around for a long time, glad we've finally solved it :)

After the Security Upgrade, the issue with incorrect data has been resurfaced.
dinurajd
 
Posts: 3
Joined: Thu Nov 18, 2021 12:26 pm


Return to Support

Who is online

Users browsing this forum: Google [Bot] and 75 guests