Hi Daniel<br><br><br>I tested the patch but the mtree module fails to start:<br>Jan 18 04:15:55 kam-test ./kamailio[4616]: DEBUG: <core> [db.c:202]: using db bind api for db_postgres<br>Jan 18 04:15:55 kam-test ./kamailio[4616]: DEBUG: <core> [db.c:285]: connection 0x931768 not found in pool<br>
Jan 18 04:15:55 kam-test ./kamailio[4616]: DEBUG: db_postgres [km_pg_con.c:51]: db_id = 0x931768<br>Jan 18 04:15:55 kam-test ./kamailio[4616]: DEBUG: db_postgres [km_pg_con.c:64]: 0x9319b8=pkg_malloc(72)<br>Jan 18 04:15:55 kam-test ./kamailio[4616]: DEBUG: db_postgres [km_pg_con.c:76]: opening connection: postgres://<a href="http://xxxx:xxxx@79.170.68.171/routing">xxxx:xxxx@79.170.68.171/routing</a><br>
Jan 18 04:15:55 kam-test ./kamailio[4616]: DEBUG: db_postgres [km_pg_con.c:80]: PQsetdbLogin(0x2259780)<br>Jan 18 04:15:55 kam-test ./kamailio[4616]: DEBUG: mtree [mtree_mod.c:255]: database connection opened successfully<br>
Jan 18 04:15:55 kam-test ./kamailio[4616]: WARNING: db_postgres [km_dbase.c:161]: postgres result check failed with code 7 (PGRES_FATAL_ERROR)<br>Jan 18 04:15:55 kam-test ./kamailio[4616]: WARNING: db_postgres [km_dbase.c:165]: postgres query command failed, connection status 0, error []<br>
Jan 18 04:15:55 kam-test ./kamailio[4616]: WARNING: db_postgres [km_dbase.c:165]: postgres query command failed, connection status 0, error [another command is already in progress#012]<br>Jan 18 04:15:55 kam-test ./kamailio[4616]: WARNING: db_postgres [km_dbase.c:165]: postgres query command failed, connection status 0, error [another command is already in progress#012]<br>
Jan 18 04:15:55 kam-test ./kamailio[4616]: ERROR: db_postgres [km_dbase.c:173]: 0x931660 PQsendQuery Error: another command is already in progress#012 Query: select tname,tprefix,tvalue from mtree order by tname<br>Jan 18 04:15:55 kam-test ./kamailio[4616]: ERROR: <core> [db_query.c:101]: error while submitting query<br>
Jan 18 04:15:55 kam-test ./kamailio[4616]: ERROR: mtree [mtree_mod.c:626]: Error while querying db<br>Jan 18 04:15:55 kam-test ./kamailio[4616]: ERROR: mtree [mtree_mod.c:296]: cannot load trees from database<br>Jan 18 04:15:55 kam-test ./kamailio[4616]: DEBUG: <core> [db_pool.c:102]: removing connection from the pool<br>
Jan 18 04:15:55 kam-test ./kamailio[4616]: DEBUG: db_postgres [km_pg_con.c:124]: PQfinish(0x2259780)<br>Jan 18 04:15:55 kam-test ./kamailio[4616]: DEBUG: db_postgres [km_pg_con.c:128]: pkg_free(0x9319b8)<br>Jan 18 04:15:55 kam-test ./kamailio[4616]: ERROR: <core> [sr_module.c:875]: init_mod(): Error while initializing module mtree (/usr/local/kamailio.20110117/lib/kamailio/modules/mtree.so)<br>
<br>I don't see an easy way to fix this, because even after the server restart, PQsendQuery will return 1, and therefore db_postgres_fetch_result is executed and fails. I will keep looking into this, for now it's not a big issue since we know that the second attempt works fine....<br>
<br><br>Thanks<br><br>Javi<br><br><div class="gmail_quote">On Sun, Jan 16, 2011 at 11:09 PM, Daniel-Constantin Mierla <span dir="ltr"><<a href="mailto:miconda@gmail.com">miconda@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div bgcolor="#ffffff" text="#000000">
Hello,<br>
<br>
made a commit to fix in such case, when the broken connection is
reported after doing actually the query, attempting to reconnect
then. Let me know if works now.<br>
<br>
Cheers,<br><font color="#888888">
Daniel</font><div><div></div><div class="h5"><br>
<br>
On 1/16/11 6:17 PM, Javier Gallart wrote:
<blockquote type="cite">Hi Daniel, <br>
<br>
I'm using postgres 8.4 at the server and libpq5 library for the
client. I've been testing again and the problem is that after a db
restart (no matter if done through a service restart or having t
down for some time), the PQstatus(CON_CONNECTION(_con)) function
returns a CONNECTION_OK and therefore it doesn't try to reconnect.
It's on the second attempt of the mt_reload that it returns the
correct CONNECTION_BAD value, and reconnection at that point works
perfectly well.<br>
<br>
First attempt after db restart (I've added a dbg message if we get
a CONNECTION_OK):<br>
<br>
Jan 16 12:03:00 kam-test ./kamailio[22214]: DEBUG: db_postgres
[km_dbase.c:129]: connection OK<br>
Jan 16 12:03:00 kam-test ./kamailio[22214]: DEBUG: db_postgres
[km_dbase.c:155]: sending query ok: 0x92f888 - [select
tname,tprefix,tvalue from mtree order by tname]<br>
Jan 16 12:03:00 kam-test ./kamailio[22214]: DEBUG: <core>
[db_res.c:118]: allocate 48 bytes for result set at 0x94cd08<br>
Jan 16 12:03:00 kam-test ./kamailio[22214]: DEBUG: db_postgres
[km_dbase.c:222]: 0x92f888 PQresultStatus(PGRES_FATAL_ERROR)
PQgetResult(0x1a30d10)<br>
Jan 16 12:03:00 kam-test ./kamailio[22214]: ERROR: db_postgres
[km_dbase.c:238]: 0x92f888 - invalid query, execution aborted<br>
Jan 16 12:03:00 kam-test ./kamailio[22214]: ERROR: db_postgres
[km_dbase.c:239]: 0x92f888 - PQresultStatus(PGRES_FATAL_ERROR)<br>
Jan 16 12:03:00 kam-test ./kamailio[22214]: ERROR: db_postgres
[km_dbase.c:240]: 0x92f888: server closed the connection
unexpectedly#012#011This probably means the server terminated
abnormally#012#011before or while processing the request.#012<br>
<br>
Second attempt:<br>
<br>
Jan 16 12:03:08 kam-test ./kamailio[22214]: DEBUG: db_postgres
[km_dbase.c:132]: connection reset<br>
Jan 16 12:03:08 kam-test ./kamailio[22214]: DEBUG: db_postgres
[km_dbase.c:315]: PQclear(0x1a30d10) result set<br>
Jan 16 12:03:08 kam-test ./kamailio[22214]: DEBUG: db_postgres
[km_dbase.c:155]: sending query ok: 0x92f888 - [select
tname,tprefix,tvalue from mtree order by tname]<br>
<br>
Thanks!<br>
<br>
Javi<br>
<br>
<br>
<br>
<div class="gmail_quote">
On Fri, Jan 14, 2011 at 11:42 AM, Daniel-Constantin Mierla <span dir="ltr"><<a href="mailto:miconda@gmail.com" target="_blank">miconda@gmail.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div bgcolor="#ffffff" text="#000000"> Hello,<br>
<br>
what version of postgres and postgres client library are you
using?<br>
<br>
According to the docs from devel manual, this is the
function that should be used to reconnect:<br>
<br>
<dt><code>PQreset</code> </dt>
<dd> Resets the communication channel to the server.
<pre>void PQreset(PGconn *conn);
</pre>
This function will close the connection to the server and
attempt to reestablish a new connection to the same
server, using all the same parameters previously used.
This may be useful for error recovery if a working
connection is lost.</dd>
<br>
<br>
You may see more here:<br>
<a href="http://www.network-theory.co.uk/docs/postgresql/vol2/DatabaseConnectionControlFunctions.html" target="_blank">http://www.network-theory.co.uk/docs/postgresql/vol2/DatabaseConnectionControlFunctions.html</a><br>
<br>
Somehow seems not to work in this case. Can you try also
with a shorter down time for postgres server, like a normal
restart? Would be intrested to see if the client side loses
somehow connection parameters due to long time unconnected.<br>
<br>
Cheers,<br>
<font color="#888888"> Daniel</font>
<div>
<div><br>
<br>
On 1/14/11 11:15 AM, Javier Gallart wrote:
<blockquote type="cite">Hi Daniel<br>
<br>
<br>
this is my test:<br>
<br>
1- Kamailio starts and correctly loads the mtree <br>
2- I make whatever modification in the tree and reload
with mt_reload<br>
Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG:
db_postgres [km_dbase.c:153]: sending query ok:
0x92f880 - [select tname,tprefix,tvalue from mtree
order by tname]<br>
Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG:
<core> [db_res.c:118]: allocate 48 bytes for
result set at 0x94cd00<br>
Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG:
db_postgres [km_dbase.c:220]: 0x92f880
PQresultStatus(PGRES_TUPLES_OK) PQgetResult(0x29f05f0)<br>
Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG:
db_postgres [km_res.c:108]: 3 columns returned from
the query<br>
Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG:
<core> [db_res.c:155]: allocate 24 bytes for
result names at 0x94cc60<br>
[....]<br>
Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG:
db_postgres [km_res.c:267]: freeing row buffer at
0x94c940<br>
Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG:
mtree [mtree.c:738]: adding new tname [1]<br>
Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG:
<core> [db_res.c:54]: freeing 3 rows<br>
[...]<br>
Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG:
<core> [db_res.c:94]: freeing result names at
0x94cc60<br>
Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG:
<core> [db_res.c:99]: freeing result types at
0x94cbc0<br>
Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG:
<core> [db_res.c:136]: freeing result set at
0x94cd00<br>
Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG:
db_postgres [km_dbase.c:313]: PQclear(0x29f05f0)
result set<br>
<br>
3. Postgres DB restarts (either through restart, or
/stop/wait a minute/start)<br>
No log is written in Kamailio when this happens.<br>
<br>
4.- I try again to reload the tree, and these are the
logs:<br>
Jan 14 04:21:42 kam-test ./kamailio[31205]: DEBUG:
db_postgres [km_dbase.c:153]: sending query ok:
0x92f880 - [select tname,tprefix,tvalue from mtree
order by tname]<br>
Jan 14 04:21:42 kam-test ./kamailio[31205]: DEBUG:
<core> [db_res.c:118]: allocate 48 bytes for
result set at 0x94cd00<br>
Jan 14 04:21:42 kam-test ./kamailio[31205]: DEBUG:
db_postgres [km_dbase.c:220]: 0x92f880
PQresultStatus(PGRES_FATAL_ERROR)
PQgetResult(0x29ef0a0)<br>
Jan 14 04:21:42 kam-test ./kamailio[31205]: ERROR:
db_postgres [km_dbase.c:236]: 0x92f880 - invalid
query, execution aborted<br>
Jan 14 04:21:42 kam-test ./kamailio[31205]: ERROR:
db_postgres [km_dbase.c:237]: 0x92f880 -
PQresultStatus(PGRES_FATAL_ERROR)<br>
Jan 14 04:21:42 kam-test ./kamailio[31205]: ERROR:
db_postgres [km_dbase.c:238]: 0x92f880: server closed
the connection unexpectedly#012#011This probably means
the server terminated abnormally#012#011before or
while processing the request.#012<br>
Jan 14 04:21:42 kam-test ./kamailio[31205]: DEBUG:
<core> [db_res.c:81]: freeing 0 columns<br>
Jan 14 04:21:42 kam-test ./kamailio[31205]: DEBUG:
<core> [db_res.c:136]: freeing result set at
0x94cd00<br>
Jan 14 04:21:42 kam-test ./kamailio[31205]: ERROR:
mtree [mtree_mod.c:633]: Error while fetching result<br>
Jan 14 04:21:42 kam-test ./kamailio[31205]: ERROR:
db_postgres [km_dbase.c:329]: invalid parameter value<br>
Jan 14 04:21:42 kam-test ./kamailio[31205]: ERROR:
mtree [mtree_mod.c:744]: cannot re-load info from
database<br>
<br>
Thanks in advance<br>
<br>
<br>
Regards<br>
<br>
Javi<br>
<br>
<div class="gmail_quote">On Thu, Jan 13, 2011 at 1:56
PM, Daniel-Constantin Mierla <span dir="ltr"><<a href="mailto:miconda@gmail.com" target="_blank">miconda@gmail.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div bgcolor="#ffffff" text="#000000"> Can you
send the log messages (debug=3) for that moment
(restart of pg server and making a new
call/registration)?<br>
<br>
Daniel
<div>
<div><br>
<br>
On 1/13/11 12:00 PM, Javier Gallart wrote: </div>
</div>
<blockquote type="cite">
<div>
<div>Hi Daniel<br>
<br>
I have just tested it and I'm getting the
same results. This is the kamailio version
I'm testing:<br>
root@kam-test:/usr/local/kamailio/sbin#
./kamailio -V<br>
version: kamailio 3.2.0-dev2
(x86_64/linux) 35568f<br>
<br>
Thanks!<br>
<br>
Javi<br>
<br>
<br>
<div class="gmail_quote">On Tue, Jan 4,
2011 at 3:46 PM, Javier Gallart <span dir="ltr"><<a href="mailto:jgallartm@gmail.com" target="_blank">jgallartm@gmail.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"> Hi list
<div><br>
</div>
<div>we have been testing the mtree
module. We do periodical reloads of
the mtree table with the "mi
mt_reload" command through xmlrpc.
It works fine except when the db is
restarted. If we manually restart
the db and then try the mt_reload
commands we get this error:</div>
<div>
<div>Jan 4 09:13:17
server /usr/local/kamailio-3.1.1/sbin/kamailio[28534]:
ERROR: db_postgres
[km_dbase.c:152]: 0xa6a490
PQsendQuery Error: server closed
the connection
unexpectedly#012#011This probably
means the server terminated
abnormally#012#011before or while
processing the request.#012 Query:
select partition,prefix,route from
trees order by partition</div>
<div>Jan 4 09:13:17 server
/usr/local/kamailio-3.1.1/sbin/kamailio[28534]:
ERROR: <core>
[db_query.c:101]: error while
submitting query</div>
<div>Jan 4 09:13:17 server
/usr/local/kamailio-3.1.1/sbin/kamailio[28534]:
ERROR: mtree [mtree_mod.c:622]:
Error while querying db</div>
<div>Jan 4 09:13:17 server
/usr/local/kamailio-3.1.1/sbin/kamailio[28534]:
ERROR: mtree [mtree_mod.c:738]:
cannot re-load info from database</div>
</div>
<div><br>
</div>
<div>Is there a way to reconnect to
the db before sending that command?</div>
<div><br>
</div>
<div>Thanks in advance</div>
<div><br>
</div>
<div>Regards</div>
<div><br>
</div>
<div>Javi</div>
<div><br>
</div>
</blockquote>
</div>
<br>
</div>
</div>
<pre><fieldset></fieldset>
_______________________________________________
SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
<a href="mailto:sr-users@lists.sip-router.org" target="_blank">sr-users@lists.sip-router.org</a>
<a href="http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users" target="_blank">http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users</a>
</pre>
</blockquote>
<div> <br>
<pre cols="72">--
Daniel-Constantin Mierla
Kamailio (OpenSER) Advanced Training
Jan 24-26, 2011, Irvine, CA, USA
<a href="http://www.asipto.com" target="_blank">http://www.asipto.com</a></pre>
</div>
</div>
</blockquote>
</div>
<br>
<pre><fieldset></fieldset>
_______________________________________________
SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
<a href="mailto:sr-users@lists.sip-router.org" target="_blank">sr-users@lists.sip-router.org</a>
<a href="http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users" target="_blank">http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users</a>
</pre>
</blockquote>
<br>
<pre cols="72">--
Daniel-Constantin Mierla
Kamailio (OpenSER) Advanced Training
Jan 24-26, 2011, Irvine, CA, USA
<a href="http://www.asipto.com" target="_blank">http://www.asipto.com</a></pre>
</div>
</div>
</div>
</blockquote>
</div>
<br>
</blockquote>
<br>
<pre cols="72">--
Daniel-Constantin Mierla
Kamailio (OpenSER) Advanced Training
Jan 24-26, 2011, Irvine, CA, USA
<a href="http://www.asipto.com" target="_blank">http://www.asipto.com</a></pre>
</div></div></div>
</blockquote></div><br>