[Home]

Summary:ASTERISK-08118: MYSQL will allow table LOCK, but error on a UNLOCK
Reporter:real (albaker)Labels:
Date Opened:2006-11-12 15:51:24.000-0600Date Closed:2011-06-07 14:07:50
Priority:MinorRegression?No
Status:Closed/CompleteComponents:Applications/General
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:
Description:From the Asterisk dialplan I am attemeting to use the MSQL function to LOCK a mySQL table befor operation on it and UNLOCK it after completing the operations. This is desired to allow Asterisk on several boxes to share and update information on a mySQL database server in an orderly , reiable manner

The debugging information from CLI shows that the LOCK is acceptted and processed by the MYSQL function.

The UNLOCK TABLE is rejected by the MYSQL funtion

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

============================ extensions.conf =======
exten => 18008888888,1,Playback(you-have-reached-a-test-number)
exten => 18008888888,n,MYSQL(Connect connid localhost root mawxxx NAM_DB)
exten => 18008888888,n,MYSQL(Query resultid ${connid} LOCK\ TABLES\ AGENT\ WRITE)
exten => 18008888888,n,MYSQL(Query resultid ${connid} SELECT\ FIRST_NAME\ ','\ LAST_NAME\ FROM\ AGENT\ WHERE\ AGENT_NUMBER=260276888)
[b]exten => 18008888888,n,MYSQL(Query resultid ${connid} UNLOCK\ TABLES)[/b]
exten => 18008888888,n,MYSQL(Disconnect ${connid})
exten => 18008888888,n,SayAlpha( ${firstname} )
=================================================
-- Executing Playback("SIP/mark-09d5d658", "you-have-reached-a-test-number") in new stack
-- Playing 'you-have-reached-a-test-number' (language 'en')
-- Executing MYSQL("SIP/mark-09d5d658", "Connect connid localhost root mawxxx NAM_DB") in new stack
-- Executing MYSQL("SIP/mark-09d5d658", "Query resultid 3 LOCK TABLES AGENT WRITE") in new stack
-- Executing MYSQL("SIP/mark-09d5d658", "Query resultid 3 SELECT FIRST_NAME , LAST_NAME FROM AGENT WHERE AGENT_NUMBER=260276888") in new stack
-- Executing MYSQL("SIP/mark-09d5d658", "Fetch foundRow 4 firstname lastname") in new stack
-- Executing MYSQL("SIP/mark-09d5d658", "Query resultid 3 UNLOCK TABLES") in new stack
[b]Nov 12 17:05:53 WARNING[16299]: app_addon_sql_mysql.c:275 aMYSQL_query: aMYSQL_query: mysql_store_result() failed on query UNLOCK TABLES[/b]
-- Executing MYSQL("SIP/mark-09d5d658", "Disconnect 3") in new stack
-- Executing SayAlpha("SIP/mark-09d5d658", " Alice ") in new stack
-- Playing 'letters/space' (language 'en')

================== CLI DEBUG =============================

Comments:By: Tilghman Lesher (tilghman) 2006-11-12 18:24:05.000-0600

I see that you're not doing "AUTOCOMMIT=0" which is what the man page for MySQL explicitly says you must do.  Otherwise, the table is immediately unlocked after the LOCK TABLES statement succeeds.  See:  http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html
By: real (albaker) 2006-11-14 14:41:58.000-0600

Thank you for the feedback.

I re-read the man page on SQL and if I was using a TRANSACTION table I fully agree that the AUTOCOMMIT=1, vs AUTOCOMMIT=0 would affect if SQL did what I wanted. But I am just using a regualt table.

BUT - More Importantly

the ERROR I am getting is in ASTERISK, not from SQL if I am reading the error correctly.

app_addon_sql_mysql.c:275 aMYSQL_query: aMYSQL_query: mysql_store_result() failed on query UNLOCK TABLES

seems to indicate Asterisk is having a problem NOT SQL.

For example, if you invoke  mysql from the commad line and type

UNLOCK TABLES;

no error is generated, even if no table has ever been locked.

Hence, I think Asterisk is croaking over the statment "UNLOCK TABLES" regardless of thet fact the mySQL is fine with it .

Am I missing something ???

i.e. I think if mySQL will accepet a statemet and not complain, then I think that the Asterisk MYSQL interface should accept it and not error .

Am I looking at this wrong ?


By: Tilghman Lesher (tilghman) 2006-11-14 15:53:37.000-0600

You're looking at it wrong.  Asterisk is merely reporting back the error from mysql_store_result() which is part of the MySQL API.

I suggest that you add the statement "AUTOCOMMIT=0" to your sequence of queries, prior to running "LOCK TABLES..." and try again.
By: Jason Parker (jparker) 2006-11-21 12:27:39.000-0600

albaker, any update here?
By: real (albaker) 2006-11-21 23:38:10.000-0600

Hello

Got called out of towm where I could not test this and just back in.

Initial test of the suggestion appears to casue some very strange results...

Before I post CLI debug log and other stuff I want look at this far more carefully tomorrow  before I take up folks time to be sure I am not FUBARing something here.

Will post results, Good / Bad / Ugly.

Thx to everyone who has taken time on ths so far!
By: Joshua C. Colp (jcolp) 2006-12-04 14:10:19.000-0600

I'll chime in here after watching this from afar... what happened? :D
By: Anthony LaMantia (alamantia) 2006-12-16 03:28:19.000-0600


By: Jason Parker (jparker) 2007-01-09 14:27:49.000-0600

albaker: It's been over a month since your last reply.  I'm going to have to assume that this is no longer an issue.  If it is still an issue, please reopen this bug, or contact a bug marshal on irc at irc.freenode.net, in the channel #asterisk-bugs.