Index: cdr_addon_mysql.c =================================================================== RCS file: /usr/cvsroot/asterisk-addons/cdr_addon_mysql.c,v retrieving revision 1.9 diff -u -r1.9 cdr_addon_mysql.c --- cdr_addon_mysql.c 23 Jun 2005 17:40:37 -0000 1.9 +++ cdr_addon_mysql.c 15 Sep 2005 18:47:47 -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. * @@ -32,6 +38,10 @@ #include #include +#include +#include +#include + #define DATE_FORMAT "%Y-%m-%d %T" static char *desc = "MySQL CDR Backend"; @@ -45,6 +55,7 @@ static int records = 0; static int totalrecords = 0; static int userfield = 0; +static unsigned int timeout = 0; AST_MUTEX_DEFINE_STATIC(mysql_lock); @@ -104,23 +115,34 @@ 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 = 5; +#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); +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 */ @@ -130,77 +152,80 @@ 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_ERROR, "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 %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); + 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 %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 - { + 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 { #ifdef MYSQL_LOGUNIQUEID - 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); + 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 %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); + 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 + } - 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_DEBUG, "cdr_mysql: SQL command as follows: %s\n", sqlcmd); + + if (connected) { + if (mysql_real_query(&mysql, sqlcmd, strlen(sqlcmd))) { + ast_log(LOG_ERROR, "mysql_cdr: Failed to insert into database: (%d) %s", mysql_errno(&mysql), mysql_error(&mysql)); + connected = 0; } else { records++; totalrecords++; @@ -277,130 +302,143 @@ 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","table"); + tmp = ast_variable_retrieve(cfg, "global", "table"); if (tmp) { dbtable = malloc(strlen(tmp) + 1); if (dbtable != NULL) { dbtable_alloc = 1; - strcpy(dbtable,tmp); + strcpy(dbtable, 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_NOTICE,"MySQL database table not specified. Assuming \"cdr\"\n"); + ast_log(LOG_NOTICE, "MySQL database table not specified. Assuming \"cdr\"\n"); dbtable = "cdr"; } - tmp = ast_variable_retrieve(cfg,"global","password"); + 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; } } ast_config_destroy(cfg); - 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);