[asterisk-users] MySQL question

[Steve Edwards]

On Wed, 9 Sep 2009, Barton Fisher wrote:

> Here's my dialplan:
> 
> [initialize-log]
>
>  exten => _X.,1,Noop(Initialize CallLog ${CallersDT} ${CallersTel} ${LOGCONFIRM})
>  exten => _X.,n,MYSQL(Connect connid ${HOST} ${USER} ${PASSWORD} ${DATABASE})
>  exten => _X.,n,MYSQL(QUERY resultid ${connid} INSERT\ INTO\ tbl_calllog\ SET\ log_start=\'${CallersDT}\'\,\
> log_phone=\'${CallersTel}\'\,\ log_confirmation=\'${LOGCONFIRM}\')
>
>  exten => _X.,n,Set(ERROR=1042)
>  exten => _X.,n,GotoIf($["${MYSQL_STATUS}" = "-1"]?error,${IVR-EXTEN},1)
>  exten => _X.,n,Noop(Successful MYSQL STAT:[${MYSQL_STATUS}] CONNID:[${connid}] RESULTS:[${resultid}])
>  exten => _X.,n,MYSQL(Clear ${resultid})
>  exten => _X.,n,MYSQL(Disconnect ${connid})
>  exten => _X.,n,Return
> 
> The problem is I don't know if the record was inserted successfully for 
> certain? I've used this code before and it appears to work. But on this 
> project the record fails to insert.  Me confused...

I'm not a big fan of MySQL in the dialplan. It seems too "ugly" and 
limiting to me. I do all my MySQL cruft in AGIs. I've never used 
app_addon_sql_mysql.c so I'm kind of winging it here :)

> CLI:

(Looks more like syslog to me.)

> [2009-09-09 09:02:17] VERBOSE[32573] logger.c: == [3388-SIP/8001-08d77ab8] [INITIALIZE LOG]: Initialize CallLog
> 2009-09-09 11:02:178001 1252512137.1
> [2009-09-09 09:02:17] VERBOSE[32573] logger.c: -- Executing [3388 at initialize-log:2] MYSQL("SIP/8001-08d77ab8",
> "Connect connid xx.xx.xx.xx dbuser pass mysqldb") in new stack

> [2009-09-09 09:02:17] VERBOSE[32573] logger.c: -- Executing [3388 at initialize-log:3] Set("SIP/8001-08d77ab8",
> "ERROR=1041") in new stack

This isn't in your dial plan snippet.

> [2009-09-09 09:02:17] VERBOSE[32573] logger.c: -- Executing [3388 at initialize-log:4] GotoIf("SIP/8001-08d77ab8",
> "0?error|3388|1") in new stack

Nor this?

> [2009-09-09 09:02:17] VERBOSE[32573] logger.c: -- Executing [3388 at initialize-log:5] MYSQL("SIP/8001-08d77ab8", "QUERY
> resultid 1 INSERT INTO tbl_calllog SET log_start='2009-09-09 11:02:17', log_phone='8001',
> log_confirmation='1252512137.1'") in new stack
> [2009-09-09 09:02:17] VERBOSE[32573] logger.c: -- Executing [3388 at initialize-log:6] Set("SIP/8001-08d77ab8",
> "ERROR=1042") in new stack
> [2009-09-09 09:02:17] VERBOSE[32573] logger.c: -- Executing [3388 at initialize-log:7] GotoIf("SIP/8001-08d77ab8",
> "0?error|3388|1") in new stack

The "0" before the "?" means the condition ("${MYSQL_STATUS}" = "-1") 
evaluated to false and the goto was not taken. My guess is 
app_addon_sql_mysql.c didn't detect any error.

What happens if you execute:

 	mysql\
 		--database=mysqldb\
 		--execute="INSERT INTO tbl_calllog SET log_start='2009-09-09 11:02:17', log_phone='8001', log_confirmation='1252512137.1'"\
 		--host=localhost\
 		--password=pass\
 		--user=dbuser

from the command line?

> [2009-09-09 09:02:17] VERBOSE[32573] logger.c: -- Executing [3388 at initialize-log:8] Verbose("SIP/8001-08d77ab8", "1|
> == [3388-SIP/8001-08d77ab8] [INITIALIZE LOG]: Initialize CallLog was successful MYSQL STAT:[0] CONNID:[1] RESULTS:[]")
> in new stack
> [2009-09-09 09:02:17] VERBOSE[32573] logger.c: == [3388-SIP/8001-08d77ab8] [INITIALIZE LOG]: Initialize CallLog was
> successful MYSQL STAT:[0] CONNID:[1] RESULTS:[]
> [2009-09-09 09:02:17] VERBOSE[32573] logger.c: -- Executing [3388 at initialize-log:9] MYSQL("SIP/8001-08d77ab8", "Clear
> ") in new stack
> [2009-09-09 09:02:17] WARNING[32573] app_addon_sql_mysql.c: Identifier 0, identifier_type 2 not found in identifier
> list
> [2009-09-09 09:02:17] WARNING[32573] app_addon_sql_mysql.c: Invalid result identifier 0 passed in aMYSQL_clear

The ${resultid} from your insert appears to be NULL. This is somewhat 
reasonable (insert's don't have result sets), so "clear" objects to not 
having an argument.

Curiously, my copy of app_addon_sql_mysql.c (1.2.9) refers to 
mysql_store_result() in an error message ("aMYSQL_query: 
mysql_store_result() failed on query") but never actually calls it.

-- 
Thanks in advance,
-------------------------------------------------------------------------
Steve Edwards       sedwards at sedwards.com      Voice: +1-760-468-3867 PST
Newline                                              Fax: +1-760-731-3000