I have an asterisk AGI called from dialplan. Everything in the agi script, including other queries, work fine. This select returns 1 row from from CLI and MySQL Workbench but returns 0 rows when run in AGI. I have ensured the EOLs are all unix and checked for other typical AGI oddities but found none. Here’s the offending portion of the AGI script:
$mysqli = new mysqli('127.0.0.1', 'mysql_user', 'password'); //these work everywhere
$query="select queuename, agent from asteriskcdrdb.queuelog where event='CONNECT' and callid='$origuid';";
$result = $mysqli->query($query);
$row = $result->fetch_assoc();
$row_cnt = $result->num_rows;
$myagi->verbose("CDR Query row count: $row_cnt");
if (row_cnt == 1) {
$qnum=$row['queuename'];
$agent=$row['agent'];
$myagi->verbose("Agent: $agent Queue: $qnum");
} else {
$err=$mysqli->error;
$myagi->verbose("CDR Query ERROR: $err\n$query");
}
This never returns any rows. Yet, if I grab the query printed as out from the asterisk AGI, log into mysql (MariaDB) as same user/pw as AGI from Linux CLI and paste query it returns a row as expected.
Thanks @comtech Here is the AGI being executed, then the select cut and pasted into MySQL cli with same user/pw as AGI:
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_request: ch-queue-exten.php
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_channel: Local/1099@from-queue-00000278;1
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_language: en
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_type: Local
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_uniqueid: 1693503091.1662
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_version: 18.19.0
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_callerid: 1099
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_calleridname: Fonesoft SangomaConnect Test
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_callingpres: 0
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_callingani2: 0
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_callington: 0
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_callingtns: 0
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_dnid: unknown
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_rdnis: unknown
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_context: subQueueAlert
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_extension: s
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_priority: 4
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_enhanced: 0.0
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_accountcode:
<Local/1099@from-queue-00000278;1>AGI Tx >> agi_threadid: 140686297151232
<Local/1099@from-queue-00000278;1>AGI Tx >>
<Local/1099@from-queue-00000278;1>AGI Rx << GET VARIABLE ORIGUID
<Local/1099@from-queue-00000278;1>AGI Tx >> 200 result=1 (1693503090.1661)
<Local/1099@from-queue-00000278;1>AGI Rx << VERBOSE "ORIGUID: 1693503090.1661" 1
<Local/1099@from-queue-00000278;1>AGI Tx >> 200 result=1
<Local/1099@from-queue-00000278;1>AGI Rx << VERBOSE "CDR Query row count: 0" 1
<Local/1099@from-queue-00000278;1>AGI Tx >> 200 result=1
<Local/1099@from-queue-00000278;1>AGI Rx << VERBOSE "CDR Query ERROR: " 1
<Local/1099@from-queue-00000278;1>AGI Tx >> 200 result=1
<Local/1099@from-queue-00000278;1>AGI Rx << VERBOSE "select queuename, agent from asteriskcdrdb.queuelog where event='CONNECT' and callid='1693503090.1661';" 1
<Local/1099@from-queue-00000278;1>AGI Tx >> 200 result=1
<Local/1099@from-queue-00000278;1>AGI Rx << VERBOSE "Exten: 1099" 1
<Local/1099@from-queue-00000278;1>AGI Tx >> 200 result=1
<Local/1099@from-queue-00000278;1>AGI Rx << VERBOSE "Update query: update Coilhose.call_data set agent='', extension='1099', qnum='' where uniqueid='1693503090.1661';" 1
<Local/1099@from-queue-00000278;1>AGI Tx >> 200 result=1
<Local/1099@from-queue-00000278;1>AGI Rx << VERBOSE "Successfully updated call_data." 1
<Local/1099@from-queue-00000278;1>AGI Tx >> 200 result=1
<Local/1099@from-queue-00000278;1>AGI Rx <<
CLI>
Disconnected from Asterisk server
Asterisk cleanly ending (0).
Executing last minute cleanups
[root@hostname ~]# mysql -u mysql_user -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 27603
Server version: 5.5.65-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select queuename, agent from asteriskcdrdb.queuelog where event='CONNECT' and callid='1693503090.1661';
+-----------+------------------------------+
| queuename | agent |
+-----------+------------------------------+
| 9090 | Fonesoft SangomaConnect Test |
+-----------+------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
So you can see it successfully executes the insert, but without the data from the select. Note that the code prints the query when “CDR Query row count: 0” but there is no MySQL error.
Yes. If I past this code into sql_test.php and run from command line it works fine:
<?php
$mysqli = new mysqli('127.0.0.1', 'user', 'password'); //these work everywhere
$query="select queuename, agent from asteriskcdrdb.queuelog where event='CONNECT' and callid='1693503090.1661';";
$result = $mysqli->query($query);
$row = $result->fetch_assoc();
$row_cnt = $result->num_rows;
if ($row_cnt == 1) {
$qnum=$row['queuename'];
$agent=$row['agent'];
echo "Agent: $agent Queue: $qnum";
} else {
$err=$mysqli->error;
echo "Query ERROR: $err\n$query";
}
echo "Rows: $row_cnt Qnum: $qnum Agent: $agent \nQuery: $query\n";
?>
Result:
Agent: Fonesoft SangomaConnect Test Queue: 9090 Rows: 1 Qnum: 9090 Agent: Fonesoft SangomaConnect Test
Query: select queuename, agent from asteriskcdrdb.queuelog where event=‘CONNECT’ and callid=‘1693503090.1661’;