### 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:activitiesrpid:on-the-phone/</rpid:activities>dm:noteOn 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
Is postgres db server converting and saving blob data in hexa format?
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
How to set it at database level on server side [example](https://www.postgresql.org/message-id/BEDD02CA-A662-48B6-82E5-E1F924C08724%4...). I executed `ALTER DATABASE kamailio SET bytea_output TO 'escape';` and restarted kamailio but thisnot help. Error is same
I set globally `bytea_output = 'escape'` via [postgresql.conf](https://github.com/postgres/postgres/blob/master/src/backend/utils/misc/post...) but this also wont help too
I think `bytea_output` is overrided on client side during kamailio connection to postgres.
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.
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`?
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.
Think it related to PostgreSQL lib difference between server (`9.6.3`) and client (`9.2.21`)
I tested config when on server used `9.6.3` and on client used `10.1` results is same.
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 :(
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
@sergey-safarov hot fix for what exactly ? this seems `PostgreSQL` driver specific
@lazedo, think i will do hotfix/workarround using other way. Thanks
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.
Closed #1255.
Hello Daniel @miconda Could you help run kamailio under GDB with ability to trace PostgresSQL calls. May then i can try create PR.
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_...).
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.
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: ```
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.
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'" ```
-
Anthony Messina -
Daniel-Constantin Mierla
-
henningw
-
lazedo
-
sergey-safarov