Index: cdr_addon_mysql.c =================================================================== --- cdr_addon_mysql.c (revision 220) +++ cdr_addon_mysql.c (working copy) @@ -14,6 +14,10 @@ * Added an automatic reconnect as to not lose a cdr record * Cleaned up the original code to match the coding guidelines * + * Modified June 11, 2006 + * Joseph Benden + * Added SQL spooling of INSERT statements for when the database connection is lost. + * * This program is free software, distributed under the terms of * the GNU General Public License. * @@ -47,8 +51,8 @@ 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, *dbtable = NULL; -static int hostname_alloc = 0, dbname_alloc = 0, dbuser_alloc = 0, password_alloc = 0, dbsock_alloc = 0, dbtable_alloc = 0; +static char *hostname = NULL, *dbname = NULL, *dbuser = NULL, *password = NULL, *dbsock = NULL, *dbtable = NULL, *dbspool = NULL; +static int hostname_alloc = 0, dbname_alloc = 0, dbuser_alloc = 0, password_alloc = 0, dbsock_alloc = 0, dbtable_alloc = 0, dbspool_alloc = 0; static int dbport = 0; static int connected = 0; static time_t connect_time = 0; @@ -64,7 +68,93 @@ static char cdr_mysql_status_help[] = "Usage: cdr mysql status\n" " Shows current connection status for cdr_mysql\n"; +static char cdr_mysql_unspool_help[] = +"Usage: cdr mysql unspool\n" +" Attempts to unspool the available queries to the database\n"; +static char cdr_mysql_connect_help[] = +"Usage: cdr mysql connect\n" +" Attempts to connect to the database server\n"; +static char cdr_mysql_disconnect_help[] = +"Usage: cdr mysql disconnect\n" +" Disconnects from the database server\n"; +static int handle_cdr_mysql_unspool(void) +{ + struct stat st; + char buf[2048] = ""; + int error = 0, good = 0; + char *spoolbackup = NULL; + FILE *back = NULL, *mf = NULL; + char *myerror; + + if (!dbspool) + return 0; + + if (0 == stat(dbspool, &st)) { + ast_log(LOG_WARNING, "cdr_mysql: Loading spool records from %s\n", dbspool); + mf = fopen(dbspool, "r"); + if (!mf) + ast_log(LOG_ERROR, "cdr_mysql: Unable to open %s (%s)\n", dbspool, strerror(errno)); + else { + while (fgets(buf, 2048, mf)) { + if (!error) { + // execute mysql statement + ast_log(LOG_DEBUG, "cdr_mysql: Executing SQL: %s\n", buf); + if (mysql_real_query(&mysql,buf,strlen(buf))) { + ast_log(LOG_ERROR,"cdr_mysql: Failed to insert into database: (%d) %s",mysql_errno(&mysql),mysql_error(&mysql)); + error++; + } else { + good++; + } + } + if (error && !spoolbackup) { + spoolbackup = malloc(strlen(dbspool) + 5); + if (!spoolbackup) { + ast_log(LOG_ERROR, "cdr_mysql: Fatal error in spool load, out of memory.\n"); + return -1; + } + sprintf(spoolbackup, "%s.bak", dbspool); + back = fopen(spoolbackup, "a"); + if (!back) { + ast_log(LOG_ERROR, "cdr_mysql: Fatal error in spool load, couldn't open %s\n", spoolbackup); + return -1; + } + } + if (back) { + fprintf(back, "%s", buf); + } + } + fclose(mf); + unlink(dbspool); + if (back) { + fclose(back); + rename(spoolbackup, dbspool); + free(spoolbackup); + } + ast_log(LOG_WARNING, "cdr_mysql: Finished loading from spool: %d commands executed successfully, %d failed\n", good, error); + } + } /* end stat check */ + return 0; +} + +static int handle_cdr_mysql_spool(char *cmd) +{ + FILE *mf = NULL; + + if (!dbspool) + return 0; + + mf = fopen(dbspool, "a"); + if (!mf) + ast_log(LOG_ERROR, "cdr_mysql: Unable to open %s (%s)\n", dbspool, strerror(errno)); + else { + fprintf(mf, "%s\n", cmd); + fflush(mf); + fclose(mf); + } + return 0; +} + static int handle_cdr_mysql_status(int fd, int argc, char *argv[]) { if (connected) { @@ -103,10 +193,79 @@ } } +static int cli_cdr_mysql_unspool(int fd, int argc, char *argv[]) +{ + int res = RESULT_FAILURE; + ast_mutex_lock(&mysql_lock); + if (!connected) { + ast_cli(fd, "Not currently connected. Skipping.\n"); + } else { + handle_cdr_mysql_unspool(); + res = RESULT_SUCCESS; + } + ast_mutex_unlock(&mysql_lock); + return res; +} + +static int cli_cdr_mysql_connect(int fd, int argc, char *argv[]) +{ + int res = RESULT_FAILURE; + + if (connected) { + ast_cli(fd, "Already connected.\n"); + return res; + } + + ast_mutex_lock(&mysql_lock); + + mysql_init(&mysql); + + if (timeout && mysql_options(&mysql, MYSQL_OPT_CONNECT_TIMEOUT, (char *)&timeout)!=0) { + ast_cli(fd, "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_cli(fd, "Failed to connect to mysql database %s on %s.\n", dbname, hostname); + connected = 0; + records = 0; + } else { + ast_cli(fd, "Successfully connected to MySQL database.\n"); + connected = 1; + records = 0; + connect_time = time(NULL); + res = RESULT_SUCCESS; + } + ast_mutex_unlock(&mysql_lock); + return res; +} + +static int cli_cdr_mysql_disconnect(int fd, int argc, char *argv[]) +{ + ast_mutex_lock(&mysql_lock); + connected = 0; + records = 0; + mysql_close(&mysql); + ast_mutex_unlock(&mysql_lock); + ast_cli(fd, "Disconnected.\n"); + return RESULT_SUCCESS; +} + static struct ast_cli_entry cdr_mysql_status_cli = { { "cdr", "mysql", "status", NULL }, handle_cdr_mysql_status, "Show connection status of cdr_mysql", cdr_mysql_status_help, NULL }; +static struct ast_cli_entry cdr_mysql_unspool_cli = + { { "cdr", "mysql", "unspool", NULL }, + cli_cdr_mysql_unspool, "Unspool CDR records", + cdr_mysql_unspool_help, NULL }; +static struct ast_cli_entry cdr_mysql_connect_cli = + { { "cdr", "mysql", "connect", NULL }, + cli_cdr_mysql_connect, "Connect to database", + cdr_mysql_connect_help, NULL }; +static struct ast_cli_entry cdr_mysql_disconnect_cli = + { { "cdr", "mysql", "disconnect", NULL }, + cli_cdr_mysql_disconnect, "Disconnect from the database", + cdr_mysql_disconnect_help, NULL }; static int mysql_log(struct ast_cdr *cdr) { @@ -140,8 +299,12 @@ connected = 1; connect_time = time(NULL); records = 0; + handle_cdr_mysql_unspool(); } else { - ast_log(LOG_ERROR, "cdr_mysql: cannot connect to database server %s.\n", hostname); + if (dbspool) + ast_log(LOG_ERROR, "cdr_mysql: cannot connect to database server %s. CDR will be spooled.\n", hostname); + else + ast_log(LOG_ERROR, "cdr_mysql: cannot connect to database server %s. CDR will not be spooled.\n", hostname); connected = 0; } } else { @@ -150,7 +313,16 @@ if ((error = mysql_ping(&mysql))) { connected = 0; records = 0; + /* This is needed because the value returned from + mysql_ping do not contain the full error + possibilities */ + error = mysql_errno(&mysql); switch (error) { + case CR_CONN_HOST_ERROR: + case CR_CONNECTION_ERROR: + case CR_SOCKET_CREATE_ERROR: + ast_log(LOG_ERROR, "cdr_mysql: Unable to connect to server.\n"); + goto db_skipconnection; case CR_SERVER_GONE_ERROR: case CR_SERVER_LOST: ast_log(LOG_ERROR, "cdr_mysql: Server has gone away. Attempting to reconnect.\n"); @@ -166,6 +338,7 @@ } } +db_skipconnection: /* 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 @@ -223,6 +396,7 @@ if (connected) { if (mysql_real_query(&mysql, sqlcmd, strlen(sqlcmd))) { + handle_cdr_mysql_spool(sqlcmd); ast_log(LOG_ERROR, "mysql_cdr: Failed to insert into database: (%d) %s", mysql_errno(&mysql), mysql_error(&mysql)); mysql_close(&mysql); connected = 0; @@ -230,6 +404,8 @@ records++; totalrecords++; } + } else { + handle_cdr_mysql_spool(sqlcmd); } ast_mutex_unlock(&mysql_lock); return 0; @@ -243,6 +419,9 @@ static int my_unload_module(void) { ast_cli_unregister(&cdr_mysql_status_cli); + ast_cli_unregister(&cdr_mysql_unspool_cli); + ast_cli_unregister(&cdr_mysql_connect_cli); + ast_cli_unregister(&cdr_mysql_disconnect_cli); if (connected) { mysql_close(&mysql); connected = 0; @@ -273,11 +452,21 @@ dbtable = NULL; dbtable_alloc = 0; } + if (dbtable && dbtable_alloc) { + free(dbtable); + dbtable = NULL; + dbtable_alloc = 0; + } if (password && password_alloc) { free(password); password = NULL; password_alloc = 0; } + if (dbspool && dbspool_alloc) { + free(dbspool); + dbspool = NULL; + dbspool_alloc = 0; + } dbport = 0; ast_cdr_unregister(name); return 0; @@ -408,6 +597,21 @@ } } + tmp = ast_variable_retrieve(cfg, "global", "spool"); + if (tmp) { + dbspool = malloc(strlen(tmp) + 1); + if (dbspool != NULL) { + dbspool_alloc = 1; + strcpy(dbspool, tmp); + } else { + ast_log(LOG_ERROR, "Out of memory error.\n"); + return -1; + } + } else { + ast_log(LOG_NOTICE, "MySQL spool file not specified. Will not catch lost database queries.\n"); + dbspool = NULL; + } + tmp = ast_variable_retrieve(cfg, "global", "userfield"); if (tmp) { if (sscanf(tmp, "%d", &userfield) < 1) { @@ -449,6 +653,9 @@ ast_log(LOG_ERROR, "Unable to register MySQL CDR handling\n"); } else { res = ast_cli_register(&cdr_mysql_status_cli); + res = ast_cli_register(&cdr_mysql_unspool_cli); + res = ast_cli_register(&cdr_mysql_connect_cli); + res = ast_cli_register(&cdr_mysql_disconnect_cli); } return res;