Incorrect data in the table vicidial_campaign_stats

[frank83]

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

[carpenox]

have you run a mysqlcheck --all-databases ?

[frank83]

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

thanks

[mflorell]

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.

[frank83]

Thanks Matt, I will try to investigate.

[frank83]

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

[mflorell]

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.

[frank83]

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

Thanks Matt

[mflorell]

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

[dinurajd]

After the Security Upgrade, the issue with incorrect data has been resurfaced.

[dinurajd]

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.