[asterisk-bugs] [Asterisk 0012166]: MYSQL Query using wrong delimiters and not returning results

[noreply at bugs.digium.com]

The following issue has been REOPENED. 
====================================================================== 
http://bugs.digium.com/view.php?id=12166 
====================================================================== 
Reported By:                ruffle
Assigned To:                
====================================================================== 
Project:                    Asterisk
Issue ID:                   12166
Category:                   Addons/app_addon_sql_mysql
Reproducibility:            always
Severity:                   major
Priority:                   normal
Status:                     new
Asterisk Version:           1.4.18 
SVN Branch (only for SVN checkouts, not tarball releases): N/A 
SVN Revision (number only!):  
Disclaimer on File?:        N/A 
Request Review:              
====================================================================== 
Date Submitted:             03-07-2008 04:43 CST
Last Modified:              03-07-2008 05:48 CST
====================================================================== 
Summary:                    MYSQL Query using wrong delimiters and not returning
results
Description: 
app_addon_sql_mysql seems to be changing the delimiters for a query and not
returning the result as expected (this code was working in previous
versions).

My Dialplan has:

;   Update MySql autorecord database
exten => s,n,MYSQL(Connect connid localhost asterisk secret asterisk)
exten => s,n,NoOp(Connid is ${connid})
exten => s,n,GotoIf($[${connid} = ""]?s-GNORECORD|1)
exten => s,n,NoOp(Updating MYSQL)
exten => s,n,NoOp(Uniqueid is ${UNIQUEID})
exten => s,n,NoOp(RecordFile is ${RECORDFILE})
exten => s,n,MYSQL(Query resultid ${connid} INSERT\ INTO\ autorecord\ SET\
calldate=now()\,src=\"${CALLERID(num)}\"\,dest=\"${ARG1}\"\,filename=\"${RECORDFILE}\",uniqueid=\"${UNIQUEID}\")
exten => s,n,NoOp(MYSQL resultid is ${resultid})
exten => s,n,MYSQL(Clear ${resultid})
exten => s,n,MYSQL(Disconnect ${connid})

Which inserts a record into my autorecord database. However, running this
with 1.4.18/1.4.6 the separator between the filename and uniqueid get
changed from a comma to a pipe!  and the resultid is blank (not sure if
this is new or not. See the CLI output below.
-- Executing [s at macro-stdexten:10] MYSQL("SIP/117-00713c90", "Connect
connid localhost asterisk secret asterisk") in new stack
-- Executing [s at macro-stdexten:11] NoOp("SIP/117-00713c90", "Connid is 1")
in new stack
-- Executing [s at macro-stdexten:12] GotoIf("SIP/117-00713c90",
"0?s-GNORECORD|1") in new stack
-- Executing [s at macro-stdexten:13] NoOp("SIP/117-00713c90", "Updating
MYSQL") in new stack
-- Executing [s at macro-stdexten:14] NoOp("SIP/117-00713c90", "Uniqueid is
lls-1204885650.24") in new stack
-- Executing [s at macro-stdexten:15] NoOp("SIP/117-00713c90", "RecordFile is
/var/spool/asterisk/autorecord/2008/03/07/102730-lls-1204885650.24-112-117")
in new stack
-- Executing [s at macro-stdexten:16] MYSQL("SIP/117-00713c90", "Query
resultid 1 INSERT INTO autorecord SET
calldate=now(),src="117",dest="112",filename="/var/spool/asterisk/autorecord/2008/03/07/102730-lls-1204885650.24-112-117"|uniqueid="lls-1204885650.24"")
in new stack
*******  This should be a comma -------------------------------------^
-- Executing [s at macro-stdexten:17] NoOp("SIP/117-00713c90", "MYSQL
resultid is ") in new stack
-- Executing [s at macro-stdexten:18] MYSQL("SIP/117-00713c90", "Clear ") in
new stack
-- Executing [s at macro-stdexten:19] MYSQL("SIP/117-00713c90", "Disconnect
1") in new stack

Hmm that didn't work very well with the wrapping on the web site. I'll
include the dialplan and CLI output as an attachment.




====================================================================== 

---------------------------------------------------------------------- 
 ruffle - 03-07-08 05:48  
---------------------------------------------------------------------- 
OK, I can escape it and that does fix the problem.

However a) it was working prior to 1.4.18/1.4.6 and b) the documentation
for MYSQL (both within Asterisk and in the C code) does not mention the
need to do so.

This change and lack of documentation will confuse others :-(

asterisk*CLI> show application MYSQL 
...
MYSQL(Query resultid ${connid} query-string)
...

cat app_addon_sql_mysql.c
....

/*  
EXAMPLES OF USE : 

exten => s,2,MYSQL(Connect connid localhost asterisk mypass credit)
exten => s,3,MYSQL(Query resultid ${connid} SELECT username,credit FROM
credit WHERE callerid=${CALLERIDNUM})
exten => s,4,MYSQL(Fetch fetchid ${resultid} datavar1 datavar2)
exten => s,5,GotoIf(${fetchid}?6:8)
...

Presumably that example will also now fail because the last comma is not
delimited? 

Issue History 
Date Modified   Username       Field                    Change               
====================================================================== 
03-07-08 05:48  ruffle         Note Added: 0083588                          
======================================================================