<div dir="ltr"><div>Thanks guys, I was moving down the path of using redis as an alternative to the dialplan module and it sounds like that is the right direction to go.<br><br></div>Liviu, There were not any additional errors, I'll see if I saved a copy of the VM I was exploring using dialplan on and look at the MySQL side of things.<br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, Apr 21, 2019 at 11:51 PM Liviu Chircu <<a href="mailto:liviu@opensips.org">liviu@opensips.org</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 bgcolor="#FFFFFF">
<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="gmail-m_2487433457824477026moz-signature" cols="72">Liviu Chircu
OpenSIPS Developer
<a class="gmail-m_2487433457824477026moz-txt-link-freetext" href="http://www.opensips-solutions.com" target="_blank">http://www.opensips-solutions.com</a></pre>
<div class="gmail-m_2487433457824477026moz-cite-prefix">On 17.04.2019 15:59, Jon Abrams wrote:<br>
</div>
<blockquote type="cite">
<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" target="_blank">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="gmail-m_2487433457824477026moz-txt-link-freetext">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="gmail-m_2487433457824477026moz-txt-link-freetext">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="gmail-m_2487433457824477026moz-txt-link-freetext">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">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="gmail-m_2487433457824477026moz-txt-link-freetext">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="gmail-m_2487433457824477026moz-txt-link-freetext">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="gmail-m_2487433457824477026moz-txt-link-freetext">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">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="gmail-m_2487433457824477026moz-txt-link-freetext">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_2487433457824477026gmail-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">Users@lists.opensips.org</a><br>
<a href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users" rel="noreferrer" target="_blank">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a><br>
</blockquote>
</div>
<br>
<fieldset class="gmail-m_2487433457824477026mimeAttachmentHeader"></fieldset>
<pre class="gmail-m_2487433457824477026moz-quote-pre">_______________________________________________
Users mailing list
<a class="gmail-m_2487433457824477026moz-txt-link-abbreviated" href="mailto:Users@lists.opensips.org" target="_blank">Users@lists.opensips.org</a>
<a class="gmail-m_2487433457824477026moz-txt-link-freetext" href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users" target="_blank">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a>
</pre>
</blockquote>
</div>
_______________________________________________<br>
Users mailing list<br>
<a href="mailto:Users@lists.opensips.org" target="_blank">Users@lists.opensips.org</a><br>
<a href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users" rel="noreferrer" target="_blank">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a><br>
</blockquote></div><br clear="all"><br>-- <br><div dir="ltr" class="gmail_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>