Hopper load script error (RESOLVED)

[leseul]

Hi All!

I'm having some problems with the hopper load script. It has manifested this morning when we noticed the hopper was empty.

This has been stable and running fine for almost a year now until today.

Running /usr/share/astguiclient/AST_VDhopper.pl --debug gives the following output.

Code: Select all

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near '93.6' at line 1 at /usr/share/astguiclient/AST_VDhopper.pl line 2077.
executing: SELECT lead_id,list_id,gmt_offset_now,phone_number,state,status,modify_date,user FROM vicidial_list where called_since_last_reset='N' and (list_id='60312'
and status IN('DROP','AFTHRS','A','NA','B','AB','AA','NEW')) and lead_id NOT IN('0') and ((gmt_offset_now
IN('6.25','6.00','5.75','5.50','5.25','5.00','4.75','4.50','4.25','4.00','3.75','3.50','3.25','3.00','2.75','2.50','2.25','2.00','1.75','1.50','1.25','1.00','0.75','0.50
','0.25','0.00','-0.25','-0.50','-0.75','-1.00','-1.25','-1.50','-1.75','-2.00','-2.2
5','-2.50','-2.75','99') ) ) and called_count < 7 and ( ( (status IN('DROP','XDROP')) and (last_local_call_time <
CONCAT(DATE_ADD(NOW(), INTERVAL -259200 SECOND),' ',CURTIME()) ) ) or (status NOT IN('DROP','XDROP')) ) order by last_local_call_time,
lead_id asc limit 93.6; You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right
syntax to use near '93.6' at line 1 at /usr/share/astguiclient/AST_VDhopper.pl line 2077.

Without using list mix or with just 2 lists mixed 50/50 it seems to load fine, but when adding a third list or having a low mix % (like 5%) it reappears with a vengeance.

I was wondering if anyone could shed some light on this issue?

EDIT: Also figured you might want to know what filters we have applied, well its: called_count < 7

UPDATE: following a mysqlcheck repair and reboot it appears to be loading leads again (with no load). It brought a couple of 'a client is using this table or has not closed properly' errors, could these be symptoms or causes?

UPDATE 2: It would appear that this is a symptom as the problem has cropped up again, I can reproduce it by setting the list mix to mix 4 lists with one mixed under 10% and returns to normal if I remove the list with the under 10% mix -- does anyone have any ideas?


Thanks in advance.

[gers55]

Try repairing your mysql database

Code: mysqlcheck --auto-repair asterisk -p

[DomeDan]

I'm trying to get your version "Vicidial 2.4-299 Build 110214-0001" so I can look in the code to see whats happening.
I guess that someone just forgot to remove the decimals from the limit-variable

I can see this in the latest version:
# 120109-1510 - Fixed list mix bug
so a bug regarding list mix was fixed 2012-01-09...

I suggest you try to upgrade to the latest version and check if the bug goes away.


edit, seems like I was right, this is the bugfix:

Code: Select all

--- trunk_bin_1771/AST_VDhopper.pl   2012-03-09 12:41:50.061220072 +0100
+++ trunk_bin_1772/AST_VDhopper.pl   2012-03-09 12:43:05.422162220 +0100
@@ -68,6 +68,7 @@
 # 110212-2255 - Added scheduled callback custom statuses capability
 # 110214-2319 - Added lead_order_secondary option
 # 111006-1416 - Added call_count_limit option
+# 120109-1510 - Fixed list mix bug
 #
 
 # constants
@@ -2066,8 +2067,10 @@
                      @list_mix_stepARY = split(/\|/,$list_mixARY[$x]);
                      if ($list_mix_stepARY[2] > 0)
                         {
-                        $LM_step_goal[$x] = ( ($list_mix_stepARY[2] / 100) * $hopper_level[$i]);
-                        $LM_step_even[$x] = ( (100 / $list_mix_stepARY[2]) * 100000);
+                        $LM_step_goal[$x] = int( ($list_mix_stepARY[2] / 100) * $hopper_level[$i]);
+                        $LM_step_even[$x] = int( (100 / $list_mix_stepARY[2]) * 100000);
+                        if ($LM_step_goal[$x] < 1)   {$LM_step_goal[$x] = 1;}
+                        if ($LM_step_even[$x] < 1)   {$LM_step_even[$x] = 1;}
                         }
                      else
                         {


So yes, upgrade to the latest version or manually do this change in AST_VDhopper.pl

[leseul]

Thank DomeDan, you're a lifesaver.

I'll apply this patch and update on the results.

I wonder how this bug has only started to manifest now, after a year in production?

[Acidshock]

Did your problem get resolved?

[leseul]

Thanks DomeDan, The issue is now resolved.

This must have been occurring fairly regularly, but as it's an intermittent issue, when dialing relatively slowly, the hopper manages to refill but can
look like the standard list-mix behavior when 1 or more mixed lists have run out. When we increased our number of dials per minute the crippled hopper
couldn't catch up and emptied.

I manually made the change to the hopper load script as the system is in production. Will upgrade when I get the out of hours time.


Thanks again.

[DomeDan]

Your welcome, great it fixed the issue!

Oh so thats why you didn't notice the bug until now, I did wonder a while about that.