<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type"
content="text/html; charset=ISO-8859-1">
<meta name="Generator" content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
span.EmailStyle18
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.Section1
{page:Section1;}
/* List Definitions */
@list l0
{mso-list-id:1666781020;
mso-list-type:hybrid;
mso-list-template-ids:-1618968420 134807569 134807577 134807579 134807567 134807577 134807579 134807567 134807577 134807579;}
@list l0:level1
{mso-level-text:"%1\)";
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level2
{mso-level-tab-stop:72.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level3
{mso-level-tab-stop:108.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level4
{mso-level-tab-stop:144.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level5
{mso-level-tab-stop:180.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level6
{mso-level-tab-stop:216.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level7
{mso-level-tab-stop:252.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level8
{mso-level-tab-stop:288.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level9
{mso-level-tab-stop:324.0pt;
mso-level-number-position:left;
text-indent:-18.0pt;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body bgcolor="#ffffff" text="#000000">
Hello,<o:p></o:p>
<div class="Section1">
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoListParagraph" style="text-indent: -18pt;"><!--[if !supportLists]--><span
style="">1)<span
style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal; -x-system-font: none;">
</span></span><!--[endif]-->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:<o:p></o:p></p>
<p class="MsoListParagraph">“acc” and “missed_calls”
table : column “id” must allow “NULL” (remove
“Not Null” setting)<o:p></o:p></p>
<p class="MsoListParagraph">“presentity” table: the column
“sender” must allow “NULL” (remove “Not
Null” setting)<o:p></o:p></p>
<p class="MsoListParagraph">“pua” table: the columns
“extra_headers”, “version”,
“remote_contact”, “contact” and
“desired_expires” must allow “NULL” (remove “Not
Null” setting)<o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoListParagraph">E.g.<o:p></o:p></p>
<p class="MsoListParagraph">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<o:p></o:p></p>
<p class="MsoListParagraph">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<o:p></o:p></p>
<p class="MsoListParagraph">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<o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoListParagraph">I recommend adapting the script
“utils/kamctl/postgres/presence-create.sql”!<o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoListParagraph" style="text-indent: -18pt;"><!--[if !supportLists]--><span
style="">2)<span
style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal; -x-system-font: none;">
</span></span><!--[endif]-->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……<o:p></o:p></p>
<p class="MsoListParagraph">e.g.<o:p></o:p></p>
<p class="MsoListParagraph">INSERTION (no “sender” value is inserted):<o:p></o:p></p>
<p class="MsoListParagraph">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=<a class="moz-txt-link-rfc2396E" href="mailto:116333@192.168.150.11">"116333@192.168.150.11"</a>>\\012 <tuple
id="0x828d7d8">\\012
<status>\\012
<basic>open</basic>\\012
</status>\\012 </tuple><a moz-do-not-send="true"
href="file:///%5C%5C012%3c%5Cpresence%3e%5C012%27,1276884901%29">\\012</presence>\\012',1276884901)</a>)<o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoListParagraph">SELECTION (a “sender” value is explicitly
queried):<o:p></o:p></p>
<p class="MsoListParagraph">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')<o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoListParagraph">What does the column “sender” represent?
In the presence description on the Kamailio homepage (version 1.5) this
column
still is not included.<o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoListParagraph" style="text-indent: -18pt;"><!--[if !supportLists]--><span
style="">3)<span
style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal; -x-system-font: none;">
</span></span><!--[endif]-->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<sup>th</sup> (<a
moz-do-not-send="true"
href="http://lists.sip-router.org/pipermail/sr-dev/2010-June/007865.html">http://lists.sip-router.org/pipermail/sr-dev/2010-June/007865.html</a>).
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.<o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoListParagraph">The Kamailio error message looks like:<o:p></o:p></p>
<p class="MsoListParagraph">Jun 18 13:08:16 TestKam
/usr/sbin/kamailio[3167]:
ERROR: presence_xml [notify_body.c:515]: while parsing xml body message<o:p></o:p></p>
<p class="MsoListParagraph">Jun 18 13:08:16 TestKam
/usr/sbin/kamailio[3167]:
ERROR: presence_xml [notify_body.c:84]: while aggregating body<o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoListParagraph" style="text-indent: -18pt;"><!--[if !supportLists]--><span
style="">4)<span
style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal; -x-system-font: none;">
</span></span><!--[endif]-->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). <o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoListParagraph">However, postgresql generates an error
message that
looks like following:<o:p></o:p></p>
<p class="MsoListParagraph"><em><span
style="font-family: "Calibri","sans-serif";">WARNING:
nonstandard use of \\ in a string literal at character 162<o:p></o:p></span></em></p>
<p class="MsoListParagraph">HINT: Use the escape string syntax for
backslashes, e.g., E'\\'.<o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoListParagraph">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.<o:p></o:p></p>
<p class="MsoListParagraph"><o:p> </o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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. <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"> 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.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks in advance and regards,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Klaus Feichtinger<o:p></o:p></p>
</div>
</body>
</html>