[Home]

Summary:ASTERISK-03697: Realtime Application : Probleme and error message.
Reporter:Interplex Telecom Inc. (interplex2)Labels:
Date Opened:2005-03-17 16:30:17.000-0600Date Closed:2008-01-15 15:32:02.000-0600
Priority:MajorRegression?No
Status:Closed/CompleteComponents:Applications/app_queue
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:
Description:I run asterisk on Debian Linux with Krenel 2.4.29 .
I use Odbc(Unix-Odbc 2.2.4) for the Realtime module of Asterisk to have acess to a PostgreSQL database (version 8.01).
My Asterisk version : Asterisk CVS-HEAD-03/17/05-13:49:21
My cdr work well with Odbc.



I use Realtime module only for sipfriends (sipusers, sippeers) for now.

I have this error message by Asterisk each time a sip user attempts to log in on Asterisk :

Mar 17 12:40:53 WARNING[5613]: SQL Get Data error!
[SELECT * FROM sip_buddies WHERE name = ?]



I have made the source skipping the error message.  All works well for the moment.

For helping to debugging, i give the cvs's diff of my source and the source of Asterisk.

File : asterisk/res/res_config_odbc.c
140c140
<                       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
---
>                       if (0 && (res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {


With this change all sip users can have acces to Asterisk.



To avoid all useless questions, i place here the SQL requests used to create my sip_buddies table.

CREATE TABLE sip_buddies
(
 id int4 NOT NULL DEFAULT nextval('public.sip_buddies_id_seq1'::text),
 name varchar(80) NOT NULL DEFAULT ''::character varying,
 accountcode varchar(20),
 amaflags varchar(7),
 callgroup varchar(10),
 callerid varchar(80),
 canreinvite char(3) DEFAULT 'yes'::bpchar,
 context varchar(80),
 defaultip varchar(15),
 dtmfmode varchar(7),
 fromuser varchar(80),
 fromdomain varchar(80),
 host varchar(31) NOT NULL DEFAULT 'dynamic'::character varying,
 insecure varchar(4),
 "language" char(2),
 mailbox varchar(50),
 md5secret varchar(80),
 nat int2 NOT NULL DEFAULT (1)::smallint,
 permit varchar(95),
 deny varchar(95),
 mask varchar(95),
 pickupgroup varchar(10),
 port varchar(5) NOT NULL DEFAULT ''::character varying,
 qualify char(3),
 restrictcid char(1),
 rtptimeout char(3),
 rtpholdtimeout char(3),
 secret varchar(80),
 "type" varchar(6) NOT NULL DEFAULT 'friend'::character varying,
 username varchar(80) NOT NULL DEFAULT ''::character varying,
 allow varchar(100) DEFAULT 'g729;ilbc;gsm;ulaw;alaw'::character varying,
 disallow varchar(100) DEFAULT 'all'::character varying,
 musiconhold varchar(100),
 regseconds int4 NOT NULL DEFAULT 0,
 ipaddr varchar(15) NOT NULL DEFAULT ''::character varying,
 cancallforward char(3) DEFAULT 'yes'::bpchar,
 CONSTRAINT primary_sip_buddies_id PRIMARY KEY (id)
)
WITHOUT OIDS;

Thanks for the work of Asterisk's team, and the help of all Net Surfers.
Comments:By: raiden (raiden) 2005-03-17 17:00:55.000-0600

I have a very similiar realtime problem with asterisk using the latest unixODBC (2.2.11) and PostgreSQL(8.0.1). My asterisk system and my Postgres database are on two different systems. My sip peers/users can register to my asterisk server with no problem and everything works fine. The problem is when I reboot my Postgres server. The database server takes up to two minutes to come back alive(just using two minutes as an example). During those two minutes, I have a few sip phones trying to register but fail.

Mar 17 16:57:05 WARNING[19548]: res_odbc.c:90 odbc_smart_execute: SQL Execute error! Attempting a reconnect...
Mar 17 16:57:05 WARNING[19548]: res_odbc.c:411 odbc_obj_disconnect: res_odbc: disconnected 0 from PostgreSQL-asterisk [PostgreSQL-asterisk]
Mar 17 16:57:05 NOTICE[19548]: res_odbc.c:468 odbc_obj_connect: Connecting PostgreSQL-asterisk
Mar 17 16:57:05 WARNING[19548]: res_odbc.c:479 odbc_obj_connect: res_odbc: Error SQLConnect=-1 errno=101 [unixODBC]Could not connect to the server;
Could not connect to remote socket.
Mar 17 16:57:05 WARNING[19548]: res_config_odbc.c:380 update_odbc: SQL Execute error!
[UPDATE sip SET ipaddr=?, port=?, regseconds=?, username=? WHERE name=?]

It seems that it only tries to reconnect once and if it doesn't reconnect during that first attempt, All requests will fail with "WARNING[19548]: es_config_odbc.c:380 update_odbc: SQL Execute error!
[UPDATE sip SET ipaddr=?, port=?, regseconds=?, username=? WHERE name=?]".

Is their no auto-reconnect every 60 seconds or something?

edited on: 03-17-05 17:02
By: Brian West (bkw918) 2005-03-17 17:03:20.000-0600

Not a bug in asterisk but the unixodbc/postgres driver.
/b
By: Interplex Telecom Inc. (interplex2) 2005-04-21 14:23:54

I have verified the code of asterisk/res/res_config_odbc.c file, and code of odbc and pgsql drivers.  And i have found the thing causing trouble.  In res/res_config_odbc.c, the line 139 is :
res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), NULL);

But in ODBC API Reference on MSDN where we found that for the 6th argument:
-----------------------------
StrLen_or_IndPtr [Output]
   Pointer to the buffer in which to return the length or indicator value. If this is a null pointer, no length or indicator value is returned. This returns an error when the data being fetched is NULL.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlgetdata.asp
-----------------------------
By: Interplex Telecom Inc. (interplex2) 2005-04-21 14:25:40

=That means, if StrLen_or_IndPtr == NULL , SQLGetData will return an error.
For fix the bug, we need to change NULL for a SQLINTEGER variable referenced.

For helping to debugging, i give the cvs's diff of the source of Asterisk and my source.

File : asterisk/res/res_config_odbc.c
//////////--------------------------------------
139c139,140
<                       res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), NULL);
---
>                       SQLINTEGER buffer_ptr;
>                       res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &buffer_ptr);
296c297,298
<                       res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), NULL);
---
>                         SQLINTEGER buffer_ptr;
>                       res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &buffer_ptr);
//////////////---------------------------
By: Kevin P. Fleming (kpfleming) 2005-04-21 21:09:45

That fix was not quite complete, but I have committed a proper fix to CVS. Thanks for the report and the debugging!
By: Kevin P. Fleming (kpfleming) 2005-04-21 21:10:00

Not for stable.
By: Digium Subversion (svnbot) 2008-01-15 15:32:02.000-0600

Repository: asterisk
Revision: 5493

U   trunk/res/res_config_odbc.c

------------------------------------------------------------------------
r5493 | kpfleming | 2008-01-15 15:32:01 -0600 (Tue, 15 Jan 2008) | 2 lines

ensure that res_config_odbc can handle columns with NULL values (bug ASTERISK-3697)

------------------------------------------------------------------------

http://svn.digium.com/view/asterisk?view=rev&revision=5493