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

[Jon Abrams]

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>
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 at 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 at 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
> http://lists.opensips.org/cgi-bin/mailman/listinfo/users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.opensips.org/pipermail/users/attachments/20190417/6f59c34b/attachment-0001.html>