asterisk users - Sep 2009 - Error When Using Postgresql Schema With Realtime Sip

I am using asterisk 1.6.1.6 and have been setting up a system to use a
Postgresql database as the realtime DB via the ODBC route. I have got
extensions and voicemail working but am having trouble with SIP

The problem seems to be with using a schema. If I put the table "sip"
in
the schema "foo" then I add this entry to extconfig.conf

sippeers => odbc,psqldb,foo.sip

Restart everything and then try to register a client. The registration
fails and I get this set of messages in the log

[2009-09-23 11:10:57.3q] DEBUG[10431] chan_sip.c: -REALTIME- loading
peer from database to memory. Name: stone. Peer objects: 8
[2009-09-23 11:10:57.3q] VERBOSE[10431] chan_sip.c:     -- Registered
SIP 'stone' at 10.215.42.138 port 5060
[2009-09-23 11:10:57.3q] VERBOSE[10431] chan_sip.c:        > Saved
useragent "ipDialog SipTone 1.2.0 rc Z_21 UA" for peer stone
[2009-09-23 11:10:57.3q] WARNING[10431] res_config_odbc.c: Key field
'ipaddr' does not exist in table 'foo.sip at asterisk'.  Update
will fail
[2009-09-23 11:10:57.3q] DEBUG[10431] res_config_odbc.c: Skip: 62; SQL:
UPDATE public.sip SET ipaddr=? WHERE name=?
[2009-09-23 11:10:57.3q] DEBUG[10431] res_config_odbc.c: Parameter 1
('ipaddr') = '10.215.42.138'
[2009-09-23 11:10:57.3q] DEBUG[10431] res_config_odbc.c: Skipping field
'port'='5060' (2/76)
[2009-09-23 11:10:57.3q] DEBUG[10431] res_config_odbc.c: Skipping field
'regseconds'='1253704257' (4/76)
[2009-09-23 11:10:57.3q] DEBUG[10431] res_config_odbc.c: Skipping field
'useragent'='ipDialog SipTone 1.2.0 rc Z_21 UA' (10/76)
[2009-09-23 11:10:57.3q] DEBUG[10431] res_config_odbc.c: Skipping field
'lastms'='0' (20/76)
[2009-09-23 11:10:57.3q] DEBUG[10431] res_config_odbc.c: Skipping field
'defaultuser'='stone' (40/76)

I now drop the table and recreate it in the public schema. I change
extconfig to 

sippeers => odbc,psqldb,public.sip

Restart and repeat with the same result.

The public schema does not need to be explicitly named so now I edit
extconfig to say

sippeers => odbc,mydb,sip

Restart and repeat, but this time the client is able to register and I
am able the set up calls to it.

So the only thing that has changed is the pointer in extconfig to the
database name. The fact that it works in the last instance proves that
my database structure is correct and the correct grants are used. The
fact that it failed for "public.sip" but worked for "sip"
shows it is
nothing about the permissions of the schema itself. I can double check
this by running queries through the ODBC driver myself by using the isql
application. Selects on "sip", "public.sip" and
"foo.sip" all ran
correctly and returned the same results.

So it seems to be something to do with having the schema name in the
table name. But as I say I have already got extensions and voicemail
working, and they both uses schemas, so it seems to be peculiar to SIP.

Does anybody have any ideas about what it might be?

Steve Hindmarch
BT Design

On Wednesday 23 September 2009 05:49:54 stephen.hindmarch at bt.com
wrote:
> I am using asterisk 1.6.1.6 and have been setting up a system to use a
> Postgresql database as the realtime DB via the ODBC route. I have got
> extensions and voicemail working but am having trouble with SIP
>
> The problem seems to be with using a schema. If I put the table
"sip" in
> the schema "foo" then I add this entry to extconfig.conf
>
> sippeers => odbc,psqldb,foo.sip
>
> Restart everything and then try to register a client. The registration
> fails and I get this set of messages in the log
<snip>
> So it seems to be something to do with having the schema name in the
> table name. But as I say I have already got extensions and voicemail
> working, and they both uses schemas, so it seems to be peculiar to SIP.
>
> Does anybody have any ideas about what it might be?
Yep, I never bothered to include support for specifying either the catalog or
the schema, since I've never had reason to use either one.  Please report
this
issue on the bugtracker (https://issues.asterisk.org) and I'll get a patch
up
straightaway, but I'll need your testing to ensure the patch works.

-- 
Tilghman Lesher
Digium, Inc. | Senior Software Developer
twitter: Corydon76 | IRC: Corydon76-dig (Freenode)
Check us out at: www.digium.com & www.asterisk.org

I have investigated further and found that it is a bug in ODBC, not
Asterisk. The SQLColumns function, which asterisk uses to describe the
table, does not return any columns when the table name includes the
schema specification. You can show this by using isql to do "help
table"
which returns info about all the columns, and then "help public.table"
which returns nothing. As chan_sip seems to be the only application that
tests the structure of the table before writing to it this is why
REGISTER fails.

When I have time I will chase up ODBC and see if the issue is tracked
there. Do you still want me to raise it as an issue on bugtracker?

The problem manifests itself in res_odbc.c inside the
ast_odbc_find_table function, around line 176 in my copy of the code.
>Tilghman Lesher wrote:
>> Yep, I never bothered to include support for specifying either the
>catalog or
>> the schema, since I've never had reason to use either one.  Please
report this
>> issue on the bugtracker (https://issues.asterisk.org) and I'll get
a
patch up
>> straightaway, but I'll need your testing to ensure the patch works.
>
>++  But I won't be able to test for awhile.
>
>Stephen.  As a test/work-around/option you could try setting the 
>search_path for the user connecting to the database.
>
>This has worked for me with RT and LedgerSMB.
>
Steve Hindmarch
BT Design

On Thursday 24 September 2009 05:06:02 stephen.hindmarch at bt.com
wrote:
> I have investigated further and found that it is a bug in ODBC, not
> Asterisk. The SQLColumns function, which asterisk uses to describe the
> table, does not return any columns when the table name includes the
> schema specification. You can show this by using isql to do "help
table"
> which returns info about all the columns, and then "help
public.table"
> which returns nothing. As chan_sip seems to be the only application that
> tests the structure of the table before writing to it this is why
> REGISTER fails.
>
> When I have time I will chase up ODBC and see if the issue is tracked
> there. Do you still want me to raise it as an issue on bugtracker?
Yes, I want you to raise this on the bugtracker, and no, this is not a bug
in ODBC, but a deficiency in my code.  Since you tracked this down to the
code in res_odbc.c, I might as well tell you that the first two NULL sets of
arguments (NULL, 0) are for specifying the catalog and schema, respectively,
of the database table, and it is because I never bothered parsing the schema
out of the tablename that this does not work.

-- 
Tilghman Lesher
Digium, Inc. | Senior Software Developer
twitter: Corydon76 | IRC: Corydon76-dig (Freenode)
Check us out at: www.digium.com & www.asterisk.org

OK, have done. Issue ID 0015963.

Steve Hindmarch
BT Design 

> -----Original Message-----
> From: asterisk-users-bounces at lists.digium.com [mailto:asterisk-users-
> bounces at lists.digium.com] On Behalf Of Tilghman Lesher
> Sent: 24 September 2009 15:11
> To: Asterisk Users Mailing List - Non-Commercial Discussion
> Subject: Re: [asterisk-users] Error When Using Postgresql Schema
> WithRealtime Sip
> 
> On Thursday 24 September 2009 05:06:02 stephen.hindmarch at bt.com wrote:
> > I have investigated further and found that it is a bug in ODBC, not
> > Asterisk. The SQLColumns function, which asterisk uses to describe
> the
> > table, does not return any columns when the table name includes the
> > schema specification. You can show this by using isql to do "help
> table"
> > which returns info about all the columns, and then "help
> public.table"
> > which returns nothing. As chan_sip seems to be the only application
> that
> > tests the structure of the table before writing to it this is why
> > REGISTER fails.
> >
> > When I have time I will chase up ODBC and see if the issue is
tracked
> > there. Do you still want me to raise it as an issue on bugtracker?
> 
> Yes, I want you to raise this on the bugtracker, and no, this is not a
> bug
> in ODBC, but a deficiency in my code.  Since you tracked this down to
> the
> code in res_odbc.c, I might as well tell you that the first two NULL
> sets of
> arguments (NULL, 0) are for specifying the catalog and schema,
> respectively,
> of the database table, and it is because I never bothered parsing the
> schema
> out of the tablename that this does not work.
> 
> --
> Tilghman Lesher
> Digium, Inc. | Senior Software Developer
> twitter: Corydon76 | IRC: Corydon76-dig (Freenode)
> Check us out at: www.digium.com & www.asterisk.org
> 
> _______________________________________________
> -- Bandwidth and Colocation Provided by http://www.api-digital.com --
> 
> AstriCon 2009 - October 13 - 15 Phoenix, Arizona
> Register Now: http://www.astricon.net
> 
> asterisk-users mailing list
> To UNSUBSCRIBE or update options visit:
>    http://lists.digium.com/mailman/listinfo/asterisk-users