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@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
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:
- 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?
- 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@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.
- 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.
- 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
Hello Klaus,
Am 21.06.2010 10:40, schrieb Klaus Darilion:
Hi Klaus!
Am 20.06.2010 14:39, schrieb Klaus Feichtinger:
[...] 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?
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@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@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 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.
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).
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@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.
- 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.
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....
- 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'.
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.
regards Klaus
regards Klaus F.
Am 21.06.2010 21:48, schrieb Klaus Feichtinger:
Hello 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.
Do you know if the E'' syntax is supported in old versions too? (we should still support older psql versions too)
klaus
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:
Hello 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.
Do you know if the E'' syntax is supported in old versions too? (we should still support older psql versions too)
klaus
Am 20.06.2010 14:39, schrieb Klaus Feichtinger:
- 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
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,
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.
Do you know if the E'' syntax is supported in old versions too? (we should still support older psql versions too)
klaus
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:
- 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
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
On Sunday 20 June 2010, Klaus Feichtinger wrote:
The default settings of 4 PGSQL tables after initializing thedatabase 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?
I do not know if this has a direct influence on the problems I havewith 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?
The next problem I have is, that the PIDF-body, which is stored inthe 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.
I don’t know if the parser might be influenced by a WARNING that isgenerated 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
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
On Wednesday 23 June 2010, Klaus Darilion wrote:
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?
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
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
Am 24.06.2010 18:46, schrieb Henning Westerholt:
On Sunday 20 June 2010, Klaus Feichtinger wrote:
The default settings of 4 PGSQL tables after initializing thedatabase 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?
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.
“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?
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
I do not know if this has a direct influence on the problems Ihave
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?
The next problem I have is, that the PIDF-body, which is storedin
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.
I don’t know if the parser might be influenced by a WARNINGthat 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
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
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
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@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
-
Henning Westerholt -
Klaus Darilion -
Klaus Feichtinger