[SR-Users] Reconnection to db

Daniel-Constantin Mierla miconda at gmail.com
Fri Jan 14 11:42:02 CET 2011 

Hello,

what version of postgres and postgres client library are you using?

According to the docs from devel manual, this is the function that 
should be used to reconnect:

|PQreset|
    Resets the communication channel to the server.

    void PQreset(PGconn *conn);

    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.

You may see more here:
http://www.network-theory.co.uk/docs/postgresql/vol2/DatabaseConnectionControlFunctions.html

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.

Cheers,
Daniel

On 1/14/11 11:15 AM, Javier Gallart wrote:
> Hi Daniel
>
>
> this is my test:
>
> 1- Kamailio starts and correctly loads the mtree
> 2- I make whatever modification in the tree and reload with mt_reload
> 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]
> Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG: <core> 
> [db_res.c:118]: allocate 48 bytes for result set at 0x94cd00
> Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG: db_postgres 
> [km_dbase.c:220]: 0x92f880 PQresultStatus(PGRES_TUPLES_OK) 
> PQgetResult(0x29f05f0)
> Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG: db_postgres 
> [km_res.c:108]: 3 columns returned from the query
> Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG: <core> 
> [db_res.c:155]: allocate 24 bytes for result names at 0x94cc60
> [....]
> Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG: db_postgres 
> [km_res.c:267]: freeing row buffer at 0x94c940
> Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG: mtree 
> [mtree.c:738]: adding new tname [1]
> Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG: <core> 
> [db_res.c:54]: freeing 3 rows
> [...]
> Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG: <core> 
> [db_res.c:94]: freeing result names at 0x94cc60
> Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG: <core> 
> [db_res.c:99]: freeing result types at 0x94cbc0
> Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG: <core> 
> [db_res.c:136]: freeing result set at 0x94cd00
> Jan 14 04:19:06 kam-test ./kamailio[31205]: DEBUG: db_postgres 
> [km_dbase.c:313]: PQclear(0x29f05f0) result set
>
> 3. Postgres DB restarts (either through restart, or /stop/wait a 
> minute/start)
> No log is written in Kamailio when this happens.
>
> 4.- I try again to reload the tree, and these are the logs:
> 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]
> Jan 14 04:21:42 kam-test ./kamailio[31205]: DEBUG: <core> 
> [db_res.c:118]: allocate 48 bytes for result set at 0x94cd00
> Jan 14 04:21:42 kam-test ./kamailio[31205]: DEBUG: db_postgres 
> [km_dbase.c:220]: 0x92f880 PQresultStatus(PGRES_FATAL_ERROR) 
> PQgetResult(0x29ef0a0)
> Jan 14 04:21:42 kam-test ./kamailio[31205]: ERROR: db_postgres 
> [km_dbase.c:236]: 0x92f880 - invalid query, execution aborted
> Jan 14 04:21:42 kam-test ./kamailio[31205]: ERROR: db_postgres 
> [km_dbase.c:237]: 0x92f880 - PQresultStatus(PGRES_FATAL_ERROR)
> 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
> Jan 14 04:21:42 kam-test ./kamailio[31205]: DEBUG: <core> 
> [db_res.c:81]: freeing 0 columns
> Jan 14 04:21:42 kam-test ./kamailio[31205]: DEBUG: <core> 
> [db_res.c:136]: freeing result set at 0x94cd00
> Jan 14 04:21:42 kam-test ./kamailio[31205]: ERROR: mtree 
> [mtree_mod.c:633]: Error while fetching result
> Jan 14 04:21:42 kam-test ./kamailio[31205]: ERROR: db_postgres 
> [km_dbase.c:329]: invalid parameter value
> Jan 14 04:21:42 kam-test ./kamailio[31205]: ERROR: mtree 
> [mtree_mod.c:744]: cannot re-load info from database
>
> Thanks in advance
>
>
> Regards
>
> Javi
>
> On Thu, Jan 13, 2011 at 1:56 PM, Daniel-Constantin Mierla 
> <miconda at gmail.com <mailto:miconda at gmail.com>> wrote:
>
>     Can you send the log messages (debug=3) for that moment (restart
>     of pg server and making a new call/registration)?
>
>     Daniel
>
>
>     On 1/13/11 12:00 PM, Javier Gallart wrote:
>>     Hi Daniel
>>
>>     I have just tested it and I'm getting the same results. This is
>>     the kamailio version I'm testing:
>>     root at kam-test:/usr/local/kamailio/sbin# ./kamailio -V
>>     version: kamailio 3.2.0-dev2 (x86_64/linux) 35568f
>>
>>     Thanks!
>>
>>     Javi
>>
>>
>>     On Tue, Jan 4, 2011 at 3:46 PM, Javier Gallart
>>     <jgallartm at gmail.com <mailto:jgallartm at gmail.com>> wrote:
>>
>>         Hi list
>>
>>         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:
>>         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
>>         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
>>         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
>>         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
>>
>>         Is there a way to reconnect to the db before sending that
>>         command?
>>
>>         Thanks in advance
>>
>>         Regards
>>
>>         Javi
>>
>>
>>
>>     _______________________________________________
>>     SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
>>     sr-users at lists.sip-router.org  <mailto:sr-users at lists.sip-router.org>
>>     http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
>
>     -- 
>     Daniel-Constantin Mierla
>     Kamailio (OpenSER) Advanced Training
>     Jan 24-26, 2011, Irvine, CA, USA
>     http://www.asipto.com
>
>
>
> _______________________________________________
> SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
> sr-users at lists.sip-router.org
> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users

-- 
Daniel-Constantin Mierla
Kamailio (OpenSER) Advanced Training
Jan 24-26, 2011, Irvine, CA, USA
http://www.asipto.com

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-users/attachments/20110114/e1bfe01f/attachment-0001.htm>

More information about the sr-users mailing list