freeswitch
freeswitch copied to clipboard
signalwire
Error SQL script in module of callcenter using random strategy to call transfer which using mysql 8.0.25 as database
Describe the bug
when I use the callcenter module make call transfer, it will use the function 'random()' to generate SQL script which can not run in mysql(8.0.25) when I use queue strategy is 'random'. And 'random' is a keyword in mysql 8.0 and latter.
the source code of mod_callcenter.c like this:
else if(!strcasecmp(queue_strategy, "random")) { sql_order_by = switch_mprintf("level, random()"); }
freeSWITCH version: 1.10.6
Trace logs
2023-04-04 17:50:24.424539 [ERR] switch_core_sqldb.c:1355 ERR: [SELECT instance_id, name, status, contact, no_answer_count, max_no_answer, reject_delay_time, busy_delay_time, no_answer_delay_time, tiers.state, agents.last_bridge_end, agents.wrap_up_time, agents.state, agents.ready_time, tiers.position, tiers.level, agents.type, agents.uuid, external_calls_count FROM agents LEFT JOIN tiers ON (agents.name = tiers.agent) WHERE tiers.queue = 'queue001' AND (agents.status = 'Available' OR agents.status = 'On Break' OR agents.status = 'Available (On Demand)') ORDER BY level, random()] [STATE: 42000 CODE 1064 ERROR: [MySQL][ODBC 8.0(a) Driver][mysqld-8.0.25]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 ] 2023-04-04 17:50:24.424539 [ERR] mod_callcenter.c:697 SQL ERR: [SELECT instance_id, name, status, contact, no_answer_count, max_no_answer, reject_delay_time, busy_delay_time, no_answer_delay_time, tiers.state, agents.last_bridge_end, agents.wrap_up_time, agents.state, agents.ready_time, tiers.position, tiers.level, agents.type, agents.uuid, external_calls_count FROM agents LEFT JOIN tiers ON (agents.name = tiers.agent) WHERE tiers.queue = 'queue001' AND (agents.status = 'Available' OR agents.status = 'On Break' OR agents.status = 'Available (On Demand)') ORDER BY level, random()] STATE: 42000 CODE 1064 ERROR: [MySQL][ODBC 8.0(a) Driver][mysqld-8.0.25]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
