Index: configs/res_odbc.conf.sample =================================================================== --- configs/res_odbc.conf.sample (revision 186774) +++ configs/res_odbc.conf.sample (working copy) @@ -4,34 +4,72 @@ ; Note that all environmental variables can be seen by all connections, ; so you can't have different values for different connections. [ENV] -INFORMIXSERVER => my_special_database -INFORMIXDIR => /opt/informix +;INFORMIXSERVER => my_special_database +;INFORMIXDIR => /opt/informix +;ORACLE_HOME => /home/oracle ; All other sections are arbitrary names for database connections. +; +; The context name is what will be used in other configuration files, such +; as extconfig.conf and func_odbc.conf, to reference this connection. [asterisk] +; +; Permit disabling sections without needing to comment them out. +; If not specified, it is assumed the section is enabled. enabled => no +; +; This value should match an entry in /etc/odbc.ini +; (or /usr/local/etc/odbc.ini, on FreeBSD and similar systems). dsn => asterisk +; +; Username for connecting to the database. The default user is "root". ;username => myuser +; +; Password for authenticating the user to the database. The default +; password is blank. ;password => mypass +; +; Build a connection at startup? pre-connect => yes +; +; On some databases, the connection times out and a reconnection will be +; necessary. This setting configures the amount of time a connection +; may sit idle (in seconds) before a reconnection will be attempted. +;idlecheck => 3600 +; +; Should we use a single connection for each query? Most databases will +; allow sharing the connection, though Sybase and MS SQL Server will not. +;pooling => no +; +; If we aren't sharing connections, what is the maximum number of connections +; that we should attempt? +;limit => 5 +; +; Is the backslash a native escape character? The default is yes, but for +; MS SQL Server, the setting should be no. +;backslash_is_escape => yes +; +; How long (in seconds) should we attempt to connect before considering the +; connection dead? The default is 10 seconds, but you may wish to reduce it, +; to increase responsiveness. +;connect_timeout => 10 +; +; When a connection fails, how long (in seconds) should we cache that +; information before we attempt another connection? This increases +; responsiveness, when a database resource is not working. +;negative_connection_cache => 300 - [mysql2] enabled => no dsn => MySQL-asterisk username => myuser password => mypass pre-connect => yes -; -; On some databases, the connection times out and a reconnection will be -; necessary. This setting configures the amount of time a connection -; may sit idle (in seconds) before a reconnection will be attempted. -;idlecheck => 3600 ; Certain servers, such as MS SQL Server and Sybase use the TDS protocol, which -; limits the number of active queries per connection to 1. By setting up pools -; of connections, Asterisk can be made to work with these servers. +; limits the number of active queries per connection to 1. By telling res_odbc +; not to share connections, Asterisk can be made to work with these servers. [sqlserver] enabled => no dsn => mickeysoft @@ -40,10 +78,17 @@ username => oscar password => thegrouch pre-connect => yes +; ; Many databases have a default of '\' to escape special characters. MS SQL ; Server does not. backslash_is_escape => no - - - +; +; If you are having problems with concurrency, please read this note from the +; mailing lists, regarding UnixODBC: +; +; http://lists.digium.com/pipermail/asterisk-dev/2009-February/036539.html +; +; In summary, try setting "Threading=2" in the relevant section within your +; odbcinst.ini. +; Index: res/res_odbc.c =================================================================== --- res/res_odbc.c (revision 186774) +++ res/res_odbc.c (working copy) @@ -69,6 +69,9 @@ unsigned int delme:1; /* Purge the class */ unsigned int backslash_is_escape:1; /* On this database, the backslash is a native escape sequence */ unsigned int idlecheck; /* Recheck the connection if it is idle for this long */ + unsigned int conntimeout; + unsigned int negative_connection_cache; + time_t last_negative_connect; AST_LIST_HEAD(, odbc_obj) odbc_obj; }; @@ -217,7 +220,7 @@ struct ast_config *config; struct ast_variable *v; char *cat, *dsn, *username, *password; - int enabled, pooling, limit, bse; + int enabled, pooling, limit, bse, conntimeout, ncache = 0; unsigned int idlecheck; int connect = 0, res = 0; @@ -270,6 +273,17 @@ password = v->value; } else if (!strcasecmp(v->name, "backslash_is_escape")) { bse = ast_true(v->value); + } else if (!strcasecmp(v->name, "connect_timeout")) { + if (sscanf(v->value, "%d", &conntimeout) != 1 || conntimeout < 1) { + ast_log(LOG_WARNING, "connect_timeout must be a positive integer\n"); + conntimeout = 5; + } + } else if (!strcasecmp(v->name, "negative_connection_cache")) { + if (sscanf(v->value, "%d", &ncache) != 1 || ncache < 0) { + ast_log(LOG_WARNING, "negative_connection_cache must be a non-negative integer\n"); + /* 5 minutes sounds like a reasonable default */ + ncache = 300; + } } } @@ -311,6 +325,8 @@ new->backslash_is_escape = bse ? 1 : 0; new->idlecheck = idlecheck; + new->conntimeout = conntimeout; + new->negative_connection_cache = ncache; odbc_register_class(new, connect); ast_log(LOG_NOTICE, "Registered ODBC class '%s' dsn->[%s]\n", cat, dsn); @@ -330,7 +346,17 @@ AST_LIST_TRAVERSE(&odbc_list, class, list) { if ((argc == 2) || (argc == 3 && !strcmp(argv[2], "all")) || (!strcmp(argv[2], class->name))) { int count = 0; + char timestr[80]; + struct tm tm; + + if (class->last_negative_connect) { + ast_localtime(&class->last_negative_connect, &tm, NULL); + strftime(timestr, sizeof(timestr), "%Y-%m-%d %T", &tm); + } else { + ast_copy_string(timestr, "Never", sizeof(timestr)); + } ast_cli(fd, "Name: %s\nDSN: %s\n", class->name, class->dsn); + ast_cli(fd, "Last connection failure: %s\n", timestr); if (class->haspool) { ast_cli(fd, "Pooled: yes\nLimit: %d\nConnections in use: %d\n", class->limit, class->count); @@ -414,7 +440,9 @@ return NULL; AST_LIST_LOCK(&class->odbc_obj); - if (class->haspool) { + if (time(NULL) < class->last_negative_connect + class->negative_connection_cache) { + obj = NULL; + } else if (class->haspool) { /* Recycle connections before building another */ AST_LIST_TRAVERSE(&class->odbc_obj, obj, list) { if (! obj->used) { @@ -540,8 +568,8 @@ ast_mutex_unlock(&obj->lock); return ODBC_FAIL; } - SQLSetConnectAttr(obj->con, SQL_LOGIN_TIMEOUT, (SQLPOINTER *) 10, 0); - SQLSetConnectAttr(obj->con, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER *) 10, 0); + SQLSetConnectAttr(obj->con, SQL_LOGIN_TIMEOUT, (SQLPOINTER *) obj->parent->conntimeout, 0); + SQLSetConnectAttr(obj->con, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER *) obj->parent->conntimeout, 0); #ifdef NEEDTRACE SQLSetConnectAttr(obj->con, SQL_ATTR_TRACE, &enable, SQL_IS_INTEGER); SQLSetConnectAttr(obj->con, SQL_ATTR_TRACEFILE, tracefile, strlen(tracefile));