[sr-dev] PostgreSQL problems with Kamailio_3.0.2

Sun Jun 20 14:39:08 CEST 2010

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="116333 at 192.168.150.11">\\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'\\'.

Maybe this has some influence on the parser problem, too. Because in
this version of Postgresql the parameter "standard_conforming_strings"
is implicitly on -- just for previous versions it could be set to off.
That means, that any backslash symbol (\) is interpreted as standard
character (no escape). Therefore the queried result of the database does
no longer include \n and \t.

As interim "solution" of this problem I changed to the MySQL database
instead of PostgreSQL. The "Not NULL" violation is the same, but MySQL
seems to ignore this violation. Also the XML body is stored in MySQL "as
wished" -- that means: all special characters are stored and the queried
body still contains it.

The modules "pua" and "pua_usrloc" are used for testing purposes only,
because the user agents send publish messages themselves. Therefore it
is not necessary using this module. But for some regression tests I used
a command line base user agent that does not support publish messages.
But the problem is the same -- independent from the user agent and where
the publish messages is generated.

 Additionally I have attached a ZIP file that contains traces of the SIP
traffic to/from Kamailio and Kamailio internally (Publish) and two
excerpts of Kamailio syslog. The syslog excerpts are from two register /
de-register sequences, where the first sequence was okay and the second
one generated the parsing error. I haven't found any essential
difference that would clarify the different behaviour of Kamailio.

Please give me some comments to these problems ;-) I know, PostgreSQL is
only "second quality" for Kamailio, but it has some advantages against
MySQL, too.

Thanks in advance and regards,

Klaus Feichtinger

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-dev/attachments/20100620/a36187be/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Kam302_PresenceError_Usrloc.zip
Type: application/x-zip-compressed
Size: 19276 bytes
Desc: not available
URL: <http://lists.sip-router.org/pipermail/sr-dev/attachments/20100620/a36187be/attachment-0001.bin>