Description:
mysql_insert_id() is not reset after mysql_change_user().
I tend to say it is a bug because its a way to access session information that should have been reset by COM_CHANGE_USER. The purpose of COM_CHANGE_USER is to provide a session as if its a new connect. Neither the C-API nor any SQL function should return data from the previous session. That is against the idea of COM_CHANGE_USER.
If its not regarded as a bug, I'd like to see this detail documented.
FYI - currently the MySQL native driver for PHP (mysqlnd) and libmysql differ. mysqlnd will reset the mysql_insert_id() whereas libmysql does not.
How to repeat:
Compile and run the test program below
#include "stdio.h"
#include "stdlib.h"
#include "string.h"
#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>
int exit_failure(const char *msg, MYSQL *mysql, MYSQL_RES *res);
/* Connection parameter - change */
const char *con_host = "localhost";
const char *con_user = "root";
const char *con_pass = "root";
const char *con_db = "test";
unsigned int con_port = 3306;
const char *con_socket = "/tmp/mysql.sock";
unsigned long con_flags = 0;
MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;
my_ulonglong insert_id;
int main(void) {
printf("\n");
printf("Connecting...\n");
conn = mysql_init(NULL);
if (conn == NULL)
exit_failure("mysql_init() failed", NULL, NULL);
if (mysql_real_connect(conn, con_host, con_user, con_pass, con_db, con_port, con_socket, con_flags) == NULL)
exit_failure("mysql_real_connect() failed", conn, NULL);
printf("Server: %s\n", mysql_get_server_info(conn));
printf("Protocol: %u\n", mysql_get_proto_info(conn));
printf("Client: %s\n\n", mysql_get_client_info());
printf("Inserting 1 row in an auto_increment column...\n");
if (mysql_query(conn, "DROP TABLE IF EXISTS test") != 0)
exit_failure("DROP TABLE IF EXISTS failed", conn, NULL);
if (mysql_query(conn, "CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, label CHAR(10))") != 0)
exit_failure("CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, label CHAR(10)) failed", conn, NULL);
if (mysql_query(conn, "INSERT INTO test(id, label) VALUES (100, 'z')") != 0)
exit_failure("INSERT INTO test(id, label) VALUES (100, 'z') failed", conn, NULL);
insert_id = mysql_insert_id(conn);
printf("mysqli_insert_id() returns '%llu'...\n", insert_id);
printf("Calling change_user() ...\n");
if (mysql_change_user(conn, con_user, con_pass, con_db) != 0)
exit_failure("mysql_change_user() failed", conn, NULL);
printf("Checking if change_user() has reset LAST_INSERT_ID()...\n");
if (mysql_query(conn, "SELECT LAST_INSERT_ID()") != 0)
exit_failure("SELECT LAST_INSERT_ID()", conn, NULL);
res = mysql_store_result(conn);
if (res == NULL)
exit_failure("mysql_store_result() failed", conn, NULL);
if ((row = mysql_fetch_row(res)) == NULL)
exit_failure("mysql_fetch_row() failed", conn, res);
printf("SELECT LAST_INSERT_ID() returns '%s'...\n", row[0]);
mysql_free_result(res);
insert_id = mysql_insert_id(conn);
printf("mysqli_insert_id() returns '%llu'...\n", insert_id);
mysql_close(conn);
return EXIT_SUCCESS;
}
int exit_failure(const char *msg, MYSQL *mysql, MYSQL_RES *res) {
printf("ERROR: %s\n", msg);
if (mysql) {
printf("[%u] %s\n", mysql_errno(mysql), mysql_error(mysql));
if (res)
mysql_free_result(res);
mysql_close(mysql);
}
printf("\n");
exit(EXIT_FAILURE);
}
Description: mysql_insert_id() is not reset after mysql_change_user(). I tend to say it is a bug because its a way to access session information that should have been reset by COM_CHANGE_USER. The purpose of COM_CHANGE_USER is to provide a session as if its a new connect. Neither the C-API nor any SQL function should return data from the previous session. That is against the idea of COM_CHANGE_USER. If its not regarded as a bug, I'd like to see this detail documented. FYI - currently the MySQL native driver for PHP (mysqlnd) and libmysql differ. mysqlnd will reset the mysql_insert_id() whereas libmysql does not. How to repeat: Compile and run the test program below #include "stdio.h" #include "stdlib.h" #include "string.h" #include <my_global.h> #include <my_sys.h> #include <mysql.h> int exit_failure(const char *msg, MYSQL *mysql, MYSQL_RES *res); /* Connection parameter - change */ const char *con_host = "localhost"; const char *con_user = "root"; const char *con_pass = "root"; const char *con_db = "test"; unsigned int con_port = 3306; const char *con_socket = "/tmp/mysql.sock"; unsigned long con_flags = 0; MYSQL *conn; MYSQL_RES *res; MYSQL_ROW row; my_ulonglong insert_id; int main(void) { printf("\n"); printf("Connecting...\n"); conn = mysql_init(NULL); if (conn == NULL) exit_failure("mysql_init() failed", NULL, NULL); if (mysql_real_connect(conn, con_host, con_user, con_pass, con_db, con_port, con_socket, con_flags) == NULL) exit_failure("mysql_real_connect() failed", conn, NULL); printf("Server: %s\n", mysql_get_server_info(conn)); printf("Protocol: %u\n", mysql_get_proto_info(conn)); printf("Client: %s\n\n", mysql_get_client_info()); printf("Inserting 1 row in an auto_increment column...\n"); if (mysql_query(conn, "DROP TABLE IF EXISTS test") != 0) exit_failure("DROP TABLE IF EXISTS failed", conn, NULL); if (mysql_query(conn, "CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, label CHAR(10))") != 0) exit_failure("CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, label CHAR(10)) failed", conn, NULL); if (mysql_query(conn, "INSERT INTO test(id, label) VALUES (100, 'z')") != 0) exit_failure("INSERT INTO test(id, label) VALUES (100, 'z') failed", conn, NULL); insert_id = mysql_insert_id(conn); printf("mysqli_insert_id() returns '%llu'...\n", insert_id); printf("Calling change_user() ...\n"); if (mysql_change_user(conn, con_user, con_pass, con_db) != 0) exit_failure("mysql_change_user() failed", conn, NULL); printf("Checking if change_user() has reset LAST_INSERT_ID()...\n"); if (mysql_query(conn, "SELECT LAST_INSERT_ID()") != 0) exit_failure("SELECT LAST_INSERT_ID()", conn, NULL); res = mysql_store_result(conn); if (res == NULL) exit_failure("mysql_store_result() failed", conn, NULL); if ((row = mysql_fetch_row(res)) == NULL) exit_failure("mysql_fetch_row() failed", conn, res); printf("SELECT LAST_INSERT_ID() returns '%s'...\n", row[0]); mysql_free_result(res); insert_id = mysql_insert_id(conn); printf("mysqli_insert_id() returns '%llu'...\n", insert_id); mysql_close(conn); return EXIT_SUCCESS; } int exit_failure(const char *msg, MYSQL *mysql, MYSQL_RES *res) { printf("ERROR: %s\n", msg); if (mysql) { printf("[%u] %s\n", mysql_errno(mysql), mysql_error(mysql)); if (res) mysql_free_result(res); mysql_close(mysql); } printf("\n"); exit(EXIT_FAILURE); }