From 69361a721c5c21012ef6488fec6fd55f232a8e36 Mon Sep 17 00:00:00 2001 From: Stelios Fragkakis <52996999+stelfrag@users.noreply.github.com> Date: Sat, 13 Apr 2024 20:11:17 +0300 Subject: Add option to cleanup health_log table (#17385) Add option to cleanup health_log table (-W sqlite-alert-cleanup) --- src/daemon/main.c | 6 +++ src/database/sqlite/sqlite_context.c | 1 + src/database/sqlite/sqlite_db_migration.c | 1 + src/database/sqlite/sqlite_health.c | 70 +++++++++++++++++++++++++++++++ src/database/sqlite/sqlite_health.h | 1 + src/database/sqlite/sqlite_metadata.c | 1 + 6 files changed, 80 insertions(+) diff --git a/src/daemon/main.c b/src/daemon/main.c index 30f7457537..591fcc38c4 100644 --- a/src/daemon/main.c +++ b/src/daemon/main.c @@ -795,6 +795,7 @@ int help(int exitcode) { " -W sqlite-meta-recover Run recovery on the metadata database and exit.\n\n" " -W sqlite-compact Reclaim metadata database unused space and exit.\n\n" " -W sqlite-analyze Run update statistics and exit.\n\n" + " -W sqlite-alert-cleanup Perform maintenance on the alerts table.\n\n" #ifdef ENABLE_DBENGINE " -W createdataset=N Create a DB engine dataset of N seconds and exit.\n\n" " -W stresstest=A,B,C,D,E,F,G\n" @@ -1515,6 +1516,11 @@ int main(int argc, char **argv) { return 0; } + if(strcmp(optarg, "sqlite-alert-cleanup") == 0) { + sql_alert_cleanup(true); + return 0; + } + if(strcmp(optarg, "unittest") == 0) { unittest_running = true; diff --git a/src/database/sqlite/sqlite_context.c b/src/database/sqlite/sqlite_context.c index ad76a1ee2c..3f5ee016bb 100644 --- a/src/database/sqlite/sqlite_context.c +++ b/src/database/sqlite/sqlite_context.c @@ -43,6 +43,7 @@ int sql_init_context_database(int memory) return 1; } + errno = 0; netdata_log_info("SQLite database %s initialization", sqlite_database); char buf[1024 + 1] = ""; diff --git a/src/database/sqlite/sqlite_db_migration.c b/src/database/sqlite/sqlite_db_migration.c index 0131c4bf6b..ea35276115 100644 --- a/src/database/sqlite/sqlite_db_migration.c +++ b/src/database/sqlite/sqlite_db_migration.c @@ -530,6 +530,7 @@ static int migrate_database(sqlite3 *database, int target_version, char *db_name } if (likely(user_version == target_version)) { + errno = 0; netdata_log_info("%s database version is %d (no migration needed)", db_name, target_version); return target_version; } diff --git a/src/database/sqlite/sqlite_health.c b/src/database/sqlite/sqlite_health.c index ea883c51b9..de156cfe5d 100644 --- a/src/database/sqlite/sqlite_health.c +++ b/src/database/sqlite/sqlite_health.c @@ -704,6 +704,76 @@ void sql_check_removed_alerts_state(RRDHOST *host) error_report("Failed to finalize the statement"); } +#define SQL_DELETE_MISSING_CHART_ALERT \ + "DELETE FROM health_log WHERE host_id = @host_id AND chart NOT IN " \ + "(SELECT type||'.'||id FROM chart WHERE host_id = @host_id)" + +static void sql_remove_alerts_from_deleted_charts(RRDHOST *host, uuid_t *host_id) +{ + sqlite3_stmt *res = NULL; + int ret; + + ret = sqlite3_prepare_v2(db_meta, SQL_DELETE_MISSING_CHART_ALERT, -1, &res, 0); + if (unlikely(ret != SQLITE_OK)) { + error_report("HEALTH [%s]: Failed to prepare sql statement to sql_remove_alerts_from_deleted_charts", rrdhost_hostname(host)); + return; + } + + if (host) + ret = sqlite3_bind_blob(res, 1, &host->host_uuid, sizeof(host->host_uuid), SQLITE_STATIC); + else + ret = sqlite3_bind_blob(res, 1, host_id, sizeof(*host_id), SQLITE_STATIC); + + if (unlikely(ret != SQLITE_OK)) { + error_report("Failed to bind host_id parameter for sql_remove_alerts_from_deleted_charts."); + sqlite3_finalize(res); + return; + } + + ret = execute_insert(res); + if (ret != SQLITE_DONE) + error_report("Failed to execute command to delete missing charts from health_log"); + + ret = sqlite3_finalize(res); + if (unlikely(ret != SQLITE_OK)) + error_report("Failed to finalize statement when deleting missing charts from health_log"); +} + +static int clean_host_alerts(void *data, int argc, char **argv, char **column) +{ + UNUSED(argc); + UNUSED(data); + UNUSED(column); + + char guid[UUID_STR_LEN]; + uuid_unparse_lower(*(uuid_t *)argv[0], guid); + + netdata_log_info("Checking host %s (%s)", guid, (const char *) argv[1]); + sql_remove_alerts_from_deleted_charts(NULL, (uuid_t *)argv[0]); + + return 0; +} + + +#define SQL_HEALTH_CHECK_ALL_HOSTS "SELECT host_id, hostname FROM host" + +void sql_alert_cleanup(bool cli) +{ + UNUSED(cli); + + errno = 0; + if (sql_init_meta_database(DB_CHECK_NONE, 0)) { + netdata_log_error("Failed to open database"); + return; + } + netdata_log_info("Alert cleanup running ..."); + int rc = sqlite3_exec_monitored(db_meta, SQL_HEALTH_CHECK_ALL_HOSTS, clean_host_alerts, NULL, NULL); + if (rc != SQLITE_OK) + netdata_log_error("Failed to check host alerts"); + else + netdata_log_info("Alert cleanup done"); + +} /* Health related SQL queries Load from the health log table */ diff --git a/src/database/sqlite/sqlite_health.h b/src/database/sqlite/sqlite_health.h index 1b889436e5..f5287e327f 100644 --- a/src/database/sqlite/sqlite_health.h +++ b/src/database/sqlite/sqlite_health.h @@ -36,4 +36,5 @@ int sql_get_alert_configuration( bool debug __maybe_unused); bool sql_find_alert_transition(const char *transition, void (*cb)(const char *machine_guid, const char *context, time_t alert_id, void *data), void *data); +void sql_alert_cleanup(bool cli); #endif //NETDATA_SQLITE_HEALTH_H diff --git a/src/database/sqlite/sqlite_metadata.c b/src/database/sqlite/sqlite_metadata.c index 86ecee1f68..fa1aca59b8 100644 --- a/src/database/sqlite/sqlite_metadata.c +++ b/src/database/sqlite/sqlite_metadata.c @@ -747,6 +747,7 @@ int sql_init_meta_database(db_check_action_type_t rebuild, int memory) return 1; } + errno = 0; netdata_log_info("SQLite database %s initialization", sqlite_database); rc = sqlite3_create_function(db_meta, "u2h", 1, SQLITE_ANY | SQLITE_DETERMINISTIC, 0, sqlite_uuid_parse, 0, 0); -- cgit v1.2.3