29 Sep
2017
29 Sep
'17
1:29 a.m.
### Description
I use kazoo and postgres modules on my host. I found that many database operation
generates error like
```
19(150) exec: *** cfgtrace:request_route=[kazoo:consumer-event-presence-dialog-update]
c=[//etc/kamailio/presence-role.cfg] l=270 a=32 n=kazoo_pua_publish_presence
17(148) exec: *** cfgtrace:request_route=[kazoo:consumer-event-presence-dialog-update]
c=[//etc/kamailio/presence-role.cfg] l=271 a=27 n=pres_refresh_watchers
Entity: line 1: parser error : Start tag expected, '<' not found
\x3c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d38223f3e3c
^
17(148) ERROR: presence_xml [notify_body.c:551]: agregate_xmls(): while parsing xml body
message
17(148) ERROR: presence_xml [notify_body.c:156]: pres_agg_nbody(): while aggregating body
19(150) exec: *** cfgtrace:request_route=[kazoo:consumer-event-presence-dialog-update]
c=[//etc/kamailio/presence-role.cfg] l=271 a=27 n=pres_refresh_watchers
Entity: line 1: parser error : Start tag expected, '<' not found
\x3c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d38223f3e3c
^
19(150) ERROR: presence_xml [notify_body.c:551]: agregate_xmls(): while parsing xml body
message
19(150) ERROR: presence_xml [notify_body.c:156]: pres_agg_nbody(): while aggregating body
18(149) exec: *** cfgtrace:request_route=[kazoo:consumer-event-nodes-advertise]
c=[//etc/kamailio/nodes-role.cfg] l=66 a=63 n=assign
18(149) exec: *** cfgtrace:request_route=[kazoo:consumer-event-nodes-advertise]
c=[//etc/kamailio/nodes-role.cfg] l=74 a=16 n=if
18(149) exec: *** cfgtrace:request_route=[kazoo:consumer-event-nodes-advertise]
c=[//etc/kamailio/nodes-role.cfg] l=74 a=16 n=if
18(149) exec: *** cfgtrace:request_route=[kazoo:consumer-event-nodes-advertise]
c=[//etc/kamailio/nodes-role.cfg] l=75 a=27 n=mq_add
Entity: line 1: parser error : Start tag expected, '<' not found
\x3c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d38223f3e3c
^
17(148) ERROR: presence_xml [notify_body.c:551]: agregate_xmls(): while parsing xml body
message
17(148) ERROR: presence_xml [notify_body.c:156]: pres_agg_nbody(): while aggregating body
Entity: line 1: parser error : Start tag expected, '<' not found
\x3c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d38223f3e3c
^
19(150) ERROR: presence_xml [notify_body.c:551]: agregate_xmls(): while parsing xml body
message
19(150) ERROR: presence_xml [notify_body.c:156]: pres_agg_nbody(): while aggregating body
Entity: line 1: parser error : Start tag expected, '<' not found
\x3c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d38223f3e3c
^
17(148) ERROR: presence_xml [notify_body.c:551]: agregate_xmls(): while parsing xml body
message
17(148) ERROR: presence_xml [notify_body.c:156]: pres_agg_nbody(): while aggregating body
17(148) exec: *** cfgtrace:request_route=[kazoo:consumer-event-presence-dialog-update]
c=[//etc/kamailio/presence-role.cfg] l=276 a=26 n=xlog
17(148) INFO: <script>: 3O75Qw3gqR3ZNla5RfCgQLVW0-8KKbrT|log|finished processing
dialog update for sip:2496@rcsnet.ru state confirmed from sip:217.12.247.98:11000 at
1506632313/1506632313/1506632313
Entity: line 1: parser error : Start tag expected, '<' not found
\x3c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d38223f3e3c
^
```
After some debugging i found that kamailio wants save to database xml string like this as
BLOB object
```
<?xml version="1.0" encoding="UTF-8"?><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="2496">
<tuple xmlns="urn:ietf:params:xml:ns:pidf"
id="t8No1hQnp65U33jS1oqFMgxbIRPq-iFP"><status><basic>open</basic></status></tuple><note
xmlns="urn:ietf:params:xml:ns:pidf">On the Phone</note><dm:person
xmlns:dm="urn:ietf:params:xml:ns:pidf:data-model"
xmlns:rpid="urn:ietf:params:xml:ns:pidf:rpid"
id="1"><rpid:activities><rpid:on-the-phone/></rpid:activities><dm:note>On
the Phone</dm:note></dm:person></presence>
```
And later tries to read. But when this XML read reverse conversion of BLOB to string is
not take place. And late used BLOB as is. This breaks presentation logic completely.
#### Reproduction
Configure kamailio on kazoo cluster to use PostgreSQL database
On phone create subscribe to second
From second phone create new call.
#### Operating System
CentOS 7
Linux node2.docker.rcsnet.ru 3.10.0-514.16.1.el7.x86_64 #1 SMP Wed Apr 12 15:04:24 UTC
2017 x86_64 x86_64 x86_64 GNU/Linux
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255
29 Sep
29 Sep
8:49 a.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
Is postgres db server converting and saving blob data in hexa format?
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-333045369
10:12 a.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
Think yes. According
[doc](https://www.postgresql.org/docs/devel/static/datatype-binary.html)
`The “hex” format encodes binary data as 2 hexadecimal digits per byte, most significant
nibble first. The entire string is preceded by the sequence \x`
According logs kamailio got string with escape `\x`.
Also according same page
`The bytea type supports two external formats for input and output: PostgreSQL's
historical “escape” format, and “hex” format. Both of these are always accepted on input.
The output format depends on the configuration parameter bytea_output;`
I will try make workarround at table config
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-333061390
10:30 a.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
How to set it at database level on server side
[example](https://www.postgresql.org/message-id/BEDD02CA-A662-48B6-82E5-E1F9….
I executed `ALTER DATABASE kamailio SET bytea_output TO 'escape';` and restarted
kamailio but thisnot help. Error is same
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-333065452
10:57 a.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
I set globally `bytea_output = 'escape'` via
[postgresql.conf](https://github.com/postgres/postgres/blob/master/src/backe…
but this also wont help too
I think `bytea_output` is overrided on client side during kamailio connection to
postgres.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-333071570
11 Oct
11 Oct
6:14 p.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
Have you found a solution for this one? I know people were using postgres with presence
quite a lot in the past and they didn't have any such issue.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-335863767
9:21 p.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
Hello Daniel
No i not resolved. Now i switched to use other database driver in kamailio config.
Could you point me how to debug kamailio using `GDB`?
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-335919492
4 Nov
4 Nov
8:48 p.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
Hello, using GDB to debug Kamailio is dificult, due its multi-process architecture. You
will have results if you restrict the number of children within your test configuration
and use only one UDP worker child.
If you have database issues within Kamailio my suggestion would be to try to isolate it to
a test case and then enable debugging mode in the configuration. This way the core and
database modules will give you a lot of information about the data transfer and data
conversion.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-341924798
5 Nov
5 Nov
3:22 p.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
Think it related to PostgreSQL lib difference between server (`9.6.3`) and client
(`9.2.21`)
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-341976747
17 Dec
17 Dec
4:21 p.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
I tested config when on server used `9.6.3` and on client used `10.1` results is same.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-352263115
5:41 p.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
It may be more than is necessary, but PostgreSQL can store XML documents natively with the
XML data type: https://www.postgresql.org/docs/9.6/static/datatype-xml.html, but I guess
that would require a rewrite of how Kamailio stores presence data :(
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-352268538
1 Apr
1 Apr
4:24 p.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
Hello Luis (@lazedo)
This is related to presence generated by kazoo module.
Could you make hot fix for this?
Then I can take logs on my servers using latest master for ticket
https://github.com/kamailio/kamailio/issues/1489
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-377790359
3 Apr
3 Apr
8:50 a.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
@sergey-safarov hot fix for what exactly ? this seems `PostgreSQL` driver specific
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-378146365
9:32 a.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
@lazedo, think i will do hotfix/workarround using other way. Thanks
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-378155849
13 Sep
13 Sep
9:25 a.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
Closing being specific to postgres config/data types. If someone wants to push a patch to
make it easier from kamailio point of view, just make a pull request.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-420910277
9:25 a.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
Closed #1255.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#event-1842897307
9:56 a.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
Hello Daniel @miconda
Could you help run kamailio under GDB with ability to trace PostgresSQL calls.
May then i can try create PR.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-420917827
7 Oct
7 Oct
10:17 p.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
Hello Daniel
Think i found way how to properly escape blob strings.
need to execute `SET bytea_output=escape;` command when connection created
This maybe done in function
[`db_postgres_new_connection`](https://github.com/kamailio/kamailio/blob/master/src/modules/db_postgres/km_pg_con.c#L48-L152).
By default psql output BLOBs in hex format. After `SET bytea_output=escape;` this
behaviour is changed to expected by Kamailio.
```
pg1b:~$ psql kamailio
psql (9.6.3)
Type "help" for help.
kamailio=# CREATE TABLE blob_table (blob bytea);
CREATE TABLE
kamailio=# INSERT INTO blob_table VALUES ('Kamailio');
INSERT 0 1
kamailio=# SELECT * FROM blob_table;
blob
--------------------
\x4b616d61696c696f
(1 row)
kamailio=# SET bytea_output=escape;
SET
kamailio=# SELECT * FROM blob_table;
blob
----------
Kamailio
(1 row)
kamailio=#
```
Could you suggest patch and then i will test on my servers.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-427683551
8 Oct
8 Oct
6:27 a.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
I created patch for postgres module and will test soon.
```
diff --git a/src/modules/db_postgres/km_pg_con.c b/src/modules/db_postgres/km_pg_con.c
index 5a7225c..b66a94a 100644
--- a/src/modules/db_postgres/km_pg_con.c
+++ b/src/modules/db_postgres/km_pg_con.c
@@ -52,6 +52,7 @@ struct pg_con *db_postgres_new_connection(struct db_id *id)
int i = 0;
const char *keywords[10], *values[10];
char to[16];
+ PGresult *res;
LM_DBG("db_id = %p\n", id);
@@ -141,6 +142,15 @@ struct pg_con *db_postgres_new_connection(struct db_id *id)
}
#endif
+ res = PQexec(ptr->con, "SET bytea_output=escape");
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ LM_ERR("cannot set blob output escaping format\n");
+ PQclear(res);
+ goto err;
+ }
+ PQclear(res);
+
return ptr;
err:
```
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-427719271
10 Oct
10 Oct
10:45 a.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
I tested this patch all. Now issue is fixed.
Also exist other way to fix this. Need to use
```
SELECT convert_from(body,'UTF-8') FROM presentity;
```
in SQL request to PostgreSQL server instreat of
```
SELECT body FROM presentity;
```
Example
```
pg1:~$ psql kamailio
psql (9.6.3)
Type "help" for help.
kamailio=# CREATE TABLE blob_table (blob bytea);
CREATE TABLE
kamailio=# INSERT INTO blob_table VALUES ('Kamailio');
INSERT 0 1
kamailio=# SELECT blob, convert_from(blob,'UTF-8') FROM blob_table;
blob | convert_from
--------------------+--------------
\x4b616d61696c696f | Kamailio
(1 row)
```
Second way is more elegant but i do not know hot to need change source code.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-428489293
30 Jan
30 Jan
11:10 a.m.
New subject: [kamailio/kamailio] DB Postgres BLOB conversion for presence (#1255)
As troubleshooting remainder.
Command to troubleshoot BLF issues for Yealink phones
```
psql kamailio -c "SET bytea_output=escape; select * from presentity where
domain='tenant.domain.name'"
```
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1255#issuecomment-458887195
1937
days inactive
2426
days old
20 comments
5 participants
participants (5)
-
Anthony Messina -
Daniel-Constantin Mierla
-
henningw
-
lazedo
-
sergey-safarov