[Home]

Summary:ASTERISK-05608: [post 1.2] Realtime fails using unixODBC and FreeTDS against MSSQL
Reporter:Marius Traelnes (traelnes)Labels:
Date Opened:2005-11-15 05:42:23.000-0600Date Closed:2006-04-18 23:45:02
Priority:MajorRegression?No
Status:Closed/CompleteComponents:Core/General
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:( 0) connection_pooling.diff
( 1) unixODBC_log.txt
Description:I get an error "...[FreeTDS][SQL Server]Invalid cursor state ...." when trying to look up a local sip user.

We have setup unixODBC using FreeTDS against MSSQL and other things works ok. Like registering clients, writing CDR (using same DNS) etc.

I posted this problem to the FreeTDS mailing list and got this answer regarding the "invalid cursor state" error message:
"This can be caused by application trying do use 2 active statement on same connection (like a select and an update together) or if application do not close correctly statement (not getting all data)."

If I look in the log it looks  like this happens. There are 2 equal sql statements prepared/executed and then it fails. Looking at other operations including sql statements it is never 2 equal statments following each other.

Also the FreeTDS documentations states:
"Is FreeTDS thread safe?
Different threads may all use separate connections without interfering with each other. Threads may not share a DBPROCESS or CS_CONNECTION without controlling access via a mutex."

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

To summarize my configurations that leads up to this failure:

1. Dialing number 201 from 202 will execute this part in extensions.conf:
[local]
exten => _2XX,1,Dial(SIP/${EXTEN})

2. I guess asterisk then, since this is configured to use realtime, look up this number form our sip_buddies table (we have named it RealTimeSipBuddies).  I think here is where the error happens: asterisk try to prepare/execute statement multiple times on same connection which is not allowed according to FreeTDS.
This is executed 2 times according to unixODBC log:
[ODBC][10551][SQLPrepare.c][180]
               Entry:
                       Statement = 0x81328f0
                       SQL = [SELECT * FROM RealTimeSipBuddies WHERE name = ?][length = 47 (SQL_NTS)]

3. Asterisk returns an error message and the operation is cancelled.

Here is log details given to asterisk console:
Nov 15 12:26:33 WARNING[19734]: res_odbc.c:171 odbc_smart_execute: SQL Execute returned an error -1: 24000: [FreeTDS][SQL Server]Invalid cursor state (41)
Nov 15 12:26:33 WARNING[19734]: res_odbc.c:171 odbc_smart_execute: SQL Execute returned an error -1: 00000: [FreeTDS][SQL Server]Invalid cursor state (41)
Nov 15 12:26:33 WARNING[19734]: res_config_odbc.c:124 realtime_odbc: SQL Execute error!
[SELECT * FROM RealTimeSipBuddies WHERE name = ?]


Here is log which gives error from unixODBC:

[ODBC][10551][SQLExecute.c][354]
               Exit:[SQL_ERROR]
               DIAG [24000] [FreeTDS][SQL Server]Invalid cursor state

4. After this I will get several other error messages when trying to do other things that executes realtime statements. Like closing client, and try to register again then login fails. This only happens after the "Invalid cursor state" happens. Before that registering clients is no problem.

Look in attached unixODBC log and see that there are 2 sql statements and later followed by SQL_ERROR.
Comments:By: Olle Johansson (oej) 2005-11-15 08:16:09.000-0600

As I understand it Asterisk interfaces to UnixODBC. So another question is if UnixODBC is thread safe? The threadsafeness of FreeTDS seems to me a UnixODBC problem to handle, if we are not accessing FreeTDS directly.

(BTW, thank you for a detailed report!)
By: Marius Traelnes (traelnes) 2005-11-15 11:19:00.000-0600

I asked this question to the FreeTDS people:

"If it is so that there are 2 active statements on same connection would the problem then be in unixODBC (it doesn't handle threads?) or in the client application? (which in this case is Asterisk....)"

The answer was:

"The client application.  It's not a matter of threading; it's a matter of the design of the TDS protocol and the of the server.  Cf. http://www.freetds.org/faq.html#pending.

I think for the moment you're stuck.  :-("

So then my question is: If this is for a fact should we then conclude with that Asterisk Realtime Architecture does not support FreeTDS at the moment? And would that be defined as a bug that has a chance to be fixed for the final 1.2 release or would it be an improvement (to support FreeTDS)?
By: Olle Johansson (oej) 2005-11-15 11:44:05.000-0600

Bugs will continue to be fixed in the 1.2 tree, so if it's a bug, it will be fixed at some point.

I am in no way a UnixODBC/FreeTDS guru, so I'll let those questions pass to someone else. To me it seems strange that we should be seen as the FreeTDS client since we're interfacing to an ODBC layer that's supposed to hide DB specific issues. But that's just a generic opinion with no insight ;-)
By: Marius Traelnes (traelnes) 2005-11-15 14:08:53.000-0600

I also posted a question to the unixODBC mail list and here is that answer:

"Its a driver thing. The TDS protocol only allows one current active statement. Sounds like the app is trying to use two. Its not down to the driver manager to manage this, and its unlikly to be related to threads."
By: Olle Johansson (oej) 2005-11-15 14:12:20.000-0600

That seems to clear this up, we need to control the number of simultaneous connections to unixODBC. Does this only apply to FreeTDS or is it generic for all database connections in unixODBC (Might be a stupid question, but we might as well try to check this up once and for all).

Thanks for all your research.
By: Marius Traelnes (traelnes) 2005-11-16 02:29:21.000-0600

Tomorrow I will change to MySql and then of course change the unixODBC from using FreeTDS to MySql driver. Then we will see if the problem persists.
By: Tilghman Lesher (tilghman) 2005-11-16 10:34:31.000-0600

I'm afraid this is too late for the 1.2 series.  The solution suggested by the FreeTDS people -- connection pooling -- is not difficult to accomplish, and we can certainly do that in future.  However, it's going to require significant changes to the core res_odbc architecture.  The architecture of 1.2 is currently feature-frozen.  Any changes that will require a change to architecture will have to wait until 1.2 is branched, and then it will be in CVS HEAD until 1.4 is released (which is probably at least a year down the road).

I'm going to mark this change as post-1.2.
By: Marius Traelnes (traelnes) 2005-11-17 13:13:06.000-0600

I can confirm that when I still use unixODBC and change from using FreeTDS to using MyODBC driver the problem dissappers. So it seems that the problem only appears when using FreeTDS.
By: Marius Traelnes (traelnes) 2005-12-23 08:14:34.000-0600

Corydon76: Thank you for LOWERING my Karma when I actually gave an extensive bug report (spent around 15-20 hours testing) and additionally it was also wrong because Asterisk DID crash because of this problem. That is how to encourage people to work: keep 'em down!! ;-)

I saw you have been assigned to this. Feel free to ask for help regarding this matter. I am available.
By: Tilghman Lesher (tilghman) 2005-12-23 08:21:13.000-0600

If Asterisk crashed, where's your stack backtrace?

I know how to solve this; I'm working on major updates to res_odbc to allow connection pooling.
By: Marius Traelnes (traelnes) 2005-12-23 08:30:06.000-0600

I don't have it. The thing with the crash (and the reason I didn't emphasize it) was it happened only 2-3 times in the beginning. After that I could not reproduce it.

Great that you can solve it.
By: Vadim Berezniker (kryptolus) 2006-01-10 07:01:58.000-0600

I'm attaching the connection pooling code that I wrote for my employer.
Going by the comments, I'm not expecting this patch to be merged.
I just want to put it up so that any people who want to use asterisk with MSSQL can get this patch. I've been running it on three machines for a couple of days without any problems. It got rid of the frequent SQL errors as expected.
By default, it works exactly the same as without the patch. To enable it you have to add
pooled => yes
poolsize => 10 (or whatever)
to your res_odc.conf
By: Tilghman Lesher (tilghman) 2006-04-03 12:39:30

Please test http://svn.digium.com/svn/asterisk/team/tilghman/res_odbc_rewrite/
By: Juan Pedro Escalona Rueda (otioti) 2006-04-17 06:39:02

Hello

I have a problem with your Subversion. I try to copy from http://svn.digium.com/svn/asterisk/team/tilghman/res_odbc_rewrite/ and I try the command:  

# svn co http://svn.digium.com/svn/asterisk/team/tilghman/res_odbc_rewrite .

That responded:

svn: REPORT request failed on '/svn/asterisk/!svn/vcc/default'
svn: REPORT of '/svn/asterisk/!svn/vcc/default': 400 Bad Request (http://svn.digium.com)

I don´t understand why causes this, but maybe about a bad link (I think…).

Thank you very much!
See you soon!
By: Tilghman Lesher (tilghman) 2006-04-17 12:36:13

If you're behind a proxy, you're going to have to either disable it or enable the additional methods needed to use SVN.
By: Tilghman Lesher (tilghman) 2006-04-18 23:45:02

Branch has been merged to trunk.