20 Jun
2010
20 Jun
'10
3:39 p.m.
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(a)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
21 Jun
21 Jun
11:40 a.m.
Hi Klaus!
Am 20.06.2010 14:39, schrieb Klaus Feichtinger:
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”!
So, the bugs are also in the mysql table definitions, but mysql does not
care about "not null" contraints?
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……
"sender" sounds like it would be the From header. Probably code review
is needed to find out what it really is used for.
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(a)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
Have you tried the use the patch from the bugtracker? It might be the
cause of your problems.
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'\\'.
Can you test if this solves your problem? Just grep for the SQL queries
(SELECT, UPDATE, INSERT, DELETE) and replace 'strings' with E'strings'.
regards
Klaus
10:48 p.m.
Hello Klaus,
Am 21.06.2010 10:40, schrieb Klaus Darilion:
okay - this is not so important.
I just wondered why this column was
added to the database / table but no data are inserted (even when the
not_null rule is set).
This afternoon I've tried the patch from the bugtracker
and adapted the
"km_val.c" file. The result was, that the NOTIFY message was now ALWAYS
sent to the subscriber. However, I don't know why, but very often the
same presence_xml error messages as above were generated in syslog -
even when the NOTIFY messages were generated. The difference today was,
that the error messages were generated after registration and not after
de-registration as before....
That's what I've already tested before. The prefix
"E" in front of the
body string helped and therefore no warning message was generated by
postgresql.
Hi Klaus!
Am 20.06.2010 14:39, schrieb Klaus Feichtinger:
Correct, the same NOT_NULL settings are in MySQL tables, too. I tried a
manual example (copied and adapted for escape characters from the
original sql command in syslog debug output) as follows:
openser_test=# \d presentity
Table "public.presentity"
Column | Type | Modifiers
---------------+------------------------+---------------------------------------------------------
id | integer | not null default
nextval('presentity_id_seq'::regclass)
username | character varying(64) | not null
domain | character varying(64) | not null
event | character varying(64) | not null
etag | character varying(64) | not null
expires | integer | not null
received_time | integer | not null
body | bytea | not null
sender | character varying(128) | not null
Indexes:
"presentity_pkey" PRIMARY KEY, btree (id)
"presentity_presentity_idx" UNIQUE, btree (username, domain, event, etag)
openser_test=# insert into presentity
(domain,username,event,etag,expires,body,received_time ) values
('192.168.150.11','116333','presence','a.1276884785.3151.1.0',1276885262,E'<?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(a)192.168.150.11">\\012 <tuple
id="0x828d7d8">\\012
<status>\\012 <basic>open</basic>\\012 </status>\\012
</tuple>\\012</presence>\\012',1276884901);
ERROR: null value in column "sender" violates not-null constraint
openser_test=#
++++++++++++++++++++++++++++++++++++++++++
mysql> describe presentity;
+---------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(64) | NO | MUL | NULL | |
| domain | varchar(64) | NO | | NULL | |
| event | varchar(64) | NO | | NULL | |
| etag | varchar(64) | NO | | NULL | |
| expires | int(11) | NO | | NULL | |
| received_time | int(11) | NO | | NULL | |
| body | blob | NO | | NULL | |
| sender | varchar(128) | NO | | NULL | |
+---------------+------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
mysql> 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(a)192.168.150.11">\\012 <tuple
id="0x828d7d8">\\012
<status>\\012 <basic>open</basic>\\012 </status>\\012
</tuple>\\012</presence>\\012',1276884901);
Query OK, 1 row affected, 1 warning (0.07 sec)
As you can see PGSQL is detecting an error and does not insert this entry, but MySQL seems
to ignore it and is generating an internal warning message only (but this could be
influenced by the escape characters, too).
Note: the acc and missed_calls tables are okay; the not_null problems seem to occur in
presence related tables only.
[...]
I recommend adapting the script
“utils/kamctl/postgres/presence-create.sql”!
So, the bugs are also in the mysql table definitions, but mysql does
not care about "not null" contraints?
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……
"sender" sounds like it would be the From header. Probably code review
is needed to find out what it really is used for.
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(a)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
Have you tried the use the patch from the bugtracker? It might be the
cause of your problems. 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'\\'.
Can you test if this solves your problem? Just grep for the SQL
queries (SELECT, UPDATE, INSERT, DELETE) and replace 'strings' with
E'strings'.
regards
Klaus
regards
Klaus F.
22 Jun
22 Jun
9:25 a.m.
Am 21.06.2010 21:48, schrieb Klaus Feichtinger:
Hello Klaus,
Do you know if the E'' syntax is supported in old versions too? (we
should still support older psql versions too)
klaus
Can you test if this solves your problem? Just
grep for the SQL
queries (SELECT, UPDATE, INSERT, DELETE) and replace 'strings' with
E'strings'.
That's what I've already tested before. The prefix
"E" in front of the
body string helped and therefore no warning message was generated by
postgresql.
23 Jun
23 Jun
1:32 p.m.
I have searched in the internet and found that this escape syntax is supported since
version 8.1 (released 2005-11-08).
The postgres documentation of version 8.0 describes another syntax and therefore I guess
that it was not supported.
Conclusion:
escape syntax E'' requires postgresql version >=8.1
regards
Klaus
Am 21.06.2010 21:48, schrieb Klaus Feichtinger:
--
GMX DSL: Internet-, Telefon- und Handy-Flat ab 19,99 EUR/mtl.
Bis zu 150 EUR Startguthaben inklusive! http://portal.gmx.net/de/go/dsl
Hello Klaus,
Do you know if the E'' syntax is supported in old versions too? (we
should still support older psql versions too)
klaus Can you test if this solves your problem? Just
grep for the SQL
queries (SELECT, UPDATE, INSERT, DELETE) and replace 'strings' with
E'strings'.
That's what I've already tested before. The prefix
"E" in front of the
body string helped and therefore no warning message was generated by
postgresql.
7:06 p.m.
Am 23.06.2010 12:32, schrieb Klaus Feichtinger:
I have searched in the internet and found that this
escape syntax is supported since version 8.1 (released 2005-11-08).
The postgres documentation of version 8.0 describes another syntax and therefore I guess
that it was not supported.
Conclusion:
escape syntax E'' requires postgresql version>=8.1
Debian etch (oldstable) uses 7.5.22
Debian lenny (stable) uses 8.3.11-0lenny1
So this would require that users still running Etch would need to update
postgresql too if they want to update to a newer Kamailio version. For
me that would be ok - what do others think?
regards
klaus
regards
Klaus
Am 21.06.2010 21:48, schrieb Klaus Feichtinger:
Hello Klaus,
Do you know if the E'' syntax is supported in old versions too? (we
should still support older psql versions too)
klaus Can you test if this solves your problem? Just
grep for the SQL
queries (SELECT, UPDATE, INSERT, DELETE) and replace 'strings' with
E'strings'.
That's what I've already tested before. The prefix
"E" in front of the
body string helped and therefore no warning message was generated by
postgresql. 
24 Jun
24 Jun
7:52 p.m.
On Wednesday 23 June 2010, Klaus Darilion wrote:
Hi Klaus,
an alternative to this would be to specifiy it in the configuration, i think.
But as Debian Etch is now really out of maintenance (no security updates since
march), i think it should be also ok to change it in the code, especially in
the 3.0 branch.
Cheers,
Henning
Conclusion:
escape syntax E'' requires postgresql version>=8.1
Debian etch (oldstable) uses 7.5.22
Debian lenny (stable) uses 8.3.11-0lenny1
So this would require that users still running Etch would need to update
postgresql too if they want to update to a newer Kamailio version. For
me that would be ok - what do others think?
23 Jun
23 Jun
7:05 p.m.
Am 20.06.2010 14:39, schrieb Klaus Feichtinger:
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.
I just wonder if this can be solved by storing the body as BLOB instead
of strings. Could you test using a BLOB for the body (in database and
presence modules)
regards
klaus
11:02 p.m.
According information I found in the PostgreSQL manual, the data type
"BYTEA" is already representing a postgres-like binary data format.
Excerpt of the documentation
(http://www.postgresql.org/docs/8.3/static/datatype-binary.html):
"The bytea data type allows storage of binary strings. [...] The SQL
standard defines a different binary string type, called BLOB or BINARY
LARGE OBJECT. The input format is different from bytea, but the provided
functions and operators are mostly the same."
"Real" BLOB (according SQL definition) is therefore not supported by
postgres. This page (documentation of binary data types) includes
examples of literal escaped octets, too. Maybe is is helpful for developers.
Klaus
Am 23.06.2010 18:05, schrieb Klaus Darilion:
Am 20.06.2010 14:39, schrieb Klaus Feichtinger:
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.
I just wonder if this can be solved by storing the body as BLOB
instead of strings. Could you test using a BLOB for the body (in
database and presence modules)
regards
klaus
24 Jun
24 Jun
12:18 p.m.
Am 23.06.2010 22:02, schrieb Klaus Feichtinger:
According information I found in the PostgreSQL
manual, the data type
"BYTEA" is already representing a postgres-like binary data format.
Excerpt of the documentation
(http://www.postgresql.org/docs/8.3/static/datatype-binary.html):
"The bytea data type allows storage of binary strings. [...] The SQL
standard defines a different binary string type, called BLOB or BINARY
LARGE OBJECT. The input format is different from bytea, but the provided
functions and operators are mostly the same."
"Real" BLOB (according SQL definition) is therefore not supported by
postgres. This page (documentation of binary data types) includes
examples of literal escaped octets, too. Maybe is is helpful for developers.
That sounds more complex and we still have to use the E'' prefix. I
think it would be easier to just add the E'' prefix to the current code.
But now I wonder what data type is used currently - the recent bug
report on the bug-tracker mentions problems with BLOB, which would
indicate that the body is already saved as BLOB.
IIRC you said you already tried adding the E'' prefixes to the
db_postgresql code. Does this, together with the \0 patch from the
bug-tracker solve your problems?
regards
klaus
regards
klaus
9:59 p.m.
Yes - from (subscriber) user agents point of view it "solves" the original
problem. Now each Notify message (except the Subscribe confirmance notifications) include
PIDF bodies.
But as written on Monday, kamailio still produces the internal error messages that look
the same as before:
ERROR: presence_xml > [notify_body.c:515]: while parsing xml body message
ERROR: presence_xml > [notify_body.c:84]: while aggregating body
I think this should be clarified / tested....
That sounds more complex and we still have to use the E'' prefix. I
think it would be easier to just add the E'' prefix to the current code.
But now I wonder what data type is used currently - the recent bug
report on the bug-tracker mentions problems with BLOB, which would
indicate that the body is already saved as BLOB.
IIRC you said you already tried adding the E'' prefixes to the
db_postgresql code. Does this, together with the \0 patch from the
bug-tracker solve your problems?
regards
klaus
regards
klaus
--
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
29 Jun
29 Jun
7:30 p.m.
On Thursday 24 June 2010, Klaus Feichtinger wrote:
Yes - from (subscriber) user agents point of view it
"solves" the original
problem. Now each Notify message (except the Subscribe confirmance
notifications) include PIDF bodies.
But as written on Monday, kamailio still produces the internal error
messages that look the same as before:
ERROR: presence_xml > [notify_body.c:515]: while parsing xml body message
ERROR: presence_xml > [notify_body.c:84]: while aggregating body
I think this should be clarified / tested....
> That sounds more complex and we still have to use the E'' prefix. I
> think it would be easier to just add the E'' prefix to the current code.
>
> But now I wonder what data type is used currently - the recent bug
> report on the bug-tracker mentions problems with BLOB, which would
> indicate that the body is already saved as BLOB.
>
> IIRC you said you already tried adding the E'' prefixes to the
> db_postgresql code. Does this, together with the \0 patch from the
> bug-tracker solve your problems?
Hi Klaus,
i've commited the null-termination patch to the repository. Do you've also a
patch for this escaping issue? Did i understand it correctly that its a
generic issue for all BLOB users on postgres?
Cheers,
Henning
4 Jul
4 Jul
2:47 p.m.
Hi Henning,
I do not have another patch for this issue. The patch from the
repository solved the problem in this way that PIDF bodies are now
included in NOTIFY messages. However, the kamailio internal error
message was still present! But I will test it once again with the actual
repository next week.
I confirm that it seems to be a generic issue for all modules using data
type "BLOB" (which does not exist in the specified form).
regards,
Klaus
Am 29.06.2010 18:30, schrieb Henning Westerholt:
Hi Klaus,
i've commited the null-termination patch to the repository. Do you've also a
patch for this escaping issue? Did i understand it correctly that its a
generic issue for all BLOB users on postgres?
Cheers,
Henning
5 Jul
5 Jul
12:32 p.m.
Escaping for newer postgresql versions is still not fixed.
regards
klaus
Am 04.07.2010 13:47, schrieb Klaus Feichtinger:
Hi Henning,
I do not have another patch for this issue. The patch from the
repository solved the problem in this way that PIDF bodies are now
included in NOTIFY messages. However, the kamailio internal error
message was still present! But I will test it once again with the actual
repository next week.
I confirm that it seems to be a generic issue for all modules using data
type "BLOB" (which does not exist in the specified form).
regards,
Klaus
Am 29.06.2010 18:30, schrieb Henning Westerholt:
Hi Klaus,
i've commited the null-termination patch to the repository. Do you've also a
patch for this escaping issue? Did i understand it correctly that its a
generic issue for all BLOB users on postgres?
Cheers,
Henning
_______________________________________________
sr-dev mailing list
sr-dev(a)lists.sip-router.org
http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
24 Jun
24 Jun
7:49 p.m.
On Wednesday 23 June 2010, Klaus Feichtinger wrote:
According information I found in the PostgreSQL
manual, the data type
"BYTEA" is already representing a postgres-like binary data format.
Excerpt of the documentation
(http://www.postgresql.org/docs/8.3/static/datatype-binary.html): "The
bytea data type allows storage of binary strings. [...] The SQL standard
defines a different binary string type, called BLOB or BINARY LARGE
OBJECT. The input format is different from bytea, but the provided
functions and operators are mostly the same."
"Real" BLOB (according SQL definition) is therefore not supported by
postgres. This page (documentation of binary data types) includes examples
of literal escaped octets, too. Maybe is is helpful for developers.
Hello Klaus,
the kamailio internal BLOB type is indeed mapped to the BYTEA type in
postgres, as there is no native BLOB type comparing to e.g. mysql.
Regards,
Henning
7:46 p.m.
On Sunday 20 June 2010, Klaus Feichtinger wrote:
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:
Hello Klaus,
thanks for the report.
“acc” and “missed_calls” table : column “id” must
allow “NULL” (remove “Not
Null” setting)
This two tables are related to the acc module. Do you get some errors here as
well by using this module?
“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)
We can fix this in the data definition (the SQL is derived from some XML
source). Can you maybe quote a bit more context to the error messages you
provided, that i can take a look to the module in question how its inserted?
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……
This looks like a bug in the module to me.
[..]
What does the column “sender” represent? In the presence description on the
Kamailio homepage (version 1.5) this column still is not included.
In sr repository the docs are also not that meaningful:
<description>Sender contact</description>
If this was added recently, maybe the author can comment on the purpose of
them?
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 18th
(http://lists.sip-router.org/pipermail/sr-dev/2010-June/007865.html).
This is something related to the BLOB handling as well, maybe its related.
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.
Sounds indeed possible that this caused the problem.
[..]
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.
I'll comment on the mails later on as well. Yes, postgres is indeed not that
used that much, means that more bugs will be present especially in module that
are as well not that much used like e.g. usrloc. If there are problems in the
driver module it should be of course fixed.
Henning
10:15 p.m.
Am 24.06.2010 18:46, schrieb Henning Westerholt:
For the table "PRESENTITY" following context was displayed in syslog:
3034: 0(5844) DEBUG: db_postgres [km_val.c:158]: PQescapeStringConn: in: 14 chars, out: 14
chars
3035: 0(5844) DEBUG: db_postgres [km_val.c:158]: PQescapeStringConn: in: 5 chars, out: 5
chars
3036: 0(5844) DEBUG: db_postgres [km_val.c:158]: PQescapeStringConn: in: 8 chars, out: 8
chars
3037: 0(5844) DEBUG: db_postgres [km_val.c:158]: PQescapeStringConn: in: 21 chars, out: 21
chars
3038: 0(5844) DEBUG: db_postgres [km_dbase.c:149]: 0x82b2e48 PQsendQuery(insert into
presentity (domain,username,event,etag,expires,body,received_time ) values
('192.168.150.11','16666','presence','a.1275999321.5844.1.0',1275999709,'<?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="pres:16666@192.168.150.11">\\012 <tuple
id="0x8298198">\\012 <status>\\012
<basic>open</basic>\\012 </status>\\012
</tuple>\\012</presence>\\012',1275999348))
3044: 0(5844) DEBUG: db_postgres [km_dbase.c:403]: 0x82b2e48
PQresultStatus(PGRES_FATAL_ERROR) PQgetResult(0x9c73a00)
3045: 0(5844) ERROR: db_postgres [km_dbase.c:427]: invalid query, execution aborted
3046: 0(5844) ERROR: db_postgres [km_dbase.c:428]: driver error: PGRES_FATAL_ERROR, ERROR:
null value in column "sender" violates not-null constraint
3050: 0(5844) DEBUG: db_postgres [km_dbase.c:302]: PQclear(0x9c73a00) result set
3051: 0(5844) WARNING: db_postgres [km_dbase.c:473]: unexpected result returned 0(5844)
DEBUG: presence [presentity.c:102]: send 200OK reply
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
For the table "PUA" following context was displayed in syslog:
4132: 1(5855) DEBUG: db_postgres [km_val.c:158]: PQescapeStringConn: in: 24 chars, out: 24
chars
4133: 1(5855) DEBUG: db_postgres [km_val.c:158]: PQescapeStringConn: in: 54 chars, out: 54
chars
4134: 1(5855) DEBUG: db_postgres [km_val.c:158]: PQescapeStringConn: in: 0 chars, out: 0
chars
4135: 1(5855) DEBUG: db_postgres [km_val.c:158]: PQescapeStringConn: in: 0 chars, out: 0
chars
4136: 1(5855) DEBUG: db_postgres [km_val.c:158]: PQescapeStringConn: in: 0 chars, out: 0
chars
4137: 1(5855) DEBUG: db_postgres [km_val.c:158]: PQescapeStringConn: in: 0 chars, out: 0
chars
4138: 1(5855) DEBUG: db_postgres [km_val.c:158]: PQescapeStringConn: in: 21 chars, out: 21
chars
4139: 1(5855) DEBUG: db_postgres [km_val.c:158]: PQescapeStringConn: in: 9 chars, out: 9
chars
4140: 1(5855) DEBUG: db_postgres [km_val.c:158]: PQescapeStringConn: in: 0 chars, out: 0
chars
4141: 1(5855) DEBUG: db_postgres [km_val.c:158]: PQescapeStringConn: in: 0 chars, out: 0
chars
4142: 1(5855) DEBUG: db_postgres [km_val.c:158]: PQescapeStringConn: in: 0 chars, out: 0
chars
4143: 1(5855) DEBUG: db_postgres [km_dbase.c:149]: 0x82b2e40 PQsendQuery(insert into pua
(pres_uri,pres_id,flag,event,watcher_uri,call_id,to_tag,from_tag,etag,tuple_id,cseq,expires,desired_expires,record_route,contact,remote_contact,version
) values
('sip:16666@192.168.150.11','UL_PUBLISH.2365073952-4024904-12759993470@172.16.51.15',1,1,'','','','','a.1275999321.5844.1.0','0x8298198',0,1275999709,1275999708,'','','',0))
4145: 1(5855) DEBUG: db_postgres [km_dbase.c:403]: 0x82b2e40
PQresultStatus(PGRES_FATAL_ERROR) PQgetResult(0x9c73938)
4146: 1(5855) ERROR: db_postgres [km_dbase.c:427]: invalid query, execution aborted
4147: 1(5855) ERROR: db_postgres [km_dbase.c:428]: driver error: PGRES_FATAL_ERROR, ERROR:
null value in column "extra_headers" violates not-null constraint
4151: 1(5855) DEBUG: db_postgres [km_dbase.c:302]: PQclear(0x9c73938) result set
4152: 1(5855) WARNING: db_postgres [km_dbase.c:473]: unexpected result returned 1(5855)
DEBUG: db_postgres [km_dbase.c:149]: 0x82b2e40 PQsendQuery(delete from pua where
expires<1275999411)
4154: 1(5855) DEBUG: db_postgres [km_dbase.c:403]: 0x82b2e40
PQresultStatus(PGRES_COMMAND_OK) PQgetResult(0x9c73578)
4155: 1(5855) DEBUG: db_postgres [km_dbase.c:302]: PQclear(0x9c73578) result set
--
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
On Sunday 20 June 2010, Klaus Feichtinger wrote:
Hello Henning,
No - I have still clarified in an older mail that the acc and missed_calls tables are not
affected by this error. I had problems, but I think these problems were home-made. Because
of integrating SIREMIS I had to adapt these tables manually. I guess the error occured
during table manipulation / extension.
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:
Hello Klaus,
thanks for the report.
“acc” and “missed_calls” table : column “id” must
allow
“NULL” (remove “Not
Null” setting)
This two tables are related to the acc module. Do you get some errors here
as
well by using this module?
“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)
We can fix this in the data definition (the SQL is derived from some XML
source). Can you maybe quote a bit more context to the error messages you
provided, that i can take a look to the module in question how its
inserted?
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……
This looks like a bug in the module to me.
[..]
What does the column “sender” represent? In the presence description
on the
Kamailio homepage (version 1.5) this column
still is not included.
In sr repository the docs are also not that meaningful:
<description>Sender contact</description>
If this was added recently, maybe the author can comment on the purpose of
them?
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 18th
(http://lists.sip-router.org/pipermail/sr-dev/2010-June/007865.html).
This is something related to the BLOB handling as well, maybe its related.
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.
Sounds indeed possible that this caused the problem.
[..]
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.
I'll comment on the mails later on as well. Yes, postgres is indeed not
that
used that much, means that more bugs will be present especially in module
that
are as well not that much used like e.g. usrloc. If there are problems in
the
driver module it should be of course fixed.
Henning
5257
days inactive
5272
days old
16 comments
3 participants
participants (3)
-
Henning Westerholt -
Klaus Darilion -
Klaus Feichtinger