Hello,
I'm using the sqlops module for some custom queries against mysql. Sometimes during a cluster failure the mysqld nodes will hang there waiting for the backend to finish up whatever it's doing, it still accepts the connection and the query but doesn't return results. I have a pair of F5 load balancers in front of the two sql nodes, so there is redundancy as long as the cluster is operational. I'd prefer to keep the cross-site failover in kamailio.
I looked through the docs on sqlops and I can't find any reference to a timeout value. I'd like to implement a failover in the script to my other database cluster but if kam waits for a long time before returning a negative it may not work very well. Consider this code in my script for 911 services in my US offices: (I rewrite the rpid/pai and ruri based on IP address then send it to my provider(s))
modparam("sqlops","sqlcon","gokam=>mysql://*****:*****@172.20.180.21/sip _gokam")
if (!sql_query("gokam", "select location,cidname,cidnum,ciddomain,e911number,inet_ntoa(network) as network,inet_ntoa(netmask) \
as netmask from e911 where (inet_aton('$si') & netmask) = network", "result")) {
sl_send_reply("500", "Database error");
exit;
}
What I'd like to do is:
modparam("sqlops","sqlcon","gokam=>mysql://****:****@172.20.180.21/sip_g okam")
modparam("sqlops","sqlcon","gokam_site2=>mysql://*****:*****@172.23.180. 21/sip_gokam")
if (!sql_query("gokam", "select location,cidname,cidnum,ciddomain,e911number,inet_ntoa(network) as network,inet_ntoa(netmask) \
as netmask from e911 where (inet_aton('$si') & netmask) = network", "result")) {
xlog("L_CRIT", "Primary database failure, using alternate\n");
if (!sql_query("gokam_site2", "select location,cidname,cidnum,ciddomain,e911number,inet_ntoa(network) as network,inet_ntoa(netmask) \
as netmask from e911 where (inet_aton('$si') & netmask) = network", "result")) {
sl_send_reply("500", Database error");
exit;
}
What is the default timeout for sql_query before it returns a negative, is it configurable?
Thanks!
Bob
-------------------------------------------------------------------------- This email with all information contained herein or attached hereto may contain confidential and/or privileged information intended for the addressee(s) only. If you have received this email in error, please contact the sender and immediately delete this email in its entirety and any attachments thereto.
Robert,
Such parameters are specific to the database backend module used by sqlops, if they exist at all. Thus, you should consult the db_mysql documentation; sqlops is an abstraction layer over the underlying database connection facility.
It sounds like this is what you are looking for:
http://www.kamailio.org/docs/modules/1.5.x/db_mysql.html#id2452828
Cheers,
-- Alex
On 03/29/2010 08:58 AM, Robert McGilvray wrote:
Hello,
I’m using the sqlops module for some custom queries against mysql. Sometimes during a cluster failure the mysqld nodes will hang there waiting for the backend to finish up whatever it’s doing, it still accepts the connection and the query but doesn’t return results. I have a pair of F5 load balancers in front of the two sql nodes, so there is redundancy as long as the cluster is operational. I’d prefer to keep the cross-site failover in kamailio.
I looked through the docs on sqlops and I can’t find any reference to a timeout value. I’d like to implement a failover in the script to my other database cluster but if kam waits for a long time before returning a negative it may not work very well. Consider this code in my script for 911 services in my US offices: (I rewrite the rpid/pai and ruri based on IP address then send it to my provider(s))
modparam("sqlops","sqlcon","gokam=>mysql://*****:*****@172.20.180.21/sip_gokam")
if (!sql_query("gokam", "select location,cidname,cidnum,ciddomain,e911number,inet_ntoa(network) as network,inet_ntoa(netmask) \
as netmask from e911 where (inet_aton('$si') & netmask) = network", "result")) {
sl_send_reply("500", "Database error");
exit;
}
What I’d like to do is:
modparam("sqlops","sqlcon","gokam=>mysql://****:****@172.20.180.21/sip_gokam")
modparam("sqlops","sqlcon","gokam_site2=>mysql://*****:*****@172.23.180.21/sip_gokam")
if (!sql_query("gokam", "select location,cidname,cidnum,ciddomain,e911number,inet_ntoa(network) as network,inet_ntoa(netmask) \
as netmask from e911 where (inet_aton('$si') & netmask) = network", "result")) {
xlog(“L_CRIT”, “Primary database failure, using alternate\n”);
if (!sql_query(“gokam_site2”, “select location,cidname,cidnum,ciddomain,e911number,inet_ntoa(network) as network,inet_ntoa(netmask) \
as netmask from e911 where (inet_aton('$si') & netmask) = network", "result")) {
sl_send_reply(“500”, Database error”);
exit;
}
What is the default timeout for sql_query before it returns a negative, is it configurable?
Thanks!
Bob
This email with all information contained herein or attached hereto may contain confidential and/or privileged information intended for the addressee(s) only. If you have received this email in error, please contact the sender and immediately delete this email in its entirety and any attachments thereto.
Kamailio (OpenSER) - Users mailing list Users@lists.kamailio.org http://lists.kamailio.org/cgi-bin/mailman/listinfo/users http://lists.openser-project.org/cgi-bin/mailman/listinfo/users
On 3/29/10 3:04 PM, Alex Balashov wrote:
Robert,
Such parameters are specific to the database backend module used by sqlops, if they exist at all. Thus, you should consult the db_mysql documentation; sqlops is an abstraction layer over the underlying database connection facility.
It sounds like this is what you are looking for:
http://www.kamailio.org/docs/modules/1.5.x/db_mysql.html#id2452828
For some reason the readme for 3.0 was not built on web site, should be fixed. The parameters are the same as for 1.5.
Daniel
Cheers,
-- Alex
On 03/29/2010 08:58 AM, Robert McGilvray wrote:
Hello,
I’m using the sqlops module for some custom queries against mysql. Sometimes during a cluster failure the mysqld nodes will hang there waiting for the backend to finish up whatever it’s doing, it still accepts the connection and the query but doesn’t return results. I have a pair of F5 load balancers in front of the two sql nodes, so there is redundancy as long as the cluster is operational. I’d prefer to keep the cross-site failover in kamailio.
I looked through the docs on sqlops and I can’t find any reference to a timeout value. I’d like to implement a failover in the script to my other database cluster but if kam waits for a long time before returning a negative it may not work very well. Consider this code in my script for 911 services in my US offices: (I rewrite the rpid/pai and ruri based on IP address then send it to my provider(s))
modparam("sqlops","sqlcon","gokam=>mysql://*****:*****@172.20.180.21/sip_gokam")
if (!sql_query("gokam", "select location,cidname,cidnum,ciddomain,e911number,inet_ntoa(network) as network,inet_ntoa(netmask) \
as netmask from e911 where (inet_aton('$si') & netmask) = network", "result")) {
sl_send_reply("500", "Database error");
exit;
}
What I’d like to do is:
modparam("sqlops","sqlcon","gokam=>mysql://****:****@172.20.180.21/sip_gokam")
modparam("sqlops","sqlcon","gokam_site2=>mysql://*****:*****@172.23.180.21/sip_gokam")
if (!sql_query("gokam", "select location,cidname,cidnum,ciddomain,e911number,inet_ntoa(network) as network,inet_ntoa(netmask) \
as netmask from e911 where (inet_aton('$si') & netmask) = network", "result")) {
xlog(“L_CRIT”, “Primary database failure, using alternate\n”);
if (!sql_query(“gokam_site2”, “select location,cidname,cidnum,ciddomain,e911number,inet_ntoa(network) as network,inet_ntoa(netmask) \
as netmask from e911 where (inet_aton('$si') & netmask) = network", "result")) {
sl_send_reply(“500”, Database error”);
exit;
}
What is the default timeout for sql_query before it returns a negative, is it configurable?
Thanks!
Bob
This email with all information contained herein or attached hereto may contain confidential and/or privileged information intended for the addressee(s) only. If you have received this email in error, please contact the sender and immediately delete this email in its entirety and any attachments thereto.
Kamailio (OpenSER) - Users mailing list Users@lists.kamailio.org http://lists.kamailio.org/cgi-bin/mailman/listinfo/users http://lists.openser-project.org/cgi-bin/mailman/listinfo/users
-
Alex Balashov -
Daniel-Constantin Mierla -
Robert McGilvray