[Home]

Summary:ASTERISK-11596: MYSQL Query using wrong delimiters and not returning results
Reporter:Russell Brown (ruffle)Labels:
Date Opened:2008-03-07 04:43:31.000-0600Date Closed:2008-03-31 15:23:41
Priority:MajorRegression?No
Status:Closed/CompleteComponents:Addons/app_mysql
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:( 0) aabug
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@macro-stdexten:10] MYSQL("SIP/117-00713c90", "Connect connid localhost asterisk secret asterisk") in new stack
-- Executing [s@macro-stdexten:11] NoOp("SIP/117-00713c90", "Connid is 1") in new stack
-- Executing [s@macro-stdexten:12] GotoIf("SIP/117-00713c90", "0?s-GNORECORD|1") in new stack
-- Executing [s@macro-stdexten:13] NoOp("SIP/117-00713c90", "Updating MYSQL") in new stack
-- Executing [s@macro-stdexten:14] NoOp("SIP/117-00713c90", "Uniqueid is lls-1204885650.24") in new stack
-- Executing [s@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@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@macro-stdexten:17] NoOp("SIP/117-00713c90", "MYSQL resultid is ") in new stack
-- Executing [s@macro-stdexten:18] MYSQL("SIP/117-00713c90", "Clear ") in new stack
-- Executing [s@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.





****** ADDITIONAL INFORMATION ******

I'm using

Asterisk 1.4.18 built by root @ asterisk on a x86_64 running Linux on 2008-03-07 10:34:55 UTC
Asterisk-Addons 1.4.6
Comments:By: Michiel van Baak (mvanbaak) 2008-03-07 05:36:55.000-0600

This is a configuration issue.
You should escape that last comma.
By: Russell Brown (ruffle) 2008-03-07 05:48:32.000-0600

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 in the C code) does not mention the need to do so.

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

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 comma is not delimited?

Oh... and resultid still doesn't appear to get set.


By: Digium Subversion (svnbot) 2008-03-07 09:00:10.000-0600

Repository: asterisk-addons
Revision: 544

U   branches/1.4/apps/app_addon_sql_mysql.c

------------------------------------------------------------------------
r544 | tilghman | 2008-03-07 09:00:08 -0600 (Fri, 07 Mar 2008) | 3 lines

Fix documentation
(Closes issue ASTERISK-11596)

------------------------------------------------------------------------

http://svn.digium.com/view/asterisk-addons?view=rev&revision=544
By: Digium Subversion (svnbot) 2008-03-07 09:02:00.000-0600

Repository: asterisk-addons
Revision: 545

_U  trunk/

------------------------------------------------------------------------
r545 | tilghman | 2008-03-07 09:01:59 -0600 (Fri, 07 Mar 2008) | 10 lines

Blocked revisions 544 via svnmerge

........
r544 | tilghman | 2008-03-07 09:04:06 -0600 (Fri, 07 Mar 2008) | 3 lines

Fix documentation
(Closes issue ASTERISK-11596)

........

------------------------------------------------------------------------

http://svn.digium.com/view/asterisk-addons?view=rev&revision=545
By: Digium Subversion (svnbot) 2008-03-11 14:31:35

Repository: asterisk-addons
Revision: 548

_U  team/group/asterisk-addons-NoLossCDR-Redux2/
U   team/group/asterisk-addons-NoLossCDR-Redux2/configure
U   team/group/asterisk-addons-NoLossCDR-Redux2/configure.ac

------------------------------------------------------------------------
r548 | juggie | 2008-03-11 14:31:32 -0500 (Tue, 11 Mar 2008) | 25 lines

Merged revisions 545-546 via svnmerge from
https://origsvn.digium.com/svn/asterisk-addons/trunk

................
r545 | tilghman | 2008-03-07 10:05:58 -0500 (Fri, 07 Mar 2008) | 10 lines

Blocked revisions 544 via svnmerge

........
r544 | tilghman | 2008-03-07 09:04:06 -0600 (Fri, 07 Mar 2008) | 3 lines

Fix documentation
(Closes issue ASTERISK-11596)

........

................
r546 | russell | 2008-03-11 12:19:30 -0400 (Tue, 11 Mar 2008) | 4 lines

check for a different function to ensure that the version of spandsp is new enough
(closes issue ASTERISK-11611)
(reported by adriavidal, patched by me)

................

------------------------------------------------------------------------

http://svn.digium.com/view/asterisk-addons?view=rev&revision=548
By: Digium Subversion (svnbot) 2008-03-31 15:23:41

Repository: asterisk-addons
Revision: 570

_U  branches/1.6.0/
U   branches/1.6.0/Makefile
U   branches/1.6.0/Makefile.moddir_rules
U   branches/1.6.0/cdr/cdr_addon_mysql.c
U   branches/1.6.0/configs/cdr_mysql.conf.sample
U   branches/1.6.0/configure
U   branches/1.6.0/configure.ac
U   branches/1.6.0/res/res_config_mysql.c

------------------------------------------------------------------------
r570 | qwell | 2008-03-31 15:23:39 -0500 (Mon, 31 Mar 2008) | 73 lines

Merged revisions 540,545-546,550,554-557,559 via svnmerge from
https://origsvn.digium.com/svn/asterisk-addons/trunk

................
r540 | qwell | 2008-03-03 11:03:25 -0600 (Mon, 03 Mar 2008) | 13 lines

Merged revisions 539 via svnmerge from
https://origsvn.digium.com/svn/asterisk-addons/branches/1.4

........
r539 | qwell | 2008-03-03 11:01:57 -0600 (Mon, 03 Mar 2008) | 5 lines

Make sure to create the modules directory.
In some cases, it's possible that this won't already exist.

Closes issue ASTERISK-11550.

........

................
r545 | tilghman | 2008-03-07 09:05:58 -0600 (Fri, 07 Mar 2008) | 10 lines

Blocked revisions 544 via svnmerge

........
r544 | tilghman | 2008-03-07 09:04:06 -0600 (Fri, 07 Mar 2008) | 3 lines

Fix documentation
(Closes issue ASTERISK-11596)

........

................
r546 | russell | 2008-03-11 11:19:30 -0500 (Tue, 11 Mar 2008) | 4 lines

check for a different function to ensure that the version of spandsp is new enough
(closes issue ASTERISK-11611)
(reported by adriavidal, patched by me)

................
r550 | tilghman | 2008-03-12 00:50:55 -0500 (Wed, 12 Mar 2008) | 6 lines

Fix module, based on recent required API change
(closes issue ASTERISK-11619)
Reported by: IgorG
Patches:
      addons_res_mysql.diff uploaded by IgorG (license 20)

................
r554 | tilghman | 2008-03-18 12:43:14 -0500 (Tue, 18 Mar 2008) | 3 lines

When the dbsock is blank, we want to use NULL, not the empty string (which causes MySQL to use the default)
Reported via -dev mailing list, fixed by me.

................
r555 | qwell | 2008-03-18 12:48:23 -0500 (Tue, 18 Mar 2008) | 1 line

Add the same format checking that was added to Asterisk.
................
r556 | qwell | 2008-03-18 12:55:50 -0500 (Tue, 18 Mar 2008) | 1 line

Be more pedantic about format checking, since we can be.
................
r557 | qwell | 2008-03-18 13:06:31 -0500 (Tue, 18 Mar 2008) | 1 line

Switch to menuselect trunk, add targets for newt and curses versions of menuselect
................
r559 | tilghman | 2008-03-18 14:47:56 -0500 (Tue, 18 Mar 2008) | 2 lines

Create a compatibility mode, for people who want the old (incorrect) calldate behavior

................

------------------------------------------------------------------------

http://svn.digium.com/view/asterisk-addons?view=rev&revision=570