Repository: sysown/proxysql (language: cpp)

Issue:
stats_mysql_commands_counters table doesn't address the new source replica terminology commands in MYSQL 8.0, 8.4+
ProxySQL needs to consider the new mysql source/replica terminology commands wherever applicable. MySQL 8.4+ no longer recognize the old master/slave terminology commands.

AFAICS the below three stats in stats_mysql_commands_counters table needs to be updated to consider the new terminology commands:
CHANGE_MASTER (shall consider CHANGE REPLICATION SOURCE)
RESET_MASTER (shall consider RESET BINARY LOGS AND GTIDS)
RESET_SLAVE (shall consider RESET REPLICA)

ProxySQL version : 2.7.1
OS version: centos 9

Steps to reproduce:
1. Setup proxysql with at least one backend mysql 8.4+ server correctly configured. 
2. Configure the mysql user in proxysql, ensure that the user with necessary privileges exists on mysql 8.4+ server.
3. Open proxysql connection via the user configured.
4. Execute [CHANGE MASTER](https://dev.mysql.com/doc/refman/8.0/en/change-master-to.html) command with right syntax as per mysql 8.0, the query errors out as syntax ERROR but the stats_mysql_commands_counters table will increase the count for CHANGE_MASTER.
5. Execute [CHANGE REPLICATION SOURCE](https://dev.mysql.com/doc/refman/8.4/en/change-replication-source-to.html) command with right syntax, the query executes successfully on server but no effect in CHANGE_MASTER counter.

Same behavior for RESET_MASTER and RESET_SLAVE

Fix:
Rename CHANGE_MASTER, RESET_MASTER and RESET_SLAVE  to CHANGE_REPLICATION_SOURCE, RESET_BINARY_LOGS_AND_GTIDS  and RESET_REPLICA respectively and when server is 8.4+ then consider only the new terminology commands as old terminology commands are invalid and when the version is lower then 8.4 then consider both the new and old terminology commands.




Discussion / hints:
I also encountered this problem:
mysql version: 8.4.3
proxysql version: 2.7.1

error info:
2024-11-25 02:28:53 MySQL_Monitor.cpp:7849:monitor_replication_lag_process_ready_tasks(): [ERROR] Error after 0ms on server 172.16.1.154:3306 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SLAVE STATUS' at line 1
2024-11-25 02:28:53 MySQL_Monitor.cpp:7849:monitor_replication_lag_process_ready_tasks(): [ERROR] Error after 0ms on server xx.xx.xx.xx:3306 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SLAVE STATUS' at line 1
2024-11-25 02:29:03 MySQL_Monitor.cpp:7849:monitor_replication_lag_process_ready_tasks(): [ERROR] Error after 0ms on server xx.xx.xx.xx:3306 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SLAVE STATUS' at line 1
2024-11-25 02:29:03 MySQL_Monitor.cpp:7849:monitor_replication_lag_process_ready_tasks(): [ERROR] Error after 0ms on server xx.xx.xx.xx:3306 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SLAVE STATUS' at line 1
2024-11-25 02:29:13 MySQL_Monitor.cpp:7849:monitor_replication_lag_process_ready_tasks(): [ERROR] Error after 0ms on server xx.xx.xx.xx:3306 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SLAVE STATUS' at line 1
Until proxysql will add support, you can fix on mysql side using  [terminology_use_previous](https://dev.mysql.com/doc/refman/8.4/en/replication-options-replica.html#sysvar_terminology_use_previous)

![Image](https://github.com/user-attachments/assets/3ec601c7-600f-4910-ade5-2bfdbed1c803)
@yakirgb that does not work for `SHOW SLAVE STATUS`.

Connected to a replica of a InnoDB Group Replication:

```
mysql> SET @@SESSION.terminology_use_previous=BEFORE_8_0_26;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT @@SESSION.terminology_use_previous;
+------------------------------------+
| @@SESSION.terminology_use_previous |
+------------------------------------+
| BEFORE_8_0_26                      |
+------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW SLAVE STATUS;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SLAVE STATUS' at line 1
```

Also tested with `--terminology-use-previous=#`, same result.
With AWS nudging people to upgrade from older 8.0.x releases at the moment, we finally did the jump to 8.4 and also found this issue. This was deprecated over 4 years ago (Dec 2020). [8.4.0](https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-0.html) released 2024-04-30 removed the master/slave statements.

It seems like the new statements are only available from 8.0 on though, not in 5.7. I'm not sure what versions ProxySQL aims to support, but if dropping 5.7 is acceptable it should be a rather easy string replacement.
This should be a fairly good start to a fix

```diff
diff --git a/lib/ClickHouse_Server.cpp b/lib/ClickHouse_Server.cpp
index f8b005e5..5f90568b 100644
--- a/lib/ClickHouse_Server.cpp
+++ b/lib/ClickHouse_Server.cpp
@@ -1146,6 +1146,8 @@ __end_show_commands:
 			||
 			(strncasecmp("SHOW SLAVE STATUS",query_no_space,17)==0)
 			||
+			(strncasecmp("SHOW REPLICA STATUS",query_no_space,19)==0)
+			||
 			(strncasecmp("SHOW MASTER LOGS",query_no_space,16)==0)
 		) {
 			GloClickHouseServer->send_MySQL_ERR(&sess->client_myds->myprot, (char *)"Access Denied");
diff --git a/lib/MySQL_Monitor.cpp b/lib/MySQL_Monitor.cpp
index 4b8b8314..4b700b83 100644
--- a/lib/MySQL_Monitor.cpp
+++ b/lib/MySQL_Monitor.cpp
@@ -688,10 +688,12 @@ void MySQL_Monitor_State_Data::init_async() {
 #else
 		if (mysql_thread___monitor_replication_lag_use_percona_heartbeat && 
 			mysql_thread___monitor_replication_lag_use_percona_heartbeat[0] != '\0') {
-			use_percona_heartbeat = true;
-			query_ = "SELECT MAX(ROUND(TIMESTAMPDIFF(MICROSECOND, ts, SYSDATE(6))/1000000)) AS Seconds_Behind_Master FROM ";
-			query_ += mysql_thread___monitor_replication_lag_use_percona_heartbeat;
-		} else {
+            use_percona_heartbeat = true;
+            query_ = "SELECT MAX(ROUND(TIMESTAMPDIFF(MICROSECOND, ts, SYSDATE(6))/1000000)) AS Seconds_Behind_Master FROM ";
+            query_ += mysql_thread___monitor_replication_lag_use_percona_heartbeat;
+        } else if (strncasecmp(this->mysql->server_version, "8", 1) == 0) {
+            query_ = "SHOW REPLICA STATUS"
+        } else {
 			query_ = "SHOW SLAVE STATUS";
 		}
 #endif
@@ -2711,7 +2713,11 @@ void * monitor_replication_lag_thread(void *arg) {
 		}
 	}
 	if (use_percona_heartbeat == false) {
-		mmsd->async_exit_status=mysql_query_start(&mmsd->interr,mmsd->mysql,"SHOW SLAVE STATUS");
+        if (strncasecmp(this->mysql->server_version, "8", 1) == 0) {
+            mmsd->async_exit_status = mysql_query_start(&mmsd->interr, mmsd->mysql, "SHOW REPLICA STATUS");
+        }else {
+            mmsd->async_exit_status = mysql_query_start(&mmsd->interr, mmsd->mysql, "SHOW SLAVE STATUS");
+        }
 	}
 #endif // TEST_REPLICATIONLAG
 	while (mmsd->async_exit_status) {
```
Thank you for the interest in this issue.
We will prioritize it.

@heiglandreas : your approach is spot on : the monitor query should depend from the backend version.

Fix patch:
diff --git a/Makefile b/Makefile
index 094d7008f..c7bdbd966 100644
--- a/Makefile
+++ b/Makefile
@@ -372,6 +372,17 @@ clean:
 	cd test/deps && ${MAKE} clean
 	rm -f pkgroot || true
 
+.PHONY: cleandeps
+cleandeps:
+	cd deps && ${MAKE} cleanall
+	cd lib && ${MAKE} clean
+	cd src && ${MAKE} clean
+
+.PHONY: cleandev
+cleandev:
+	cd lib && ${MAKE} clean
+	cd src && ${MAKE} clean
+
 .PHONY: cleanall
 cleanall:
 	cd deps && ${MAKE} cleanall
diff --git a/include/proxysql_structs.h b/include/proxysql_structs.h
index 2450e8a82..5927ce81d 100644
--- a/include/proxysql_structs.h
+++ b/include/proxysql_structs.h
@@ -487,6 +487,7 @@ enum MYSQL_COM_QUERY_command {
 	MYSQL_COM_QUERY_BEGIN,
 	MYSQL_COM_QUERY_CALL,
 	MYSQL_COM_QUERY_CHANGE_MASTER,
+	MYSQL_COM_QUERY_CHANGE_REPLICATION_SOURCE,
 	MYSQL_COM_QUERY_COMMIT,
 	MYSQL_COM_QUERY_CREATE_DATABASE,
 	MYSQL_COM_QUERY_CREATE_INDEX,
@@ -518,7 +519,9 @@ enum MYSQL_COM_QUERY_command {
 	MYSQL_COM_QUERY_RELEASE_SAVEPOINT,
 	MYSQL_COM_QUERY_RENAME_TABLE,
 	MYSQL_COM_QUERY_RESET_MASTER,
+	MYSQL_COM_QUERY_RESET_BINARY_LOGS_AND_GTIDS,
 	MYSQL_COM_QUERY_RESET_SLAVE,
+	MYSQL_COM_QUERY_RESET_REPLICA,
 	MYSQL_COM_QUERY_REPLACE,
 	MYSQL_COM_QUERY_REVOKE,
 	MYSQL_COM_QUERY_ROLLBACK,
diff --git a/lib/ClickHouse_Server.cpp b/lib/ClickHouse_Server.cpp
index f8b005e5f..5f90568b0 100644
--- a/lib/ClickHouse_Server.cpp
+++ b/lib/ClickHouse_Server.cpp
@@ -1146,6 +1146,8 @@ void ClickHouse_Server_session_handler(MySQL_Session* sess, void *_pa, PtrSize_t
 			||
 			(strncasecmp("SHOW SLAVE STATUS",query_no_space,17)==0)
 			||
+			(strncasecmp("SHOW REPLICA STATUS",query_no_space,19)==0)
+			||
 			(strncasecmp("SHOW MASTER LOGS",query_no_space,16)==0)
 		) {
 			GloClickHouseServer->send_MySQL_ERR(&sess->client_myds->myprot, (char *)"Access Denied");
diff --git a/lib/MySQL_Monitor.cpp b/lib/MySQL_Monitor.cpp
index 4b8b83144..579a4006f 100644
--- a/lib/MySQL_Monitor.cpp
+++ b/lib/MySQL_Monitor.cpp
@@ -683,8 +683,12 @@ void MySQL_Monitor_State_Data::init_async() {
 	case MON_REPLICATION_LAG:
 		async_state_machine_ = ASYNC_QUERY_START;
 #ifdef TEST_REPLICATIONLAG
-		query_ = "SELECT SLAVE STATUS "; // replaced SHOW with SELECT to avoid breaking simulator logic
-		query_ += std::string(hostname) + ":" + std::to_string(port);
+		// Simulator syntax allows 'SELECT <REPLICA|SLAVE> STATUS'
+		{
+			const std::string REPLICA { mysql_get_server_version(mysql) < 80023 ? "SLAVE" : "REPLICA" };
+			query_ = "SELECT " + REPLICA + " STATUS ";
+			query_ += std::string(hostname) + ":" + std::to_string(port);
+		}
 #else
 		if (mysql_thread___monitor_replication_lag_use_percona_heartbeat && 
 			mysql_thread___monitor_replication_lag_use_percona_heartbeat[0] != '\0') {
@@ -692,7 +696,11 @@ void MySQL_Monitor_State_Data::init_async() {
 			query_ = "SELECT MAX(ROUND(TIMESTAMPDIFF(MICROSECOND, ts, SYSDATE(6))/1000000)) AS Seconds_Behind_Master FROM ";
 			query_ += mysql_thread___monitor_replication_lag_use_percona_heartbeat;
 		} else {
-			query_ = "SHOW SLAVE STATUS";
+			if (mysql_get_server_version(mysql) < 80023) {
+				query_ = "SHOW SLAVE STATUS";
+			} else {
+				query_ = "SHOW REPLICA STATUS";
+			}
 		}
 #endif
 		task_timeout_ = mysql_thread___monitor_replication_lag_timeout;
@@ -2694,9 +2702,11 @@ void * monitor_replication_lag_thread(void *arg) {
 
 #ifdef TEST_REPLICATIONLAG
 	{
-		std::string s = "SELECT SLAVE STATUS "; // replaced SHOW with SELECT to avoid breaking simulator logic
-		s += std::string(mmsd->hostname) + ":" + std::to_string(mmsd->port);
-		mmsd->async_exit_status = mysql_query_start(&mmsd->interr, mmsd->mysql, s.c_str());
+		const string REPLICA { mysql_get_server_version(mmsd->mysql) < 80023 ? "SLAVE" : "REPLICA" };
+		const string SELECT {
+			"SELECT " + REPLICA + " STATUS " + string(mmsd->hostname) + ":" + std::to_string(mmsd->port)
+		};
+		mmsd->async_exit_status = mysql_query_start(&mmsd->interr, mmsd->mysql, SELECT.c_str());
 	}
 #else
 	if (percona_heartbeat_table) {
@@ -2711,7 +2721,10 @@ void * monitor_replication_lag_thread(void *arg) {
 		}
 	}
 	if (use_percona_heartbeat == false) {
-		mmsd->async_exit_status=mysql_query_start(&mmsd->interr,mmsd->mysql,"SHOW SLAVE STATUS");
+		const char* query {
+			mysql_get_server_version(mmsd->mysql) < 80023 ? "SHOW SLAVE STATUS" : "SHOW REPLICA STATUS"
+		};
+		mmsd->async_exit_status=mysql_query_start(&mmsd->interr,mmsd->mysql, query);
 	}
 #endif // TEST_REPLICATIONLAG
 	while (mmsd->async_exit_status) {
@@ -2819,7 +2832,10 @@ void * monitor_replication_lag_thread(void *arg) {
 					{
 						for(k = 0; k < num_fields; k++) {
 							if (fields[k].name) {
-								if (strcmp("Seconds_Behind_Master", fields[k].name)==0) {
+								if (
+									strcmp("Seconds_Behind_Master", fields[k].name)==0
+									|| strcmp("Seconds_Behind_Source", fields[k].name)==0
+								) {
 									j=k;
 								}
 							}
@@ -7909,7 +7925,10 @@ bool MySQL_Monitor::monitor_replication_lag_process_ready_tasks(const std::vecto
 			{
 				for (k = 0; k < num_fields; k++) {
 					if (fields[k].name) {
-						if (strcmp("Seconds_Behind_Master", fields[k].name) == 0) {
+						if (
+							strcmp("Seconds_Behind_Master", fields[k].name)==0
+							|| strcmp("Seconds_Behind_Source", fields[k].name)==0
+						) {
 							j = k;
 						}
 					}
diff --git a/lib/MySQL_Query_Processor.cpp b/lib/MySQL_Query_Processor.cpp
index 561fd3084..b81f8650e 100644
--- a/lib/MySQL_Query_Processor.cpp
+++ b/lib/MySQL_Query_Processor.cpp
@@ -24,6 +24,7 @@ static char* commands_counters_desc[MYSQL_COM_QUERY___NONE] = {
 	[MYSQL_COM_QUERY_BEGIN] = (char*)"BEGIN",
 	[MYSQL_COM_QUERY_CALL] = (char*)"CALL",
 	[MYSQL_COM_QUERY_CHANGE_MASTER] = (char*)"CHANGE_MASTER",
+	[MYSQL_COM_QUERY_CHANGE_REPLICATION_SOURCE] = (char*)"MYSQL_COM_QUERY_CHANGE_REPLICATION_SOURCE",
 	[MYSQL_COM_QUERY_COMMIT] = (char*)"COMMIT",
 	[MYSQL_COM_QUERY_CREATE_DATABASE] = (char*)"CREATE_DATABASE",
 	[MYSQL_COM_QUERY_CREATE_INDEX] = (char*)"CREATE_INDEX",
@@ -55,7 +56,9 @@ static char* commands_counters_desc[MYSQL_COM_QUERY___NONE] = {
 	[MYSQL_COM_QUERY_RELEASE_SAVEPOINT] = (char*)"RELEASE_SAVEPOINT",
 	[MYSQL_COM_QUERY_RENAME_TABLE] = (char*)"RENAME_TABLE",
 	[MYSQL_COM_QUERY_RESET_MASTER] = (char*)"RESET_MASTER",
+	[MYSQL_COM_QUERY_RESET_BINARY_LOGS_AND_GTIDS] = (char*)"RESET_BINARY_LOGS_AND_GTIDS",
 	[MYSQL_COM_QUERY_RESET_SLAVE] = (char*)"RESET_SLAVE",
+	[MYSQL_COM_QUERY_RESET_REPLICA] = (char*)"RESET_REPLICA",
 	[MYSQL_COM_QUERY_REPLACE] = (char*)"REPLACE",
 	[MYSQL_COM_QUERY_REVOKE] = (char*)"REVOKE",
 	[MYSQL_COM_QUERY_ROLLBACK] = (char*)"ROLLBACK",
@@ -263,6 +266,10 @@ enum MYSQL_COM_QUERY_command MySQL_Query_Processor::query_parser_command_type(SQ
 				ret = MYSQL_COM_QUERY_CHANGE_MASTER;
 				break;
 			}
+			if (!strcasecmp("REPLICATION", token)) {
+				ret = MYSQL_COM_QUERY_CHANGE_REPLICATION_SOURCE;
+				break;
+			}
 			break;
 		}
 		if (!strcasecmp("COMMIT", token)) { // COMMIT
@@ -442,10 +449,18 @@ enum MYSQL_COM_QUERY_command MySQL_Query_Processor::query_parser_command_type(SQ
 				ret = MYSQL_COM_QUERY_RESET_MASTER;
 				break;
 			}
+			if (!strcasecmp("BINARY", token)) {
+				ret = MYSQL_COM_QUERY_RESET_BINARY_LOGS_AND_GTIDS;
+				break;
+			}
 			if (!strcasecmp("SLAVE", token)) {
 				ret = MYSQL_COM_QUERY_RESET_SLAVE;
 				break;
 			}
+			if (!strcasecmp("REPLICA", token)) {
+				ret = MYSQL_COM_QUERY_RESET_REPLICA;
+				break;
+			}
 			break;
 		}
 		if (!strcasecmp("REVOKE", token)) { // REVOKE
diff --git a/src/SQLite3_Server.cpp b/src/SQLite3_Server.cpp
index da0949520..f283f454d 100644
--- a/src/SQLite3_Server.cpp
+++ b/src/SQLite3_Server.cpp
@@ -871,7 +871,13 @@ void SQLite3_Server_session_handler(MySQL_Session* sess, void *_pa, PtrSize_t *p
 			}
 #endif // TEST_READONLY
 #ifdef TEST_REPLICATIONLAG
-			if (strncasecmp("SELECT SLAVE STATUS ", query_no_space, strlen("SELECT SLAVE STATUS ")) == 0) {
+			if (
+				strncasecmp("SELECT SLAVE STATUS ", query_no_space, strlen("SELECT SLAVE STATUS ")) == 0
+				|| strncasecmp("SELECT REPLICA STATUS ", query_no_space, strlen("SELECT REPLICA STATUS ")) == 0
+			) {
+				uint64_t addr_offset {
+					strstr(query_no_space, "REPLICA") ?  strlen("SELECT REPLICA STATUS ") : strlen("SELECT SLAVE STATUS ")
+				};
 				if (strlen(query_no_space) > strlen("SELECT SLAVE STATUS ") + 5) {
 					pthread_mutex_lock(&GloSQLite3Server->test_replicationlag_mutex);
 					// the current test doesn't try to simulate failures, therefore it will return immediately
@@ -879,17 +885,15 @@ void SQLite3_Server_session_handler(MySQL_Session* sess, void *_pa, PtrSize_t *p
 						// probably never initialized
 						GloSQLite3Server->load_replicationlag_table(sess);
 					}
-					const int* rc = GloSQLite3Server->replicationlag_test_value(query_no_space + strlen("SELECT SLAVE STATUS "));
+					const int* rc = GloSQLite3Server->replicationlag_test_value(query_no_space + addr_offset);
 					free(query);
-					if (rc == nullptr) {
-						const char* a = (char*)"SELECT null as Seconds_Behind_Master";
-						query = (char*)malloc(strlen(a) + 2);
-						sprintf(query, a);
-					} else {
-						const char* a = (char*)"SELECT %d as Seconds_Behind_Master";
-						query = (char*)malloc(strlen(a) + 2);
-						sprintf(query, a, *rc);
-					}
+
+					string SELECT { "SELECT " + (rc ? std::to_string(*rc) : string { "null" }) + " AS " };
+					SELECT += strstr(query_no_space, "REPLICA") ? "Seconds_Behind_Source" : "Seconds_Behind_Master";
+
+					query = static_cast<char*>(malloc(SELECT.size() + 1));
+					sprintf(query, SELECT.c_str());
+
 					pthread_mutex_unlock(&GloSQLite3Server->test_replicationlag_mutex);
 				}
 			}


Test patch: