Repository: sysown/proxysql (language: cpp)

Issue:
proxysql admin module is not compatible with MySQL 8.1.0
Use the mysql client of version 8.1.0 to connect to the proxysql admin interface, and any command will fail. for example,
```
# mysql --version
mysql  Ver 8.1.0 for Linux on x86_64 (MySQL Community Server - GPL)

# mysql -uadmin -padmin -P 6032 -h 127.0.0.1 -e "show variables"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2014 (HY000) at line 1: Commands out of sync; you can't run this command now
```
But use the mysql client of version 8.0.x (8.0.26 for instance) is OK. I tried to use the strace tool to capture the differences between the two versions, and found the only difference is that the mysql client of version 8.1.0 issues one more query "select $$" to the server.

According to https://dev.mysql.com/doc/relnotes/mysql/8.1/en/news-8-1-0.html , the use of an unquoted identifier starting with the dollar sign and containing one or more dollar signs in addition to the first one generates a syntax error.  The mysql client then added a new detection to check if the server supports dollar quotes, code can be found at https://github.com/mysql/mysql-server/blob/mysql-8.1.0/client/mysql.cc#L1269 .

It seems that proxysql admin modules does not handle this probe correctly?

Current workaround is to use the legacy 8.0.x mysql client.




Discussion / hints:
add more info: 
OS: CentOS 7.9

Fix patch:
diff --git a/include/proxysql_admin.h b/include/proxysql_admin.h
index 24a365294e..12037861cf 100644
--- a/include/proxysql_admin.h
+++ b/include/proxysql_admin.h
@@ -390,7 +390,7 @@ class ProxySQL_Admin {
 	void admin_shutdown();
 	bool is_command(std::string);
 	void send_MySQL_OK(MySQL_Protocol *myprot, char *msg, int rows=0);
-	void send_MySQL_ERR(MySQL_Protocol *myprot, char *msg);
+	void send_MySQL_ERR(MySQL_Protocol *myprot, char *msg, uint32_t code=1045);
 #ifdef DEBUG
 	// these two following functions used to just call and return one function each
 	// this approach was replaced when we introduced debug filters
diff --git a/include/proxysql_utils.h b/include/proxysql_utils.h
index eb510357a9..ffe3fe1bba 100644
--- a/include/proxysql_utils.h
+++ b/include/proxysql_utils.h
@@ -210,4 +210,12 @@ std::string replace_str(const std::string& str, const std::string& match, const
 std::string generate_multi_rows_query(int rows, int params);
 
 void close_all_non_term_fd(std::vector<int> excludeFDs);
+
+/**
+ * @brief Returns the expected error for query 'SELECT $$'.
+ * @param version The 'server_version' for which the error should match.
+ * @return A pair of the shape '{err_code,err_msg}'.
+ */
+std::pair<int,const char*> get_dollar_quote_error(const char* version);
+
 #endif
diff --git a/lib/MySQL_Session.cpp b/lib/MySQL_Session.cpp
index 86d2cba5ce..b65cc171c6 100644
--- a/lib/MySQL_Session.cpp
+++ b/lib/MySQL_Session.cpp
@@ -1372,6 +1372,22 @@ bool MySQL_Session::handler_special_queries(PtrSize_t *pkt) {
 		l_free(pkt->size,pkt->ptr);
 		return true;
 	}
+	// MySQL client check command for dollars quote support, starting at version '8.1.0'. See #4300.
+	if ((pkt->size == strlen("SELECT $$") + 5) && strncasecmp("SELECT $$", (char*)pkt->ptr + 5, pkt->size - 5) == 0) {
+		pair<int,const char*> err_info { get_dollar_quote_error(mysql_thread___server_version) };
+
+		client_myds->DSS=STATE_QUERY_SENT_NET;
+		client_myds->myprot.generate_pkt_ERR(true, NULL, NULL, 1, err_info.first, (char *)"HY000", err_info.second, true);
+		client_myds->DSS=STATE_SLEEP;
+		status=WAITING_CLIENT_DATA;
+
+		if (mirror==false) {
+			RequestEnd(NULL);
+		}
+		l_free(pkt->size,pkt->ptr);
+
+		return true;
+	}
 	if (locked_on_hostgroup >= 0 && (strncasecmp((char *)"SET ",(char *)pkt->ptr+5,4)==0)) {
 		// this is a circuit breaker, we will send everything to the backend
 		//
diff --git a/lib/ProxySQL_Admin.cpp b/lib/ProxySQL_Admin.cpp
index 12fcfc4d6d..a420b3e1e5 100644
--- a/lib/ProxySQL_Admin.cpp
+++ b/lib/ProxySQL_Admin.cpp
@@ -4561,6 +4561,14 @@ void admin_session_handler(MySQL_Session *sess, void *_pa, PtrSize_t *pkt) {
 		goto __run_query;
 	}
 
+	// MySQL client check command for dollars quote support, starting at version '8.1.0'. See #4300.
+	if (!strncasecmp("SELECT $$", query_no_space, strlen("SELECT $$"))) {
+		pair<int,const char*> err_info { get_dollar_quote_error(mysql_thread___server_version) };
+		SPA->send_MySQL_ERR(&sess->client_myds->myprot, const_cast<char*>(err_info.second), err_info.first);
+		run_query=false;
+		goto __run_query;
+	}
+
 	if (query_no_space_length==SELECT_VERSION_COMMENT_LEN) {
 		if (!strncasecmp(SELECT_VERSION_COMMENT, query_no_space, query_no_space_length)) {
 			l_free(query_length,query);
@@ -11166,14 +11174,14 @@ void ProxySQL_Admin::send_MySQL_OK(MySQL_Protocol *myprot, char *msg, int rows)
 	myds->DSS=STATE_SLEEP;
 }
 
-void ProxySQL_Admin::send_MySQL_ERR(MySQL_Protocol *myprot, char *msg) {
+void ProxySQL_Admin::send_MySQL_ERR(MySQL_Protocol *myprot, char *msg, uint32_t code) {
 	assert(myprot);
 	MySQL_Data_Stream *myds=myprot->get_myds();
 	myds->DSS=STATE_QUERY_SENT_DS;
 	char *a = (char *)"ProxySQL Admin Error: ";
 	char *new_msg = (char *)malloc(strlen(msg)+strlen(a)+1);
 	sprintf(new_msg, "%s%s", a, msg);
-	myprot->generate_pkt_ERR(true,NULL,NULL,1,1045,(char *)"28000",new_msg);
+	myprot->generate_pkt_ERR(true,NULL,NULL,1,code,(char *)"28000",new_msg);
 	free(new_msg);
 	myds->DSS=STATE_SLEEP;
 }
diff --git a/lib/proxysql_utils.cpp b/lib/proxysql_utils.cpp
index 27d8051beb..5475afb459 100644
--- a/lib/proxysql_utils.cpp
+++ b/lib/proxysql_utils.cpp
@@ -1,4 +1,5 @@
 #include "proxysql_utils.h"
+#include "mysqld_error.h"
 
 #include <functional>
 #include <sstream>
@@ -431,3 +432,22 @@ void close_all_non_term_fd(std::vector<int> excludeFDs) {
 		}
 	}
 }
+
+std::pair<int,const char*> get_dollar_quote_error(const char* version) {
+	const char* ER_PARSE_MSG {
+		"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 '$$' at line 1"
+	};
+
+	if (strcasecmp(version,"8.1.0") == 0) {
+		return { ER_PARSE_ERROR, ER_PARSE_MSG };
+	} else {
+		if (strncasecmp(version, "8.1", 3) == 0) {
+			// SQLSTATE: 42000
+			return { ER_PARSE_ERROR, ER_PARSE_MSG };
+		} else {
+			// SQLSTATE: 42S22
+			return { ER_BAD_FIELD_ERROR, "Unknown column '$$' in 'field list'" };
+		}
+	}
+}


Test patch:
diff --git a/test/tap/tap/utils.h b/test/tap/tap/utils.h
index c490b9dfe4..6cc2aa997f 100644
--- a/test/tap/tap/utils.h
+++ b/test/tap/tap/utils.h
@@ -524,6 +524,20 @@ enum SQ3_RES_T {
  */
 sq3_res_t sqlite3_execute_stmt(sqlite3* db, const std::string& query);
 
+/**
+ * @brief If found returns the element index, -1 otherwise.
+ */
+template <typename T>
+int64_t get_elem_idx(const T& e, const std::vector<T>& v) {
+	const auto& it = std::find(v.begin(), v.end(), e);
+
+	if (it == v.end()) {
+		return -1;
+	} else {
+		return it - v.begin();
+	}
+}
+
 /**
  * @brief Returns a 'JSON' object holding 'PROXYSQL INTERNAL SESSION' contents.
  * @param proxy And already openned connection to ProxySQL.
diff --git a/test/tap/tests/reg_test_4300-dollar_quote_check-t.cpp b/test/tap/tests/reg_test_4300-dollar_quote_check-t.cpp
new file mode 100644
index 0000000000..72ecc5b70c
--- /dev/null
+++ b/test/tap/tests/reg_test_4300-dollar_quote_check-t.cpp
@@ -0,0 +1,149 @@
+/**
+ * @file reg_test_4300-dollar_quote_check-t.cpp
+ * @brief Checks that ProxySQL properly handles the query 'SELECT $$'.
+ * @details A check via 'SELECT $$' was introduced by MySQL client in version '8.1.0'. It's used for testing
+ *  whether the server supports multiple '$$' signs or returns a syntax error (deprecated).
+ */
+
+#include <cassert>
+#include <cstring>
+#include <string>
+#include <utility>
+#include <vector>
+
+#include <stdio.h>
+#include <time.h>
+#include <unistd.h>
+
+#include <mysql.h>
+#include <mysql/mysqld_error.h>
+
+#include "tap.h"
+#include "utils.h"
+#include "command_line.h"
+
+using std::vector;
+using std::string;
+
+const vector<string> versions { "5.6", "5.7", "8.0", "8.1.0", "8.1", "8.1.4" };
+
+int test_supports_dollar_quote(MYSQL* conn, int v_idx, int v8_1_0_idx) {
+	int rc = mysql_query_t(conn, "SELECT $$");
+
+	int exp_code = -1;
+	int act_code = mysql_errno(conn);
+
+	const char* exp_msg = "";
+	const char* act_msg = mysql_error(conn);
+
+	if (v_idx < v8_1_0_idx) {
+		exp_code = ER_BAD_FIELD_ERROR;
+		exp_msg = "Unknown column '$$' in 'field list'";
+	} else {
+		exp_code = ER_PARSE_ERROR;
+		exp_msg = "You have an error in your SQL syntax";
+	}
+
+	bool code_match = exp_code == act_code;
+	bool msg_match = strstr(act_msg, exp_msg) != nullptr;
+
+	ok(
+		rc && code_match && msg_match,
+		"Error code and message should match expected - exp:{%d,'%s'}, act:{%d,'%s'}",
+		exp_code, exp_msg, act_code, act_msg
+	);
+
+	const string rnd_str { random_string(30) };
+	rc = mysql_query_t(conn, ("SELECT '" + rnd_str + "'").c_str());
+	MYSQL_RES* myres = mysql_store_result(conn);
+	MYSQL_ROW myrow = mysql_fetch_row(myres);
+	string rnd_res { myrow[0] };
+	mysql_free_result(myres);
+
+	ok(
+		rc == 0 && rnd_str == rnd_res,
+		"Simple 'SELECT' is correctly executed after intercepted one - exp:'%s', act:'%s'",
+		rnd_str.c_str(), rnd_res.c_str()
+	);
+
+	return EXIT_SUCCESS;
+}
+
+int test_versions_mysql(MYSQL* admin, MYSQL* proxy, const vector<string>& versions) {
+	const int64_t v8_1_0_idx { get_elem_idx(string { "8.1.0" }, versions) };
+	assert(v8_1_0_idx != -1 && "Invalid test payload, no '8.1.0' present in tested versions");
+
+	for (size_t i = 0; i < versions.size(); i++) {
+		const string& v { versions[i] };
+		const string v_minor { v == "8.1.0" ? v : v + "." + std::to_string(rand()) };
+
+		MYSQL_QUERY_T(admin, ("UPDATE global_variables SET variable_value='" + v_minor + "' WHERE variable_name='mysql-server_version'").c_str());
+		MYSQL_QUERY_T(admin, "LOAD MYSQL VARIABLES TO RUNTIME");
+
+		test_supports_dollar_quote(proxy, i, v8_1_0_idx);
+	}
+
+	return EXIT_SUCCESS;
+}
+
+int test_versions_admin(CommandLine& cl, MYSQL* admin, const vector<string>& versions) {
+	const int64_t v8_1_0_idx { get_elem_idx(string { "8.1.0" }, versions) };
+	assert(v8_1_0_idx != -1 && "Invalid test payload, no '8.1.0' present in tested versions");
+
+
+	for (size_t i = 0; i < versions.size(); i++) {
+		const string& v { versions[i] };
+		const string v_minor { v == "8.1.0" ? v : v + "." + std::to_string(rand()) };
+
+		MYSQL_QUERY_T(admin, ("UPDATE global_variables SET variable_value='" + v_minor + "' WHERE variable_name='mysql-server_version'").c_str());
+		MYSQL_QUERY_T(admin, "LOAD MYSQL VARIABLES TO RUNTIME");
+
+		MYSQL* new_admin = mysql_init(NULL);
+
+		if (!mysql_real_connect(new_admin, cl.host, cl.admin_username, cl.admin_password, NULL, cl.admin_port, NULL, 0)) {
+			fprintf(stderr, "File %s, line %d, Error: %s\n", __FILE__, __LINE__, mysql_error(new_admin));
+			return EXIT_FAILURE;
+		}
+
+		test_supports_dollar_quote(new_admin, i, v8_1_0_idx);
+
+		mysql_close(new_admin);
+	}
+
+	return EXIT_SUCCESS;
+}
+
+int main(int argc, char** argv) {
+	CommandLine cl;
+
+	plan((versions.size()*2 + 1)*2);
+
+	if (cl.getEnv()) {
+		diag("Failed to get the required environmental variables.");
+		return EXIT_FAILURE;
+	}
+
+	MYSQL* proxy = mysql_init(NULL);
+	MYSQL* admin = mysql_init(NULL);
+
+	if (!mysql_real_connect(proxy, cl.host, cl.username, cl.password, NULL, cl.port, NULL, 0)) {
+		fprintf(stderr, "File %s, line %d, Error: %s\n", __FILE__, __LINE__, mysql_error(proxy));
+		return EXIT_FAILURE;
+	}
+
+	if (!mysql_real_connect(admin, cl.host, cl.admin_username, cl.admin_password, NULL, cl.admin_port, NULL, 0)) {
+		fprintf(stderr, "File %s, line %d, Error: %s\n", __FILE__, __LINE__, mysql_error(admin));
+		return EXIT_FAILURE;
+	}
+
+	int rc = test_versions_mysql(admin, proxy, versions);
+	ok(rc == EXIT_SUCCESS, "Multiple 'mysql-server_version' tested correctly against MySQL interface");
+
+	rc = test_versions_admin(cl, admin, versions);
+	ok(rc == EXIT_SUCCESS, "Multiple 'mysql-server_version' tested correctly against Admin interface");
+
+	mysql_close(proxy);
+	mysql_close(admin);
+
+	return exit_status();
+}