MySQL Bugs: #5864: character_set_client does not seems to work

Bug #5864	character_set_client does not seems to work
Submitted:	2 Oct 2004 23:55	Modified:	7 Oct 2004 10:46
Reporter:	Levap Aretnyd	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.1-alpha-nt	OS:	Windows (Windows 2003 web)
Assigned to:		CPU Architecture:	Any

Description:
Statement SET character_set_client = x (in my case utf8) from the client does not seems to change the way the server encodes data of incoming queries. Sending SET CHARACTER SET x, or only additional SET collation_connection = @@collation_database seems to solve the problem. According to manual, setting character_set_client should be enough.
I am using MySQL Connector/Net Beta 1.0.0 at the client side but it not seems that problem is there.

How to repeat:
Insert some special unicode characters (for example "&#283;š&#269;&#345;žýáíéú&#367;") with utf8 client data encoding with previous setting SET character_set_client = utf8 .

Hi,

Thank you for the report, but this is not a bug. SET character_set_client=utf8 only sets character_set_client variable, but doesn't set character_set_connection variables.
Use SET NAMES utf8 instead or if character set of the current database is utf8 you can also use SET CHARACTER SET utf8.

What exactly part of the manual did you mention?

SET NAMES is propably much more reasonable query to execute because it sets both character_set_client and character_set_connection, but i see a problem in this:

Manual under "1.3.6 Connection Character Sets and Collations" says that SET CHARACTER SET x is equal to executing
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET collation_connection = @@collation_database;

When I execute SET CHARACTER SET x then strings are inserted to db in correct coding - that means that one or more variables set by SET CHARACTER SET must have done that. But character_set_results variable is for coding result sets from server to client (and I am not checking correctness of inserts with this client), and collation_connection variable is for comparisons of literal strings.
When I send those SETs separately, it seems that the combination 'SET character_set_client = x' and then 'SET collation_connection = @@collation_database' does the thing. But I thought collation has nothing to do with coding of inserted values.
When I execute only 'SET character_set_client = x' then the coding is wrong - that is probably because character_set_connection is configured to some other coding. That is where I see something strange, why 'SET collation_connection = @@collation_database' has turned the correct coding on? And why SET CHARACTER SET is setting correct coding too even when that I thing only for this case important variable it sets is character_set_client, which when set alone does not turns the correct coding on?

character_set_connection is responsible for data encoding. If character_set_client and  character_set_connection have different values all data is converted to the character_set_connection.
If you set collation_connection variable, MySQL implicitly changes value of character_set_connection variable and vice versa.

All right, that makes sense. Thanks.