[OpenSIPS-Users] Opensips, avpops and avp_db_query( ), and pgpool2 -- questions, suggestions, issues

[Bogdan-Andrei Iancu]

Hi Bobby,

Bobby Smith wrote:
> All,
>
> We're currently (successfully) testing a configuration of Opensips as 
> a stateless proxy.  I wanted to be able to execute an arbitrary 
> database query to a database that's NOT the opensips database, when I 
> realized the power of the avp_db_query( ) function in avpops.  The 
> database is a backend postgres database in an basic failover 
> configuration (16 max connections cached, primary db, failover to 
> secondary db for those connections).
>
> Currently, opensips has 16 children processes.  In the configuration, 
> we have the following:
>
> modparam("avpops", "db_url", 
> "postgres://opensips:opensips@localhost:9999/my_database")
> modparam("avpops", "avp_table", "")
>
> A few of the issues I'm concerned about or experiencing with the 
> operation of it.
>
> a) Why is that second parameter necessary (avp_table)?  It feels like 
> if I have to set this that it's always going to keep open connections 
> with the database, and indeed, it is.
This parameter is required by other functions exported by the avpops 
module (like avp_db_load() / avp_db_store() ), functions which do not 
explicitly get as function parameter the name of the table to operate with.

The safe way to do it is is to set it to the "usr_preferences" table - 
this table is by default created for the avpops module.

> When I ps -ef, it shows I have 16 pgpool connections that are idle to 
> my_database.  
OpenSIPS will open one DB connection from each working process. So, more 
children, more DB connections.
> The problem that this is causing is that, if for some reason one of 
> the connections has an issue and dies or times out, this happens in 
> the logs:
>
> Apr 16 05:46:02 serinbound1 /sbin/opensips[30773]: 
> ERROR:db_postgres:db_postgres_store_result: 0x76e008 - invalid query, 
> execution aborted
> Apr 16 05:46:02 serinbound1 /sbin/opensips[30773]: 
> ERROR:db_postgres:db_postgres_store_result: 0x76e008: PGRES_FATAL_ERROR
> Apr 16 05:46:02 serinbound1 /sbin/opensips[30773]: 
> ERROR:db_postgres:db_postgres_store_result: 0x76e008: server closed 
> the connection unexpectedly#012#011This probably means the server 
> terminated abnormally#012#011before or while processing the request.#012
> Apr 16 05:46:02 serinbound1 /sbin/opensips[30773]: 
> ERROR:core:db_do_raw_query: error while storing result
> Apr 16 05:46:02 serinbound1 /sbin/opensips[30773]: 
> ERROR:avpops:db_query_avp: cannot do the query
> Apr 16 05:46:02 serinbound1 /sbin/opensips[30773]: Database Error!  No 
> Lookup!
> Apr 16 05:46:02 serinbound1 /sbin/opensips[30773]: Query Executed:  
> Account Number is <null>
>
> The query I'm executing (from the config): 
> avp_db_query("SELECT value FROM schema.table WHERE value = 
> '$avp(s:string_a)'", "$avp(s:string_b)");
>
> When these error messages pop up, pgpool still has active database 
> connections to postgres, but not held by opensips.  
Have you tried to see what is going on at postgres connection level ? 
why the query is invalid an how it closing the connection ? is there any 
error on the pool/server side?
> I can use one of the pooled connections to connect to the backend 
> database and execute a query, but for some reason opensips cannot.
>
> And, having a restart work (scripted), I occasionally see the following:
>
> Apr 16 03:59:05 serinbound2 /sbin/opensips[23105]: 
> ERROR:db_postgres:db_postgres_new_connection: server closed the 
> connection unexpectedly#012#011This probably means the server 
> terminated abnormally#012#011before or while processing the request.#012
> Apr 16 03:59:05 serinbound2 /sbin/opensips[23105]: 
> ERROR:db_postgres:db_postgres_new_connection: cleaning up 
> 0x76e090=pkg_free()
> Apr 16 03:59:05 serinbound2 /sbin/opensips[23105]: 
> ERROR:core:db_do_init: could not add connection to the pool
> Apr 16 03:59:05 serinbound2 /sbin/opensips[23105]: 
> ERROR:avpops:avpops_db_init: cannot initialize database connection
> Apr 16 03:59:05 serinbound2 /sbin/opensips[23105]: 
> ERROR:core:init_mod_child: failed to initializing module avpops, rank -1
> Apr 16 03:59:05 serinbound2 /sbin/opensips[23105]: 
> ERROR:core:start_timer_processes: init_child failed for timer proc
> Apr 16 03:59:05 serinbound2 /sbin/opensips[23083]: 
> INFO:core:handle_sigs: child process 23105 exited normally, status=255
> Apr 16 03:59:05 serinbound2 /sbin/opensips[23083]: 
> INFO:core:handle_sigs: terminating due to SIGCHLD
> Apr 16 03:59:05 serinbound2 /sbin/opensips[23107]: INFO:core:sig_usr: 
> signal 15 received
>
> At this point, the application crashes.
does not crash, it simply fails to start - if DB connection are 
required, they must be successfully setup at startup.
>
> Any suggestions or workarounds for this?  More specifically, I'd like 
> opensips to not have to grab the DB connection if it doesn't need it 
> at that time performing a lookup (as i'm not really using avp's in the 
> lookup, just to save the results)
failed to understand when you need or not the connection, but in 
opensips all the DB connection are created at starup and held for the 
whole runtime.
> , also, I'd like to see it not crash completely if it loses that 
> connection.  Instead, just send a error message back and allow me to 
> insert a statement like:
>
>                if(! what i expect the value of the saved avp from teh 
> database query to be)
>                 {
>                         sl_send_reply("500", "Internal Server Error");
>                         exit;
>                 }
well, this is should be the normal way - but do you experience crashes  
at runtime (please do not confuse  a crash with a failed startup due the 
init error).

Regards,
Bogdan
>
> Thanks for your help, I know this was fairly detailed but without much 
> in terms of logging.
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Users mailing list
> Users at lists.opensips.org
> http://lists.opensips.org/cgi-bin/mailman/listinfo/users
>