Add option to cleanup health_log table (#17385)

Add option to cleanup health_log table (-W sqlite-alert-cleanup)

6 files changed, 80 insertions, 0 deletions

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);