Re: [sr-dev] PostgreSQL problems with Kamailio_3.0.2

Hello, Currently I have big troubles in the combination of PRESENCE / PRESENCE_XML (/ PUA / PUA_USRLOC) with POSTGRESQL database. During last days I’ve analyzed the output of Kamailio 3.0.2 and PostgreSQL (8.3) database, running on Debian Lenny OS. Following items were found: 1) The default settings of 4 PGSQL tables after initializing the database with “kamdbctl init” are not useful; the tables “PRESENTITY”, “PUA”, “ACC” and “MISSED_CALLS” have wrong settings for “Not NULL” characteristics of some columns. In detail following columns had to be adapted manually in the database: “acc” and “missed_calls” table : column “id” must allow “NULL” (remove “Not Null” setting) “presentity” table: the column “sender” must allow “NULL” (remove “Not Null” setting) “pua” table: the columns “extra_headers”, “version”, “remote_contact”, “contact” and “desired_expires” must allow “NULL” (remove “Not Null” setting) E.g. 883:4451: 0(7123) ERROR: db_postgres [km_dbase.c:428]: driver error: PGRES_FATAL_ERROR, ERROR: null value in column "sender" violates not-null constraint 1008:5057: 1(7134) ERROR: db_postgres [km_dbase.c:428]: driver error: PGRES_FATAL_ERROR, ERROR: null value in column "extra_headers" violates not-null constraint 1025:5078: 1(7134) ERROR: db_postgres [km_dbase.c:428]: driver error: PGRES_FATAL_ERROR, ERROR: null value in column "version" violates not-null constraint I recommend adapting the script “utils/kamctl/postgres/presence-create.sql”!

2) I do not know if this has a direct influence on the problems I have with presence, but the column “sender” in the table “presentity” seems to be used only “half”. When the pua_usrloc module is inserting an entry into the table it does NOT insert a value for the column “sender”. However, when a query is sent for selecting information from this table, the column “sender” is explicitly requested……

e.g. INSERTION (no “sender” value is inserted): Jun 18 20:15:01 TestKam /usr/sbin/kamailio[3151]: DEBUG: db_postgres [km_dbase.c:149]: 0x826ba68 PQsendQuery(insert into presentity (domain,username,event,etag,expires,body,received_time ) values ('192.168.150.11','116333','presence','a.1276884785.3151.1.0',1276885262,'<?xml version="1.0"?>\\012<presence xmlns="urn:ietf:params:xml:ns:pidf" xmlns:dm="urn:ietf:params:xml:ns:pidf:data-model" xmlns:rpid="urn:ietf:params:xml:ns:pidf:rpid" xmlns:c="urn:ietf:params:xml:ns:pidf:cipid" entity=&quot;116333(a)192.168.150.11&quot;&gt;\\012 <tuple id="0x828d7d8">\\012 <status>\\012 <basic>open</basic>\\012 </status>\\012 </tuple>\\012</presence>\\012',1276884901) <file:///%5C%5C012%3c%5Cpresence%3e%5C012%27,1276884901%29>) SELECTION (a “sender” value is explicitly queried): Jun 18 20:15:08 TestKam /usr/sbin/kamailio[3151]: DEBUG: db_postgres [km_dbase.c:149]: 0x826ba68 PQsendQuery(select body,sender from presentity where domain='192.168.150.11' AND username='116333' AND event='presence' AND etag='a.1276884785.3151.1.0') What does the column “sender” represent? In the presence description on the Kamailio homepage (version 1.5) this column still is not included. 3) The next problem I have is, that the PIDF-body, which is stored in the PGSQL database, seems to cause an error in the presence_xml module and therefore no body is attached to the NOTIFY message. The NOTIFY message contains a SIP header “Content-Type: application/pidf+xml”, but no PIDF-body is sent in this message. As result of this SIP request the SIP user agent (= subscriber) is a little bit confused….. I think that problem in general has something to do with the “error” described in the new task from Friday June 18^th (http://lists.sip-router.org/pipermail/sr-dev/2010-June/007865.html). First I wondered, why this problem only occurred in case that a (subscribed) user agent de-registers from Kamailio registrar server. But I guess the NOTIFY message after registration of the user agent is created without dependency on a PGSQL query (= generated with information from memory). Another behaviour of the server was, that (after emptying all related tables) the first registration / de-registration flow didn’t cause any error (both NOTIFY messages were readable and contained a PIDF-body); only beginning at the second flow the body could not be parsed. This was tested with SIPp sending register/de-register messages in a period of 3 seconds. The Kamailio error message looks like: Jun 18 13:08:16 TestKam /usr/sbin/kamailio[3167]: ERROR: presence_xml [notify_body.c:515]: while parsing xml body message Jun 18 13:08:16 TestKam /usr/sbin/kamailio[3167]: ERROR: presence_xml [notify_body.c:84]: while aggregating body

4) I don’t know if the parser might be influenced by a WARNING that is generated by the postgresql daemon whenever an entry into the presentity table is done (including XML body). From Kamailio log output I saw that the special characters “#011” and “#012” are included in the XML body. I guess that is the octal notation of \t (horizontal tab) and \n (newline). However, postgresql generates an error message that looks like following: /WARNING: nonstandard use of \\ in a string literal at character 162/ HINT: Use the escape string syntax for backslashes, e.g., E'\\'.