<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p><tt>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.</tt></p>
<p><tt>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.<br>
</tt></p>
<pre class="moz-signature" cols="72">Liviu Chircu
OpenSIPS Developer
<a class="moz-txt-link-freetext" href="http://www.opensips-solutions.com">http://www.opensips-solutions.com</a></pre>
<div class="moz-cite-prefix">On 17.04.2019 15:59, Jon Abrams wrote:<br>
</div>
<blockquote type="cite"
cite="mid:CAP9sNzGSMuOocprL-wwzgZffdFCRosvzoVJeTEqY7e5-j5W+og@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">
<div>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. <br>
</div>
<div><br>
</div>
<div>Alternatively you could try to do something with the
drouting module, which is designed for very large data sets.<br>
</div>
<div><br>
</div>
<div>- Jon Abrams<br>
</div>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Mon, Apr 8, 2019 at 12:58
PM John Kiniston <<a href="mailto:johnkiniston@gmail.com"
moz-do-not-send="true">johnkiniston@gmail.com</a>> wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div>
<div>
<div>Good Morning,<br>
<br>
</div>
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.<br>
</div>
<br>
</div>
If I attempt to use the full data set with 167424
rows opensips fails to start with a failure to query
the database.<br>
<br>
When I use a smaller data set of only 500 rows I'm
able to start opensips without errors.<br>
<br>
</div>
<div>I'm not seeing the query time out, if I run the
query by hand I get results back from MySQL?<br>
<br>
</div>
<div>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.<br>
<br>
</div>
<div dir="ltr"><br>
pr 8 13:37:50 federated-sip /sbin/opensips[4666]:
<a class="moz-txt-link-freetext" href="INFO:db_mysql:connect_with_retry">INFO:db_mysql:connect_with_retry</a>: re-connected
successful for 0x7f33bef25a18<br>
Apr 8 13:37:50 federated-sip /sbin/opensips[4666]:
DBG:db_mysql:mysql_raise_event: MySQL status has not
changed: connected<br>
Apr 8 13:37:50 federated-sip /sbin/opensips[4666]:
CRITICAL:db_mysql:db_mysql_submit_query: too many
mysql server reconnection failures<br>
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]<br>
Apr 8 13:37:50 federated-sip /sbin/opensips[4666]:
ERROR:dialplan:dp_load_db: failed to query database!<br>
Apr 8 13:37:50 federated-sip /sbin/opensips[4666]:
ERROR:dialplan:dp_load_all_db: unable to load
dialplan table<br>
Apr 8 13:37:50 federated-sip /sbin/opensips[4666]:
ERROR:dialplan:mi_reload_rules: failed to reload
database<br>
Apr 8 13:39:44 federated-sip /sbin/opensips[4666]:
DBG:mi_fifo:mi_parse_tree: adding node <> ;
val <9><br>
Apr 8 13:39:44 federated-sip /sbin/opensips[4666]:
DBG:mi_fifo:mi_parse_node: end of input tree<br>
Apr 8 13:39:44 federated-sip /sbin/opensips[4666]:
DBG:mi_fifo:mi_fifo_server: done parsing the mi tree<br>
Apr 8 13:39:57 federated-sip /sbin/opensips[4666]:
DBG:mi_fifo:mi_parse_node: end of input tree<br>
Apr 8 13:39:57 federated-sip /sbin/opensips[4666]:
DBG:mi_fifo:mi_fifo_server: done parsing the mi tree<br>
Apr 8 13:39:59 federated-sip /sbin/opensips[4666]:
<a class="moz-txt-link-freetext" href="INFO:db_mysql:switch_state_to_disconnected">INFO:db_mysql:switch_state_to_disconnected</a>:
disconnect event for 0x7f33bef25a18<br>
Apr 8 13:39:59 federated-sip /sbin/opensips[4666]:
<a class="moz-txt-link-freetext" href="INFO:db_mysql:reset_all_statements">INFO:db_mysql:reset_all_statements</a>: resetting all
statements on connection: (0x7f33bef26538)
0x7f33bef25a18<br>
Apr 8 13:39:59 federated-sip /sbin/opensips[4666]:
DBG:db_mysql:db_mysql_connect: opening connection:
mysql://<a
href="http://xxxx:xxxx@172.16.52.35/opensips"
target="_blank" moz-do-not-send="true">xxxx:xxxx@172.16.52.35/opensips</a><br>
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<br>
Apr 8 13:39:59 federated-sip /sbin/opensips[4666]:
DBG:db_mysql:db_mysql_connect: protocol version is
10<br>
Apr 8 13:39:59 federated-sip /sbin/opensips[4666]:
DBG:db_mysql:db_mysql_connect: server version is
5.5.60-MariaDB<br>
Apr 8 13:39:59 federated-sip /sbin/opensips[4666]:
<a class="moz-txt-link-freetext" href="INFO:db_mysql:connect_with_retry">INFO:db_mysql:connect_with_retry</a>: re-connected
successful for 0x7f33bef25a18<br>
Apr 8 13:40:01 federated-sip /sbin/opensips[4666]:
<a class="moz-txt-link-freetext" href="INFO:db_mysql:switch_state_to_disconnected">INFO:db_mysql:switch_state_to_disconnected</a>:
disconnect event for 0x7f33bef25a18<br>
Apr 8 13:40:01 federated-sip /sbin/opensips[4666]:
<a class="moz-txt-link-freetext" href="INFO:db_mysql:reset_all_statements">INFO:db_mysql:reset_all_statements</a>: resetting all
statements on connection: (0x7f33bef26538)
0x7f33bef25a18<br>
Apr 8 13:40:01 federated-sip /sbin/opensips[4666]:
DBG:db_mysql:db_mysql_connect: opening connection:
mysql://<a
href="http://xxxx:xxxx@172.16.52.35/opensips"
target="_blank" moz-do-not-send="true">xxxx:xxxx@172.16.52.35/opensips</a><br>
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<br>
Apr 8 13:40:01 federated-sip /sbin/opensips[4666]:
DBG:db_mysql:db_mysql_connect: protocol version is
10<br>
Apr 8 13:40:01 federated-sip /sbin/opensips[4666]:
DBG:db_mysql:db_mysql_connect: server version is
5.5.60-MariaDB<br>
Apr 8 13:40:01 federated-sip /sbin/opensips[4666]:
<a class="moz-txt-link-freetext" href="INFO:db_mysql:connect_with_retry">INFO:db_mysql:connect_with_retry</a>: re-connected
successful for 0x7f33bef25a18<br>
Apr 8 13:40:01 federated-sip /sbin/opensips[4666]:
DBG:db_mysql:mysql_raise_event: MySQL status has not
changed: connected<br>
Apr 8 13:40:01 federated-sip /sbin/opensips[4666]:
CRITICAL:db_mysql:db_mysql_submit_query: too many
mysql server reconnection failures<br>
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]<br>
Apr 8 13:40:01 federated-sip /sbin/opensips[4666]:
ERROR:dialplan:dp_load_db: failed to query database!<br>
Apr 8 13:40:01 federated-sip /sbin/opensips[4666]:
ERROR:dialplan:dp_load_all_db: unable to load
dialplan table<br>
Apr 8 13:40:01 federated-sip /sbin/opensips[4666]:
ERROR:dialplan:mi_reload_rules: failed to reload
database<br>
<br clear="all">
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>-- <br>
<div dir="ltr"
class="gmail-m_-2320689943070734797gmail_signature">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.<br>
---Heinlein</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
_______________________________________________<br>
Users mailing list<br>
<a href="mailto:Users@lists.opensips.org" target="_blank"
moz-do-not-send="true">Users@lists.opensips.org</a><br>
<a
href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users"
rel="noreferrer" target="_blank" moz-do-not-send="true">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a><br>
</blockquote>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
Users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Users@lists.opensips.org">Users@lists.opensips.org</a>
<a class="moz-txt-link-freetext" href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a>
</pre>
</blockquote>
</body>
</html>