Bug #30472 libmysql doesn't reset charset, insert_id after succ. mysql_change_user() call
Submitted: 17 Aug 2007 13:19 Modified: 25 Oct 2007 0:06
Reporter: Andrey Hristov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S2 (Serious)
Version:5.1.22-dev/4.1/5.0 OS:Any
Assigned to: Alexander Nozdrin CPU Architecture:Any
Tags: mysql_change_user

[17 Aug 2007 13:19] Andrey Hristov
Description:
mysql_change_user() doesn't reset the character after successful COM_CHANGE_USER. See "How to repeat" for more information.

How to repeat:
[16:09:36] andrey@lmy004:~/test> ./charsetreset
Character set is : latin1
variable=character_set_client           value=    latin1
variable=character_set_connection       value=    latin1
variable=character_set_database         value=    latin1
variable=character_set_filesystem       value=    binary
variable=character_set_results          value=    latin1
variable=character_set_server           value=    latin1
variable=character_set_system           value=      utf8
variable=character_sets_dir             value=/usr/local/mysql/share/mysql/charsets/

Charset changed to utf8

Character set is : utf8
variable=character_set_client           value=      utf8
variable=character_set_connection       value=      utf8
variable=character_set_database         value=    latin1
variable=character_set_filesystem       value=    binary
variable=character_set_results          value=      utf8
variable=character_set_server           value=    latin1
variable=character_set_system           value=      utf8
variable=character_sets_dir             value=/usr/local/mysql/share/mysql/charsets/

Changed user

Character set is : utf8   <---- Still utf8, on the server however latin1
variable=character_set_client           value=    latin1
variable=character_set_connection       value=    latin1
variable=character_set_database         value=    latin1
variable=character_set_filesystem       value=    binary
variable=character_set_results          value=    latin1
variable=character_set_server           value=    latin1
variable=character_set_system           value=      utf8
variable=character_sets_dir             value=/usr/local/mysql/share/mysql/charsets/

-----------------------

#include <mysql.h>
#include <stdio.h>

#define MHOST "127.0.0.1"
#define MUSER "root"
#define MPASS ""
#define MDB   "test"
#define MPORT 3306

int main()
{
  MYSQL mysql;
  MYSQL_RES *result;
  MYSQL_ROW row;

  mysql_init(&mysql);

  if (NULL == mysql_real_connect(&mysql, MHOST, MUSER, MPASS,
                                 MDB, MPORT,
                                 NULL, CLIENT_MULTI_RESULTS)) {
    printf("Error while connecting");
    exit(1);
  }
  printf("Character set is : %s\n",
       mysql_character_set_name(&mysql));

  if (mysql_query(&mysql, "show variables like 'char%'")) {
    printf("Error while querying [1]");
    exit(1);
  }
  if (!(result = mysql_store_result(&mysql))) {
    printf("Error while storing result [1]");
    exit(1);  
  }
  while (row = mysql_fetch_row(result)) {
    printf("variable=%-30s value=%10s\n", row[0], row[1]);
  }
  mysql_free_result(result);

  if (mysql_set_character_set(&mysql, "utf8")) {
    printf("Error while changing charset");
    exit(1);
  }
  printf("\nCharset changed to utf8\n\n");
  printf("Character set is : %s\n",
       mysql_character_set_name(&mysql));

  if (mysql_query(&mysql, "show variables like 'char%'")) {
    printf("Error while querying [2]");
    exit(1);
  }
  if (!(result = mysql_store_result(&mysql))) {
    printf("Error while storing result [2]");
    exit(1);  
  }
  while (row = mysql_fetch_row(result)) {
    printf("variable=%-30s value=%10s\n", row[0], row[1]);
  }
  mysql_free_result(result);

  if (mysql_change_user(&mysql, MUSER, MPASS, MDB)) {
    printf("Error while changing user");
    exit(1);
  }
  printf("\nChanged user\n\n");

  printf("Character set is : %s\n",
       mysql_character_set_name(&mysql));

  if (mysql_query(&mysql, "show variables like 'char%'")) {
    printf("Error while querying [3]");
    exit(1);
  }
  if (!(result = mysql_store_result(&mysql))) {
    printf("Error while storing result [3]");
    exit(1);  
  }
  while (row = mysql_fetch_row(result)) {
    printf("variable=%-30s value=%10s\n", row[0], row[1]);
  }
  mysql_free_result(result);

  if (mysql_change_user(&mysql, MUSER, MPASS, MDB)) {
    printf("Error while changing user");
    exit(1);
  }
  mysql_close(&mysql);
}
[17 Aug 2007 14:05] MySQL Verification Team
Thank you for the bug report.

[miguel@skybr dbs]$ ./bug30472
Character set is : latin1
variable=character_set_client           value=    latin1
variable=character_set_connection       value=    latin1
variable=character_set_database         value=    latin1
variable=character_set_results          value=    latin1
variable=character_set_server           value=    latin1
variable=character_set_system           value=      utf8
variable=character_sets_dir             value=/home/miguel/dbs/4.1/share/mysql/charsets/

Charset changed to utf8

Character set is : utf8
variable=character_set_client           value=      utf8
variable=character_set_connection       value=      utf8
variable=character_set_database         value=    latin1
variable=character_set_results          value=      utf8
variable=character_set_server           value=    latin1
variable=character_set_system           value=      utf8
variable=character_sets_dir             value=/home/miguel/dbs/4.1/share/mysql/charsets/

Changed user

Character set is : utf8
variable=character_set_client           value=    latin1
variable=character_set_connection       value=    latin1
variable=character_set_database         value=    latin1
variable=character_set_results          value=    latin1
variable=character_set_server           value=    latin1
variable=character_set_system           value=      utf8
variable=character_sets_dir             value=/home/miguel/dbs/4.1/share/mysql/charsets/
=======================================================================================
[miguel@skybr dbs]$ ./bug30472
Character set is : latin1
variable=character_set_client           value=    latin1
variable=character_set_connection       value=    latin1
variable=character_set_database         value=    latin1
variable=character_set_filesystem       value=    binary
variable=character_set_results          value=    latin1
variable=character_set_server           value=    latin1
variable=character_set_system           value=      utf8
variable=character_sets_dir             value=/home/miguel/dbs/5.0/share/mysql/charsets/

Charset changed to utf8

Character set is : utf8
variable=character_set_client           value=      utf8
variable=character_set_connection       value=      utf8
variable=character_set_database         value=    latin1
variable=character_set_filesystem       value=    binary
variable=character_set_results          value=      utf8
variable=character_set_server           value=    latin1
variable=character_set_system           value=      utf8
variable=character_sets_dir             value=/home/miguel/dbs/5.0/share/mysql/charsets/

Changed user

Character set is : utf8
variable=character_set_client           value=    latin1
variable=character_set_connection       value=    latin1
variable=character_set_database         value=    latin1
variable=character_set_filesystem       value=    binary
variable=character_set_results          value=    latin1
variable=character_set_server           value=    latin1
variable=character_set_system           value=      utf8
variable=character_sets_dir
[20 Aug 2007 9:22] Andrey Hristov
mysql->last_insert_id is also not cleared.
[28 Sep 2007 11:40] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/34635

ChangeSet@1.2612, 2007-09-28 15:42:37+04:00, anozdrin@station. +4 -0
  Prerequisite patch for BUG#30472: libmysql doesn't reset charset,
  insert_id after succ. mysql_change_user() call.
  
  Supply a correct packet length to dispatch command.
[28 Sep 2007 19:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/34658

ChangeSet@1.2613, 2007-09-28 23:30:54+04:00, anozdrin@station. +5 -0
  Patch for BUG#30472: libmysql doesn't reset charset,
  insert_id after succ. mysql_change_user() call.
  
  See also WL 4066.
    
  This bug reveals two problems:
    - the problem on the client side which was described originally;
    - the problem in protocol / the server side: connection context
      on client and server should be like after mysql_real_connect()
      and be consistent. The server however just resets character
      set variables to the global defaults.
  
  The fix seems to be as follows:
    - extend the protocol so that the client be able to send
      character set information in COM_CHANGE_USER command;
    - change the server so that it understands client character set
      in the command;
    - change the client:
      - reset character set to the default value (which has been
        read from the configuration);
      - send character set in COM_CHANGE_USER command.
[30 Sep 2007 20:19] Alexander Nozdrin
Pushed into 5.1-runtime.
[1 Oct 2007 11:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/34707

ChangeSet@1.2618, 2007-10-01 15:41:18+04:00, anozdrin@station. +2 -0
  BUG#30472: libmysql doesn't reset charset, insert_id after
  succ. mysql_change_user() call.
  
  Use 2 bytes for character set number.
[19 Oct 2007 18:52] Bugs System
Pushed into 5.1.23-beta
[25 Oct 2007 0:06] Paul DuBois
Noted in 5.1.23 changelog.

The mysql_change_user() C API function did not correctly reset the
character set variables to the values they had just after initially
connecting.