[Asterisk-Dev] Issue with current PostgresSQL application and also with 3rd party MySQL application

[Steven Critchfield]

On Thu, 2004-09-02 at 05:36, Andreas Sikkema wrote:
> Hi,
> 
> We're using the MySQL app quite a lot and have noticed a 
> problem with it that will also have an impact on the 
> PostgresSQL application since it shares a lot of the 
> same kind of code.
> 
> Suppose one has the following configuration (which uses 
> MySQL, but I think the same will happen with Postgres):
> 
> exten => s,1,MYSQL(Connect connid ${billing_db_host} ${billing_db_user} ${billing_db_password} ${billing_db_name})
> exten => s,2,MYSQL(Query resultid ${connid} SELECT userid\, credit FROM users WHERE username=\'${CALLERIDNUM}\')
> exten => s,3,MYSQL(Fetch fetchid ${resultid} userid credit)
> 
> <do some magic here with the results from the query
> 
> ; We're done with the database, so we can disconnect
> ; No need to keep a db connection open during the call
> exten => s,200,MYSQL(Disconnect ${connid});
> exten => s,201,Dial(SIP/${DNID}@sip_proxy-out,80,S(${max_duration}));
> 
> But, because a hangup can also occur while we're finding 
> out if the user can dial the extension, there's also need 
> for the following:
> 
> exten => h,1,MYSQL(Disconnect ${connid});
> 
> This opens a whole can of worms regarding race conditions 
> etc. We've experencied numerous crashes of Asterisk with 
> this scenario, until I only disconnected from the database 
> when the call was hanging up. 
> 
> Our theory for the reason why Asterisk is crashing is that 
> the second disconnect from call A is closing the database 
> connection of another call, while it is in progress. This 
> might have some unexpected results.

There is no need to modify disconnect to take more than one variable.
You can set your connid to -1 after close on your own and do a
conditional check before close in hangup. 

Of course if your query could finish quickly then you know you can
finish the query part faster than someone can hangup. 

The next part is to not do that kind of query directly in the dialplan.
Do it in a application or a AGI script where you can completely control
the connections. 


-- 
Steven Critchfield <critch at basesys.com>