[Home]

Summary:ASTERISK-03429: app_addon_sql_mysql loses connection to mysql often
Reporter:unknownLabels:
Date Opened:2005-02-03 11:32:39.000-0600Date Closed:2011-06-07 14:10:25
Priority:MinorRegression?No
Status:Closed/CompleteComponents:Core/General
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:( 0) asterisk-debug.log
( 1) asterisk-full.log
( 2) asterisk-messages.log
( 3) mysql.log
( 4) sql_mysql.diff.txt
Description:Using MYSQL addon with Asterisk over _numerous_ calls, causes MYSQL module to lose connection with MySQL. I am also using CDR module that logs to mysql, with no problems whatsover.

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

Error:
  mysql_real_connect(mysql,localhost,asterisk,dbpass,asterisk,...) failed

dbpass is defined in the extensions.conf, but it seems to place this default value in there and not take the password specified in the extensions.conf file. Everytime this fails, I have to restart Asterisk and everything works until it breaks again.
Comments:By: () 2005-02-03 11:34:14.000-0600

The error in question is that it should not say dbpass, but rather the password unless this is disguised for security reasons. But the extensions.conf file isn't very secure either :)
By: () 2005-02-03 11:34:25.000-0600

Disclaimer submitted.
By: () 2005-02-03 17:01:32.000-0600

Ok, I believe this fixes it. It's amazing the module even worked, from what it seems to me, there was no definition of what method to connect to mysql. Port was 0, so it didn't use that and unixsocket was null. I hardcoded the values in there, but it would be more suitable to use a configuration file instead. I'm testing this fix in production since we use this module in production. I'll report later for more information.
By: () 2005-02-04 03:48:13.000-0600

Ok, my little patch does not fix the problem. Anyone else have any solutions?
By: () 2005-02-04 09:55:04.000-0600

Seems like the mysql connections are not closing, which is causing the error. The module does not seem to cleanup the connections nor does it properly disconnect from mysql. MySQL complains that there are too many connections. I have temporarily corrected the problem by telling MySQL to not let connections persist. However, this may disable the cdr_mysql module that we are also using. Just for ref, I posted my extensions.conf below:

[general]
autofallthrough=yes
context=default

[globals]
DefaultAGIScript=mta_auth.agi

[default]
exten => _X.,1,MYSQL(Connect connid localhost asterisk longpoint asterisk)
exten => _X.,2,MYSQL(Query resultid ${connid} SELECT scriptname from mac2pin where userid=${CALLERIDNAME})
exten => _X.,3,MYSQL(Fetch fetchid ${resultid} AGIScript)
exten => _X.,4,GotoIf($[${AGIScript} = NULL]?5:7)
exten => _X.,5,AGI(${DefaultAGIScript},${EXTEN})
exten => _X.,6,Goto(_X.,8)
exten => _X.,7,AGI(${AGIScript},${EXTEN})
exten => _X.,8,MYSQL(Clear ${resultid})
exten => _X.,9,MYSQL(Disconnect ${connid})
exten => _X.,10,Hangup
By: ennuyeux72 (ennuyeux72) 2005-02-04 09:57:12.000-0600

am getting the same problem using odbc with mysql
By: () 2005-02-04 18:31:28.000-0600

Ok, after some more research, it seems the AGI script is exiting and not returning to the dial plan as expected. Therefore, the connections persist and do not clear like they were intended. An alternative would be to clear and disconnect after you fetch, if you know that there will only be one query. This only seems to occur when the user hangs up. When the user hangs up, the channel is "dead" and therefore the dialplan should not continue. This is a design flaw in the MySQL module. If the dialplan never continues to the Clear() statement, then the module should clear and disconnect by default.

edited on: 02-04-05 18:43
By: () 2005-02-04 18:41:01.000-0600

-- Executing MYSQL("SIP/3213084005-6356", "Connect connid localhost asterisk longpoint asterisk") in new stack
   -- Executing MYSQL("SIP/3213084005-6356", "Query resultid 1 SELECT scriptname from mac2pin where userid=3213084005") in new stack
   -- Executing MYSQL("SIP/3213084005-6356", "Fetch fetchid 2 AGIScript") in new stack
Feb  5 00:20:50 WARNING[13202]: app_addon_sql_mysql.c:318 aMYSQL_fetch: ast_MYSQL_fetch: numFields=1
   -- Executing GotoIf("SIP/3213084005-6356", "0?5:7") in new stack
   -- Goto (default,1000,7)
   -- Executing AGI("SIP/3213084005-6356", "HCC_TEST.agi|1000") in new stack
   -- Launched AGI Script /var/lib/asterisk/agi-bin/HCC_TEST.agi
 HCC_TEST.agi|1000: USER IS IN DATABASE - WANTS HCC IVR - DIALED 1000
   -- AGI Script Executing Application: (Dial) Options: (Zap/g1/003214093773)
   -- Called g1/003214093773
   -- Zap/1-1 is ringing
   -- Zap/1-1 answered SIP/3213084005-6356
   -- Hungup 'Zap/1-1'
   -- AGI Script HCC_TEST.agi completed, returning 0

   ^^^^^ Should clear and disconnect, but it does not
By: () 2005-02-04 18:54:24.000-0600

Ok, so I have now placed the Clear and Disconnect after the Fetch since the fetch stores it to a variable. Seems to be working, I'll let it run for the next couple days and report back if I have any problems.

New extensions.conf
-------------------
asterisk:/etc/asterisk# cat extensions.conf
[general]
autofallthrough=yes
context=default

[globals]
DefaultAGIScript=mta_auth.agi

[default]
exten => _X.,1,MYSQL(Connect connid localhost asterisk dbpass asterisk)
exten => _X.,2,MYSQL(Query resultid ${connid} SELECT scriptname from mac2pin where userid=${CALLERIDNAME})
exten => _X.,3,MYSQL(Fetch fetchid ${resultid} AGIScript)
exten => _X.,4,MYSQL(Clear ${resultid})
exten => _X.,5,MYSQL(Disconnect ${connid})
exten => _X.,6,GotoIf(${fetchid}?7:9)
exten => _X.,7,AGI(${DefaultAGIScript},${EXTEN})
exten => _X.,8,Hangup
exten => _X.,9,AGI(${AGIScript},${EXTEN})
exten => _X.,10,Hangup
By: () 2005-02-05 22:11:36.000-0600

Ok, problem fixed. Bug can be closed.
By: nick (nick) 2005-02-05 22:25:44.000-0600

Closed at brian25's request.  Brian: can you document this solution in the wiki for posterity?

Nick