{"action":"created","issue":{"url":"https://api.github.com/repos/kamailio/kamailio/issues/1255","repository_url":"https://api.github.com/repos/kamailio/kamailio","labels_url":"https://api.github.com/repos/kamailio/kamailio/issues/1255/labels{/name}","comments_url":"https://api.github.com/repos/kamailio/kamailio/issues/1255/comments","events_url":"https://api.github.com/repos/kamailio/kamailio/issues/1255/events","html_url":"https://github.com/kamailio/kamailio/issues/1255","id":261490979,"node_id":"MDU6SXNzdWUyNjE0OTA5Nzk=","number":1255,"title":"DB Postgres BLOB conversion for presence","user":{"login":"sergey-safarov","id":2562241,"node_id":"MDQ6VXNlcjI1NjIyNDE=","avatar_url":"https://avatars2.githubusercontent.com/u/2562241?v=4","gravatar_id":"","url":"https://api.github.com/users/sergey-safarov","html_url":"https://github.com/sergey-safarov","followers_url":"https://api.github.com/users/sergey-safarov/followers","following_url":"https://api.github.com/users/sergey-safarov/following{/other_user}","gists_url":"https://api.github.com/users/sergey-safarov/gists{/gist_id}","starred_url":"https://api.github.com/users/sergey-safarov/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sergey-safarov/subscriptions","organizations_url":"https://api.github.com/users/sergey-safarov/orgs","repos_url":"https://api.github.com/users/sergey-safarov/repos","events_url":"https://api.github.com/users/sergey-safarov/events{/privacy}","received_events_url":"https://api.github.com/users/sergey-safarov/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":16,"created_at":"2017-09-28T23:29:47Z","updated_at":"2018-10-08T04:27:20Z","closed_at":"2018-09-13T07:25:33Z","author_association":"CONTRIBUTOR","body":"### Description\r\n\r\nI use kazoo and postgres modules on my host. I found that many database operation generates error like\r\n```\r\n19(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\r\n17(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\r\nEntity: line 1: parser error : Start tag expected, '<' not found\r\n\\x3c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d38223f3e3c\r\n^\r\n17(148) ERROR: presence_xml [notify_body.c:551]: agregate_xmls(): while parsing xml body message\r\n17(148) ERROR: presence_xml [notify_body.c:156]: pres_agg_nbody(): while aggregating body\r\n19(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\r\nEntity: line 1: parser error : Start tag expected, '<' not found\r\n\\x3c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d38223f3e3c\r\n^\r\n19(150) ERROR: presence_xml [notify_body.c:551]: agregate_xmls(): while parsing xml body message\r\n19(150) ERROR: presence_xml [notify_body.c:156]: pres_agg_nbody(): while aggregating body\r\n18(149) exec: *** cfgtrace:request_route=[kazoo:consumer-event-nodes-advertise] c=[//etc/kamailio/nodes-role.cfg] l=66 a=63 n=assign\r\n18(149) exec: *** cfgtrace:request_route=[kazoo:consumer-event-nodes-advertise] c=[//etc/kamailio/nodes-role.cfg] l=74 a=16 n=if\r\n18(149) exec: *** cfgtrace:request_route=[kazoo:consumer-event-nodes-advertise] c=[//etc/kamailio/nodes-role.cfg] l=74 a=16 n=if\r\n18(149) exec: *** cfgtrace:request_route=[kazoo:consumer-event-nodes-advertise] c=[//etc/kamailio/nodes-role.cfg] l=75 a=27 n=mq_add\r\nEntity: line 1: parser error : Start tag expected, '<' not found\r\n\\x3c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d38223f3e3c\r\n^\r\n17(148) ERROR: presence_xml [notify_body.c:551]: agregate_xmls(): while parsing xml body message\r\n17(148) ERROR: presence_xml [notify_body.c:156]: pres_agg_nbody(): while aggregating body\r\nEntity: line 1: parser error : Start tag expected, '<' not found\r\n\\x3c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d38223f3e3c\r\n^\r\n19(150) ERROR: presence_xml [notify_body.c:551]: agregate_xmls(): while parsing xml body message\r\n19(150) ERROR: presence_xml [notify_body.c:156]: pres_agg_nbody(): while aggregating body\r\nEntity: line 1: parser error : Start tag expected, '<' not found\r\n\\x3c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d38223f3e3c\r\n^\r\n17(148) ERROR: presence_xml [notify_body.c:551]: agregate_xmls(): while parsing xml body message\r\n17(148) ERROR: presence_xml [notify_body.c:156]: pres_agg_nbody(): while aggregating body\r\n17(148) exec: *** cfgtrace:request_route=[kazoo:consumer-event-presence-dialog-update] c=[//etc/kamailio/presence-role.cfg] l=276 a=26 n=xlog\r\n17(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\r\nEntity: line 1: parser error : Start tag expected, '<' not found\r\n\\x3c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d38223f3e3c\r\n^\r\n```\r\nAfter some debugging i found that kamailio wants save to database xml string like this as BLOB object\r\n```\r\n<?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>\r\n```\r\nAnd 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.\r\n\r\n#### Reproduction\r\n\r\nConfigure kamailio on kazoo cluster to use PostgreSQL database\r\nOn phone create subscribe to second\r\nFrom second phone create new call.\r\n\r\n\r\n#### Operating System\r\nCentOS 7\r\nLinux 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"},"comment":{"url":"https://api.github.com/repos/kamailio/kamailio/issues/comments/427719271","html_url":"https://github.com/kamailio/kamailio/issues/1255#issuecomment-427719271","issue_url":"https://api.github.com/repos/kamailio/kamailio/issues/1255","id":427719271,"node_id":"MDEyOklzc3VlQ29tbWVudDQyNzcxOTI3MQ==","user":{"login":"sergey-safarov","id":2562241,"node_id":"MDQ6VXNlcjI1NjIyNDE=","avatar_url":"https://avatars2.githubusercontent.com/u/2562241?v=4","gravatar_id":"","url":"https://api.github.com/users/sergey-safarov","html_url":"https://github.com/sergey-safarov","followers_url":"https://api.github.com/users/sergey-safarov/followers","following_url":"https://api.github.com/users/sergey-safarov/following{/other_user}","gists_url":"https://api.github.com/users/sergey-safarov/gists{/gist_id}","starred_url":"https://api.github.com/users/sergey-safarov/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sergey-safarov/subscriptions","organizations_url":"https://api.github.com/users/sergey-safarov/orgs","repos_url":"https://api.github.com/users/sergey-safarov/repos","events_url":"https://api.github.com/users/sergey-safarov/events{/privacy}","received_events_url":"https://api.github.com/users/sergey-safarov/received_events","type":"User","site_admin":false},"created_at":"2018-10-08T04:27:20Z","updated_at":"2018-10-08T04:27:20Z","author_association":"CONTRIBUTOR","body":"I created patch for postgres module and will test soon.\r\n```\r\ndiff --git a/src/modules/db_postgres/km_pg_con.c b/src/modules/db_postgres/km_pg_con.c\r\nindex 5a7225c..b66a94a 100644\r\n--- a/src/modules/db_postgres/km_pg_con.c\r\n+++ b/src/modules/db_postgres/km_pg_con.c\r\n@@ -52,6 +52,7 @@ struct pg_con *db_postgres_new_connection(struct db_id *id)\r\n \tint i = 0;\r\n \tconst char *keywords[10], *values[10];\r\n \tchar to[16];\r\n+\tPGresult   *res;\r\n \r\n \tLM_DBG(\"db_id = %p\\n\", id);\r\n \r\n@@ -141,6 +142,15 @@ struct pg_con *db_postgres_new_connection(struct db_id *id)\r\n \t}\r\n #endif\r\n \r\n+\tres = PQexec(ptr->con, \"SET bytea_output=escape\");\r\n+\tif (PQresultStatus(res) != PGRES_COMMAND_OK)\r\n+\t{\r\n+\t\tLM_ERR(\"cannot set blob output escaping format\\n\");\r\n+\t\tPQclear(res);\r\n+\t\tgoto err;\r\n+\t}\r\n+\tPQclear(res);\r\n+\r\n \treturn ptr;\r\n \r\n err:\r\n```"}}
{"actor":{"display_login":"sergey-safarov"}}