[sr-dev] PostgreSQL problems with Kamailio_3.0.2
Klaus Feichtinger
klaus.feichtinger at gmx.net
Mon Jun 21 21:48:49 CEST 2010
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 at 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 at 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.
>
>>
>> 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.
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 at 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.
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....
>
>> 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'.
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-dev/attachments/20100621/59592968/attachment-0001.htm>
More information about the sr-dev
mailing list