spine/sql.c

/*
 ex: set tabstop=4 shiftwidth=4 autoindent:
 +-------------------------------------------------------------------------+
 | Copyright (C) 2004-2026 The Cacti Group                                 |
 |                                                                         |
 | This program is free software; you can redistribute it and/or           |
 | modify it under the terms of the GNU Lesser General Public              |
 | License as published by the Free Software Foundation; either            |
 | version 2.1 of the License, or (at your option) any later version. 	   |
 |                                                                         |
 | This program is distributed in the hope that it will be useful,         |
 | but WITHOUT ANY WARRANTY; without even the implied warranty of          |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the           |
 | GNU Lesser General Public License for more details.                     |
 |                                                                         |
 | You should have received a copy of the GNU Lesser General Public        |
 | License along with this library; if not, write to the Free Software     |
 | Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA           |
 | 02110-1301, USA                                                         |
 |                                                                         |
 +-------------------------------------------------------------------------+
 | spine: a backend data gatherer for cacti                                |
 +-------------------------------------------------------------------------+
 | This poller would not have been possible without:                       |
 |   - Larry Adams (current development and enhancements)                  |
 |   - Rivo Nurges (rrd support, mysql poller cache, misc functions)       |
 |   - RTG (core poller code, pthreads, snmp, autoconf examples)           |
 |   - Brady Alleman/Doug Warner (threading ideas, implementation details) |
 +-------------------------------------------------------------------------+
 | - Cacti - http://www.cacti.net/                                         |
 +-------------------------------------------------------------------------+
*/

#include "common.h"
#include "spine.h"

/*! \fn int db_insert(MYSQL *mysql, int type, const char *query)
 *  \brief inserts a row or rows in a database table.
 *  \param mysql the database connection object
 *  \param type  the database to connect to local or remote
 *  \param query the database query to execute
 *
 *	Unless the SQL_readonly boolean is set to TRUE, the function will execute
 *	the SQL statement specified in the query variable.
 *
 *  \return TRUE if successful, or FALSE if not.
 *
 */
/* Returns FALSE when the statement should not be retried. A failed reconnect
 * ends the attempt at once: db_connect() has already spent its own tries, and
 * repeating it here only multiplies the wait while a worker holds its device. */
static bool retry_disconnected_query(MYSQL *mysql, int type, int error, const char *function, int *error_count) {
	/* Every attempt counts. A signal that interrupted the call gets a few
	 * in-place retries, but the client library does not reset errno, so a
	 * stale EINTR used to skip both the reconnect and the count forever. */
	if (++*error_count > 30) {
		SPINE_LOG(("ERROR: Too many Reconnect Attempts in Function %s", function));
		return FALSE;
	}
	if (errno == EINTR && *error_count <= 3) {
		spine_sleep_usec(50000);
		return TRUE;
	}
	return db_reconnect(mysql, type, error, function) >= 0;
}

int db_insert(MYSQL *mysql, int type, const char *query) {
	int error_count = 0;
	char query_frag[LRG_BUFSIZE];
	snprintf(query_frag, sizeof(query_frag), "%s", query);
	SPINE_LOG_DEVDBG(("DEVDBG: SQL:%s", query_frag));
	if (set.poller.SQL_readonly != FALSE) return TRUE;
	while (mysql_query(mysql, query) != 0) {
		int error = mysql_errno(mysql);
		if (error == 2013 || error == 2006) {
			if (retry_disconnected_query(mysql, type, error, "db_insert", &error_count)) continue;
			SPINE_LOG(("ERROR: SQL Failed! Connection lost, SQL Fragment:'%s'", query_frag));
			return FALSE;
		}
		if (error == 1213 || error == 1205) {
			spine_sleep_usec(50000);
			if (++error_count > 30) {
				SPINE_LOG(("ERROR: Too many Lock/Deadlock errors occurred!, SQL Fragment:'%s'", query_frag));
				return FALSE;
			}
			continue;
		}
		SPINE_LOG(("ERROR: SQL Failed! Error:'%i', Message:'%s', SQL Fragment:'%s'", error, mysql_error(mysql), query_frag));
		return FALSE;
	}
	return TRUE;
}

/*! \fn int db_set_session_mode(MYSQL *mysql)
 *  \brief relaxes the session sql_mode to what Cacti's schema needs.
 *
 *  Uses mysql_query() directly so a failure here never recurses into the
 *  reconnect path. Every new session needs it, including one opened by a
 *  reconnect, since the server starts each session at its default mode.
 *  SQL_readonly does not skip it: it changes no data.
 *
 *  \return TRUE when every statement succeeded
 */
int db_set_session_mode(MYSQL *mysql) {
	static const char *const modes[] = {
		"NO_ZERO_DATE", "NO_ZERO_IN_DATE", "ONLY_FULL_GROUP_BY", "NO_AUTO_VALUE_ON_ZERO",
		"TRADITIONAL", "STRICT_ALL_TABLES", "STRICT_TRANS_TABLES"
	};
	char query[BUFSIZE];
	size_t i;

	for (i = 0; i < sizeof(modes) / sizeof(modes[0]); i++) {
		snprintf(query, sizeof(query), "SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode,'%s', ''))", modes[i]);
		if (mysql_query(mysql, query) != 0) {
			SPINE_LOG(("ERROR: Unable to set the session sql_mode: %s", mysql_error(mysql)));
			return FALSE;
		}
	}

	return TRUE;
}

/* Returns TRUE after a reconnect, FALSE when the session was still alive, and
 * -1 when the server could not be reached. After -1 the handle is initialized
 * but unconnected, so the next attempt closes and reconnects it again. */
int db_reconnect(MYSQL *mysql, int type, int error, const char *function) {
	unsigned long  mysql_thread = 0;
	char   query[100];
	int    ping_status;

	mysql_thread = mysql_thread_id(mysql);
	ping_status  = mysql_ping(mysql);

	if (mysql_thread_id(mysql) != mysql_thread) {
		SPINE_LOG(("WARNING: Connection Broken in Function %s with Error %i.  Reconnect via mysql_ping() successful.", function, error));
		snprintf(query, 100, "KILL %lu;", mysql_thread);
		mysql_query(mysql, query);

		if (!db_set_session_mode(mysql)) return -1;

		sleep(1);

		return TRUE;
	}

	/* A live session on the same thread has nothing to reconnect. */
	if (ping_status == 0) {
		return FALSE;
	}

	/* mysql_ping() did not reconnect; do it explicitly */
	SPINE_LOG(("WARNING: Connection Broken in Function %s with Error %i.  Attempting explicit reconnect.", function, error));

	mysql_close(mysql);

	if (db_connect(type, mysql) && mysql_thread_id(mysql) > 0 && db_set_session_mode(mysql)) {
		SPINE_LOG(("WARNING: Explicit reconnect successful in Function %s.", function));
		return TRUE;
	}

	SPINE_LOG(("WARNING: Connection Broken with Error %i.  Reconnect failed.", error));
	return -1;
}

/*! \fn MYSQL_RES *db_query(MYSQL *mysql, int type, const char *query)
 *  \brief executes a query and returns a pointer to the result set.
 *  \param mysql the database connection object
 *  \param query the database query to execute
 *
 *	This function will execute the SQL statement specified in the query variable.
 *
 *  \return MYSQL_RES a MySQL result structure, or NULL when the statement
 *          failed or produced no result set; the caller decides whether a
 *          failure ends a device, a startup step or the process
 *
 */
MYSQL_RES *db_query(MYSQL *mysql, int type, const char *query) {
	int error_count = 0;
	char query_frag[LRG_BUFSIZE];
	snprintf(query_frag, sizeof(query_frag), "%s", query);
	SPINE_LOG_DEVDBG(("DEVDBG: SQL:%s", query_frag));
	while (mysql_query(mysql, query) != 0) {
		int error = mysql_errno(mysql);
		if (error == 2013 || error == 2006) {
			if (retry_disconnected_query(mysql, type, error, "db_query", &error_count)) continue;
			SPINE_LOG(("ERROR: Database connection lost, SQL Fragment:'%s'", query_frag));
			return NULL;
		}
		if (error == 1213 || error == 1205) {
			spine_sleep_usec(50000);
			if (++error_count > 30) {
				SPINE_LOG(("ERROR: Too many Lock/Deadlock errors occurred!, SQL Fragment:'%s'", query_frag));
				return NULL;
			}
			continue;
		}
		SPINE_LOG(("ERROR: Database Error:'%i', Message:'%s'", error, mysql_error(mysql)));
		SPINE_LOG(("ERROR: The Query Was:'%s'", query));
		return NULL;
	}
	return mysql_store_result(mysql);
}

/*! \fn void db_connect(char *database, MYSQL *mysql)
 *  \brief opens a connection to a MySQL database.
 *  \param database a string pointer to the database name
 *  \param mysql a pointer to a mysql database connection object
 *
 *	This function will attempt to open a connection to a MySQL database and then
 *	return the connection object to the calling function.  If the database connection
 *  fails more than 20 times, the function will fail and Spine will terminate.
 *
 */
void db_address_init(db_address_t *address, const char *value, bool parse_socket) {
	address->storage = strdup(value);
	if (address->storage == NULL) die("ERROR: Fatal malloc error: database address!");
	address->hostname = address->storage;
	address->socket = NULL;
	if (!parse_socket) return;
	struct stat socket_stat;
	if (stat(address->storage, &socket_stat) == 0) {
		if (S_ISSOCK(socket_stat.st_mode)) {
			address->socket = address->storage;
			address->hostname = NULL;
		}
		return;
	}
	address->socket = strchr(address->storage, ':');
	if (address->socket != NULL) *address->socket++ = '\0';
}

void db_address_release(db_address_t *address) {
	free(address->storage);
	address->storage = NULL;
	address->hostname = NULL;
	address->socket = NULL;
}

void db_set_option(MYSQL *mysql, enum mysql_option option, const void *value, const char *description) {
	if (mysql_options(mysql, option, value) != 0) {
		set.exit.exit_code = EXIT_FAILURE;
		die("FATAL: MySQL options unable to set %s option", description);
	}
}

#ifdef HAS_MYSQL_OPT_SSL_KEY
static void db_set_ssl_options(MYSQL *mysql, int type) {
	#ifdef HAS_MYSQL_OPT_SSL_VERIFY_SERVER_CERT
	int ssl_enabled = type == LOCAL ? set.database.ssl : set.remote_database.ssl;
	if (ssl_enabled == 0) {
		bool ssl_enforce = false;
		db_set_option(mysql, MYSQL_OPT_SSL_VERIFY_SERVER_CERT, &ssl_enforce, "ssl disable");
	}
	#endif
	const char *key = type == REMOTE ? set.remote_database.ssl_key : set.database.ssl_key;
	const char *ca = type == REMOTE ? set.remote_database.ssl_ca : set.database.ssl_ca;
	const char *cert = type == REMOTE ? set.remote_database.ssl_cert : set.database.ssl_cert;
	if (key[0] != '\0') db_set_option(mysql, MYSQL_OPT_SSL_KEY, key, "ssl key");
	if (ca[0] != '\0') db_set_option(mysql, MYSQL_OPT_SSL_CA, ca, "ssl ca");
	if (cert[0] != '\0') db_set_option(mysql, MYSQL_OPT_SSL_CERT, cert, "ssl cert");
}
#endif

/* Reports failure instead of exiting: worker threads reconnect through here,
 * and a lost database must fail a device rather than the poller. Startup
 * callers turn FALSE into a process exit. */
int db_connect(int type, MYSQL *mysql) {
	int     tries;
	int     attempts;
	int     timeout;
	int     rtimeout;
	int     wtimeout;
	int     success;
	int     error = 0;
	const MYSQL *connect_error;
	db_address_t address;
	static int connections = 0;

	bool local_address = set.poller.poller_id <= 1 || type == LOCAL;
	db_address_init(&address, local_address ? set.database.host : set.remote_database.host, local_address);

	/* initialalize variables */
	tries     = 2;
	success   = FALSE;
	timeout   = 5;
	rtimeout  = 30;
	wtimeout  = 30;
	attempts  = 1;

	if (mysql_init(mysql) == NULL) {
		db_address_release(&address);
		printf("ERROR: Database unable to allocate memory and therefore can not connect\n");
		return FALSE;
	}

	db_set_option(mysql, MYSQL_OPT_READ_TIMEOUT, &rtimeout, "read timeout");
	db_set_option(mysql, MYSQL_OPT_WRITE_TIMEOUT, &wtimeout, "write timeout");
	db_set_option(mysql, MYSQL_OPT_CONNECT_TIMEOUT, &timeout, "general timeout");

	#ifdef HAS_MYSQL_OPT_RETRY_COUNT
	db_set_option(mysql, MYSQL_OPT_RETRY_COUNT, &tries, "retry count");
	#endif

	#ifdef HAS_MYSQL_OPT_SSL_KEY
	db_set_ssl_options(mysql, type);
	#endif

	while (tries > 0) {
		tries--;

		if (set.poller.poller_id > 1) {
			if (type == LOCAL) {
				connect_error = mysql_real_connect(mysql, address.hostname, set.database.user, set.database.password, set.database.database, set.database.port, address.socket, 0);
			} else {
				connect_error = mysql_real_connect(mysql, address.hostname, set.remote_database.user, set.remote_database.password, set.remote_database.database, set.remote_database.port, address.socket, 0);
			}
		} else {
			connect_error = mysql_real_connect(mysql, address.hostname, set.database.user, set.database.password, set.database.database, set.database.port, address.socket, 0);
		}

		if (!connect_error) {
			error = mysql_errno(mysql);

			/* A stale EINTR in errno used to refund the attempt here and made
			 * the loop unbounded; every failed connect now spends a try. */
			if (error == 2002) {
				printf("Database: Connection Failed: Attempt:'%d', Error:'%u', Message:'%s'\n", attempts, mysql_errno(mysql), mysql_error(mysql));
				sleep(1);
				success = FALSE;
			} else if (error != 1049 && error != 2005 && error != 1045) {
				printf("Database: Connection Failed: Error:'%d', Message:'%s'\n", error, mysql_error(mysql));
				success = FALSE;
				spine_sleep_usec(50000);
			} else {
				tries   = 0;
				success = FALSE;
			}
		} else {
			success = TRUE;
			break;
		}

		attempts++;
	}

	db_address_release(&address);



	if (!success){
		printf("ERROR: Connection Failed, Error:'%i', Message:'%s'\n", error, mysql_error(mysql));
		return FALSE;
	}

	SPINE_LOG_DEBUG(("DEBUG: Total Connections made %i", connections));

	connections++;

	return TRUE;
}

/*! \fn void db_disconnect(MYSQL *mysql)
 *  \brief closes connection to MySQL database
 *  \param mysql the database connection object
 *
 */
void db_disconnect(MYSQL *mysql) {
	if (mysql != NULL) {
		mysql_close(mysql);
	}

}

/*! \fn void db_create_connection_pool(int type)
 *  \brief Creates a connection pool for spine
 *  \param type the connection type, LOCAL or REMOTE
 *
 */
void db_create_connection_pool(int type) {
	int id;

	if (type == LOCAL) {
		SPINE_LOG_DEBUG(("DEBUG: Creating Local Connection Pool of %i threads.", set.poller.threads));

		for(id = 0; id < set.poller.threads; id++) {
			SPINE_LOG_DEBUG(("DEBUG: Creating Local Connection %i.", id));

			if (!db_connect(type, &db_pool_local[id].mysql)) die("FATAL: Unable to create the local connection pool");

			if (!db_set_session_mode(&db_pool_local[id].mysql)) die("FATAL: Unable to configure the local connection pool");

			db_pool_local[id].free = TRUE;
			db_pool_local[id].id   = id;
		}
	} else {
		SPINE_LOG_DEBUG(("DEBUG: Creating Remote Connection Pool of %i threads.", set.poller.threads));

		for(id = 0; id < set.poller.threads; id++) {
			SPINE_LOG_DEBUG(("DEBUG: Creating Remote Connection %i.", id));

			if (!db_connect(type, &db_pool_remote[id].mysql)) die("FATAL: Unable to create the remote connection pool");

			if (!db_set_session_mode(&db_pool_remote[id].mysql)) die("FATAL: Unable to configure the remote connection pool");

			db_pool_remote[id].free = TRUE;
			db_pool_remote[id].id   = id;
		}
	}
}

/*! \fn void db_close_connection_pool(int type)
 *  \brief Closes a connection pool for spine
 *  \param type the connection type, LOCAL or REMOTE
 *
 */
void db_close_connection_pool(int type) {
	int id;

	if (type == LOCAL) {
		for(id = 0; id < set.poller.threads; id++) {
			SPINE_LOG_DEBUG(("DEBUG: Closing Local Connection Pool ID %i", id));
			db_disconnect(&db_pool_local[id].mysql);
		}

		free(db_pool_local);
	} else {
		for(id = 0; id < set.poller.threads; id++) {
			SPINE_LOG_DEBUG(("DEBUG: Closing Remote Connection Pool ID %i", id));
			db_disconnect(&db_pool_remote[id].mysql);
		}

		free(db_pool_remote);
	}
}

/*! \fn pool_t db_get_connection(int type)
 *  \brief returns a free mysql connection from the pool
 *  \param type the connection type, LOCAL or REMOTE
 *
 */
pool_t *db_get_connection(int type) {
	int id;

	thread_mutex_lock(LOCK_POOL);

	if (type == LOCAL) {
		SPINE_LOG_DEBUG(("DEBUG: Traversing Local Connection Pool for free connection."));
		for (id = 0; id < set.poller.threads; id++) {
			SPINE_LOG_DEBUG(("DEBUG: Checking Local Pool ID %i.", id));
			if (db_pool_local[id].free == TRUE) {
				SPINE_LOG_DEBUG(("DEBUG: Allocating Local Pool ID %i.", id));
				db_pool_local[id].free = FALSE;
				thread_mutex_unlock(LOCK_POOL);
				return &db_pool_local[id];
			}
		}
	} else {
		SPINE_LOG_DEBUG(("DEBUG: Traversing Remote Connection Pool for free connection."));
		for (id = 0; id < set.poller.threads; id++) {
			SPINE_LOG_DEBUG(("DEBUG: Checking Remote Pool ID %i.", id));
			if (db_pool_remote[id].free == TRUE) {
				SPINE_LOG_DEBUG(("DEBUG: Allocating Remote Pool ID %i.", id));
				db_pool_remote[id].free = FALSE;
				thread_mutex_unlock(LOCK_POOL);
				return &db_pool_remote[id];
			}
		}
	}

	SPINE_LOG(("FATAL: Connection Pool Fatal Error."));

	thread_mutex_unlock(LOCK_POOL);

	return NULL;
}

/*! \fn voi db_release_connection(int id)
 *  \brief marks a database connection as free
 *  \param id the connection id
 *
 */
void db_release_connection(int type, int id) {
	thread_mutex_lock(LOCK_POOL);

	if (type == LOCAL) {
		SPINE_LOG_DEBUG(("DEBUG: Freeing Local Pool ID %i", id));
		db_pool_local[id].free = TRUE;
	} else {
		SPINE_LOG_DEBUG(("DEBUG: Freeing Remote Pool ID %i", id));
		db_pool_remote[id].free = TRUE;
	}

	thread_mutex_unlock(LOCK_POOL);
}

/*! \fn int append_hostrange(char *obuf, const char *colname, const config_t *set)
 *  \brief appends a host range to a sql select statement
 *  \param obuf the sql select statement to have the host range appended
 *  \param colname the sql column name that will have the host range checked
 *  \param set global runtime settings
 *
 *	Several places in the code need to limit the range of hosts to
 *	those with a certain ID range, but only if those range values
 *	are actually nonzero.
 *
 *	This appends the SQL clause if necessary, returning the # of
 *	characters added to the buffer. Else return 0.
 *
 *  \return the number of characters added to the end of the character buffer
 *
 */
int append_hostrange(char *obuf, size_t capacity, const char *colname) {
	if (HOSTID_DEFINED(set.hosts.start_host_id) && HOSTID_DEFINED(set.hosts.end_host_id)) {
		return spine_snprintf(obuf, capacity, " AND %s BETWEEN %d AND %d",
			colname,
			set.hosts.start_host_id,
			set.hosts.end_host_id);
	} else {
		return 0;
	}
}

/*! \fn void db_escape(MYSQL *mysql, char *output, int max_size, const char *input)
 *  \brief Escapes a text string to make it safe for mysql insert/updates
 *  \param mysql the connection object
 *  \param output a pointer to the output string
 *  \param a pointer to the input string
 *
 *	A simple implementation of the mysql_real_escape_string that one
 *  day should be portable.
 *
 *  \return void
 *
 */
void db_escape(MYSQL *mysql, char *output, int max_size, const char *input) {
	size_t input_len;
	size_t max_input;

	if (input == NULL || output == NULL) return;

	if (max_size <= 1) {
		if (max_size == 1) {
			*output = '\0';
		}
		return;
	}

	/* Escaping can double every byte and adds a NUL terminator. Derive the
	 * input limit from the caller's destination rather than a fixed staging
	 * buffer, so full RESULTS_BUFFER values survive in a 2N+1 destination. */
	max_input = ((size_t) max_size - 1) / 2;
	input_len = strlen(input);
	if (input_len > max_input) {
		input_len = max_input;
	}

	mysql_real_escape_string(mysql, output, input, (unsigned long) input_len);
}

void db_free_result(MYSQL_RES *result) {
	mysql_free_result(result);
}

/* TRUE or FALSE, or -1 when the probe itself failed. */
int db_column_exists(MYSQL *mysql, int type, const char *table, const char *column) {
	char       query_frag[BUFSIZE];
   MYSQL_RES *result;
	int        exists;

	/* save a fragment just in case */
	memset(query_frag, 0, BUFSIZE);
	snprintf(query_frag, BUFSIZE, "SHOW COLUMNS FROM `%s` LIKE '%s'", table, column);

	/* show the sql query */
	SPINE_LOG_DEVDBG(("DEVDBG: db_column_exists('%s','%s'): %s", table, column, query_frag));

	result = db_query(mysql, type, query_frag);
	if (result == NULL) return -1;
	if (mysql_num_rows(result)) {
		exists = TRUE;
	} else {
		exists = FALSE;
	}

	db_free_result(result);
	return exists;
}