hopper script error

[baka-na]

Hi all,
Good day!
Got some problems with the hopper just a while back. Leads are not getting into the hopper. I read some troubleshooting steps here and come across to one saying to debug the hopper script. I don't know how to interpret the result so I think I might as well post it here for support.

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 ') and ( ( (status IN('DROP','XDROP')) and (last_local_call_time < CONCAT(DATE_AD' at line 1 at /usr/share/astguiclient/AST_VDhopper.pl line 1825.

Please kindly advise what steps should I do next. Thanks a lot to all!

[baka-na]

I did an SVN update, same error.

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 ') and ( ( (status IN('DROP','XDROP')) and (last_local_call_time < CONCAT(DATE_AD' at line 1 at /usr/share/astguiclient/AST_VDhopper.pl line 1825.
executing: SELECT count(*) FROM vicidial_list where called_since_last_reset='N' and status IN('DROP','OptrIn','Static','Redial','N','FaxMac','CalBsy','NA','ERI','B','AFAX','AB','AnsMac','AA','NEW') and list_id IN('999876543210') and ((gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.50','9.25','9.00','8.75','8.50','8.25','8.00','7.75','7.50','7.25','7.00','6.75','6.50','6.25','6.00','5.75','5.50','-6.75','-7.00','-7.25','-7.50','-7.75','-8.00','-8.25','-8.50','-8.75','-9.00','-9.25','-9.50','-9.75','-10.00','-10.25','-10.50','-10.75','-11.00','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99') ) ) and () and ( ( (status IN('DROP','XDROP')) and (last_local_call_time < CONCAT(DATE_ADD(NOW(), INTERVAL -259200 SECOND),' ',CURTIME()) ) ) or (status NOT IN('DROP','XDROP')) ) and (called_count < 7); 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 ') and ( ( (status IN('DROP','XDROP')) and (last_local_call_time < CONCAT(DATE_AD' at line 1 at /usr/share/astguiclient/AST_VDhopper.pl line 1825.

[DomeDan]

You got some lead filter that the script did'nt expect would be empty or something like that
post the output from this query: "SELECT * FROM vicidial_lead_filters"

[amedina]

Hi,

I encountered the same problem when I deleted some of my created filters. What should we do to fix?

Please help!

----------------------------------------------------------------------------------
VERSION: 3.1.10 | BUILD: 110430-1642 | Asterisk Version: 1.4.38-vici | Cluster | No Digium/Sangoma Hardware | No Extra Software After Installation

[DomeDan]

When you deleted some filters you said...
in the campaign settings for the campaigns that causes the problem, set "Lead Filter" to "NONE" and try to run the hopper script again

[williamconley]

set lead filter to NONE on ALL of your campaigns. then turn them back on one at a time until it crashes again and you'll have found your error. you can also try to use the test function for each script which may show the flaw before the hopper hits it as well.

[amedina]

We restarted the dialers and the hopper loaded some leads but didn't load consistently, then after some minutes it ran out of leads.
Then I set the Lead Filter to NONE, then I ran the AST_VD_Hopper.pl, below is the result.

no command line options set
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 '' at line 1 at /usr/share/astguiclient/AST_VDhopper.pl line 1743.
executing: SELECT count(*) FROM vicidial_list where called_since_last_reset='N' and status IN('NAI','PDROP','DROP','NA','N','CALLBK','B','AM','AL','AB','AA','A','NEW') and list_id IN('70061202','70071201','70071202') and ((gmt_offset_now IN('13.00','12.75','12.50','12.25','12.00','11.75','11.50','11.25','11.00','10.75','10.50','10.25','10.00','9.75','9.50','9.25','9.00','8.75','8.50','8.25','8.00','7.75','7.50','7.25','7.00','6.75','6.50','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.25','-2.50','-2.75','-3.00','-3.25','-3.50','-3.75','-4.00','-4.25','-4.50','-4.75','-5.00','-5.25','-5.50','-5.75','-6.00','-6.25','-6.50','-6.75','-7.00','-7.25','-7.50','-7.75','-8.00','-8.25','-8.50','-8.75','-9.00','-9.25','-9.50','-9.75','-10.00','-10.25','-10.50','-10.75','-11.00','-11.25','-11.50','-11.75','-12.00','-12.25','-12.50','-12.75','99') ) ) and ; 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 '' at line 1 at /usr/share/astguiclient/AST_VDhopper.pl line 1743.

[williamconley]

find the campaign to which these lists are assigned:

list_id IN('70061202','70071201','70071202')

and turn it off. (and verify that the filter was turned off on that campaign as well, not merely deleted).

[amedina]

Problem Solved! Thanks DomeDan and williamconley as always

[DomeDan]

Your welcome I've posted a patch for this issue http://www.vicidial.org/VICIDIALmantis/view.php?id=627