DB Postgres BLOB conversion for presence

[sergey-safarov]

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

[miconda]

Is postgres db server converting and saving blob data in hexa format?

[sergey-safarov]

Think yes. According doc
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

[sergey-safarov]

How to set it at database level on server side example.
I executed ALTER DATABASE kamailio SET bytea_output TO 'escape'; and restarted kamailio but thisnot help. Error is same

[sergey-safarov]

I set globally bytea_output = 'escape' via postgresql.conf but this also wont help too

I think bytea_output is overrided on client side during kamailio connection to postgres.

[miconda]

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.

[sergey-safarov]

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?

[henningw]

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.

[sergey-safarov]

Think it related to PostgreSQL lib difference between server (9.6.3) and client (9.2.21)

[sergey-safarov]

I tested config when on server used 9.6.3 and on client used 10.1 results is same.

[amessina]

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

[sergey-safarov]

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

You can test this using docker image kamailio/kamailio-ci:5.1-alpine.debug

[lazedo]

@sergey-safarov hot fix for what exactly ? this seems PostgreSQL driver specific

[sergey-safarov]

@lazedo, think i will do hotfix/workarround using other way. Thanks

[miconda]

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.

[sergey-safarov]

Hello Daniel @miconda
Could you help run kamailio under GDB with ability to trace PostgresSQL calls.
May then i can try create PR.

[sergey-safarov]

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.

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.

[sergey-safarov]

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:

[sergey-safarov]

I tested this patch. 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 change source code.

[sergey-safarov]

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