[asterisk-dev] res/res_odbc.c

[Nick Gorham]

Tilghman Lesher wrote:

>On Monday 05 November 2007 03:49:16 Nick Gorham wrote:
>  
>
>>If a "odbc show" is issued against a ODBC driver that only supports a
>>single active statement, the test query (or default select 1) can hit
>>the existing active statement, cauing it all to go horribly wrong.
>>
>>The simple solution (It seems to me) is to use the ODBC
>>SQLGetConnectAttr( SQL_CONNECTION_DEAD ) methode of checking the status
>>of the driver. This seems to work fine against SQLServer using our
>>(Easysoft's) driver, but it may need checking how well it works against
>>other drivers.
>>
>>I have made the changes against the current (02/11/07) 1.4 trunk and I
>>can send the output from "svn diff" if that helps.
>>    
>>
>
>The right place for this is actually the issue tracker:
>  
>
Ok, but I went here because I was more interested in suggesting the 
solution, instead of just reporting a bug. I have already fixed the 
problem on one of our (Easysofts) customers Asterisk installation, and I 
wanted to offer back my changes. I had already asked in several places 
about odbc related issues, and getting no reply started looking harder 
myself.

>http://bugs.digium.com, but since you're already here, I need to ask the
>obvious question of:  are you using "pooling=yes" in res_odbc.conf?  That
>option should prevent this issue from occurring.
>  
>
Yep, I am using "pooling=yes", and no it doesn't seem to stop it happening.

Checking again in the code, I can't see anything that would stop it 
happening with pooling set. The code in handle_cli_odbc_show() locks 
odbc_list, then itterates down the list, calling ast_odbc_sanity_check() 
on each connection. ast_odbc_sanity_check()() then allocates a statement 
and runs its query. I cant see anything in that where there is anything 
that syncronises the query with any other activity on the connection.

>In terms of the ODBC option, we'll look into it, but I doubt it's as effective
>as running the generic query.  There are some databases (notably MySQL)
>where doing the database ping simply doesn't work.
>
>  
>
Well, thats entirly up to you (of course), I am not sure just what you 
mean as "effective", its certainly slower than using the method the spec 
suggests as the CONNECTION_DEAD definition does not require (infact its 
not allowed to do ) a client server round trip. Myself I would think it 
would be better to get the driver writers to support the spec, but what 
do I know.

There was a problem I found in our SQLServer driver that was the main 
problem I was debugging for the customer, The driver was failing under 
heavy load once the number of open file handles went past FD_SETSIZE, 
having fixed that, the combination seems bullet proof, unless you do a 
show odbc, when it fails. As to why it fails as badly as it does, I 
haven't checked, but you certainly get a error from the driver reporting 
a connection in use by another hstmt before it all goes wrong.

-- 
Nick Gorham
Easysoft Limited
http://www.easysoft.com, http://www.unixODBC.org