[SR-Users] MYSQL and various

[Henning Westerholt]

Am Montag, 30. Juli 2018, 09:50:04 CEST schrieb Laura:
> on my kamailio 4.4.7 i use interactions with MYSQL.
> 
> For example .. i use it for some black list control and other like this..
> 
>          sql_query("cb", "select id from blacklist where cli='$var(g2)'
> and (ippeer = '$si' or ippeer = 'ALL')", "rabl");
>          if($dbr(rabl=>rows)>0){
>                  sql_result_free("rabl");
>                  sl_send_reply("403","Forbidden blacklist");
>                  exit;
>          }
>          sql_result_free("rabl");
> 
> 
> I was wonder what was the best method to prevent the query going on if
> mysql don't respond for high load or other situation..
> 
> If Mysql is not able to reply to the query tight now the call continue
> over the configuration... what is the best way to prevent this and for
> examples drop it with  a standard SIP 503 error.
> 
> I have search on docs.. but i haven't found any valid way.

Hello Laura,

if I understand you correctly you want to prevent issues that are caused from 
a blocking of the SIP worker by database operations.

It depends a bit on the details of your setup, but I'd suggest that you look 
into the following topics:

- setting a low mysql timeout_interval (in db_mysql module)
- investigate if maybe the async module is helpful here
- investigating a machine local mysql read-only replication

Alternatively you could just use one of the several modules that offer a in-
memory blacklist, to avoid the SQL query for this functionality:

- userblacklist
- htable (as universal data structure)
- carrierroute and probably also lcr

Best regards,

Henning

-- 
Henning Westerholt
https://skalatan.de/blog/