Bug #41785 C API doesn't know about change in server status after COM_CHANGE_USER
Submitted: 29 Dec 2008 16:41 Modified: 29 Dec 2008 17:46
Reporter: Georg Richter Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: C API (client library) Severity:S3 (Non-critical)
Version:5.0, 5.1, 6.0 bzr OS:Any
Assigned to: CPU Architecture:Any
Tags: mysql_change_user, regression
Triage: Triaged: D3 (Medium)

[29 Dec 2008 16:41] Georg Richter
Server doesn't reset sql_mode after COM_CHANGE_USER.

From http://dev.mysql.com/doc/refman/6.0/en/mysql-change-user.html:

"This command resets the state as if one had done a new connect"

How to repeat:
int test_change_user()
  MYSQL *mysql;
  char out[10];
  int len;

  mysql= mysql_init(NULL);
  mysql= mysql_real_connect(mysql, "localhost", "root", "", "test", 0, NULL, 0);

  len= mysql_real_escape_string(mysql, out, "\\", 1);
  printf("Len of escaped string is %d\n", len);

  mysql_query(mysql, "SET SQL_MODE=NO_BACKSLASH_ESCAPES");

  mysql_change_user(mysql, "root", "", "test");

  len= mysql_real_escape_string(mysql, out, "\\", 1);
  printf("Len of escaped string is %d\n", len);

  return OK;

Len of escaped string is 2
Len of escaped string is 1
[29 Dec 2008 17:46] Sveta Smirnova
Thank you for the report.

Verified as described. But I think this can be C API problem, because if compile test with 4.1 libraries  mysql_change_user behaves as described in manual.
[29 Dec 2008 17:47] Sveta Smirnova
Server used is same for all tests
[28 Feb 2009 21:15] Jim Winstead
The problem is more subtle than the old synopsis implied. The SQL_MODE does get reset by the server, but the updated server_status is not sent as part of the re-authentication, so that is not updated until some other command is executed that does.

You don't see any difference with 4.1 because 4.1 didn't have NO_BACKSLASH_ESCAPES, so mysql_real_escape_string() from 4.1 doesn't behave differently no matter how it is set.

Fixing this should probably be considered part of WL#4797.