Re: [SR-Users] Reconnection to db

Fri, 14 Jan 2011 02:42:24 -0800 

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 <mico...@gmail.com <mailto:mico...@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@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
    <jgalla...@gmail.com <mailto:jgalla...@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@lists.sip-router.org  <mailto:sr-users@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@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@lists.sip-router.org
http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users

Reply via email to