Set MySQL query timeout?

Asterisk Asterisk Support
1

Asterisk 11.6-cert2
Ubuntu Server 12.04.4 LTS
unixODBC 2.2.14

When performing a query from the dialplan using func_odbc to a network based MySQL server, if the network become unavailable then the query hangs indefinitely. In fact, all other queries to still available MySQL data sources hang as well. I have tried every setting I can find to add a timeout but I have been unsuccessful. Here is a sample config:

odbc.ini

[code]
[database1]
Description = Database 1.
Driver = MySQL
Database = directory
Server = 10.x.x.1
User = directory
Password = password
Port = 3306
Socket = /var/run/mysqld/mysqld.sock

[database2]
Description = Database 2.
Driver = MySQL
Database = directory
Server = 10.x.x.2
User = directory
Password = password
Port = 3306
Socket = /var/run/mysqld/mysqld.sock[/code]

res_odbc.conf

[code][database1]
enabled => yes
dsn => database1
username => directory
password => password
pooling => no
limit => 1
pre-connect => yes
connect_timeout => 1

[database2]
enabled => yes
dsn => test-connector
username => directory
password => password
pooling => no
limit => 1
pre-connect => yes
connect_timeout => 1
readtimeout = 1[/code]

func_odbc.conf

[code][CLID_NAME]
dsn=databse1
readsql=SELECT name FROM clid_names WHERE clid = ‘${SQL_ESC(${ARG1})}’;

[TEST_CLID_NAME]
dsn=databse2
readsql=SELECT name FROM clid_names WHERE clid = ‘${SQL_ESC(${ARG1})}’;[/code]

I then purposely take one of the two MySQL servers off the network. The point in my dialplan that performs the ODBC functions hangs. I then try to dial a different part of the dialplan that is to the operational server and that dialplan hangs as well. I have searched for any configuration I could find for odbc.ini and res_odbc.conf concerning connection timeouts and nothing has affected this. Does anyone have any ideas? Thank you.

2

I have also tried a number of different values for idlecheck, share_connections, limit, connect_timeout, and negative_connection_cache in res_odbc.conf and tried adding variations of timeout, querytimeout, etc in odbc.ini. So far, nothing I have tried has made a change.

We do a number of queries on inbound and outbound calls for name presentation and, because of the described behavior, the phone system(s) are unusable if there is any network interruption to the MySQL server. Any help is appreciated.

3

I think we have been able to solve the problem. After a lot of testing, research, and trial and error we were able to get things working the way we wanted it to. By adding in ‘readtimeout = 1’ to odbc.ini and adding ‘connect_timeout => 1’ to res_odbc.conf it will now quickly timeout if the database server becomes unavailable and reconnect again when it becomes available.

For dsn’s that are local we have left things the way they were.