[OpenSIPS-Users] Working with a dataset larger than dialplan will load.

Mon Apr 22 02:47:48 EDT 2019

I second this -- strictly discussing implementation, dialplan performs a 
simple (and costly) iteration over up-to-all matching rules for each 
lookup, while drouting builds an internal trie structure which will 
dramatically improve lookup latencies.

Still, regarding the original problem:  are there no additional errors 
which may be relevant?  If yes, then you may have a MySQL server 
configuration issue (notice how it's not able to return the data).  
Tuning settings like "max_allowed_packet" might fix this.

Liviu Chircu
OpenSIPS Developer
http://www.opensips-solutions.com

On 17.04.2019 15:59, Jon Abrams wrote:
> If that's the LCAD or LERG databases, I'd put the data in Redis and 
> query that. That will give you faster startup times, and won't add 
> much of any noticeable latency.
>
> Alternatively you could try to do something with the drouting module, 
> which is designed for very large data sets.
>
> - Jon Abrams
>
> On Mon, Apr 8, 2019 at 12:58 PM John Kiniston <johnkiniston at gmail.com 
> <mailto:johnkiniston at gmail.com>> wrote:
>
>     Good Morning,
>
>     I am attempting to use dialplan to lookup rate centers on dialed
>     calls to determine if a caller is dialing outside it's local area.
>
>     If I attempt to use the full data set with 167424 rows opensips
>     fails to start with a failure to query the database.
>
>     When I use a smaller data set of only 500 rows I'm able to start
>     opensips without errors.
>
>     I'm not seeing the query time out, if I run the query by hand I
>     get results back from MySQL?
>
>     Is there a different module I should be attempting to do this
>     with? I was planning to look up the NPANXX pairing for the source
>     and destination of the call and check against the attrs column
>     where I stored the ratecenter.
>
>
>     pr  8 13:37:50 federated-sip /sbin/opensips[4666]:
>     INFO:db_mysql:connect_with_retry: re-connected successful for
>     0x7f33bef25a18
>     Apr  8 13:37:50 federated-sip /sbin/opensips[4666]:
>     DBG:db_mysql:mysql_raise_event: MySQL status has not changed:
>     connected
>     Apr  8 13:37:50 federated-sip /sbin/opensips[4666]:
>     CRITICAL:db_mysql:db_mysql_submit_query: too many mysql server
>     reconnection failures
>     Apr  8 13:37:50 federated-sip /sbin/opensips[4666]:
>     ERROR:core:db_do_query: error while submitting query - [select
>     dpid,pr,match_op,match_exp,match_flags,subst_exp,repl_exp,attrs,timerec
>     from dialplan where disabled=0 order by pr]
>     Apr  8 13:37:50 federated-sip /sbin/opensips[4666]:
>     ERROR:dialplan:dp_load_db: failed to query database!
>     Apr  8 13:37:50 federated-sip /sbin/opensips[4666]:
>     ERROR:dialplan:dp_load_all_db: unable to load dialplan table
>     Apr  8 13:37:50 federated-sip /sbin/opensips[4666]:
>     ERROR:dialplan:mi_reload_rules: failed to reload database
>     Apr  8 13:39:44 federated-sip /sbin/opensips[4666]:
>     DBG:mi_fifo:mi_parse_tree: adding node <> ; val <9>
>     Apr  8 13:39:44 federated-sip /sbin/opensips[4666]:
>     DBG:mi_fifo:mi_parse_node: end of input tree
>     Apr  8 13:39:44 federated-sip /sbin/opensips[4666]:
>     DBG:mi_fifo:mi_fifo_server: done parsing the mi tree
>     Apr  8 13:39:57 federated-sip /sbin/opensips[4666]:
>     DBG:mi_fifo:mi_parse_node: end of input tree
>     Apr  8 13:39:57 federated-sip /sbin/opensips[4666]:
>     DBG:mi_fifo:mi_fifo_server: done parsing the mi tree
>     Apr  8 13:39:59 federated-sip /sbin/opensips[4666]:
>     INFO:db_mysql:switch_state_to_disconnected: disconnect event for
>     0x7f33bef25a18
>     Apr  8 13:39:59 federated-sip /sbin/opensips[4666]:
>     INFO:db_mysql:reset_all_statements: resetting all statements on
>     connection: (0x7f33bef26538) 0x7f33bef25a18
>     Apr  8 13:39:59 federated-sip /sbin/opensips[4666]:
>     DBG:db_mysql:db_mysql_connect: opening connection:
>     mysql://xxxx:xxxx@172.16.52.35/opensips
>     <http://xxxx:xxxx@172.16.52.35/opensips>
>     Apr  8 13:39:59 federated-sip /sbin/opensips[4666]:
>     DBG:db_mysql:db_mysql_connect: connection type is 172.16.52.35 via
>     TCP/IP
>     Apr  8 13:39:59 federated-sip /sbin/opensips[4666]:
>     DBG:db_mysql:db_mysql_connect: protocol version is 10
>     Apr  8 13:39:59 federated-sip /sbin/opensips[4666]:
>     DBG:db_mysql:db_mysql_connect: server version is 5.5.60-MariaDB
>     Apr  8 13:39:59 federated-sip /sbin/opensips[4666]:
>     INFO:db_mysql:connect_with_retry: re-connected successful for
>     0x7f33bef25a18
>     Apr  8 13:40:01 federated-sip /sbin/opensips[4666]:
>     INFO:db_mysql:switch_state_to_disconnected: disconnect event for
>     0x7f33bef25a18
>     Apr  8 13:40:01 federated-sip /sbin/opensips[4666]:
>     INFO:db_mysql:reset_all_statements: resetting all statements on
>     connection: (0x7f33bef26538) 0x7f33bef25a18
>     Apr  8 13:40:01 federated-sip /sbin/opensips[4666]:
>     DBG:db_mysql:db_mysql_connect: opening connection:
>     mysql://xxxx:xxxx@172.16.52.35/opensips
>     <http://xxxx:xxxx@172.16.52.35/opensips>
>     Apr  8 13:40:01 federated-sip /sbin/opensips[4666]:
>     DBG:db_mysql:db_mysql_connect: connection type is 172.16.52.35 via
>     TCP/IP
>     Apr  8 13:40:01 federated-sip /sbin/opensips[4666]:
>     DBG:db_mysql:db_mysql_connect: protocol version is 10
>     Apr  8 13:40:01 federated-sip /sbin/opensips[4666]:
>     DBG:db_mysql:db_mysql_connect: server version is 5.5.60-MariaDB
>     Apr  8 13:40:01 federated-sip /sbin/opensips[4666]:
>     INFO:db_mysql:connect_with_retry: re-connected successful for
>     0x7f33bef25a18
>     Apr  8 13:40:01 federated-sip /sbin/opensips[4666]:
>     DBG:db_mysql:mysql_raise_event: MySQL status has not changed:
>     connected
>     Apr  8 13:40:01 federated-sip /sbin/opensips[4666]:
>     CRITICAL:db_mysql:db_mysql_submit_query: too many mysql server
>     reconnection failures
>     Apr  8 13:40:01 federated-sip /sbin/opensips[4666]:
>     ERROR:core:db_do_query: error while submitting query - [select
>     dpid,pr,match_op,match_exp,match_flags,subst_exp,repl_exp,attrs,timerec
>     from dialplan where disabled=0 order by pr]
>     Apr  8 13:40:01 federated-sip /sbin/opensips[4666]:
>     ERROR:dialplan:dp_load_db: failed to query database!
>     Apr  8 13:40:01 federated-sip /sbin/opensips[4666]:
>     ERROR:dialplan:dp_load_all_db: unable to load dialplan table
>     Apr  8 13:40:01 federated-sip /sbin/opensips[4666]:
>     ERROR:dialplan:mi_reload_rules: failed to reload database
>
>     -- 
>     A human being should be able to change a diaper, plan an invasion,
>     butcher a hog, conn a ship, design a building, write a sonnet,
>     balance accounts, build a wall, set a bone, comfort the dying,
>     take orders, give orders, cooperate, act alone, solve equations,
>     analyze a new problem, pitch manure, program a computer, cook a
>     tasty meal, fight efficiently, die gallantly. Specialization is
>     for insects.
>     ---Heinlein
>     _______________________________________________
>     Users mailing list
>     Users at lists.opensips.org <mailto:Users at lists.opensips.org>
>     http://lists.opensips.org/cgi-bin/mailman/listinfo/users
>
>
> _______________________________________________
> Users mailing list
> Users at lists.opensips.org
> http://lists.opensips.org/cgi-bin/mailman/listinfo/users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.opensips.org/pipermail/users/attachments/20190422/1025e164/attachment.html>