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