Description:
'init_connect' assigned variables are forgotten after a `mysql_change_user` or `mysql_reset_connection`. In other words, `init_connect` isn't re-executed after neither `mysql_change_user` or `mysql_reset_connection`. The documentation isn't clear about the expected behavior for this case, for `mysql_change_user` the documentation states:
`https://dev.mysql.com/doc/dev/mysql-server/9.2.0/page_protocol_com_change_user.html`:
```
Also and resets the following connection state:
- user variables
- temporary tables
- prepared statements
- ... and others
It is going through the same states as the Initial Handshake.
```
And also states:
`https://dev.mysql.com/doc/c-api/9.3/en/mysql-change-user.html`:
```
This function resets the session state as if one had done a new connect and reauthenticated.
```
For what can be read in the documentation, a `COM_CHANGE_USER` should imply the same steps as a `new connect` operation, this should include `init_connect` as it's an operation always performed on connect.
How to repeat:
The following simple C++ program reproduces the issue in a MySQL 9. In the server, perform the following query:
`MySQL 9`:
```
mysql> SET GLOBAL init_connect="SET sql_safe_updates=ON";
--------------
SET GLOBAL init_connect="SET sql_safe_updates=ON"
--------------
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@version;
--------------
SELECT @@version
--------------
+-----------+
| @@version |
+-----------+
| 9.3.0 |
+-----------+
1 row in set (0.00 sec)
```
And then the following script should be executed against the server:
```cpp
/**
* @file init_connect_test-t.cpp
* @brief Simple check for 'init_connect' behavior together with 'mysql_change_user'.
*/
#include <string>
#include <stdio.h>
#include <mysql/mysql.h>
using std::string;
const char* host { "127.0.0.1" };
const int port { 13306 };
const char* user { "sbtest" };
const char* pass { "sbtest" };
int fetch_query_res(MYSQL* mysql, const string& query) {
printf("Fetching query result query=\"%s\"\n", query.c_str());
int rc = mysql_query(mysql, query.c_str());
if (rc) {
printf("Failed 'query' err=\"%s\"\n", mysql_error(mysql));
return EXIT_FAILURE;
}
MYSQL_RES* myres = mysql_store_result(mysql);
if (myres == nullptr) {
printf("Failed 'store_result' err=\"%s\"\n", mysql_error(mysql));
return EXIT_FAILURE;
}
MYSQL_ROW myrow = mysql_fetch_row(myres);
printf("Fetched value query=\"%s\" val=%s\n", query.c_str(), myrow[0]);
mysql_free_result(myres);
return EXIT_SUCCESS;
}
int main(int argc, char** argv) {
MYSQL* mysql = mysql_init(NULL);
if (!mysql_real_connect(mysql, host, user, pass, NULL, port, NULL, 0)) {
printf("Failed to connect err=\"%s\"", mysql_error(mysql));
return EXIT_FAILURE;
}
int rc = fetch_query_res(mysql, "SELECT @@init_connect");
if (rc) { return EXIT_FAILURE; }
rc = fetch_query_res(mysql, "SELECT @@sql_safe_updates");
if (rc) { return EXIT_FAILURE; }
// rc = mysql_reset_connection(mysql);
rc = mysql_change_user(mysql, user, pass, NULL);
if (rc) {
printf("Failed 'mysql_change_user' rc=%d err=\"%s\"\n", rc, mysql_error(mysql));
return EXIT_FAILURE;
} else {
printf("Issued 'mysql_change_user'\n");
}
rc = fetch_query_res(mysql, "SELECT @@init_connect");
if (rc) { return EXIT_FAILURE; }
rc = fetch_query_res(mysql, "SELECT @@sql_safe_updates");
if (rc) { return EXIT_FAILURE; }
mysql_close(mysql);
return EXIT_SUCCESS;
}
```
The output is the following:
```
$ ./init_connect_test-t
Fetching query result query="SELECT @@init_connect"
Fetched value query="SELECT @@init_connect" val=SET sql_safe_updates=ON
Fetching query result query="SELECT @@sql_safe_updates"
Fetched value query="SELECT @@sql_safe_updates" val=1
Issued 'mysql_change_user'
Fetching query result query="SELECT @@init_connect"
Fetched value query="SELECT @@init_connect" val=SET sql_safe_updates=ON
Fetching query result query="SELECT @@sql_safe_updates"
Fetched value query="SELECT @@sql_safe_updates" val=0
```
While the value for `init_connect` seems to be preserved, the session value for `sql_safe_updates` is forgotten.
Suggested fix:
`init_connect` should be re-executed after `mysql_change_user`. This will make the with what the documentation states, resetting the session 'as if one had done a new connect and reauthenticated'.
