<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>