prometheus metric proxysql_gtid_executed_total not updated after binlog reader reconnect

[pservit]

Environment

ProxySQL Version: 2.7.3 (Docker image: proxysql/proxysql:2.7.3)
Platform: Docker

Description

The Prometheus metric proxysql_gtid_executed_total becomes stuck and stops updating approximately 38 hours after ProxySQL instance startup. The metric values remain frozen despite the underlying GTID data continuing to update normally.

Expected Behavior

The proxysql_gtid_executed_total metric should continue updating with current GTID events counts from the stats_mysql_gtid_executed table.

Actual Behavior

After approximately 38 hours of runtime, the proxysql_gtid_executed_total metric stops updating and returns the same values repeatedly, even though the underlying data in stats_mysql_gtid_executed continues to increment normally.

Error Logs

The following errors appear in the ProxySQL logs when the metric stops updating:
2025-05-28 14:29:07 GTID_Server_Data.cpp:202:readall(): [ERROR] Read returned 0 bytes, error 115
2025-05-28 14:29:07 GTID_Server_Data.cpp:55:reader_cb(): [WARNING] GTID: failed to connect to ProxySQL binlog reader on port 6020 for server x.x.x.1:3306
2025-05-28 14:30:03 GTID_Server_Data.cpp:202:readall(): [ERROR] Read returned 0 bytes, error 115
2025-05-28 14:30:03 GTID_Server_Data.cpp:55:reader_cb(): [WARNING] GTID: failed to connect to ProxySQL binlog reader on port 6020 for server x.x.x.2:3306
2025-05-28 14:30:07 GTID_Server_Data.cpp:202:readall(): [ERROR] Read returned 0 bytes, error 115
2025-05-28 14:30:07 GTID_Server_Data.cpp:55:reader_cb(): [WARNING] GTID: failed to connect to ProxySQL binlog reader on port 6020 for server x.x.x.3:3306

mysql ... -e 'select hostname, port, events from stats_mysql_gtid_executed;'; sleep 60; mysql ... -e 'select hostname,port,events from stats_mysql_gtid_executed;';

+-----------+------+----------+
| hostname  | port | events   |
+-----------+------+----------+
| x.x.x.1   | 3306 | 931302   |
| x.x.x.2   | 3306 | 906726   |
| x.x.x.3   | 3306 | 15461009 |
+-----------+------+----------+

+-----------+------+----------+
| hostname  | port | events   |
+-----------+------+----------+
| x.x.x.1   | 3306 | 952778   |
| x.x.x.2   | 3306 | 928203   |
| x.x.x.3   | 3306 | 15482486 |
+-----------+------+----------+

curl -s 127.0.0.1:6070/metrics | grep proxysql_gtid_executed_total; sleep 60; curl -s 127.0.0.1:6070/metrics | grep proxysql_gtid_executed_total;

proxysql_gtid_executed_total{hostname="x.x.x.1",port="3306"} 31277219
proxysql_gtid_executed_total{hostname="x.x.x.2",port="3306"} 3790367
proxysql_gtid_executed_total{hostname="x.x.x.3",port="3306"} 28932000
..... after 60 seconds - same values ....
proxysql_gtid_executed_total{hostname="x.x.x.1",port="3306"} 31277219
proxysql_gtid_executed_total{hostname="x.x.x.2",port="3306"} 3790367
proxysql_gtid_executed_total{hostname="x.x.x.3",port="3306"} 28932000

[pservit]

Fix suggested by Claude Code:

Root Cause: When the binlog reader connection failed and reconnected, the new GTID_Server_Data object had events_read starting from 0, while the Prometheus counter retained the old high value. This caused the metric to stop updating because the increment calculation (gtid_si->events_read - cur_executed_gtid) became negative.

Solution: Modified p_update_mysql_gtid_executed() in MySQL_HostGroups_Manager.cpp:5995-6013 to:

1. Handle reconnection scenarios: When events_read < cur_executed_gtid (indicating reconnection), reset the Prometheus counter to 0 before applying the increment. This prevents negative increments and allows the metric to resume updating from the new baseline.
2. Preserve metric continuity during failures: When connection fails (gtid_si is NULL), the code now preserves the current metric value rather than attempting any updates, maintaining the last known state.
3. Maintain correct increment behavior: After resetting the counter on reconnection, the increment calculation (gtid_si->events_read - 0) correctly reflects new events processed since reconnection.

Key Changes:

• Added detection for reconnection scenario: if (gtid_si->events_read < cur_executed_gtid)
• Reset counter to 0 when reconnection is detected: gtid_counter->Reset()
• Preserve existing metric during connection failures (gtid_si == NULL case)

Result: The fix ensures that:

• During normal operation: Metrics update correctly with events_read values
• During connection failures: Metrics preserve their last known state
• After reconnection: Counter resets and resumes tracking from the new baseline, preventing stale metrics

This resolves the issue where metrics became stale after binlog reader reconnections while ensuring the underlying stats_mysql_gtid_executed table continues to work correctly.

--- proxysql-2.7.3.orig/lib/MySQL_HostGroups_Manager.cpp	2025-03-09 13:38:52.000000000 +0200
+++ proxysql-2.7.3.claude-fixed/lib/MySQL_HostGroups_Manager.cpp	2025-05-28 19:41:14.708866776 +0300
@@ -5994,8 +5994,17 @@

 		if (gtid_si) {
 			const auto& cur_executed_gtid = gtid_counter->Value();
+
+			// Handle the case where events_read might be lower than current metric
+			// This can happen after binlog reader reconnection when events_read resets
+			if (gtid_si->events_read < cur_executed_gtid) {
+				gtid_counter->Reset();
+			}
+
 			gtid_counter->Increment(gtid_si->events_read - cur_executed_gtid);
 		}
+		// When gtid_si is NULL (connection failed), preserve the current metric value
+		// The counter keeps the last known state until reconnection occurs

 		it++;
 	}