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

[Andreas Sikkema]

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.

Leaving a database connection open for the duration of the 
call is not optimal, so I really want to have Disconnect()
be safe to call on a connection identifier, even if the 
connection was already closed. I've been thinking about 
resetting the connection identifier back to -1 when 
disconnect is finished, but this is impossible with the 
current interface.

I'm thinking of adding a second parameter to Disconnect, 
being a variable name (the connection identifier variable 
again) which can be set to -1 if the disconnect succeeds.

exten => h,1,MYSQL(Disconnect ${connid} connid);

Disconnect can then check if the incoming value of connid 
is a valid identifier or -1 if it tries to disconnect a 
connection for the second time.

Does anyone have a better idea?

-- 
Andreas Sikkema                Rits tele.com
Scheepmakersstraat 11      3011 VH Rotterdam
t: +31 (0)10 2245544    f: +31 (0)10 2245540