Bug #10951 collation and charqacter set

• Submitted: 30 May 2005 5:23
• Modified: 30 May 2005 6:30
• Reporter: Pedro Han
• Status: Not a Bug
• Category: MySQL Server: Installing
• Severity: S3 (Non-critical)
• Version: 4.1.12
• OS: Windows (xp sp2)
• CPU Architecture: Any

[30 May 2005 5:23] Pedro Han

Description:
 mysql> show variables like "%character%";show 
 variables like "%collation%"; 
 
 +--------------------------+---------------------- 
 --------+ 
 | Variable_name | Value 
 | 
 +--------------------------+---------------------- 
 --------+ 
 | character_set_client | latin1 
 | 
 | character_set_connection | latin1 
 | 
 | character_set_database | utf8 
 | 
 | character_set_results | latin1 
 | 
 | character_set_server | utf8 
 | 
 | character_set_system | utf8 
 | 
 | character_sets_dir | 
 \xampp\mysql\share\charsets/ | 
 +--------------------------+---------------------- 
 --------+ 
 7 rows in set (0.00 sec) 
 
 +----------------------+-------------------+ 
 | Variable_name | Value | 
 +----------------------+-------------------+ 
 | collation_connection | latin1_swedish_ci | 
 | collation_database | utf8_general_ci | 
 | collation_server | utf8_general_ci | 
 +----------------------+-------------------+ 
 3 rows in set (0.00 sec) 

 at phpmyadmin, 

session global 
character set client utf8 utf8 
character set connection utf8 utf8 
character set database utf8 utf8 
character set results utf8 utf8 
character set server utf8 utf8 
character set system utf8 utf8 
collation connection utf8_general_ci utf8_general_ci 
collation database utf8_general_ci utf8_general_ci 
collation server utf8_general_ci utf8_general_ci 

How to repeat:
on xp sp2 apache 2.0.54, php 4.3.11, mysql 4.1.12, phpmyadmin 2.6.2pl1 
 I've wrote my.cnf 
 
 
 # password = your_password 
 port = 3306 
 socket = mysql 
 character_set_connection=utf8; 
 character_set_results=utf8; 
 character_set_client=utf8; 
 default-character-set=utf8 
 character-set-server=utf8 
 collation-server=utf8_general_ci 
 
 port = 3306 
 socket = mysql 
 skip-locking 
 default-character-set=utf8 
 character-set-server = utf8 
 collation-server = utf8_general_ci 
 
 default-character-set=utf8 
 
 default-character-set=utf8 
 
 And, db is only 
 mysql,cdcol,webauth with utf8 in db.opt 
 
 but, after 
 
 mysql> show variables like "%character%";show 
 variables like "%collation%"; 
 
 +--------------------------+---------------------- 
 --------+ 
 | Variable_name | Value 
 | 
 +--------------------------+---------------------- 
 --------+ 
 | character_set_client | latin1 
 | 
 | character_set_connection | latin1 
 | 
 | character_set_database | utf8 
 | 
 | character_set_results | latin1 
 | 
 | character_set_server | utf8 
 | 
 | character_set_system | utf8 
 | 
 | character_sets_dir | 
 \xampp\mysql\share\charsets/ | 
 +--------------------------+---------------------- 
 --------+ 
 7 rows in set (0.00 sec) 
 
 +----------------------+-------------------+ 
 | Variable_name | Value | 
 +----------------------+-------------------+ 
 | collation_connection | latin1_swedish_ci | 
 | collation_database | utf8_general_ci | 
 | collation_server | utf8_general_ci | 
 +----------------------+-------------------+ 
 3 rows in set (0.00 sec) 

 at phpmyadmin, 

session global 
character set client utf8 utf8 
character set connection utf8 utf8 
character set database utf8 utf8 
character set results utf8 utf8 
character set server utf8 utf8 
character set system utf8 utf8 
collation connection utf8_general_ci utf8_general_ci 
collation database utf8_general_ci utf8_general_ci 
collation server utf8_general_ci utf8_general_ci 
 
It's different from mysql command line.....

[30 May 2005 6:30] Jorge del Conde

Hi,

What you described is not a bug.

You can see the following from our manual:

http://dev.mysql.com/doc/mysql/en/set-option.html

"SET NAMES sets the three session system variables character_set_client, character_set_connection, and character_set_results to the given character set. Setting character_set_connection to charset_name also sets collation_connection to the default collation for charset_name. 

The default mapping can be restored by using a value of DEFAULT. 

Note that the syntax for SET NAMES differs from that for setting most other options. This statement is available as of MySQL 4.1.0. "

The reason why you see those variables set to UTF8 when seen from PHP, is because phpadmin probably executes "SET NAMES UTF8" at the start of each session.