Index: cdr_addon_mysql.c =================================================================== RCS file: /usr/cvsroot/asterisk-addons/cdr_addon_mysql.c,v retrieving revision 1.5 diff -u -r1.5 cdr_addon_mysql.c --- cdr_addon_mysql.c 9 Jul 2004 16:24:28 -0000 1.5 +++ cdr_addon_mysql.c 27 Aug 2005 17:58:09 -0000 @@ -8,6 +8,12 @@ * Modified August 2003 * Tilghman Lesher * + * Modified August 6, 2005 + * Joseph Benden + * Added mysql connection timeout parameter + * Added an automatic reconnect as to not lose a cdr record + * Cleaned up the original code to match the coding guidelines + * * This program is free software, distributed under the terms of * the GNU General Public License. * @@ -21,7 +27,6 @@ #include #include #include -#include "asterisk.h" #include #include @@ -33,19 +38,24 @@ #include #include +#include +#include +#include + #define DATE_FORMAT "%Y-%m-%d %T" static char *desc = "MySQL CDR Backend"; static char *name = "mysql"; static char *config = "cdr_mysql.conf"; -static char *hostname = NULL, *dbname = NULL, *dbuser = NULL, *password = NULL, *dbsock = NULL; -static int hostname_alloc = 0, dbname_alloc = 0, dbuser_alloc = 0, password_alloc = 0, dbsock_alloc = 0; +static char *hostname = NULL, *dbname = NULL, *dbuser = NULL, *password = NULL, *dbsock = NULL, *dbtable = NULL; +static int hostname_alloc = 0, dbname_alloc = 0, dbuser_alloc = 0, password_alloc = 0, dbsock_alloc = 0, dbtable_alloc = 0; static int dbport = 0; static int connected = 0; static time_t connect_time = 0; static int records = 0; static int totalrecords = 0; static int userfield = 0; +static unsigned int timeout = 0; AST_MUTEX_DEFINE_STATIC(mysql_lock); @@ -69,6 +79,8 @@ if (dbuser && *dbuser) snprintf(status2, 99, " with username %s", dbuser); + if (dbtable && *dbtable) + snprintf(status2, 99, " using table %s", dbtable); if (ctime > 31536000) { ast_cli(fd, "%s%s for %d years, %d days, %d hours, %d minutes, %d seconds.\n", status, status2, ctime / 31536000, (ctime % 31536000) / 86400, (ctime % 86400) / 3600, (ctime % 3600) / 60, ctime % 60); } else if (ctime > 86400) { @@ -103,23 +115,35 @@ struct localuser *u; char *userfielddata = NULL; char sqlcmd[2048], timestr[128]; + char *clid=NULL, *dcontext=NULL, *channel=NULL, *dstchannel=NULL, *lastapp=NULL, *lastdata=NULL; + int retries; +#ifdef MYSQL_LOGUNIQUEID + char *uniqueid=NULL; +#endif ast_mutex_lock(&mysql_lock); - memset(sqlcmd,0,2048); + memset(sqlcmd, 0, 2048); - localtime_r(&cdr->start.tv_sec,&tm); - strftime(timestr,128,DATE_FORMAT,&tm); + localtime_r(&cdr->start.tv_sec, &tm); + strftime(timestr, 128, DATE_FORMAT, &tm); - if ((!connected) && (hostname || dbsock) && dbuser && password && dbname) { + retries = 5; +db_reconnect: + if ((!connected) && (hostname || dbsock) && dbuser && password && dbname && dbtable ) { /* Attempt to connect */ mysql_init(&mysql); + /* Add option to quickly timeout the connection */ + if (timeout && mysql_options(&mysql, MYSQL_OPT_CONNECT_TIMEOUT, (char *)&timeout)!=0) { + ast_log(LOG_ERROR, "cdr_mysql: mysql_options returned (%d) %s\n", mysql_errno(&mysql), mysql_error(&mysql)); + } if (mysql_real_connect(&mysql, hostname, dbuser, password, dbname, dbport, dbsock, 0)) { connected = 1; connect_time = time(NULL); records = 0; } else { - ast_log(LOG_ERROR, "cdr_mysql: cannot connect to database server %s. Call will not be logged\n", hostname); + ast_log(LOG_ERROR, "cdr_mysql: cannot connect to database server %s.\n", hostname); + connected = 0; } } else { /* Long connection - ping the server */ @@ -129,77 +153,81 @@ records = 0; switch (error) { case CR_SERVER_GONE_ERROR: - ast_log(LOG_ERROR, "cdr_mysql: Server has gone away\n"); + case CR_SERVER_LOST: + ast_log(LOG_WARNING, "cdr_mysql: Server has gone away. Attempting to reconnect.\n"); break; default: - ast_log(LOG_ERROR, "cdr_mysql: Unknown connection error\n"); + ast_log(LOG_ERROR, "cdr_mysql: Unknown connection error: (%d) %s\n", mysql_errno(&mysql), mysql_error(&mysql)); } + retries--; + if (retries) + goto db_reconnect; + else + ast_log(LOG_ERROR, "cdr_mysql: Retried to connect fives times, giving up.\n"); } } - if (connected) { - char *clid=NULL, *dcontext=NULL, *channel=NULL, *dstchannel=NULL, *lastapp=NULL, *lastdata=NULL; -#ifdef MYSQL_LOGUNIQUEID - char *uniqueid=NULL; -#endif - - /* Maximum space needed would be if all characters needed to be escaped, plus a trailing NULL */ - if ((clid = alloca(strlen(cdr->clid) * 2 + 1)) != NULL) - mysql_real_escape_string(&mysql, clid, cdr->clid, strlen(cdr->clid)); - if ((dcontext = alloca(strlen(cdr->dcontext) * 2 + 1)) != NULL) - mysql_real_escape_string(&mysql, dcontext, cdr->dcontext, strlen(cdr->dcontext)); - if ((channel = alloca(strlen(cdr->channel) * 2 + 1)) != NULL) - mysql_real_escape_string(&mysql, channel, cdr->channel, strlen(cdr->channel)); - if ((dstchannel = alloca(strlen(cdr->dstchannel) * 2 + 1)) != NULL) - mysql_real_escape_string(&mysql, dstchannel, cdr->dstchannel, strlen(cdr->dstchannel)); - if ((lastapp = alloca(strlen(cdr->lastapp) * 2 + 1)) != NULL) - mysql_real_escape_string(&mysql, lastapp, cdr->lastapp, strlen(cdr->lastapp)); - if ((lastdata = alloca(strlen(cdr->lastdata) * 2 + 1)) != NULL) - mysql_real_escape_string(&mysql, lastdata, cdr->lastdata, strlen(cdr->lastdata)); + /* Maximum space needed would be if all characters needed to be escaped, plus a trailing NULL */ + + /* WARNING: This code previously used mysql_real_escape_string, but the use of said function + requires an active connection to a database. If we are not connected, then this function + cannot be used. This is a problem since we need to store off the SQL statement into our + spool file for later restoration. + + So the question is, what's the best way to handle this? This works for now. + */ + if ((clid = alloca(strlen(cdr->clid) * 2 + 1)) != NULL) + mysql_escape_string(clid, cdr->clid, strlen(cdr->clid)); + if ((dcontext = alloca(strlen(cdr->dcontext) * 2 + 1)) != NULL) + mysql_escape_string(dcontext, cdr->dcontext, strlen(cdr->dcontext)); + if ((channel = alloca(strlen(cdr->channel) * 2 + 1)) != NULL) + mysql_escape_string(channel, cdr->channel, strlen(cdr->channel)); + if ((dstchannel = alloca(strlen(cdr->dstchannel) * 2 + 1)) != NULL) + mysql_escape_string(dstchannel, cdr->dstchannel, strlen(cdr->dstchannel)); + if ((lastapp = alloca(strlen(cdr->lastapp) * 2 + 1)) != NULL) + mysql_escape_string(lastapp, cdr->lastapp, strlen(cdr->lastapp)); + if ((lastdata = alloca(strlen(cdr->lastdata) * 2 + 1)) != NULL) + mysql_escape_string(lastdata, cdr->lastdata, strlen(cdr->lastdata)); #ifdef MYSQL_LOGUNIQUEID - if ((uniqueid = alloca(strlen(cdr->uniqueid) * 2 + 1)) != NULL) - mysql_real_escape_string(&mysql, uniqueid, cdr->uniqueid, strlen(cdr->uniqueid)); + if ((uniqueid = alloca(strlen(cdr->uniqueid) * 2 + 1)) != NULL) + mysql_escape_string(uniqueid, cdr->uniqueid, strlen(cdr->uniqueid)); #endif + if (userfield && ((userfielddata = alloca(strlen(cdr->userfield) * 2 + 1)) != NULL)) + mysql_escape_string(userfielddata, cdr->userfield, strlen(cdr->userfield)); - if (userfield && ((userfielddata = alloca(strlen(cdr->userfield) * 2 + 1)) != NULL)) - mysql_real_escape_string(&mysql, userfielddata, cdr->userfield, strlen(cdr->userfield)); - - /* Check for all alloca failures above at once */ + /* Check for all alloca failures above at once */ #ifdef MYSQL_LOGUNIQUEID - if ((!clid) || (!dcontext) || (!channel) || (!dstchannel) || (!lastapp) || (!lastdata) || (!uniqueid)) { + if ((!clid) || (!dcontext) || (!channel) || (!dstchannel) || (!lastapp) || (!lastdata) || (!uniqueid)) { #else - if ((!clid) || (!dcontext) || (!channel) || (!dstchannel) || (!lastapp) || (!lastdata)) { + if ((!clid) || (!dcontext) || (!channel) || (!dstchannel) || (!lastapp) || (!lastdata)) { #endif - ast_log(LOG_ERROR, "cdr_mysql: Out of memory error (insert fails)\n"); - ast_mutex_unlock(&mysql_lock); - return -1; - } + ast_log(LOG_ERROR, "cdr_mysql: Out of memory error (insert fails)\n"); + ast_mutex_unlock(&mysql_lock); + return -1; + } - ast_log(LOG_DEBUG,"cdr_mysql: inserting a CDR record.\n"); + ast_log(LOG_DEBUG, "cdr_mysql: inserting a CDR record.\n"); - if (userfield && userfielddata) - { + if (userfield && userfielddata) { #ifdef MYSQL_LOGUNIQUEID - sprintf(sqlcmd,"INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid,userfield) VALUES ('%s','%s','%s','%s','%s', '%s','%s','%s','%s',%i,%i,'%s',%i,'%s','%s','%s')",timestr,clid,cdr->src, cdr->dst, dcontext,channel, dstchannel, lastapp, lastdata,cdr->duration,cdr->billsec,ast_cdr_disp2str(cdr->disposition),cdr->amaflags, cdr->accountcode, uniqueid, userfielddata); + sprintf(sqlcmd, "INSERT INTO %s (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid,userfield) VALUES ('%s','%s','%s','%s','%s', '%s','%s','%s','%s',%i,%i,'%s',%i,'%s','%s','%s')", dbtable, timestr, clid, cdr->src, cdr->dst, dcontext, channel, dstchannel, lastapp, lastdata, cdr->duration, cdr->billsec, ast_cdr_disp2str(cdr->disposition), cdr->amaflags, cdr->accountcode, uniqueid, userfielddata); #else - sprintf(sqlcmd,"INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,userfield) VALUES ('%s','%s','%s','%s','%s', '%s','%s','%s','%s',%i,%i,'%s',%i,'%s','%s')",timestr,clid,cdr->src, cdr->dst, dcontext,channel, dstchannel, lastapp, lastdata,cdr->duration,cdr->billsec,ast_cdr_disp2str(cdr->disposition),cdr->amaflags, cdr->accountcode, userfielddata); + sprintf(sqlcmd, "INSERT INTO %s (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,userfield) VALUES ('%s','%s','%s','%s','%s', '%s','%s','%s','%s',%i,%i,'%s',%i,'%s','%s')", dbtable, timestr, clid, cdr->src, cdr->dst, dcontext,channel, dstchannel, lastapp, lastdata, cdr->duration, cdr->billsec, ast_cdr_disp2str(cdr->disposition), cdr->amaflags, cdr->accountcode, userfielddata); #endif - } - else - { + } else { #ifdef MYSQL_LOGUNIQUEID - sprintf(sqlcmd,"INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid) VALUES ('%s','%s','%s','%s','%s', '%s','%s','%s','%s',%i,%i,'%s',%i,'%s','%s')",timestr,clid,cdr->src, cdr->dst, dcontext,channel, dstchannel, lastapp, lastdata,cdr->duration,cdr->billsec,ast_cdr_disp2str(cdr->disposition),cdr->amaflags, cdr->accountcode, uniqueid); + sprintf(sqlcmd, "INSERT INTO %s (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid) VALUES ('%s','%s','%s','%s','%s', '%s','%s','%s','%s',%i,%i,'%s',%i,'%s','%s')", dbtable, timestr, clid, cdr->src, cdr->dst, dcontext,channel, dstchannel, lastapp, lastdata, cdr->duration, cdr->billsec, ast_cdr_disp2str(cdr->disposition), cdr->amaflags, cdr->accountcode, uniqueid); #else - sprintf(sqlcmd,"INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode) VALUES ('%s','%s','%s','%s','%s', '%s','%s','%s','%s',%i,%i,'%s',%i,'%s')",timestr,clid,cdr->src, cdr->dst, dcontext,channel, dstchannel, lastapp, lastdata,cdr->duration,cdr->billsec,ast_cdr_disp2str(cdr->disposition),cdr->amaflags, cdr->accountcode); + sprintf(sqlcmd, "INSERT INTO %s (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode) VALUES ('%s','%s','%s','%s','%s', '%s','%s','%s','%s',%i,%i,'%s',%i,'%s')", dbtable, timestr, clid, cdr->src, cdr->dst, dcontext, channel, dstchannel, lastapp, lastdata, cdr->duration, cdr->billsec, ast_cdr_disp2str(cdr->disposition), cdr->amaflags, cdr->accountcode); #endif - } + } - ast_log(LOG_DEBUG,"cdr_mysql: SQL command as follows: %s\n",sqlcmd); + ast_log(LOG_DEBUG, "cdr_mysql: SQL command as follows: %s\n", sqlcmd); + if (connected) { if (mysql_real_query(&mysql,sqlcmd,strlen(sqlcmd))) { - ast_log(LOG_ERROR,"Failed to insert into database."); - ast_mutex_unlock(&mysql_lock); - return -1; + ast_log(LOG_ERROR, "mysql_cdr: Failed to insert into database: (%d) %s", mysql_errno(&mysql), mysql_error(&mysql)); + connected = 0; } else { records++; totalrecords++; @@ -242,6 +270,11 @@ dbsock = NULL; dbsock_alloc = 0; } + if (dbtable && dbtable_alloc) { + free(dbtable); + dbtable = NULL; + dbtable_alloc = 0; + } if (password && password_alloc) { free(password); password = NULL; @@ -259,7 +292,11 @@ struct ast_variable *var; char *tmp; +#if (ASTERISK_VERSION_NUM > 10199) + cfg = ast_config_load(config); +#else cfg = ast_load(config); +#endif if (!cfg) { ast_log(LOG_WARNING, "Unable to load config for mysql CDR's: %s\n", config); return 0; @@ -271,115 +308,147 @@ return 0; } - tmp = ast_variable_retrieve(cfg,"global","hostname"); + tmp = ast_variable_retrieve(cfg, "global", "hostname"); if (tmp) { hostname = malloc(strlen(tmp) + 1); if (hostname != NULL) { hostname_alloc = 1; - strcpy(hostname,tmp); + strcpy(hostname, tmp); } else { - ast_log(LOG_ERROR,"Out of memory error.\n"); + ast_log(LOG_ERROR, "Out of memory error.\n"); return -1; } } else { - ast_log(LOG_WARNING,"MySQL server hostname not specified. Assuming localhost\n"); + ast_log(LOG_WARNING, "MySQL server hostname not specified. Assuming localhost\n"); hostname = "localhost"; } - tmp = ast_variable_retrieve(cfg,"global","dbname"); + tmp = ast_variable_retrieve(cfg, "global", "dbname"); if (tmp) { dbname = malloc(strlen(tmp) + 1); if (dbname != NULL) { dbname_alloc = 1; - strcpy(dbname,tmp); + strcpy(dbname, tmp); } else { - ast_log(LOG_ERROR,"Out of memory error.\n"); + ast_log(LOG_ERROR, "Out of memory error.\n"); return -1; } } else { - ast_log(LOG_WARNING,"MySQL database not specified. Assuming asteriskcdrdb\n"); + ast_log(LOG_WARNING, "MySQL database not specified. Assuming asteriskcdrdb\n"); dbname = "asteriskcdrdb"; } - tmp = ast_variable_retrieve(cfg,"global","user"); + tmp = ast_variable_retrieve(cfg, "global", "user"); if (tmp) { dbuser = malloc(strlen(tmp) + 1); if (dbuser != NULL) { dbuser_alloc = 1; - strcpy(dbuser,tmp); + strcpy(dbuser, tmp); } else { - ast_log(LOG_ERROR,"Out of memory error.\n"); + ast_log(LOG_ERROR, "Out of memory error.\n"); return -1; } } else { - ast_log(LOG_WARNING,"MySQL database user not specified. Assuming root\n"); + ast_log(LOG_WARNING, "MySQL database user not specified. Assuming root\n"); dbuser = "root"; } - tmp = ast_variable_retrieve(cfg,"global","sock"); + tmp = ast_variable_retrieve(cfg, "global", "sock"); if (tmp) { dbsock = malloc(strlen(tmp) + 1); if (dbsock != NULL) { dbsock_alloc = 1; - strcpy(dbsock,tmp); + strcpy(dbsock, tmp); } else { - ast_log(LOG_ERROR,"Out of memory error.\n"); + ast_log(LOG_ERROR, "Out of memory error.\n"); return -1; } } else { - ast_log(LOG_WARNING,"MySQL database sock file not specified. Using default\n"); + ast_log(LOG_WARNING, "MySQL database sock file not specified. Using default\n"); dbsock = NULL; } - tmp = ast_variable_retrieve(cfg,"global","password"); + tmp = ast_variable_retrieve(cfg, "global", "table"); + if (tmp) { + dbtable = malloc(strlen(tmp) + 1); + if (dbtable != NULL) { + dbtable_alloc = 1; + strcpy(dbtable, tmp); + } else { + ast_log(LOG_ERROR, "Out of memory error.\n"); + return -1; + } + } else { + ast_log(LOG_NOTICE, "MySQL database table not specified. Assuming \"cdr\"\n"); + dbtable = "cdr"; + } + + tmp = ast_variable_retrieve(cfg, "global", "password"); if (tmp) { password = malloc(strlen(tmp) + 1); if (password != NULL) { password_alloc = 1; - strcpy(password,tmp); + strcpy(password, tmp); } else { - ast_log(LOG_ERROR,"Out of memory error.\n"); + ast_log(LOG_ERROR, "Out of memory error.\n"); return -1; } } else { - ast_log(LOG_WARNING,"MySQL database password not specified. Assuming blank\n"); + ast_log(LOG_WARNING, "MySQL database password not specified. Assuming blank\n"); password = ""; } - tmp = ast_variable_retrieve(cfg,"global","port"); + tmp = ast_variable_retrieve(cfg, "global", "port"); if (tmp) { - if (sscanf(tmp,"%d",&dbport) < 1) { - ast_log(LOG_WARNING,"Invalid MySQL port number. Using default\n"); + if (sscanf(tmp,"%d", &dbport) < 1) { + ast_log(LOG_WARNING, "Invalid MySQL port number. Using default\n"); dbport = 0; } } + + tmp = ast_variable_retrieve(cfg, "global", "timeout"); + if (tmp) { + if (sscanf(tmp,"%d", &timeout) < 1) { + ast_log(LOG_WARNING, "Invalid MySQL timeout number. Using default\n"); + timeout = 0; + } + } - tmp = ast_variable_retrieve(cfg,"global","userfield"); + tmp = ast_variable_retrieve(cfg, "global", "userfield"); if (tmp) { - if (sscanf(tmp,"%d",&userfield) < 1) { - ast_log(LOG_WARNING,"Invalid MySQL configurtation file\n"); + if (sscanf(tmp, "%d", &userfield) < 1) { + ast_log(LOG_WARNING, "Invalid MySQL configurtation file\n"); userfield = 0; } } - + +#if (ASTERISK_VERSION_NUM > 10199) + ast_config_destroy(cfg); +#else ast_destroy(cfg); +#endif - ast_log(LOG_DEBUG,"cdr_mysql: got hostname of %s\n",hostname); - ast_log(LOG_DEBUG,"cdr_mysql: got port of %d\n",dbport); + ast_log(LOG_DEBUG, "cdr_mysql: got hostname of %s\n", hostname); + ast_log(LOG_DEBUG, "cdr_mysql: got port of %d\n", dbport); + ast_log(LOG_DEBUG, "cdr_mysql: got a timeout of %d\n", timeout); if (dbsock) - ast_log(LOG_DEBUG,"cdr_mysql: got sock file of %s\n",dbsock); - ast_log(LOG_DEBUG,"cdr_mysql: got user of %s\n",dbuser); - ast_log(LOG_DEBUG,"cdr_mysql: got dbname of %s\n",dbname); - ast_log(LOG_DEBUG,"cdr_mysql: got password of %s\n",password); - + ast_log(LOG_DEBUG, "cdr_mysql: got sock file of %s\n", dbsock); + ast_log(LOG_DEBUG, "cdr_mysql: got user of %s\n", dbuser); + ast_log(LOG_DEBUG, "cdr_mysql: got dbname of %s\n", dbname); + ast_log(LOG_DEBUG, "cdr_mysql: got password of %s\n", password); + mysql_init(&mysql); + if (timeout && mysql_options(&mysql, MYSQL_OPT_CONNECT_TIMEOUT, (char *)&timeout)!=0) { + ast_log(LOG_ERROR, "cdr_mysql: mysql_options returned (%d) %s\n", mysql_errno(&mysql), mysql_error(&mysql)); + } + if (!mysql_real_connect(&mysql, hostname, dbuser, password, dbname, dbport, dbsock, 0)) { ast_log(LOG_ERROR, "Failed to connect to mysql database %s on %s.\n", dbname, hostname); connected = 0; records = 0; } else { - ast_log(LOG_DEBUG,"Successfully connected to MySQL database.\n"); + ast_log(LOG_DEBUG, "Successfully connected to MySQL database.\n"); connected = 1; records = 0; connect_time = time(NULL); @@ -407,8 +476,14 @@ int reload(void) { + int ret; + + ast_mutex_lock(&mysql_lock); my_unload_module(); - return my_load_module(); + ret = my_load_module(); + ast_mutex_unlock(&mysql_lock); + + return ret; } int usecount(void)