[sr-dev] PostgreSQL problems with Kamailio_3.0.2

Thu Jun 24 21:15:02 CEST 2010

Am 24.06.2010 18:46, schrieb Henning Westerholt:
> 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?
> 
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 at 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 at 192.168.150.11','UL_PUBLISH.2365073952-4024904-12759993470 at 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

> > 
> > 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

-- 
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01