Re: [sr-dev] [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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___
Kamailio (SER) - Development Mailing List
sr-dev@lists.kamailio.org
https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___
Kamailio (SER) - Development Mailing List
sr-dev@lists.kamailio.org
https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___
Kamailio (SER) - Development Mailing List
sr-dev@lists.kamailio.org
https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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-E1F924C08724%40mac.com). 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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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/backend/utils/misc/postgresql.conf.sample#L558) 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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
Re: [sr-dev] [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___ Kamailio (SER) - Development Mailing List sr-dev@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
