ODBC Asterisk freezing

Asterisk Asterisk Support
1

Hello everyone,

I am having an issue with Asterisk and ODBC. I am using Asterisk/ODBC to fetch names and phone numbers out of a MySQL database, and allowing the user to be transferred to the person of their choice. However, I am running into some issues with connecting to the database.

Most of the time, ODBC will connect flawlessly to the MySQL server with no hanging or hiccups at all. However, after around 40 minutes to an hour of inactivity, Asterisk will hang completely when trying to connect to the database. It will not process any requests for around 15 or 16 minutes while it waits to hear something from ODBC. Now the caller isn’t going to wait that long and will hang up, which causes other issues because Asterisk does not recognize the user hanging up during this 15-16 minute hang. However, if you wait on the line during these 15-16 minutes, asterisk will reconnect and everything will happen normally.

One more interesting piece of information is if I am using the MySQL CLI on the same machine asterisk is on, and I leave that idle for the same period, the query will hang and I am forced to CTRL+C out. I am not sure if this indicates something on the SQL server side, or not.

I am running out of ideas. I have been making changes to my ODBC ini file to to try and add in idle checks to try and ease some connection timeout problems, but nothing has worked. Here is all of my information:

Asterisk Version: 1.8.20.0 on CentOS (Installed via YUM, not by me)
unixODBC version : 2.2.14
/etc/odbc.ini :

[quote=“/etc/odbc.ini”][eivr]
Description = MySQLconnection for asterisk eivr
Driver = MySQL
Database = DATABASE
Server = SERVER
User = USERNAME
Password = PASSWORD
Port = PORT
option = 4194304
readtimeout = 15
[/quote]

/etc/asterisk/res_odbc.conf :

[quote=“/etc/asterisk/res_odbc.conf”][eivr]
enabled => yes
dsn => eivr
username => USERNAME
password => PASSWORD
pre-connect => no
idlecheck => 300
connect_timeout =>5
negative_connection_cache => 10
sanitysql => select 1
[/quote]

ODBC Trace File:

Asterisk CLI Errors :

[quote=“Asterisk CLI”][Mar 4 16:22:19] DEBUG[1569]: pbx.c:4470 pbx_extension_helper: Launching ‘GotoIf’
– Executing [1@Avaya-vm:2] GotoIf(“SIP/Avaya-vm-00000012”, “0?error,5,1”) in new stack
[Mar 4 16:22:19] DEBUG[1569]: pbx.c:10423 pbx_builtin_gotoif: Not taking any branch
[Mar 4 16:22:19] DEBUG[1569]: pbx.c:3479 ast_str_retrieve_variable: Result of ‘myVar’ is ‘226277’
[Mar 4 16:22:19] DEBUG[1569]: pbx.c:4065 ast_str_substitute_variables_full: Evaluating ‘ARG1’ (from ‘ARG1}%’ AND number LIKE ‘(XXX) XXX-%’) ORDER BY lastname’ len 4)
[Mar 4 16:22:19] DEBUG[1569]: pbx.c:3479 ast_str_retrieve_variable: Result of ‘ARG1’ is ‘226277’
[Mar 4 16:22:19] DEBUG[27008]: res_rtp_asterisk.c:1547 process_dtmf_rfc2833: - RTP 2833 Event: 00000007 (len = 4)
[Mar 4 16:22:19] DEBUG[27008]: res_rtp_asterisk.c:1547 process_dtmf_rfc2833: - RTP 2833 Event: 00000007 (len = 4)
[Mar 4 16:22:19] DEBUG[27008]: res_rtp_asterisk.c:1547 process_dtmf_rfc2833: - RTP 2833 Event: 00000007 (len = 4)
[Mar 4 16:22:19] DEBUG[27008]: res_rtp_asterisk.c:1547 process_dtmf_rfc2833: - RTP 2833 Event: 00000007 (len = 4)
[Mar 4 16:22:19] DEBUG[27008]: res_rtp_asterisk.c:1547 process_dtmf_rfc2833: - RTP 2833 Event: 00000007 (len = 4)
[Mar 4 16:22:19] DEBUG[27008]: res_rtp_asterisk.c:1547 process_dtmf_rfc2833: - RTP 2833 Event: 00000007 (len = 4)
[Mar 4 16:22:19] DEBUG[27008]: res_rtp_asterisk.c:1547 process_dtmf_rfc2833: - RTP 2833 Event: 00000007 (len = 4)
[Mar 4 16:22:19] DEBUG[27008]: res_rtp_asterisk.c:1547 process_dtmf_rfc2833: - RTP 2833 Event: 00000007 (len = 4)
[Mar 4 16:22:19] DEBUG[27008]: res_rtp_asterisk.c:1547 process_dtmf_rfc2833: - RTP 2833 Event: 00000007 (len = 4)
[Mar 4 16:22:21] DEBUG[27008]: res_rtp_asterisk.c:1807 ast_rtcp_read: Got RTCP report of 104 bytes
[Mar 4 16:22:26] DEBUG[27008]: res_rtp_asterisk.c:1807 ast_rtcp_read: Got RTCP report of 80 bytes
… …
[Mar 4 16:37:45] WARNING[1569]: res_odbc.c:1406 _ast_odbc_request_obj2: SetConnectAttr (Txn isolation) returned an error: HY000: [MySQL][ODBC 5.1 Driver]Lost connection to MySQL server during query
[Mar 4 16:37:45] DEBUG[1569]: res_odbc.c:1497 odbc_obj_disconnect: Disconnected 0 from eivr [eivr]
[Mar 4 16:37:45] DEBUG[1569]: res_odbc.c:1504 odbc_obj_disconnect: Database handle 0xb7415c40 deallocated
[Mar 4 16:37:45] NOTICE[1569]: res_odbc.c:1528 odbc_obj_connect: Re-connecting eivr
[Mar 4 16:37:45] NOTICE[1569]: res_odbc.c:1563 odbc_obj_connect: res_odbc: Connected to eivr [eivr]
[/quote]

NOTE: I sometimes get an HY010 error instead

Thanks for your time, I appreciate any assistance.

2

Hi scriptex, did you ever find a cause/solution to this?

3

Hi cchance!

I actually did find the cause, however not a real solution. This was being caused by Asterisk/ODBC not playing nicely with our MySQL proxy server.

I was using a remote MySQL DB server to store all of the information and it was located behind a proxy. For some reason, the connection would timeout and stick for like 15 minutes after prolonged inactivity.

Our fix however, was just to relocate our DB onto a local MySQL server. We did not take troubleshooting the proxy any further as a local server suited our needs and was quick to set up.

I hope this helps, if not good luck to you!