stephen.hindmarch at bt.com
2009-Sep-23 10:49 UTC
[asterisk-users] 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
Tilghman Lesher
2009-Sep-23 16:14 UTC
[asterisk-users] Error When Using Postgresql Schema With Realtime Sip
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
stephen.hindmarch at bt.com
2009-Sep-24 10:06 UTC
[asterisk-users] Error When Using Postgresql Schema With Realtime Sip
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. Pleasereport this>> issue on the bugtracker (https://issues.asterisk.org) and I'll get apatch 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
Tilghman Lesher
2009-Sep-24 14:10 UTC
[asterisk-users] Error When Using Postgresql Schema With Realtime 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
stephen.hindmarch at bt.com
2009-Sep-25 09:47 UTC
[asterisk-users] Error When Using Postgresql Schema WithRealtime Sip
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 istracked> > 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