Identifier 2, identifier_type 2 not found in identifier list

Question

I am using asterisk 11.9.0 and created a dialplan.My problem is that everytiime i perform an sql operation its working fine but the line same => n,mysql(clear ${resultid}) is giving a error Identifier 2, identifier_type 2 not found in identifier list I have checked my tables in database also checked my syntax in dialplan but there is no such error i can find. My dialplan

[from-pstn]

exten => _X.,1,NoOp(<<===Call Starts Here===>)
exten => _X.,n,NoOp(<<===IVR DEVELOPED BY DEETYA SOFT===>>)


;Set mysql user password
same => n,Set(__mysql_user=abc)
same => n,Set(__mysql_pwd=****)
same => n,Set(__mysql_db=ivr)

;Check is direct or dtmf
same => n,mysql(Connect connid localhost ${mysql_user} ${mysql_pwd} ${mysql_db})
same => n,mysql(Query resultid ${connid} select account_id,routing,tone_type from account_did where `did`='${CALLERID(dnid)}' and `active`='1')

same => n,MYSQL(Fetch fetchid ${resultid} account_id routing tone_type)
same => n,mysql(clear ${resultid})

same => n,set(__ACCOUNT_ID=${account_id})
same => n,set(__ROUTING=${routing})
same => n,set(__TONETYPE=${tone_type})

same => n,GotoIf($["${ACCOUNT_ID}" = ""]?callend:start)
same => n(callend),Hangup(<<===========callend==========>>)


same => n(start),NoOp(<<============ACCOUNT ID = ${ACCOUNT_ID}=============>>)
same => n,NoOp(<<============ROUTING = ${routing}=============>>)

same => n,set(__FROM=${CDR(clid)})
same => n,set(__DID=${CDR(dst)})
same => n,set(__START=${CDR(start)})
same => n,set(__UNIQUE_ID=${CDR(uniqueid)})

same => n,GotoIf($["${routing}" = "direct"]?direct_routing:dtmf_routing)
same => n(direct_routing),Goto(direct_routing,${CALLERID(dnid)},1)
same => n(dtmf_routing),Goto(dtmf_routing,s,1)
same => n,Hangup(<<===========No route hangup==========>>)

[direct_routing]
exten => _X.,1,NoOp(<<=============Start direct routing===============>>)

same => n,MYSQL(Query resultid ${connid} INSERT INTO `call_info` (`account_id`,`from`,`did`,`start`,`uniqueid`) VALUES (${ACCOUNT_ID},'${FROM}','${DID}','${START}','${UNIQUE_ID}'))

same => n,MYSQL(Query resultid ${connid} INSERT INTO `cust_sms` (`account_id`,`from`,`unique_id`,`call_status`,`DID`,`ANS_TIME`) VALUES (${ACCOUNT_ID},'${FROM}','${UNIQUEID}',0,'${DID}','${START}'))

;same => n,MYSQL(Query resultid ${connid} INSERT INTO `cust_sms`(`account_id`,`from`,`unique_id`,`STATUS`,`DID`,`ANS_TIME`) VALUES (${ACCOUNT_ID},'${FROM}','${UNIQUEID}',0,'${DID}','${START}'));;;;;;D;;;;;;


same => n,mysql(clear ${resultid})
same => n,Answer()

same => n,Set(__TONEFILE=${IF($[ ${TONETYPE} = file]?/var/www/html/tone/tone_${DID}:/var/www/html/new_welcome)})

same => n,Playback(${TONEFILE});

same => n,GOTO(dr_ivr,start,1)
same => n,Hangup(<<===========Direct routing hangup==========>>)

same => n,MYSQL(Connect connid deetya abc **** ivr)
same => n,mysql(Query resultid ${connid} UPDATE call_info SET `end`="${END}" where `uniqueid`='${UNIQUE_ID}')
same => n,mysql(clear ${resultid})


;endcall
exten => h,1,Goto(dr_ivr_endcall,h,1)



[dr_ivr]
exten => start,1,noop(######START######)

same => n,mysql(Query resultid ${connid} SELECT count(*),number,call_info.user_id FROM `call_info` JOIN users ON users.id=call_info.user_id JOIN direct_routing_flow ON call_info.user_id = direct_routing_flow.user_id WHERE call_info.`account_id` =${ACCOUNT_ID} AND `from` = ${FROM} AND users.active=1 order by call_info.id desc limit 1)


;same => n,mysql(Query resultid ${connid} SELECT count(*),number,call_info.user_id FROM `call_info` JOIN users ON users.id=call_info.user_id JOIN direct_routing_flow ON call_info.user_id = direct_routing_flow.user_id WHERE call_info.`account_id` =${ACCOUNT_ID} AND `from` = ${FROM} AND users.active=1 order by call_info.id desc limit 1);;;DDDDDD

same => n,MYSQL(Fetch fetchid ${resultid} count number user_id)
same => n,mysql(clear ${resultid})


same => n,GotoIf($[${count} < 1]?startroundrobin)

same => n,set(__NUMBER=${number})
same => n,set(__USER_ID=${user_id})

same => n,mysql(Query resultid ${connid} UPDATE call_info SET user_id='${USER_ID}',`to`='${NUMBER}' where `uniqueid`='${UNIQUE_ID}')
same => n,MYSQL(Query resultid ${connid} UPDATE cust_sms SET `to` = '${NUMBER}' WHERE `UNIQUE_ID`='${UNIQUE_ID}');;;;;D;;;;

same => n,NooP(##########################---------Current Number is --[${NUMBER}]-----${UNIQUE_ID}------------------###############)
same => n,Set(CALLERID(all)=120${DID})
same => n,dial(DAHDI/g0/0${NUMBER},20,mM(ANSWEREDED))




;Check Round Robin executive number
same => n(startroundrobin),set(counter=1)

same => n,mysql(Query resultid ${connid} select count(*) from direct_routing_flow as drf join users ON users.id = drf.user_id where drf.did='${DID}' and users.active = 1 and drf.account_id =${ACCOUNT_ID})
same => n,MYSQL(Fetch fetchid ${resultid} count)
same => n,mysql(clear ${resultid})

same => n,set(__COUNT=${count})



same => n,mysql(Query resultid ${connid} select last from direct_routing_flow as drf join users ON users.id = drf.user_id where drf.did='${DID}' and users.active = 1 order by last desc)
same => n,MYSQL(Fetch fetchid ${resultid} last)
same => n,mysql(clear ${resultid})

same => n,set(__LAST_RANK=${last})

same => n,Set(i=${IF($[ ${LAST_RANK} >= ${COUNT}]?1:$[${LAST_RANK} + 1])})




same => n,While($[${i} <= ${COUNT}])

same => n,mysql(Query resultid ${connid} SELECT number,user_id FROM (SELECT direct_routing_flow.*,@i := @i + 1 as rank FROM `direct_routing_flow` JOIN users ON users.id = direct_routing_flow.user_id JOIN (select @i := 0 )  temp WHERE direct_routing_flow.did='${DID}' and users.active='1' and direct_routing_flow.`account_id` =${ACCOUNT_ID} ORDER BY priority asc) temp1 where rank=${i});;;DDDDDD


;same => n,mysql(Query resultid ${connid} SELECT number,user_id FROM (SELECT direct_routing_flow.*,@i := @i + 1 as rank FROM `direct_routing_flow` JOIN users ON users.id = direct_routing_flow.user_id JOIN (select @i := 0 )  temp WHERE direct_routing_flow.did='${DID}' and users.active='1' and direct_routing_flow.`account_id` =${ACCOUNT_ID} ORDER BY priority asc) temp1 where rank=${i});;;DDDDDD


same => n,MYSQL(Fetch fetchid ${resultid} number user_id)
same => n,mysql(clear ${resultid})

same => n,set(__NUMBER=${number})
same => n,set(__USER_ID=${user_id})

;Update last dial 
same => n,mysql(Query resultid ${connid} UPDATE direct_routing_flow SET last='1' where `did`='${DID}')
same => n,mysql(Query resultid ${connid} UPDATE direct_routing_flow SET last='${i}' where `did`='${DID}' and `user_id`='${USER_ID}')




same => n,mysql(Query resultid ${connid} UPDATE call_info SET user_id='${USER_ID}',`to`='${NUMBER}' where `uniqueid`='${UNIQUE_ID}')

same => n,MYSQL(Query resultid ${connid} UPDATE cust_sms SET `to` = '${NUMBER}' WHERE `UNIQUE_ID`='${UNIQUE_ID}');;;;;D;;;;

same => n,NooP(##########################---------Current Number is --[${NUMBER}]-----${UNIQUE_ID}------------------###############)
same => n,Set(CALLERID(all)=120${DID})
same => n,dial(DAHDI/g0/0${NUMBER},20,mM(ANSWEREDED))

same => n,NooP(<<=================DISPOSIOTION -- ${CDR(disposition)} ===================>)

same => n,Set(i=$[${i} + 1])
same => n,EndWhile()

;endcall
exten => h,1,Goto(dr_ivr_endcall,h,1)      

[macro-ANSWEREDED]
exten => s,1,noop(CALL_ANSWERED ==============${UNIQUE_ID}==================)
exten => s,n,set(__DISPOSITION=${CDR(disposition)})
exten => s,n,set(__ANSWER=${CDR(answer)})
exten => s,n,set(__DISPOSITION=${CDR(disposition)})


exten => s,n,mysql(Connect connid localhost ${mysql_user} ${mysql_pwd} ${mysql_db})
exten => s,n,mysql(Query resultid ${connid} UPDATE call_info SET `user_id`='${USER_ID}',`to`='${NUMBER}', `answer`='${ANSWER}',`disposition`='${DISPOSITION}' where `uniqueid`='${UNIQUE_ID}')


exten => s,n,MYSQL(Query resultid ${connid} UPDATE cust_sms SET `to` = '${NUMBER}', `ANS_TIME`='${ANSWER}',`call_status`='${DISPOSITION}' WHERE `UNIQUE_ID`='${UNIQUE_ID}');;;;;D;;;;


exten => s,n,mysql(clear ${resultid})

exten => s,n,Mixmonitor(/var/www/html/recordings/answered/${DID}_${FROM}_${ANSWER}.wav)



[dr_ivr_endcall]
exten => h,1,noop(###### END CALL ######)


exten => h,n,set(__END=${CDR(end)})
exten => h,n,set(__DURATION=${CDR(duration)})
exten => h,n,set(__BILLSEC=${CDR(billsec)})
;exten => h,n,set(__DISPOSITION=${CDR(disposition)})
exten => h,n,Set(ETIME=${STRFTIME(${EPOCH},,%Y-%m-%d %H:%M:%S)})


exten => h,n,MYSQL(Query resultid ${connid} UPDATE call_info SET `end`='${ETIME}',`billsec`='${BILLSEC}',`duration`='${DURATION}' where `uniqueid`='${UNIQUE_ID}')


exten => h,n,MYSQL(Query resultid ${connid} UPDATE cust_sms SET `STATUS`='1' WHERE `UNIQUE_ID`='${UNIQUE_ID}');;;;;D;;;;
exten => h,n,AGI(sms.php)


exten => h,n,hangup()

The output of my asterisk cli is

 -- Executing [4862800@direct_routing:3] MYSQL("DAHDI/i1/9717055088-1", "Query resultid 1 INSERT INTO `cust_sms`(`account_id`,`from`,`unique_id`,`STATUS`,`DID`,`ANS_TIME`) VALUES (1,'9717055088','1401994718.0',0,'4862800','2014-06-06 00:28:38')") in new stack
    -- Executing [4862800@direct_routing:4] MYSQL("DAHDI/i1/9717055088-1", "clear 2") in new stack
[Jun  6 00:28:38] WARNING[7088][C-00000000]: app_mysql.c:194 find_identifier: Identifier 2, identifier_type 2 not found in identifier list
[Jun  6 00:28:38] WARNING[7088][C-00000000]: app_mysql.c:520 aMYSQL_clear: Invalid result identifier 2 passed in aMYSQL_clear
    -- Executing [4862800@direct_routing:5] Answer("DAHDI/i1/9717055088-1", "") in new stack
    -- Executing [4862800@direct_routing:6] Set("DAHDI/i1/9717055088-1", "__TONEFILE=/var/www/html/new_welcome") in new stack
    -- Executing [4862800@direct_routing:7] Playback("DAHDI/i1/9717055088-1", "/var/www/html/new_welcome") in new stack
    -- <DAHDI/i1/9717055088-1> Playing '/var/www/html/new_welcome.slin' (language 'yes')
    -- Executing [4862800@direct_routing:8] Goto("DAHDI/i1/9717055088-1", "dr_ivr,start,1") in new stack
    -- Goto (dr_ivr,start,1)
    -- Executing [start@dr_ivr:1] NoOp("DAHDI/i1/9717055088-1", "######START######") in new stack
    -- Executing [start@dr_ivr:2] MYSQL("DAHDI/i1/9717055088-1", "Query resultid 1 SELECT count(*") in new stack
[Jun  6 00:28:42] WARNING[7088][C-00000000]: app_mysql.c:405 aMYSQL_query: aMYSQL_query: mysql_query failed. Error: 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
    -- Executing [start@dr_ivr:3] MYSQL("DAHDI/i1/9717055088-1", "Fetch fetchid 2 count number user_id") in new stack
[Jun  6 00:28:42] WARNING[7088][C-00000000]: app_mysql.c:194 find_identifier: Identifier 2, identifier_type 2 not found in identifier list
[Jun  6 00:28:42] WARNING[7088][C-00000000]: app_mysql.c:503 aMYSQL_fetch: aMYSQL_fetch: Invalid result identifier 2 passed
    -- Executing [start@dr_ivr:4] MYSQL("DAHDI/i1/9717055088-1", "clear 2") in new stack
[Jun  6 00:28:42] WARNING[7088][C-00000000]: app_mysql.c:194 find_identifier: Identifier 2, identifier_type 2 not found in identifier list
[Jun  6 00:28:42] WARNING[7088][C-00000000]: app_mysql.c:520 aMYSQL_clear: Invalid result identifier 2 passed in aMYSQL_clear
[Jun  6 00:28:42] WARNING[7088][C-00000000]: ast_expr2.fl:470 ast_yyerror: ast_yyerror():  syntax error: syntax error, unexpected '<', expecting $end; Input:
 < 1
 ^
[Jun  6 00:28:42] WARNING[7088][C-00000000]: ast_expr2.fl:474 ast_yyerror: If you have questions, please refer to https://wiki.asterisk.org/wiki/display/AST/Channel+Variables 

I am unable to find the error

Any help would be appreciated.

Answer 1

app_mysql is marked to be removed and have not be used in new dialplans

Use REALTIME function and func_odbc instead.

PS There are no way find error without see your dialplan. Very likly it is variable name