Dynamic Routes FAQ
General FAQ
Q. What are the prerequisites for Dynamic Routes?
A. The Dynamic Route module itself does not introduce additional prerequisites, so if your system is set up for Asterisk and FreePBX this module can be installed without adding any additional software. However, the lookup method you choose can introduce additional prerequisites.
If you are using Dynamic Routes to connect to a MySQL database directly (without ODBC) then an additional Asterisk module is required (app_mysql). Please note that the app_mysql is not built by default. Please see this link for information. Although app_mysql is still widely used, it is no longer the recommended way to connect to the MySQL database from Asterisk.
If you are using Dynamic Routes with ODBC you will need to install the ODBC software and driver for your database and configure it. Please see this link for information.
Q. Does Dynamic Routes effect performance?
A. For most installations that are not under heavy load, there will not be performance concerns. However in high traffic installations, the performance aspect should be considered. Of course if the mysql query is not correctly tuned, then even low traffic sites will have issues. So first of all, the query to be used should be checked on a database with similar volumes as the production environment to ensure that it completes quickly. Any tuning of the query or needed indexes should be added.
Instead of running the query against an online database, you may also consider a batch procedure which periodically extracts the needed information, reorganized in the optimum way and then load into a table where asterisk will specifically query it.
Performance will be enhanced by using a local database instead of a remote database. Performance is also enhanced by using a non-SSL connection to the database, but this is not advisable for a remote database due to security concerns - see below. Even for a local database there may be security concerns of using a non-SSL connection, depending on who may access the server.
When connecting to a database Dynamic Routes can use MySQL directly (using Asterisk app_mysql) or ODBC. ODBC is now the recommended way of accessing a database from Asterisk. It is reported that the level of indirection does not introduce any noticeable performance issue.
Q. Does Dynamic Routes effect system availability?
A. With Dynamic Routes a database or a web service can be used for lookups during incoming calls. Therefore the database or web server become a necessary component for call processing. If the server is unavailable, dynamic routing will not take place. You may however define a default destination. When a lookup fails it will follow the default destination. The default destination is also used when the lookup succeeds but no match is found. Using a local database or web server reduces availability dependencies but does not remove them entirely.
Q. What are the security implications of using Dynamic Routes?
A. The asterisk mysql add-on does not support SSL encrypted connections, so information (including credentials) passed between asterisk and the database may be intercepted. That is one reason why a local database is recommended. If your installation must use a remote server, consider whether a better option, also from performance and availability perspectives, would be to periodically copy the data needed into the mysql database running on the asterisk server.
If you must use a remote server, then it is recommended (unless you can setup a local LAN between the two servers without risk of interception) to use ODBC with SSL enabled. An alternative is to setup an encrypted tunnel using a VPN or a software like stunnel.
In any case it is recommended that the database be separate to the ones used for FreePBX configurations and CDRs. It is also recommended that a specific user is setup in order to access the database and that it has only select permissions on the tables that are used in the query. You should avoid using tables in the query that contain sensitive data, such as passwords. Consider defining a view if you have this case, which only gives access to the relevant columns needed. On MySQL for performance reasons that impact the whole server avoid using grant statements that contain column level access.
Q. What does the message <<Module "Dynamic Routes" is unsigned and should be re-downloaded>> mean?
A. FreePBX now has a system whereby a hash of the source code is computed and signed by Sangoma. See this link for more information. This functionality is extremely useful because users know that the modules are the official versions and (unless the verification code itself is tampered with) they know that the code has not been modified.
While very useful, there are a couple of implications:
- Third party modules will show an unsigned module warning
- Locally modified modules (for example to add a bug fix or new feature while waiting for that to be included in the official version) produce a tampered module warning
Dynamic Routes is not part of the FreePBX core release and therefore the code is not signed by Sangoma. Although Sangoma provides for third party code signing after execution of a contract, at this time I have not applied for code signing rights.
Configuration FAQ
Q. How can I avoid matching short caller ids?
A. You can use a condition in the WHERE clause to ensure that only caller ids of sufficient length are matched. This is useful if your database contains company phone numbers with similar prefixes, but when receiving calls the caller id is set to the shorter main phone number of the company.
select destination from callerid_table where calleridnum like '%[NUMBER]' and length('[NUMBER]') > 4
Q. How can I match data in my database when sometimes the caller id arrives with an international prefix of + country codes, other times 00 country code and other times just as a national number?
A. You may use the MySQL TRIM function to adjust the presented caller id to match the type of data in your database. For example:
select destination from callerid_table where calleridnum like concat('%',TRIM(LEADING '+' FROM TRIM(LEADING '0' FROM '[NUMBER]')))
Q. Does Dynamic Route create the lookup table I will use in my query?
A. No. Dynamic Route creates the internal tables needed to store the configurations of the Dynamic Routes that you define. Specific Dynamic Routes may be configured to access a database table containing data that will be used in the routing decision. This data is external to Dynamic Routes and Dynamic Routes will only query this data as directed to do so by your configuration. Dynamic Routes will not create your external data tables or data. If you need an example of how to set up data tables to be queried by Dynamic Route, then please see this link for information.
Q. Why don't my inbound calls get routed through Dynamic Routes?
A. Dynamic Routes does not automatically intervene in the call processing of your system. If you want calls to pass through Dynamic Routes you will need to configure your system to hand the calls to a specific Dynamic Route. Typically you can do this from the FreePBX "Connectivity" menu by choosing "Inbound Routes". Choose the inbound route you want to pass through Dynamic Route and in that route put the Dynamic Route as the destination. Call flow will then pass through the specified Dynamic Route.
Trouble shooting FAQ
When reporting a problem what information should I provide?
Please see here
Q. Why do I get strange errors when using asterisk MYSQL module?
[2015-04-24 21:52:59] WARNING[14294][C-000020b6] pbx.c: Error in extension logic (missing '}')
[2015-04-24 21:52:59] WARNING[14294][C-000020b6] pbx.c: Can't find trailing parenthesis for function 'CALLERID(nu'?
[2015-04-24 21:52:59] ERROR[14294][C-000020b6] func_callerid.c: Unknown callerid data type 'nu'.
[2015-04-24 21:52:59] VERBOSE[14294][C-000020b6] pbx.c: -- Executing [s@dynroute-21:4] MYSQL("SIP/215-00000087", "Query resultid 1 select callerid_firstname from calleridlookup where owner='john' and '") in new stack
[2015-04-24 21:52:59] WARNING[14294][C-000020b6] app_mysql.c: aMYSQL_query: mysql_query failed. Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1
[2015-04-24 21:52:59] VERBOSE[14294][C-000020b6] pbx.c: -- Executing [s@dynroute-21:5] MYSQL("SIP/215-00000087", "Fetch fetchid dynroute") in new stack
[2015-04-24 21:52:59] WARNING[14294][C-000020b6] app_mysql.c: aMYSQL_fetch: missing some arguments
[2015-04-24 21:52:59] VERBOSE[14294][C-000020b6] pbx.c: -- Executing [s@dynroute-21:6] MYSQL("SIP/215-00000087", "Clear ") in new stack
[2015-04-24 21:52:59] WARNING[14294][C-000020b6] app_mysql.c: Identifier 0, identifier_type 2 not found in identifier list
[2015-04-24 21:52:59] WARNING[14294][C-000020b6] app_mysql.c: Invalid result identifier 0 passed in aMYSQL_clear
[2015-04-24 21:52:59] VERBOSE[14294][C-000020b6] pbx.c: -- Executing [s@dynroute-21:7] MYSQL("SIP/215-00000087", "Disconnect 1") in new stack
[2015-04-24 21:52:59] VERBOSE[14294][C-000020b6] pbx.c: -- Executing [s@dynroute-21:8] Set("SIP/215-00000087", "__DYNROUTE_TEST=") in new stack
[2015-04-24 21:52:59] WARNING[14294][C-000020b6] ast_expr2.fl: ast_yyerror(): syntax error: syntax error, unexpected '=', expecting $end; Input:>
A. Do not use a semicolon at the end of the query. This syntax is not supported by asterisk MYSQL module.