Keep health log history in seconds (#15314)

* rebase

* changes queries to delete based on when

* readme changes

* no need to do migration

* wip, protect un-updated events from cleanup

* remove index on when_key

* fix query for claimed cleanup

* if set less than minimum, set minimum

* fix query

* correct config assign

3 files changed, 42 insertions, 27 deletions

diff --git a/database/rrd.h b/database/rrd.h
index 2f697c81df..95da17c82b 100644
--- a/database/rrd.h
+++ b/database/rrd.h
@@ -1105,6 +1105,7 @@ typedef struct alarm_log {
     uint32_t next_alarm_id;
     unsigned int count;
     unsigned int max;
+    uint32_t health_log_history;                   // the health log history in seconds to be kept in db
     ALARM_ENTRY *alarms;
     RW_SPINLOCK spinlock;
 } ALARM_LOG;
diff --git a/database/sqlite/sqlite_functions.c b/database/sqlite/sqlite_functions.c
index d501214017..4200c15901 100644
--- a/database/sqlite/sqlite_functions.c
+++ b/database/sqlite/sqlite_functions.c
@@ -49,7 +49,6 @@ const char *database_config[] = {
     "config_hash_id blob, name text, chart text, family text, recipient text, units text, exec text, "
     "chart_context text, last_transition_id blob, UNIQUE (host_id, alarm_id)) ;",
 
-    //TODO indexes
     "CREATE INDEX IF NOT EXISTS health_log_ind_1 ON health_log (host_id);",
 
     "CREATE TABLE IF NOT EXISTS health_log_detail (health_log_id int, unique_id int, alarm_id int, alarm_event_id int, "
@@ -62,7 +61,6 @@ const char *database_config[] = {
     "CREATE INDEX IF NOT EXISTS health_log_d_ind_2 ON health_log_detail (global_id);",
     "CREATE INDEX IF NOT EXISTS health_log_d_ind_3 ON health_log_detail (transition_id);",
     "CREATE INDEX IF NOT EXISTS health_log_d_ind_4 ON health_log_detail (health_log_id);",
-    //TODO more indexes
 
     NULL
 };
diff --git a/database/sqlite/sqlite_health.c b/database/sqlite/sqlite_health.c
index c7a794f885..acaa9748e3 100644
--- a/database/sqlite/sqlite_health.c
+++ b/database/sqlite/sqlite_health.c
@@ -393,8 +393,8 @@ void sql_health_alarm_log_count(RRDHOST *host) {
 /* Health related SQL queries
    Cleans up the health_log_detail table on a non-claimed host
 */
-#define SQL_CLEANUP_HEALTH_LOG_DETAIL_NOT_CLAIMED(limit) "DELETE FROM health_log_detail where health_log_id in (select health_log_id from health_log where host_id = @host_id) ORDER BY unique_id ASC LIMIT %lu;", limit
-void sql_health_alarm_log_cleanup_not_claimed(RRDHOST *host, size_t rotate_every) {
+#define SQL_CLEANUP_HEALTH_LOG_DETAIL_NOT_CLAIMED "DELETE FROM health_log_detail WHERE health_log_id IN (SELECT health_log_id FROM health_log WHERE host_id = ?1) AND when_key + ?2 < unixepoch() AND updated_by_id <> 0 AND transition_id NOT IN (SELECT last_transition_id FROM health_log hl WHERE hl.host_id = ?3);"
+void sql_health_alarm_log_cleanup_not_claimed(RRDHOST *host) {
     sqlite3_stmt *res = NULL;
     int rc;
     char command[MAX_HEALTH_SQL_SIZE + 1];
@@ -408,9 +408,7 @@ void sql_health_alarm_log_cleanup_not_claimed(RRDHOST *host, size_t rotate_every
     char uuid_str[UUID_STR_LEN];
     uuid_unparse_lower_fix(&host->host_uuid, uuid_str);
 
-    snprintfz(command, MAX_HEALTH_SQL_SIZE, SQL_CLEANUP_HEALTH_LOG_DETAIL_NOT_CLAIMED((unsigned long int) (host->health.health_log_entries_written - rotate_every)));
-
-    rc = sqlite3_prepare_v2(db_meta, command, -1, &res, 0);
+    rc = sqlite3_prepare_v2(db_meta, SQL_CLEANUP_HEALTH_LOG_DETAIL_NOT_CLAIMED, -1, &res, 0);
     if (unlikely(rc != SQLITE_OK)) {
         error_report("Failed to prepare statement to cleanup health log detail table (un-claimed)");
         return;
@@ -423,15 +421,29 @@ void sql_health_alarm_log_cleanup_not_claimed(RRDHOST *host, size_t rotate_every
         return;
     }
 
+    rc = sqlite3_bind_int64(res, 2, (sqlite3_int64)host->health_log.health_log_history);
+    if (unlikely(rc != SQLITE_OK)) {
+        error_report("Failed to bind health log history for SQL_CLEANUP_HEALTH_LOG_NOT_CLAIMED.");
+        sqlite3_finalize(res);
+        return;
+    }
+
+    rc = sqlite3_bind_blob(res, 3, &host->host_uuid, sizeof(host->host_uuid), SQLITE_STATIC);
+    if (unlikely(rc != SQLITE_OK)) {
+        error_report("Failed to bind host_id for SQL_CLEANUP_HEALTH_LOG_NOT_CLAIMED.");
+        sqlite3_finalize(res);
+        return;
+    }
+
     rc = sqlite3_step_monitored(res);
     if (unlikely(rc != SQLITE_DONE))
-        error_report("Failed to cleanup health log table, rc = %d", rc);
+        error_report("Failed to cleanup health log detail table, rc = %d", rc);
 
     rc = sqlite3_finalize(res);
     if (unlikely(rc != SQLITE_OK))
         error_report("Failed to finalize the prepared statement to cleanup health log detail table (un-claimed)");
 
-    host->health.health_log_entries_written = rotate_every;
+    sql_health_alarm_log_count(host);
 
     snprintfz(command, MAX_HEALTH_SQL_SIZE, "aclk_alert_%s", uuid_str);
     if (unlikely(table_exists_in_database(command))) {
@@ -442,8 +454,8 @@ void sql_health_alarm_log_cleanup_not_claimed(RRDHOST *host, size_t rotate_every
 /* Health related SQL queries
    Cleans up the health_log_detail table on a claimed host
 */
-#define SQL_CLEANUP_HEALTH_LOG_DETAIL_CLAIMED(guid, limit) "DELETE from health_log_detail WHERE unique_id NOT IN (SELECT filtered_alert_unique_id FROM aclk_alert_%s) AND unique_id IN (SELECT hld.unique_id FROM health_log hl, health_log_detail hld WHERE hl.host_id = ?1 AND hl.health_log_id = hld.health_log_id) and health_log_id in (SELECT health_log_id FROM health_log WHERE host_id = ?2) ORDER BY unique_id asc LIMIT %lu;", guid, limit
-void sql_health_alarm_log_cleanup_claimed(RRDHOST *host, size_t rotate_every) {
+#define SQL_CLEANUP_HEALTH_LOG_DETAIL_CLAIMED(guid) "DELETE from health_log_detail WHERE unique_id NOT IN (SELECT filtered_alert_unique_id FROM aclk_alert_%s) AND unique_id IN (SELECT hld.unique_id FROM health_log hl, health_log_detail hld WHERE hl.host_id = ?1 AND hl.health_log_id = hld.health_log_id) AND health_log_id IN (SELECT health_log_id FROM health_log WHERE host_id = ?2) AND when_key + ?3 < unixepoch() AND updated_by_id <> 0 AND transition_id NOT IN (SELECT last_transition_id FROM health_log hl WHERE hl.host_id = ?4);", guid
+void sql_health_alarm_log_cleanup_claimed(RRDHOST *host) {
     sqlite3_stmt *res = NULL;
     int rc;
     char command[MAX_HEALTH_SQL_SIZE + 1];
@@ -459,11 +471,11 @@ void sql_health_alarm_log_cleanup_claimed(RRDHOST *host, size_t rotate_every) {
     snprintfz(command, MAX_HEALTH_SQL_SIZE, "aclk_alert_%s", uuid_str);
 
     if (!table_exists_in_database(command)) {
-        sql_health_alarm_log_cleanup_not_claimed(host, rotate_every);
+        sql_health_alarm_log_cleanup_not_claimed(host);
         return;
     }
 
-    snprintfz(command, MAX_HEALTH_SQL_SIZE, SQL_CLEANUP_HEALTH_LOG_DETAIL_CLAIMED(uuid_str, (unsigned long int) (host->health.health_log_entries_written - rotate_every)));
+    snprintfz(command, MAX_HEALTH_SQL_SIZE, SQL_CLEANUP_HEALTH_LOG_DETAIL_CLAIMED(uuid_str));
 
     rc = sqlite3_prepare_v2(db_meta, command, -1, &res, 0);
     if (unlikely(rc != SQLITE_OK)) {
@@ -485,9 +497,23 @@ void sql_health_alarm_log_cleanup_claimed(RRDHOST *host, size_t rotate_every) {
         return;
     }
 
+    rc = sqlite3_bind_int64(res, 3, (sqlite3_int64)host->health_log.health_log_history);
+    if (unlikely(rc != SQLITE_OK)) {
+        error_report("Failed to bind health log history for SQL_CLEANUP_HEALTH_LOG_CLAIMED.");
+        sqlite3_finalize(res);
+        return;
+    }
+
+    rc = sqlite3_bind_blob(res, 4, &host->host_uuid, sizeof(host->host_uuid), SQLITE_STATIC);
+    if (unlikely(rc != SQLITE_OK)) {
+        error_report("Failed to bind second host_id for SQL_CLEANUP_HEALTH_LOG_CLAIMED.");
+        sqlite3_finalize(res);
+        return;
+    }
+
     rc = sqlite3_step_monitored(res);
     if (unlikely(rc != SQLITE_DONE))
-        error_report("Failed to cleanup health log table, rc = %d", rc);
+        error_report("Failed to cleanup health log detail table, rc = %d", rc);
 
     rc = sqlite3_finalize(res);
     if (unlikely(rc != SQLITE_OK))
@@ -496,27 +522,17 @@ void sql_health_alarm_log_cleanup_claimed(RRDHOST *host, size_t rotate_every) {
     sql_health_alarm_log_count(host);
 
     sql_aclk_alert_clean_dead_entries(host);
+
 }
 
 /* Health related SQL queries
    Cleans up the health_log table.
 */
 void sql_health_alarm_log_cleanup(RRDHOST *host) {
-    static size_t rotate_every = 0;
-
-    if(unlikely(rotate_every == 0)) {
-        rotate_every = (size_t)config_get_number(CONFIG_SECTION_HEALTH, "rotate log every lines", 2000);
-        if(rotate_every < 100) rotate_every = 100;
-    }
-
-    if(likely(host->health.health_log_entries_written < rotate_every)) {
-        return;
-    }
-
     if (!claimed()) {
-        sql_health_alarm_log_cleanup_not_claimed(host, rotate_every);
+        sql_health_alarm_log_cleanup_not_claimed(host);
     } else
-        sql_health_alarm_log_cleanup_claimed(host, rotate_every);
+        sql_health_alarm_log_cleanup_claimed(host);
 }
 
 #define SQL_INJECT_REMOVED "insert into health_log_detail (health_log_id, unique_id, alarm_id, alarm_event_id, updated_by_id, updates_id, when_key, duration, non_clear_duration, flags, exec_run_timestamp, delay_up_to_timestamp, info, exec_code, new_status, old_status, delay, new_value, old_value, last_repeat, transition_id, global_id) select health_log_id, ?1, ?2, ?3, 0, ?4, unixepoch(), 0, 0, flags, exec_run_timestamp, unixepoch(), info, exec_code, -2, new_status, delay, NULL, new_value, 0, ?5, now_usec(0) from health_log_detail where unique_id = ?6 and transition_id = ?7;"